|
|
Posted by Steve Kass on 09/04/07 23:37
Erland,
Thanks for pointing the MVPs to this thread. I think the following
solution is efficient and accesses the table only once. The query
below uses the Northwind sample database data.
It requires a permanent table of Years (year ranges, actually,
with one range for each year and one "grand total" range) that
can work generally for all queries like this, and it also uses
aspects of the data model in Northwind, specifically that there
is a Customers table.
It can probably also be done with some sort of ROLLUP and
PIVOT/UNPIVOT, as queries with cross joins and MAX's often
can. Also, I only tested it on the SQL Server 2008 preview,
because at the moment, no other server is handy.
Here's the query, preceded by the created Years table (here with
inserts only sufficient for Northwind. You will have to add more.)
Steve Kass
Drew University
http://www.stevekass.com
-- Create a permanent table like this that will work for
-- the entire range of time you will ever need
CREATE TABLE Years(
y0 datetime primary key,
y1 datetime,
tag varchar(20),
ord int
);
insert into Years values ('1996','1997','1996',1996);
insert into Years values ('1997','1998','1997',1997);
insert into Years values ('1998','1999','1998',1998);
insert into Years values ('1900','2100','Total',0);
go
-- The query:
with T(CustomerID, Tag, Ord, Total, drk) as (
select
C.CustomerID,
max(case when O.OrderDate >= y0 and O.OrderDate < y1
then tag end
) as Tag,
max(case when O.OrderDate >= y0 and O.OrderDate < y1
then ord end
) as Ord,
sum(
case when O.OrderDate >= y0 and O.OrderDate < y1
then OD.Quantity * OD.UnitPrice end
) as Total,
dense_rank() over (order by sum(OD.Quantity * OD.UnitPrice) desc) as drk
from Orders O
join Customers C
on C.CustomerID = O.CustomerID
join [Order Details] OD
on O.OrderID = OD.OrderID
cross join Years
group by C.CustomerID, tag
)
select CustomerID, Tag, Total
from T
where drk <= 25
order by drk, Ord
;
go
-- DROP TABLE Years;
Erland Sommarskog wrote:
> (csimam@gmail.com) writes:
>
>>>The problem is that I ultimately only want the top 25 stores in total
>>>sales BUT with their subtotals as well... So I used ROLLUP to generate
>>>the totals and subtotals... and in the same query I generated a
>>>ranking of the data... and there I found my problem: the ranking does
>>>not distinguish between totals and subtotals. So the results look
>>>like:
>>>
>>>store1, realTotal, 1
>>>store1, subTotal, 2
>>>store2, realTotal, 3
>>>store7, realTotal, 4
>>>store10, subTotal, 5
>>>
>>>Clearly, the rows with subtotals should NOT be ranked... but I am
>>>struggling with SQL to figure out how to get exactly what I want.
>
>
> An interesting challenge to do in one query and only hitting the table
> once, and I was not able to think of a solution without using the
> imaginary Previous() function that we have discussed internally among
> us MVPs.
>
> You can easily do in one query, if you hit the table twice, simply
> one query with GROUP BY store and one with GROUP BY store, month.
> Rank the first and join.
>
> And it's possible to hit the table once, if you first get the
> monthly aggregations into a temp table.
>
> But in a single query? I don't think it's possible is you want the
> format:
>
> Store1, Grand Total, 1000000
> Store1, Jan 2000, 12000
> Store1, Feb 2000, 10000,
> Store2, Grand Total, ....
>
> But I will have to see if I get Itzik Ben-Gan to tackle this one.
>
>
>>Well... I have a solution. But I'm not sure about it. It involves
>>something I learned about only recently: a user-defined aggregate
>>function that concatenates values from across rows. Using this
>>aggregate function, stragg, I can do another pass through the results
>>mentioned above while grouping by store, and produce:
>>
>>store1, 'realTotal subTotal', 1
>>store2, 'realTotal subTotal', 3
>>store7, 'realTotal subTotal', 4
>>store10, 'realTotal subTotal', 5
>
>
> One problem is that a UDA is limited to 8000 characters, so if there
> are many subtotals, you query will bomb.
>
>
>>...but... a worry: is this REALLY better than doing a nested query? I
>>wonder if the use of the user-defined aggregate function is very
>>expensive...
>
>
> The way to find out is to run both queries a couple of times on an
> idle server. Just don't forget about the effects of caching. Either
> first run the queries once, so that you know that table is entirely
> in cache. If it's too big for that, run DBCC DROPCLEANBUFFERS between
> each run, so that both queries run under the same condition.
>
>
Navigation:
[Reply to this message]
|