在每5行中选择最大值
问题描述:
我的总行数是可变的,并且不是固定的,所以有N行,我想将每5行分成一组,并在SQL中的下表中选择价格的最大值。在每5行中选择最大值
Date Price
20170101 100
20170102 110
20170103 90
20170105 80
20170109 76
20170110 50
20170111 55
20170113 80
20170115 100
20170120 99
20170121 88
20170122 98
2017
所以在第一组5最高价格是110,而第二组是100,而最后一组最高价格是120
答
使用公用表表达式对它们进行分组。
WITH CTE AS (SELECT RANK() OVER (ORDER BY Date) AS Rank, Price
FROM yourtable)
SELECT (Rank - 1)/5 AS GroupedDate, MAX(Price) AS MAXPRICE
FROM CTE
GROUP BY ((Rank - 1)/5);
输出
GroupedDate MAXPRICE
0 110
1 100
2 120
答
你可以使用:
SELECT grp, MAX(Price) AS price
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY DATE)/5 AS grp FROM tab) sub
GROUP BY grp;
-- OUTPUT
grp price
0 110
1 100
2 120
*假设日期是唯一的
编辑:
如像:20170101 - 20170109 110
SELECT
CONVERT(VARCHAR(8),MIN(DATE),112) + '-' + CONVERT(VARCHAR(8),MAX(date),112)
, MAX(Price) AS price
FROM (SELECT *, (ROW_NUMBER() OVER(ORDER BY DATE))/5 AS grp FROM tab) sub
GROUP BY grp;
输出:
20170101-20170105 110
20170109-20170115 100
20170120-2017
答
您可以使用ROW_NUMBER如下
;With cte as (
Select *, Bucket = Sum(RowN) over(Order by [date]) from (
Select *, RowN = case when row_number() over(order by [date]) % 5 = 0 then 1 else 0 end from #data1
) a
) Select top (1) with ties [Date], [Price]
from cte
order by row_number() over (partition by Bucket order by Price desc)
工作就像一个魅力,让我知道是有可能有日期选定的价格还? – MahdiIBM
像在类似:'20170101 - 20170109 110' – Matt
不喜欢 - > 20170102 = 110 – MahdiIBM