Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Recursive Queries in SQL:1999 and SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, April 26, 2005 11:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 37,061, Visits: 31,624
Very, very nice.  I do a lot with Hierarchies and this will really help when 2005 comes out.  Your examples and text based diagrams are awsome.  Thank you very much for taking the time to do this.  We need to get someone to translate your books to English...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #177596
Posted Thursday, April 28, 2005 8:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 1:07 PM
Points: 222, Visits: 22

I apologize in advance if this set of questions takes the topic too far off track.  We can easily take the conversations to a different thread if appropriate.

Excellent article.  It is very clear that 2005 will greatly simplify traversing hierarchies and networks, at least from a syntax perspective.   

Most of my work uses multiple orthoganal hierarchies and networks as a way to aggregate and analyze large volumes of data.  For example, consider an organization hierarchy that is related to but not dependent on a geographical network.  Using 2005, what happens to performance as we add this additional real world complexity?

In data warehouse terminology, the organization tree is a slowly changing dimension.  The geographic tree and the time tree are static.  Data is aggregated and analyzed along the organization tree, along the geographic tree, and along the "time" tree.

How might one change the examples to walk two or more trees at the same time? For example, suppose one wanted to stop in towns along the way and buy a bottle of wine.  One would need to store the cost of a bottle of wine in each town, for each day, over the last few months.  The "best" route then would consider not only the physical distance, but also the average cost of a bottle of wine over the last two weeks.  This example requires us to walk the geography tree and the time tree at the same time in order to calculate the "best" route.

In my real work, there are actually nine dimensions of interest.  Three of the dimensions are slowly changing dimensions, two are static dimensions, and four are flat dimensions.  The dimensions are a mix of hierarchies and networks.  Heterogeneous devices submit data on time intervals varying from sub-second to daily.  My current approach is to use recursive algorithms to populate what Kimball refers to as bridge tables, and then associate the aggregate values with nodes in the bridge.  Once collected, the data is stored in the warehouse and available for ad hoc queries for two years.  Yes, there are multiple terabytes of data online for ad hoc query and analysis.

Given this problem space, how might one modify the examples to efficiently aggregate the data "on demand" rather than storing redundant data for each node in the hierarchies and networks?

Thanks in advance for any thoughts

Wayne

 




Post #178309
Posted Friday, April 29, 2005 4:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 5, 2013 8:27 AM
Points: 15, Visits: 118
I am no shure to have well understand your problem. (My english is very far away in my mind...) But I think that valuated the nodes is perhaps not the good solution.
You can valuate the branches...

This can be done by adding a weight on the branches of the tree.
A weight can be constituted as a vector, wich allow as many dimension as you want, but produce a final result in term of "cost".

Let me give you an example wich is always about roads.
In france we have very small road call "chemin vicinal" with are close to lane. After we have "route departementale" wich is a step over managing by the department (equivalent of state in US), after we get "route nationale" wich are deliver and manage by the country and over we have "autoroute" wich are speedway.
The speed is limit as :
Type of road Km/h Average reduce factor
Vicinal 50 0,8
Departementale 90 0,9
Nationale 90 0,9
Nationale 4 ways 110 0,95
Autoroute 130 0,98

Then we can have some factors that are about where the road is : mountain, or flat land
Flat : 1
Small mountains : 0,85
Heavy mountains : 0,75

Then we can have some factors that are about the quality of surface of the road :
Flat : 1
Small holes : 0,9
Bad surface : 0,75

All thoses factors can be specify for all the branches of the tree. Then we can calculate the real travel time, and deduce that a Mac Truck model 125477 will cost 5444$ to go from Paris to Toulouse with 23t of duties

Is that transposable to your model ?

Thanks







Post #178646
Posted Thursday, September 14, 2006 11:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 24, 2014 12:26 AM
Points: 127, Visits: 44

How did I ever miss this one!
Brilliant. Thanks ever so much.




__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Post #308794
Posted Friday, October 20, 2006 12:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 2:08 PM
Points: 60, Visits: 17

Merci Frédéric. Et la tristesse, je n'ai que SQL 2000.

And since your English is much better than my French, I'll just add that I concur with others here. Your article is one of the best I've seen on the subject. Thanks!




Post #317076
Posted Sunday, October 22, 2006 7:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:20 PM
Points: 1,945, Visits: 2,999
I wish that you could get it translated into English. It is the best intro SQL book I know, included that horrible INSTANT SQL I wrote years ago.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #317177
Posted Friday, December 1, 2006 6:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 29, 2007 7:19 AM
Points: 1, Visits: 1
What can i say.. I think is the best article i have read about using recursive queries in resolving problems. It was exactly what i was looking for. Thanks again for this great article!
Post #327183
Posted Friday, December 1, 2006 7:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:20 PM
Points: 1,945, Visits: 2,999
Unfortunately no. His intro SQL book is one of the best, but it is in French.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #327195
Posted Tuesday, February 13, 2007 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 13, 2007 11:37 AM
Points: 1, Visits: 1

This is a great article, but I am running into issues with trying to apply this to my own querey.

 

if anyone is following this string, could you look at the following and tell me if anything jumps out at you for where i am going wrong?

 

With tree (id, name) AS

(Select id, name

from clients

where parent_id=1

Union all (

Select id, name

from clients

INNER JOIN tree t

ON t.parent_id = t.id)

Select * from tree)

 

many thanks from one who is just working on this part of the database.

 

 

Post #344607
Posted Monday, September 17, 2007 5:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:52 PM
Points: 20, Visits: 28

Frédéric BROUARD,

 

Your article is awesome. It provides solutions to all my boggling SQL dilemmas.

 

 

Thanks for sharing your wisdom.

 

 

Jimi J

Post #399844
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse