Friday, December 11, 2009

Fun with Recursive SQL (Part 1)


This blog post is derived from an article that I wrote as an article for Teradata Magazine about fun uses for recursive SQL.

Show of hands: how many of you have used recursive SQL in the past six months? For something useful?  Using ANSI syntax rather than Oracle's CONNECT BY?  If you said "yes" to all three, then congratulations!  You've won a prize: 15 minutes of your day back.  You can jump down to the bottom of this post and just review my fun example.  This first part is just an introduction to recursive SQL.

Introduction to Recursive SQL
First let's just review the definition for "recursion."  Handy-dandy dictionary here.... recourse... rectify.... recursion:
See: recursion
Not very helpful.  How about this instead.  A recursion is a method of solving problems in which the algorithm or function applies itself in the execution of the solution.  For instance, if I were to define a recursive algorithm for summarizing a list of numbers, I could say something like "the sum of a list is determined by adding the first number in the list to the sum of all the remaining numbers in the list; if the list is empty, then the sum is zero."  You notice how the definition of "sum of a list" includes the use of the "sum" operation, as in "sum of all the remaining numbers."  You'll also note that there's an explicit terminal condition to define what happens when you have nothing in the list.

So, recursive SQL works the same way.  A recursive SQL statement is one in which the determination of the result set requires the execution of SQL on the result set itself.  In recursive SQL, there has to be a seed statement, as well as the recursive call, and a termination condition.

WITH RECURSIVE [temp table] [column list] AS
(
  [seed statement]
  UNION ALL
  [recursive statement]
)

A Simple Example
For the sake of comfort, a simple example might involve an employee hierarchy table where each row had the employee ID and associated manager ID.  A standard query could retrieve a list of direct reports for a given employee ID:  SELECT * FROM employee WHERE mgr_id = 123.  A recursive query, however, could return the list of all employees anywhere under the hierarchy below that same manager.  An example that might be:
WITH RECURSIVE emp_hier (emp_id, mgr_id, level) AS
(
SELECT a.emp_id, a.mgr_id, 0 
FROM   employee a
WHERE  a.emp_id = 123
UNION ALL
SELECT b.emp_id, b.mgr_id, c.level+1 
FROM   employee b,
       emp_hier c
WHERE  b.mgr_id = c.emp_id
)
SELECT e.emp_title, e.emp_id, e.mgr_id, h.level
FROM   employee e,
       emp_hier h
WHERE  e.emp_id = h.emp_id
  AND  e.emp_id <> 123;

In this query, the is a simple select that pulls the manager record for which we want all of the descendants.  The selects all records from the employee table where the employee is managed by someone already in the emp_hier temporary table – hence the join on employee.mgr_id and emp_hier.emp_id.

On to some more exciting examples...


Example 1: String Concatenation
Ever notice that there are no string aggregation functions in SQL?  Something like a SUM() for character fields that would take two parameters: the field or expression from each row and a delimiter.  Usually, programmers end up having to write code outside of the database to do that kind of string manipulation. Recursive SQL can achieve that kind of character aggregation quite nicely if used appropriately.

Consider a list of email addresses for several members of the same family.  The table has a FAMILY_ID, PERSON_ID, SEQuence number, and EMAIL_ADDR.  The desired output is a single row that contains a comma-separated list of all the email addresses for the entire family (for a mailing list perhaps).

WITH RECURSIVE email_list (coverage_id, emails, seq) AS
(
  SELECT m.family_id, m.email_addr, m.seq
  FROM   members m
  WHERE  m.seq = 1
  UNION ALL
  SELECT e.emails ||’, ’|| m.email_addr, m.seq
  FROM   email_list e, members m
  WHERE  m.seq = e.seq + 1
    AND  m.family_id = e.family_id
)
SELECT r.family_id, r.emails
FROM   email_list r
WHERE  r.seq = (SELECT MAX(h.seq) FROM email_list h 
                WHERE h.family_id = r.family_id 
                GROUP BY h.family_id)
;
Let's walk through the SQL piece by piece.  First, the seed statement starts the result set off by picking just the first person from each family (that is, the people with a SEQ=1).  That sequence number is admittedly a bit artifical and contrived to make the recursive SQL easier to write.  If you needed to, you could generate the SEQ on the fly using a RANK() of people within each family and then leverage the RANK() as a sequence number.  Hopefully the example isn't lost because of that bit of confusion.

The recursive portion of the SQL simply takes what was seeded in through the first query (i.e. people with SEQ=1) and joins that to all the records in the EMAIL_ADDR table with a SEQ = SEQ + 1 (from result set).  The EMAILS output field is created by concatenating the email address from the seed to record with the next highest sequence number.

Then that result set is taken and the same thing is done.  And so on.  And so forth.

The query terminates when the result set contains no records.  That is, when we exhaust all the SEQ numbers in the table.

The outer query refines the results down to exactly what we're looking for by keeping the final record and throwing away all the intermediate records that were used to built up the results.

Next Time
Look ahead for additional posts on how to use recursive SQLto parse a field with comma separated values and how to split and reconcile overlapping time segments into a single, flat time line.

50 comments:

  1. This has been a really valuable post! Do you have any examples of the string concatenation where I don't have a convenient sequential reference (i.e. where you suggest that Rank() be used)?

    Each time I've tried to create this using a window function, I get an "Illegal or unsupported use of subquery/derived table inside a recursive query/view" error.

    ReplyDelete
  2. Nice & Easy Keep Doing Good Job

    ReplyDelete
  3. almost same, but with test data

    http://thesimpleprogrammer.blogspot.com/2013/02/concat-rows-as-single-column-in-teradata.html

    ReplyDelete
  4. Hi Paul, thanks for your article. Recursive SQL can be tricky to understand, so I appreciate you taking the time to explain it step-by-step to us.

    ReplyDelete
  5. Hi Paul,
    Your explanation is great which made complicated logic to simple. thanks. Keep doing good job.

    ReplyDelete
  6. Thank you very much for this article, it is so rare to see nowadays written as fervently article. I enjoyed reading it and I learned a lot of things. I will go and continue reading your blog =). Good luck for the future and another one for the quality of it.You can also check out this (http://www.sqiar.com).

    ReplyDelete
  7. Thank you for taking the time to put this together. It's very helpful in understanding SQL recursion.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hey, two questions:
    1. in your email concatenation example: for the recursive select portion, do you think it lacks one column (right now only two columns)?
    2. As you know, the email column in the result would be a very long one? How can you make sure it won't be truncated given the very first record of "emails" is only for one email...I ran something similar on Teradata 15.00.0305...my results from recursive selections were all truncated by the seed selection.

    ReplyDelete

  10. Thanks for sharing this information and keep updating us. This is informatics and really useful to me.
    Selenium Training in Chennai | Selenium Training | Selenium Course in Chennai

    ReplyDelete
  11. Unfortunately the question of Nicholas concerning the rank() was not answered.
    I have exactly the same problem: Creating a tree with specific order by can only be done with a rank(), resulting in eg an order by field: 1, 1.1, 1.2, 1.3, 1.3.1, 1.3.2, ..., 2, 2.1, etc.

    ReplyDelete
  12. It's A Great Pleasure reading your Article, learned a lot of new things, we have to keep on updating it salesforce certification training Thanks for posting.

    ReplyDelete
  13. very nice good we share it useful benefits posted.
    Appium Training From India

    ReplyDelete
  14. Greetings! Very helpful advice within this article! It’s the little changes that produce the biggest changes. Thanks for sharing!
    Golden gate Classes

    ReplyDelete
  15. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic.
    oneplus service center chennai
    oneplus service center in chennai

    ReplyDelete
  16. You are doing a great job. I would like to appreciate your work for good accuracy
    Regards,
    best selenium training institute in chennai

    ReplyDelete
  17. Thank you for taking time to provide us some of the useful and exclusive information with us.
    Regards,
    selenium course in chennai

    ReplyDelete
  18. . To have more enhanced results and optimized benefits, you are able to take the help of experts making a call at QuickBooks Payroll Support Number Well! If you’re not in a position to customize employee payroll in

    ReplyDelete
  19. Dial our number to obtain in contact with our technical specialists available twenty-four hours each and every day at QuickBooks Customer Support Number.

    ReplyDelete
  20. QuickBooks Payroll is a credit card applicatoin which include made payroll a straightforward snap-of-fingers task. It is possible to very easily and automatically calculate the tax for your employees. It really is an absolute software that fits your organization completely. We provide Quickbooks Payroll tech support team with regards to customers who find QuickBooks Payroll difficult to use. As Quickbooks Support we utilize the responsibility of resolving all the problems that hinder the performance associated with the exuberant software.

    ReplyDelete
  21. QuickBooks Payroll Support Phone Number software may be the better option. You can certainly do WCA, PAYE, SDL along with UIF calculations. This generates annual IT3A as well as IRP5. Employees also result in the loan. How will you manage that? QuickBooks does that in ease. The application brings accumulative balance.

    ReplyDelete
  22. QuickBooks Enterprise Tech Support Gives Necessary Features In Real Time For The Enterprises. Because The Software Runs On Desktop And Laptop Devices, It Truly Is Prone To Get Errors And Technical Glitches.

    ReplyDelete
  23. QuickBook Customer Support Number genuinely believe that the show must go ahead and thus time is just not an issue for all of us because problems do not come with any pre-announcements.

    ReplyDelete
  24. We genuinely believe that the show must go ahead and thus time is just not an issue for all of QuickBooks Support Phone Number because problems do not come with any pre-announcements.

    ReplyDelete
  25. According to statics released by the Bing & Google search insights more than 50,000 people searching the web to find the Quickbooks Support Phone Number on a daily basis and more than 2,000 quarries related to Quickbooks issues and errors .

    ReplyDelete
  26. QuickBooks, a software solution that will be designed in such a manner that one may manage payroll, inventory, sales and every other need of your small business. Each QuickBooks software option would be developed based on different industries and their needs so that you can seamlessly manage all your valuable business finance at any time as well as in one go. You should not worry if you're stuck with QuickBooks issue in midnight as our technical specialists at QuickBooks Tech Support Phone Number can be acquired twenty-four hours a day to serve you with the best optimal solution right away.

    ReplyDelete
  27. QuickBooks Support Number have a team of experts which may be pro in handling most of the issues because of this incredible software. You'll need not to worry after all as you are seeking help under the guidance of supremely talented and skilled support engineers that leave no stone unturned to land you of all errors which are part and parcel of QuickBooks.

    ReplyDelete
  28. QuickBooks Tech Support Number was made to meet your every accounting needs and requirement with a fantastic ease. This software grows and your business and perfectly adapts with changing business environment.

    ReplyDelete
  29. It includes some luring features which will make this software most desirable. Regardless of most of the well-known QuickBooks Premier features you may find difficulty at some steps. Support For QuickBooks is the foremost destination to call in the period of such crisis.

    ReplyDelete

  30. QuickBooks is an accounting software program generated by Intuit. In QuickBooks Payroll Support 2019, assists you to all the billing, bookkeeping, and invoicing at one place. You are able to track sales and expenses, accept payments etc.

    ReplyDelete
  31. The QuickBooks Payroll Support Phone Number team at site name is held responsible for removing the errors that pop up in this desirable software. We care for not letting any issue can be purchased in in the middle of your work and trouble you in undergoing your tasks.

    ReplyDelete
  32. QuickBooks Premier is an accounting software suit, which caters into the needs of small and medium-sized business enterprises. QuickBooks Support Number comes with an original feature that allows multiple users to gain access to the same file at the same time. This type of QB now offers retrieval of data at any moment as well, because the database is stored on their server. QuickBooks Premier also facilitates evading loss of data and allows recovery of deleted items as well. With Premier, functions like tracking bills, status of created bills, purchase orders and recording other transactions becomes less complicated. QuickBooks Premier also offers high security and privacy aided by the absolute assurance that no theft or leakage of any information or data will require place. This really is why QuickBooks Support is arguably the best online accounting software suit for professionals.

    ReplyDelete
  33. We all know that for the annoying issues in QuickBooks Enterprise software, you'll need an intelligent companion who are able to enable you to get rid of the errors instantly. Due to this we at QuickBooks Enterprise Support Phone Number contact number gives you the essential reliable solution of the each and every QuickBooks Enterprise errors.

    ReplyDelete
  34. Every user will get 24/7 support services with this online technical experts using QuickBooks Enterprise Support Phone Number. When you’re stuck in times for which you can’t discover a way to get rid of a concern, all that's necessary would be to dial QuickBooks customer support contact number. Be patient; they will certainly inevitably and instantly solve your queries.

    ReplyDelete
  35. Quickbooks Payroll Support could be the toll-free quantity of where our skilled, experienced and responsible team are available 24*7 at your service. There are a selection of errors that pop up in QuickBooks Payroll Support Number which are taken care of by our highly knowledgeable and dedicated customer support executives.

    ReplyDelete
  36. We know that for the annoying issues in QuickBooks Enterprise software, you will require a sensible companion who can enable you to eradicate the errors instantly. Because of this we at QuickBooks Enterprise Support Phone number gives you the essential reliable solution of the every single QuickBooks Enterprise errors.

    ReplyDelete
  37. Make Contact With Us. Consult a professional through live chat. Our QuickBooks Tech Support Number executives are here round the clock to wait you. Whatever channel you determine to e mail us, you'll get an undivided awareness of your problem from our people. You will get a number of fixes here in just one ring. Why settle for less then? Give us a call, inform us your trouble and fix it.

    ReplyDelete
  38. Hope now you recognize that just how to interact with and QuickBooks Enterprise Help Number. We've been independent alternative party support company for intuit QuickBooks, we don't have just about any link with direct QuickBooks, the employment of name Images and logos on website simply for reference purposes only.

    ReplyDelete
  39. Every user will get 24/7 support services with this online technical experts using Quickbooks Support. When you’re stuck in a situation in which you can’t find a method to get rid of a problem, all you need is to dial QuickBooks customer support contact number. Have patience; they're going to inevitably and instantly solve your queries.

    ReplyDelete
  40. Pro, Premier, Enterprise, Point of Sale, Payroll along with Accountant, dependant on your need. QIntuit QuickBooks Support team is definitely ready to assist its customers via online support with every possible error which they come in terms with. There are times once the customers face problem in upgrading their software into the newer version, they generally face issue in generating reports etc. Though QuickBooks has made bookkeeping a child’s play, moreover it is sold with a couple of loopholes that cannot be ignored.

    ReplyDelete
  41. Now open the file and click on Update Company File for New Version. And now maybe you are all set. https://www.errorsupportnumber.com/ If this doesn’t help you, go ahead and connect to us at QuickBooks support phone number.

    ReplyDelete
  42. Mistaken for your QuickBooks software or stuck in between making invoice ?You can reach us at QuickBooks Enterprise Support Phone Number to reach us for instant help. You merely need to dial Our QuickBooks Support phone number to reach our experts.

    ReplyDelete
  43. The primary functionality of QuickBooks Support Phone Number + 1888-567-1159 depends upon company file. Based on the experts, if you'd like solve the situation, then you'll definitely definitely need certainly to accept it first. The error will likely not fix completely until you comprehend the root cause associated with problem.

    ReplyDelete
  44. The QuickBooks Payroll Support Phone Number software has tight security. Thus, all of your data are safe. You are able to put the password over there. This might protect the contents.

    ReplyDelete
  45. Basically it is founded that many of customers are facing several issues in their enterprise system, for solving their enterprise related issues QuickBooks Support Number Hosting services are made for solving their issues. Using this service of QuickBooks the customers

    ReplyDelete
  46. Problems are inevitable plus they usually do not come with a bang. Our team at QuickBooks Support Phone Number is ready beforehand to provide you customer-friendly assistance if you speak to an issue using QuickBooks Pro. All of us is skilled, talented, knowledgeable and spontaneous. Without taking most of your time, our team gets you rid of all unavoidable errors of this software.

    ReplyDelete
  47. The QuickBooks Payroll has many awesome features that are good enough when it comes to small and middle sized business. QuickBooks Payroll also offers a dedicated accounting package which include specialized features for accountants also. You can certainly all from the QuickBooks Toll Free Phone Number to find out more details. Let’s see many of the options that come with QuickBooks that features made the QuickBooks payroll service exremely popular.

    ReplyDelete
  48. A QuickBooks Payroll Support Phone Number is a type of subscription this is certainly done to activate the payroll features in your QuickBooks Desktop Software. Intuit Online Payroll exports transactions to QuickBooks Desktop along with Quickbooks Online as standalone software.

    ReplyDelete