 |
 |
|
|
|
|
|
|
|
|
|
Untitled-1
 |
 |
|
 |
|
|
|
|
| Tuesday, December 19, 2006 |
|
Adam Machanic - Medians, ROW_NUMBERs, and performance
By drsql@hotmail.com @ 10:48 PM :: 746 Views ::
0 Comments :: Summit Speaker 2005
|
A couple of days ago, Aaron Bertrand posted about a method for calculating medians in SQL Server 2005 using the ROW_NUMBER function in conjunction with the COUNT aggregate. This method (credited to Itzik Ben-Gan) is interesting, but I discovered an even better way to attack the problem in Joe Celko's Analytics and OLAP in SQL.
Rather than using a COUNT aggregate in conjunction with the ROW_NUMBER function, Celko's method uses ROW_NUMBER twice: Once with an ascending sort, and again with a descending sort. The output rows can then be matched based on the ascending row number being within +/- 1 of the descending row number. This becomes clearer with a couple of small examples:
|
A |
1 |
5 |
|
B |
2 |
4 |
|
C |
3 |
3 |
|
D |
4 |
2 |
|
E |
5 |
1 |
In the first table (even number of rows), the median rows are B and C. These can be matched based on [Ascending Column] IN ([Descending Column] + 1, [Descending Column] - 1). In the second table (odd number of rows), the median row is C, which is matched where [Ascending Column] = [Descending Column]. Note that in the second table, the match criteria for the first table does not apply -- so the generic expression to match either case is the combination of the two: [Ascending Column] IN ([Descending Column], [Descending Column] + 1, [Descending Column] - 1).
We can apply this logic within the AdventureWorks database to find the median of the "TotalDue" amount in the Sales.SalesOrderHeader table, for each customer:
|
|
|
|
|
| Wednesday, March 01, 2006 |
|
|
|
| Saturday, October 01, 2005 |
|
|
|
| Monday, September 19, 2005 |
|
Bill Graziano - Integrating .NET and SQL (LINQ)
By drsql@hotmail.com @ 4:17 PM :: 848 Views ::
0 Comments :: PASS Member, PASS Summit Attendee 2005, Volunteer, SQL Server 2005, Summit Speaker 2005
|
From the Visual Studio Data Team blog ... At the PDC Microsoft announced LINQ (Language Independant Query) Framework. This gives you the ability to use a SQL-like language on your collections (anything IEnumerable). The example from the blog is:
Dim SmallCountries = Select Country _
From Country In Countries _
Where Country.Population < 1000000
This is certainly an interesting option for collections. This will make it much easier to cache data on the client side and use it in a flexible manner. I've been hearing for years that we're just about to see the end of relational databases and OO databases are the wave of the future. Instead we're looking at a relationalal “language” coming to an OO environment. Ha!
Read More
|
|
|
|
|
| Monday, September 19, 2005 |
|
|
|
| Wednesday, September 14, 2005 |
|
Joe Webb - Where to stay in Grapevine?
By joew@webbtechsolutions.com @ 12:00 AM :: 1135 Views ::
0 Comments :: PASS Member, PASS Summit Attendee 2005, Volunteer, Summit 2005, Summit Speaker 2005
|
As many of you already know, this year's PASS Community Summit is
shaping up to be the biggest, and hopefully the best, ever! We're
really excited about...well everything; lots of great opportunities for
networking and education.
A few weeks back, the Gaylord Texan sold out of rooms for the week of
the event. So if you haven't already made your travel arrangements,
you're going to have to stay off-site.
To help out, we've contracted with a couple of other hotels in the
area, the Hyatt DFW and the Grand Hyatt. (The Hyatt DFW has already
sold out, too, by the way). Both are approximately 5 miles from the
Gaylord Texan, but we're going to run shuttles back and forth
throughout the day to help with the transportation needs. If you're
staying in one of those hotels, check the lobby of the hotel for more
details.
As always, if you have any other needs, check with the PASS Headquarters, http://www.sqlpass.org.
|
|
|
|
|
|
|
|
 |
|
 |
|
|
|
|
|