SQL Server 2005 中的数据聚合

Question

我需要查询 SQl Server 2005（SQL Server Management Studio Express）。 我将数据存储为 1 分钟时间范围（每行 1 分钟），每个表的列分别是 ID、交易品种、日期时间、开盘价、最高价、最低价、收盘价、交易量。 我需要转换（压缩）到每个可能的多个时间范围，例如 10 分钟、13 分钟、15 分钟等。 如果有人可以提供帮助，请提供完整的详细信息。 谢谢 阿尔贝托

Answer 1

Alberto，看来您需要在 SQL 语句中使用“Group By”子句（如 Leppie 所说）。所以，你最好还是寻找一下。

首先，您应该使用开始和结束日期/时间过滤要聚合的行，然后按提到的子句对它们进行分组。

这是我通过 Google 搜索“sql group by”关键字时的第一个链接。

Answer 2

;WITH cte AS
(SELECT *,
        (32 * CAST([DATETIME] AS INT)) + DATEPART(HOUR,[DATETIME]) + (DATEPART(MINUTE,[DATETIME])/15)/4.0 AS Seg
     FROM     prices
     )
,cte1 AS
(
SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Symbol,Seg ORDER BY [DATETIME])      AS RN_ASC ,
        ROW_NUMBER() OVER (PARTITION BY Symbol,Seg ORDER BY [DATETIME] DESC) AS RN_DESC
FROM cte
)     
SELECT 
      Symbol,
      Seg,
      MAX(CASE WHEN RN_ASC=1 THEN [DATETIME] END) AS OpenDateTime,
      MAX(CASE WHEN RN_ASC=1 THEN [OPEN] END) AS [OPEN],
      MAX(High) High,
      MIN(Low)  Low,
      SUM(Volume) Volume,
      MAX(CASE WHEN RN_DESC=1 THEN [CLOSE] END) AS [CLOSE],
      MAX(CASE WHEN RN_DESC=1 THEN [DATETIME] END) AS CloseDateTime
FROM cte1
GROUP BY Symbol,Seg
ORDER BY OpenDateTime

或者另一种可能值得测试的方法，看看它是否更快。

DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
DECLARE @Interval FLOAT

SET @D1  = '2010-10-18 09:00:00.000'
SET @D2  = '2010-10-19 18:00:00.000'
SET @Interval = 15

;WITH 
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4),
Ranges AS(
SELECT 
      DATEADD(MINUTE,@Interval*(i-1),@D1) AS StartRange,
      DATEADD(MINUTE,@Interval*i,@D1) AS NextRange
FROM Nums where i <= 1+CEILING(DATEDIFF(MINUTE,@D1,@D2)/@Interval))
,cte AS (
SELECT 
     * 
     ,ROW_NUMBER() OVER (PARTITION BY Symbol,r.StartRange ORDER BY [DateTime])      AS RN_ASC 
     ,ROW_NUMBER() OVER (PARTITION BY Symbol,r.StartRange ORDER BY [DateTime] DESC) AS RN_DESC
FROM Ranges r
JOIN prices p ON p.[DateTime] >= r.StartRange and p.[DateTime] < r.NextRange )
SELECT 
      Symbol,
      MAX(CASE WHEN RN_ASC=1 THEN [DateTime] END) AS OpenDateTime,
      MAX(CASE WHEN RN_ASC=1 THEN [Open] END) AS [Open],
      MAX(High) High,
      MIN(Low)  Low,
      SUM(Volume) Volume,
      MAX(CASE WHEN RN_DESC=1 THEN [Close] END) AS [Close],
      MAX(CASE WHEN RN_DESC=1 THEN [DateTime] END) AS CloseDateTime
FROM cte
GROUP BY Symbol,StartRange
ORDER BY OpenDateTime

Answer 3

不是简单的“分组依据” - 需要为组中的第一行和相应的最后一行获取打开和关闭值。或者至少对于外汇数据来说是这样:)

Answer 4

使用存储过程首先提取 MIN(datetime) 会更漂亮，但这里有一个草图：

WITH quarters(q) AS (
    SELECT DISTINCT
        15*CAST(DATEDIFF("n",'2000/01/01',dataora) / 15 as Int) AS primo
    FROM 
        Prezzi
)
SELECT
    simbolo, DATEADD("n",q,'2000/01/01') AS tick, 
        MIN(minimo) AS minimo, MAX(massimo) AS massimo,
        (SELECT 
            TOP 1 apertura FROM Prezzi P 
         WHERE 
            P.simbolo = simbolo AND 
            P.dataora >= DATEADD("n",q,'2000/01/01')
         ORDER BY
            P.dataora ASC
         ) as primaapertura,
        (SELECT 
            TOP 1 chiusura FROM Prezzi P 
         WHERE 
            P.simbolo = simbolo AND 
            P.dataora < DATEADD("s",14*60+59,DATEADD("n",q,'2000/01/01'))
         ORDER BY
            P.dataora DESC
         ) as ultimachiusara,
        SUM(volume) / COUNT(*) AS volumemedio
FROM
    quarters INNER JOIN Prezzi
    ON dataora BETWEEN DATEADD("n",q,'2000/01/01')
        AND DATEADD("s",14*60+59,DATEADD("n",q,'2000/01/01'))
GROUP BY
    simbolo, DATEADD("n",q,'2000/01/01')
ORDER BY 
    1, 2

WITH 子句在数据集中获取 15 分钟间隔的列表，向下舍入（让我们假设 2000 年之前没有任何内容）。
然后使用这些间隔按 14:59 间隔进行分组。
对于数量，您必须决定是要平均值还是总数。

语法可能有点偏离，但你应该明白了。

编辑：调整 MIN（开盘）、MIN（收盘）以获取第一个和最后一个。实际上，这不会有太大变化，因为开盘和收盘的概念取决于了解报价来源的交易所与收集数据的计算机时钟之间的时间差。

此外，除非OP拥有从所有交易所实时获取的特权，否则所有报价无论如何都会延迟20分钟。

编辑(2)：非常正确，FIRST 和 LAST 是我的 IBM 日子的遗留物 >;-)

解决方案现在使用 TOP 和 ASC/DESC 选择时间间隔内的第一个和最后一个报价。

Answer 5

    Declare @tbl1MinENI Table 
    (ID int identity,
     Simbolo char(3),
     DataOra datetime,
     Apertura numeric(15,4),
     Massimo  numeric(15,4),
     Minimo numeric(15,4),
     Chiusura numeric(15,4),
     Volume int)

    Insert Into  @tbl1MinENI (  Simbolo, DataOra, Apertura, Massimo, Minimo, Chiusura, Volume)
    Values
    ('ENI', '2010/10/18 09:00:00', 16.1100, 16.1800, 16.1100, 16.1400, 244015),
    ('ENI', '2010/10/18 09:01:00', 16.1400, 16.1400, 16.1300, 16.1400, 15692 ),
    ('ENI', '2010/10/18 09:02:00', 16.1400, 16.1500, 16.1400, 16.1500, 147035),
    ('ENI', '2010/10/18 09:03:00', 16.1500, 16.1600, 16.1500, 16.1600, 5181  ),
    ('ENI', '2010/10/18 09:04:00', 16.1600, 16.2000, 16.1600, 16.1900, 5134  ),
    ('ENI', '2010/10/18 09:05:00', 16.1900, 16.1900, 16.1800, 16.1800, 15040 ),
    ('ENI', '2010/10/18 09:06:00', 16.1900, 16.1900, 16.1600, 16.1600, 68867 ),
    ('ENI', '2010/10/18 09:07:00', 16.1600, 16.1600, 16.1600, 16.1600, 7606  ),
    ('ENI', '2010/10/18 09:08:00', 16.1500, 16.1500, 16.1500, 16.1500, 725   ),
    ('ENI', '2010/10/18 09:09:00', 16.1600, 16.1600, 16.1600, 16.1600, 81    ),
    ('ENI', '2010/10/18 09:10:00', 16.1700, 16.1800, 16.1700, 16.1700, 68594 ),
    ('ENI', '2010/10/18 09:11:00', 16.1800, 16.1800, 16.1800, 16.1800, 6619  )

    Declare @nRowsPerGroup int = 3

;With Prepare as
(
Select datediff(minute, '2010/10/18 09:00:00', DataOra)/@nRowsPerGroup as Grp,
       Row_Number() over (partition by datediff(minute, '2010/10/18 09:00:00', DataOra)/@nRowsPerGroup order by dataora) as rn,
       *
  From tbl1MinENI     
), b as
(
Select a.Grp, 
         Min(a.DataOra)          as GroupDataOra, 
         Min(ID) AperturaID,
         max(a.Massimo)          as Massimo, 
         Min(a.Minimo)           as Minimo, 
         max(id) ChiusuraID,
         sum(a.Volume)           as Volume
    From Prepare a
   Group by Grp
)
Select b.grp,
       b.GroupDataOra,
       ta.Apertura,
       b.Massimo,
       b.Minimo,
       tc.Chiusura,
       b.Volume
 From b
 Inner Join tbl1MinENI ta on ta.ID=b.AperturaID
 Inner Join tbl1MinENI tc on tc.ID=b.ChiusuraID
 ;