使用T-SQL窗口函数从1分钟数据中检索5分钟平均值

2024-05-06 • 问答

我有一个数据库表，其中包含一分钟的打开，关闭，高，低，成交量值，以获取证券。我使用的是SQL Server 2017，但可以选择使用2019 RC。

我正在尝试找到一种有效的SQL Server查询，该查询可以将这些查询聚合到5分钟的窗口中，其中：

打开=窗口的第一个打开值
Close =窗口的最后关闭值
高=最大窗口的高值
低=最小值窗口的低值
Volume =整个窗口的平均体积

理想情况下，此查询将考虑数据中的差异，即基于日期计算，而不是计算前/后行。

例如说我有（这是6分钟的数据）：

| Time             | Open | Close | High | Low | Volume |
|------------------|------|-------|------|-----|--------|
| 2019-10-30 09:30 | 5    | 10    | 15   | 1   | 125000 |
| 2019-10-30 09:31 | 10   | 15    | 20   | 5   | 100000 |
| 2019-10-30 09:32 | 15   | 20    | 25   | 10  | 120000 |
| 2019-10-30 09:33 | 20   | 25    | 30   | 15  | 10000  |
| 2019-10-30 09:34 | 20   | 22    | 40   | 2   | 13122  |
| 2019-10-30 09:35 | 22   | 30    | 35   | 4   | 15000  | Not factored in,since this would be the first row of the next 5-minute window

我正在尝试编写一个查询，该查询将给我（这是5分钟汇总的第一个示例）：

| Time             | Open | Close | High | Low | Volume  |
|------------------|------|-------|------|-----|---------|
| 2019-10-30 09:30 | 5    | 30    | 40   | 1   | 50224.4 |

有什么提示吗？我正在用OVER子句及其PARTITION / RANGE选项将我的头撞在墙上

问题的要点是将日期时间值四舍五入到5分钟边界（假设数据类型为datetime）可以使用DATEADD(MINUTE,DATEDIFF(MINUTE,time) / 5 * 5,0)完成。休息是基本的分组/窗口功能：

WITH cte AS (
  SELECT clamped_time,[Open],[Close],[High],[Low],[Volume],rn1 = ROW_NUMBER() OVER (PARTITION BY clamped_time ORDER BY [Time]),rn2 = ROW_NUMBER() OVER (PARTITION BY clamped_time ORDER BY [Time] DESC)
  FROM t
  CROSS APPLY (
      SELECT DATEADD(MINUTE,0)
  ) AS x(clamped_time)
)
SELECT clamped_time,MIN(CASE WHEN rn1 = 1 THEN [Open] END) AS [Open],MIN(CASE WHEN rn2 = 1 THEN [Close] END) AS [Close],MAX([High]) AS [High],MIN([Low]) AS [Low],AVG([Volume])
FROM cte
GROUP BY clamped_time

Demo on db<>fiddle

您希望每隔5分钟分析一次数据。您可以将窗口函数与以下分区子句一起使用：

partition by datepart(year,t.[time]),datepart(month,datepart(day,datepart(hour,(datepart(minute,t.[time]) / 5)

查询：

select *
from (
    select  
        t.time,row_number() over(
            partition by datepart(year,[time]),[time]) / 5)
            order by [time]
        ) [rn],first_value([open]) over(
            partition by datepart(year,[time]) / 5)
            order by [time]
        ) [open],last_value([close]) over(
            partition by datepart(year,[time]) / 5)
            order by [time]
        ) [close],max([high]) over (
            partition by datepart(year,[time]) / 5)
        ) [high],min([low]) over (
            partition by datepart(year,[time]) / 5)
        ) [low],avg([volume]) over (
            partition by datepart(year,[time]) / 5)
        ) [volume]
    from mytable t
) t
where rn = 1

您可以尝试一下。

  SELECT
      MIN([Time]) [Time],Min([Open]) [Open],LEAD(Min([Open])) OVER (ORDER BY MIN([Time])) AS [Close],Max([High]) [High],Min([Low]) [Low],Avg(Volume) Volume
  FROM SampleData
  GROUP BY DATEADD(Minute,-1* DATEPART(Minute,Time) %5,Time)

sql fiddle

使用T-SQL窗口函数从1分钟数据中检索5分钟平均值

crh0519 回答：使用T-SQL窗口函数从1分钟数据中检索5分钟平均值

大家都在问