May 4, 2022 at 10:43 pm
I was wondering if anyone has any sql code samples where they have had to flatten a hierarchy of nodes without the use of a CTE.
For example, consider an organizational hierarchy tree with the CEO is at the top and all levels of management down to the lowest levels.
So, if the structure of the data was like this:
employeeid, bossid, employeename, employeetitle where the CEO would have bossid = null, but every other bossid would connect employee to the his/her boss,
I would want the output to be:
John Doe, CEO, ~ Jane Smith, VP Engineering ~ Jacob Crowley, Engineering Manager ~ Jen Stein, Engineer
John Doe, CEO, ~ Don Young, VP Sales ~ Susan White, Sales Manager ~ Joyce Abram, Sales
etc.
Any ideas?
Dan
May 4, 2022 at 10:45 pm
This was removed by the editor as SPAM
May 5, 2022 at 3:27 pm
A recursive CTE is likely to be your best option. Why are you excluding CTEs?
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 5, 2022 at 9:17 pm
Yeah....I agree. I didn't think we could get the resource issue fixed for running the CTE solution. It's in Starburst, not SQL Server. But, thank you. This would be a real tough one today without CTEs.
May 5, 2022 at 9:24 pm
Thom A:
Yeah...see my other comment. We are definitely trying to fix our resource problem in Starburst and use CTEs; however, this problem might be fun to try and solve.
For the sample DDL I gave you above, the only other field I would add would be:
branch: varchar(2000)
It would contain something like this to show a branch of levels in the tree.
John Doe, CEO ~ Jane Smith, VP Engineering ~ Jacob Crowley, Engineering Manager ~ Jen Stein, Engineer
Thank you,
Dan
May 6, 2022 at 8:05 am
For the sample DDL I gave you above, the only other field I would add would be:
branch: varchar(2000)
What DDL? What about the DML? You havem't given any. DDL is things like CREATE and ALTER statements, and DML statements are INSERT, UPDATE, etc statements. Provide DDL (CREATE) and DML (INSERT) statements for your data.
Are you saying that you are storing delimited data in your database? If so that's your real problem; you need to fix that and normalise your design.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 6, 2022 at 8:07 am
Yeah....I agree. I didn't think we could get the resource issue fixed for running the CTE solution. It's in Starburst, not SQL Server. But, thank you. This would be a real tough one today without CTEs.
This is a SQL Server community, not Starburst, and you've posted in the SQL Server 2019 development forum; if you aren't using SQL Server you're in the wrong place I'm afraid. You'll want to use a community that is specific to Starburst, or isn't about a specific RDBMS (SQL Server in this case).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 6, 2022 at 8:06 pm
Why did you post any DDL? I know this is not SQL Server, but you could've at least tried to help us a bit. If you have models or hierarchies using the nested sets model (if you don't know what that is. Google it), then this is trivial. If it is not you will simply imitate a linked list of a lot of recursive procedural code tracing the tree down. You start thinking, and sets. However, a lot of things like this just go away. I haven't got time to post the hundreds of words needed to explain this to you in any detail. You can get my book on treason hierarchies. If you want a lot more detail than you ever cared to see. 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
May 6, 2022 at 8:42 pm
Joe Celko's Trees and Hierarchies in SQL for Smarties -- Treason hierarchies is a whole different topic 🙂
Auto-correct did a number on your post, Joe.
May 6, 2022 at 11:06 pm
To be honest, CTEs can suck real bad. They're not so bad at Hierarchies but, still, a well written While loop will be a little bit faster than a CTE and it will use about 9 time fewer logical reads.
The really big question though is... how often does your hierarchy actually change? If it not changing once a second or anything crazy like that, then it would be to your huge advantage to store a hierarchy in a Nested Set form. Key your original Adjacency list form because hierarchies are a lot easier to maintain and repair there but, for "downline" reporting, Nested Sets are the berries.
It used to be that people would convert their Adjacency Lists to Nested Sets and then throw away the Adjacency Lists because it used to take a month of Sundays to do the conversion. For example, it used to take about 2.5 DAYS to do such a conversion of just a million node hierarchy.
There's no need for that anymore. Here's the link for how to do it that was written about a decade ago. Back then, it got that 2.5 Days time down to 54 seconds on an old 32 bit box. On today's machines, it only takes about 19 seconds.
Here's the link to that article. If you know me, you just had to know that a special-purpose Tally Table would be involved. It's uses a recursive CTE in a slight different manner to do the conversion and it's fast enough to do the rebuild (for example) once every hour or half hour. Using a "swap'n'drop" method, you could have virtually zero down time on top of it only taking 19 seconds for a million node hierarchy. And pay attention to the "SortPath" column... it contains the IDs for the entire "upline" for every node. Super simple to split (fixed width 4 byte elements) and use and nasty fast for "upline" queries".
If you have "downline Aggregations" that are needed, then the second article in that short series may be the answer. See the following article for that.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2022 at 1:23 am
Jeff and others that referred me to Nested Sets...thank you! I did find an article on Nested Sets as well. Jeff, I appreciate the links and explanation as well.
Regards,
Dan
May 11, 2022 at 5:13 am
Thanks for the feedback, Dan.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2022 at 11:30 pm
an aside: Azure Synapse cannot do recursive CTEs (or heirarchyid...), if you happen to be working in that realm.
I settled for a few layers of nested CTEs down to the expected "depth", plus a little more, for something that would've been less painful to do with recursive CTEs. ( "find earliest qualifying event, get records for the ensuing 12 month span from that 1st date. then, find next qualifying event date after that span, and... lather-rinse-repeat. the event dates are ascending but definitely not continuous. and, lucky for me, was just working with a 5-year moving window of data, so at most 5 layers (plus a couple extras...) of those CTEs were needed.
I did not see a way to use LAG/LEAD for this, either.
May 14, 2022 at 3:55 am
an aside: Azure Synapse cannot do recursive CTEs (or heirarchyid...), if you happen to be working in that realm.
I settled for a few layers of nested CTEs down to the expected "depth", plus a little more, for something that would've been less painful to do with recursive CTEs. ( "find earliest qualifying event, get records for the ensuing 12 month span from that 1st date. then, find next qualifying event date after that span, and... lather-rinse-repeat. the event dates are ascending but definitely not continuous. and, lucky for me, was just working with a 5-year moving window of data, so at most 5 layers (plus a couple extras...) of those CTEs were needed.
I did not see a way to use LAG/LEAD for this, either.
A Recursive CTE is nothing but a different method of doing a While loop. You can use a While loop to replace a recursive CTE and, even if you do the While loop in a typical manner, it will be almost as fast as the Recursive CTE (rCTE from here on) and use almost 9 times fewer memory reads. If you do an "optimized" While loop (Explicit Transaction around the WHILE, it will also beat the rCTE for both CPU and Duration not to mention the same amount of fewer memory reads (almost 9 times fewer) than the rCTE.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply