在每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

SQL小提琴：http://sqlfiddle.com/#!6/b5857/3/0

工作就像一个魅力，让我知道是有可能有日期选定的价格还？ – MahdiIBM

像在类似：'20170101 - 20170109 110' – Matt

不喜欢 - > 20170102 = 110 – MahdiIBM

答

你可以使用：

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

Rextester Demo

*假设日期是唯一的

编辑：

如像：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

Rextester Demo2

答

您可以使用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)

在每5行中选择最大值

相关推荐