SQL Tutorial: Introduction to recursion скачать в хорошем качестве

SQL Tutorial: Introduction to recursion

Want to learn more? Take the full course at https://learn.datacamp.com/courses/hi... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- In this second video, you will learn what recursion means and how you could use it for many problems. The first question we want to answer is what recursion is in general. As you can see in the picture, it is something that is always repeated. Accordingly: Recursion is the use of a procedure, subroutine, function or algorithm that calls itself one or more times until a specific condition is met. Therefore, a recursion could simplify a complex problem by sharing the problem in smaller problems performed repeatedly. One really important fact about recursion is that you need a termination condition. If you have no termination condition, the call of the small problems repeats all the time. As you can see in the shown picture. An real world example everyone knows is the family tree. Everyone has a father and your father has a father. For example, to find all fathers for the last 5 generations, you can use recursion. You have to reduce the problem to smaller problems of the same type. The whole problem is to find all generations and the small problem is to find your father. In the first step, find your father and in the second step, find the father of the father until the termination condition of 5 is reached. Knowing how recursion works in general, we are now summarizing facts about recursion. There are several advantages: you can solve problems recursively, if the problem can be reduced to the smaller problems of the same type. A recursively solved problem is easy to read and to follow and a termination condition could be used to limited the number of iterations. Thinking of disadvantages, the execution time could slower because the recursive function is called many times. Knowing what is recursion, let's try out a simple recursion problem. The problem we want solve is to calculate the sum of consecutive numbers. It can be defined as shown on the slide. It is 1 for the first iteration and for each following iteration the number of the current iteration and the sum of the previous iteration is added. As an example, the result of 5 is 15. It is the sum of 1+2+3+4+5. Now, we are going to solve this problem with SQL. In general, we could solve recursive problems with CTEs (common table expressions). Therefore, you could see the initial query, which initializes the field for "iteration" and the "sumOfNumber" with 1. In the recursive part, the current iteration number is added with the sum of the previous iteration. You can see that the termination condition is set to 6. This means this query calculates the sum of numbers until 6. Finally, the CTE is selected to get the wanted result. You heard a lot about recursion so far. Now it is time to practice! #DataCamp #SQLTutorial #Hierarchical #Recursive #Queries #SQLServer