和之前发布的TSQL Challenge 1是同一系列的文章，看到那篇学习哪篇，没有固定的顺序，只为锻炼下思维。
Compare rows in the same table and group the data
The challenge is to compare the data of the rows and group the input data. The data needs to be grouped based on the Product ID, Date, TotalLines, LinesOutOfService. You need to check each row in the table to see if that particular product has the different range of values for TotalLines and Linesoutofservice when compared to the rows below. If yes, then insert that date where a different range is encountered into the "End Date" column. If no change in the values are detected then insert a future date "01/12/2050". In the TotalCustomerCalls column you place the sum of CustomerCalls for the given Start Date / End Date group.
Sample Data
01.ProductID Date TotalLines LinesOutOfService CustomerCalls
02.-------- ---------- ---------- ----------------- -------------
03.522 2010-04-05 345 5 100
04.522 2010-04-06 345 5 80
05.522 2010-04-07 120 4 50
06.522 2010-04-08 345 5 60
07.522 2010-04-09 345 5 40
08.522 2010-04-10 345 5 70
09.522 2010-04-11 117 20 300
10.522 2010-04-12 345 5 55
11.522 2010-04-14 345 5 75
12.522 2010-04-15 260 10 150
13.522 2010-04-16 345 5 30
14.522 2010-04-17 345 5 95
15.522 2010-04-19 345 5 60
Expected Results
01.ProductID Start Date End Date TotalLines LinesOutOfService TotalCustomerCalls
02.--------- ---------- ---------- ---------- ----------------- ------------------
03.522 2010-04-05 2010-04-06 345 5 180
04.522 2010-04-07 2010-04-07 120 4 50
05.522 2010-04-08 2010-04-10 345 5 170
06.522 2010-04-11 2010-04-11 117 20 300
07.522 2010-04-12 2010-04-14 345 5 130
08.522 2010-04-15 2010-04-15 260 10 150
09.522 2010-04-16 2050-12-01 345 5 185
Rules
- The output should be ordered by ProductID, Start Date, End Date.
- There will be no duplicate dates within a Product ID.
- The data will contain more than one ProductID.
Restrictions
- The solution should be a single query that starts with a "SELECT" or “;WITH”
The Answe:
SELECT ProductId,
[Start Date] = MIN([Date]),
[End Date] = MAX(CASE WHEN [Date] <> gd.ProductMaxDate THEN [DATE] ELSE '2050-12-01' END) ,
TotalLines,
LinesOutOfService,
TotalCustomerCalls = SUM(CustomerCalls)
FROM ( SELECT *,
GroupId = ROW_NUMBER() OVER(ORDER BY ProductId,TotalLines,LinesOutOfService) - ROW_NUMBER() OVER (ORDER BY ProductId,[Date]),
ProductMaxDate = MAX([DATE]) OVER(PARTITION BY ProductId)
FROM TC83) as gd
GROUP BY ProductId,GroupId,TotalLines,LinesOutOfService
ORDER BY productid,[Start Date] ,[End Date]
仍在研究中~~~~~
发现另一种优雅：
SELECT *,maxScore=MAX(score)OVER(PARTITION BY id) FROM #tt SELECT *,maxScore=(SELECT MAX(score) FROM #tt WHERE id=a.id) FROM #tt a
以后有机会可以在合适的场合使用。