|
|
Posted by --CELKO-- on 11/23/06 16:10
See if this is what you want:
CREATE TABLE SalesTransactions
(sales_nbr INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
sales_amt DECIMAL(12,2) NOT NULL);
Create a VIEW or CTE with each customers sales ordered from high to
low. This is a greedy algorithm. The ROW_NUMBER() will randomly pick
an ordering in the event of ties.
Using that derived table, we can find the subset of purchase in each
customer that are at or below the threshold. amount, something like
this:
WITH (SELECT customer_id, sales_amt,
ROW_NUMBER()
OVER (PARTITION BY customer_id
ORDER BY sales_amt DESC)
FROM SalesTransactions AS S1)
AS SalesScores (customer_id, sales_amt, score)
SELECT S1.customer_id, S1.score
FROM SalesScores AS S1
WHERE @threshold_amt <=
(SELECT SUM(S2.sales_amt)
FROM SalesScores AS S2
WHERE S1.customer_id = S2.customer_id
AND S1.score >= S2.score);
You can do this in one statement with the full OLAP features, which
would have a RANGE clause in the SUM() OVER() construct. SQL Server is
a bit behind.
But the important point is that you use virtual tables, rather than
mimicing a deck of punch cards. Think LOGICAL and not PHYSICAL! Think
sets, not sequences.
Navigation:
[Reply to this message]
|