Tableau de classment sur une colonne tout en sortingant sur une autre

J'ai un sous-set d'une table SQL Server 2008 R2 comme ceci:

cust_id | prod_id | day | price --------+---------+-----+------- 137656 194528 42373 9.11 137656 194528 42374 9.11 137656 194528 42375 9.61 137656 194528 42376 9.61 137656 194528 42377 9.11 137656 194528 42378 9.11 

J'ai besoin de classr les différentes périodes de prix comme suit:

 cust_id | prod_id | day | price | rank --------+---------+-----+-------+------ 137656 194528 42373 9.11 1 137656 194528 42374 9.11 1 137656 194528 42375 9.61 2 137656 194528 42376 9.61 2 137656 194528 42377 9.11 3 137656 194528 42378 9.11 3 

de sorte qu'il sortinge par cust_id , prod_id et le day ascendant mais augmente le rang quand le prix change. J'ai essayé d'utiliser DENSE_RANK() comme ceci:

 SELECT cust_id, prod_id, [day], price, DENSE_RANK() OVER (ORDER BY cust_id, prod_id, price) FROM @prices 

Cela renvoie quelque chose comme:

 cust_id | prod_id | day | price | rank --------+---------+-----+-------+------ 137656 194528 42373 9.11 1 137656 194528 42374 9.11 1 137656 194528 42377 9.11 1 137656 194528 42378 9.11 1 137656 194528 42375 9.61 2 137656 194528 42376 9.61 2 

Évidemment, exclure le jour de la sorte me donnera ces résultats, mais chaque fois que DENSE_RANK() le jour dans l'ordre par section du DENSE_RANK() – il se partitionne chaque nouveau jour comme un nouvel ID ….

Est-ce que quelqu'un a des idées sur la façon dont cela devrait fonctionner? Appréciez tout conseil et pouvez donner plus d'informations si nécessaire

    La première variante avec LAG et SUM

     SELECT *, 1+SUM(IncCount)OVER(PARTITION BY cust_id ORDER BY [day]) [rank] --1+SUM(IncCount)OVER(PARTITION BY cust_id ORDER BY [day] ROWS BETWEEN unbounded preceding AND current row) [rank] FROM ( SELECT *, IIF(LAG(price)OVER(PARTITION BY cust_id ORDER BY [day])<>price,1,0) IncCount --CASE WHEN LAG(price)OVER(PARTITION BY cust_id ORDER BY [day])<>price THEN 1 ELSE 0 END IncCount FROM Test ) q 

    La deuxième variante sans LAG

     WITH numCTE AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY cust_id ORDER BY [day]) RowNum FROM Test ) SELECT t1.*, 1+SUM(CASE WHEN t2.price<>t1.price THEN 1 ELSE 0 END)OVER(PARTITION BY t1.cust_id ORDER BY t1.[day]) [rank] --1+SUM(CASE WHEN t2.price<>t1.price THEN 1 ELSE 0 END)OVER(PARTITION BY t1.cust_id ORDER BY t1.[day] ROWS BETWEEN unbounded preceding AND current row) [rank] FROM numCTE t1 LEFT JOIN numCTE t2 ON t2.RowNum+1=t1.RowNum AND t2.cust_id=t1.cust_id 

    La troisième variante avec CTE récursif

     WITH numCTE AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY cust_id ORDER BY [day]) RowNum FROM Test ), rankCTE AS( SELECT RowNum,cust_id,prod_id,[day],price,1 [rank] FROM numCTE WHERE RowNum=1 UNION ALL SELECT n.RowNum,n.cust_id,n.prod_id,n.[day],n.price, r.[rank]+CASE WHEN n.price<>r.price THEN 1 ELSE 0 END [rank] FROM numCTE n JOIN rankCTE r ON n.RowNum=r.RowNum+1 AND n.cust_id=r.cust_id ) SELECT * FROM rankCTE OPTION(MAXRECURSION 0)