在每5行中选择max值。

My total rows are variable and not fixed , So there are N rows and I want to separate each 5 rows as a group and select the max value of price in following table in SQL.

我的总行是可变的，不是固定的，所以有N行，我想将每5行作为一个组分开，并在SQL中的下表中选择价格的最大值。

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
20170123 120

So in first 5 group the max price is 110 , and second group is 100, and last group max price is 120.

前五组的最大值是110，第二组是100，最后一组的最大值是120。

3 个解决方案

#1

Use a common table expression to group them.

使用公共表表达式对它们进行分组。

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);

Output

输出

GroupedDate MAXPRICE
0           110
1           100
2           120

SQL Fiddle: http://sqlfiddle.com/#!6/b5857/3/0

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

#2

You can use row_number as below

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

#3

You could use:

您可以使用:

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

Rextester演示

*assuming that date is unique

假设日期是唯一的。

EDIT:

编辑:

As in something like : 20170101 - 20170109 110

比如: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;

Output:

输出:

20170101-20170105   110
20170109-20170115   100
20170120-20170123   120

Rextester Demo2

Rextester以及接下来

#1