使用 T-SQL 聚合相邻的记录

Question

我有（针对示例进行了简化）一个包含以下数据的表。

Row Start       Finish       ID  Amount
--- ---------   ----------   --  ------
  1 2008-10-01  2008-10-02   01      10
  2 2008-10-02  2008-10-03   02      20
  3 2008-10-03  2008-10-04   01      38
  4 2008-10-04  2008-10-05   01      23
  5 2008-10-05  2008-10-06   03      14
  6 2008-10-06  2008-10-07   02       3
  7 2008-10-07  2008-10-08   02       8
  8 2008-10-08  2008-11-08   03      19

日期代表一个时间段，ID 是系统在该时间段内所处的状态，金额是与该状态相关的值。

我想要做的是将具有相同 ID 号的相邻行的金额进行聚合，但保持相同的整体顺序，以便可以组合连续的运行。 因此，我希望最终得到如下数据：

Row Start       Finish       ID  Amount
--- ---------   ----------   --  ------
  1 2008-10-01  2008-10-02   01      10
  2 2008-10-02  2008-10-03   02      20
  3 2008-10-03  2008-10-05   01      61
  4 2008-10-05  2008-10-06   03      14
  5 2008-10-06  2008-10-08   02      11
  6 2008-10-08  2008-11-08   03      19

我正在寻找可以放入 SP 中的 T-SQL 解决方案，但是我不知道如何通过简单查询来做到这一点。 我怀疑它可能需要某种迭代，但我不想走这条路。

我想要进行此聚合的原因是，该过程的下一步是执行 SUM() 和 Count() 按序列中出现的唯一 ID 进行分组，以便我的最终数据看起来像这样：

ID  Counts Total
--  ------ -----
01       2    71
02       2    31
03       2    33

但是，如果我做了一个简单的

SELECT COUNT(ID), SUM(Amount) FROM data GROUP BY ID

在原始表上我得到类似的东西

ID  Counts Total
--  ------ -----
01       3    71
02       3    31
03       2    33

这不是我想要的。

Answer 1

如果您阅读 RT Snodgrass 的《用 SQL 开发面向时间的数据库应用程序》一书（其 pdf 格式可从他的网站上的出版物中找到），直到第 165-166 页上的图 6.25，您会发现不平凡的 SQL，可在当前示例中使用它来对各个行进行分组相同的ID值和连续的时间间隔。

下面的查询开发接近正确，但最后发现了一个问题，其根源在于第一个 SELECT 语句。 没有查出为什么给出了错误的答案。 [如果有人可以在他们的 DBMS 上测试 SQL 并告诉我第一个查询是否在那里正常工作，这将是一个很大的帮助！]

我还 类似于：

-- Derived from Figure 6.25 from Snodgrass "Developing Time-Oriented
-- Database Applications in SQL"
CREATE TABLE Data
(
    Start   DATE,
    Finish  DATE,
    ID      CHAR(2),
    Amount  INT
);

INSERT INTO Data VALUES('2008-10-01', '2008-10-02', '01', 10);
INSERT INTO Data VALUES('2008-10-02', '2008-10-03', '02', 20);
INSERT INTO Data VALUES('2008-10-03', '2008-10-04', '01', 38);
INSERT INTO Data VALUES('2008-10-04', '2008-10-05', '01', 23);
INSERT INTO Data VALUES('2008-10-05', '2008-10-06', '03', 14);
INSERT INTO Data VALUES('2008-10-06', '2008-10-07', '02',  3);
INSERT INTO Data VALUES('2008-10-07', '2008-10-08', '02',  8);
INSERT INTO Data VALUES('2008-10-08', '2008-11-08', '03', 19);

SELECT DISTINCT F.ID, F.Start, L.Finish
    FROM Data AS F, Data AS L
    WHERE F.Start < L.Finish
      AND F.ID = L.ID
      -- There are no gaps between F.Finish and L.Start
      AND NOT EXISTS (SELECT *
                        FROM Data AS M
                        WHERE M.ID = F.ID
                        AND F.Finish < M.Start
                        AND M.Start < L.Start
                        AND NOT EXISTS (SELECT *
                                            FROM Data AS T1
                                            WHERE T1.ID = F.ID
                                              AND T1.Start <  M.Start
                                              AND M.Start  <= T1.Finish))
      -- Cannot be extended further
      AND NOT EXISTS (SELECT *
                          FROM Data AS T2
                          WHERE T2.ID = F.ID
                            AND ((T2.Start <  F.Start  AND F.Start  <= T2.Finish)
                              OR (T2.Start <= L.Finish AND L.Finish <  T2.Finish)));

该查询的输出是：

01  2008-10-01      2008-10-02
01  2008-10-03      2008-10-05
02  2008-10-02      2008-10-03
02  2008-10-06      2008-10-08
03  2008-10-05      2008-10-06
03  2008-10-05      2008-11-08
03  2008-10-08      2008-11-08

已编辑：倒数第二行有问题 - 它不应该在那里。 我还不清楚它来自哪里。

现在，我们需要将该复杂表达式视为另一个 SELECT 语句的 FROM 子句中的查询表达式，它将对与上面显示的最大范围重叠的条目上给定 ID 的金额值进行求和。

SELECT M.ID, M.Start, M.Finish, SUM(D.Amount)
    FROM Data AS D,
         (SELECT DISTINCT F.ID, F.Start, L.Finish
              FROM Data AS F, Data AS L
              WHERE F.Start < L.Finish
                AND F.ID = L.ID
                -- There are no gaps between F.Finish and L.Start
                AND NOT EXISTS (SELECT *
                                    FROM Data AS M
                                    WHERE M.ID = F.ID
                                    AND F.Finish < M.Start
                                    AND M.Start < L.Start
                                    AND NOT EXISTS (SELECT *
                                                        FROM Data AS T1
                                                        WHERE T1.ID = F.ID
                                                          AND T1.Start <  M.Start
                                                          AND M.Start  <= T1.Finish))
                  -- Cannot be extended further
                AND NOT EXISTS (SELECT *
                                    FROM Data AS T2
                                    WHERE T2.ID = F.ID
                                      AND ((T2.Start <  F.Start  AND F.Start  <= T2.Finish)
                                        OR (T2.Start <= L.Finish AND L.Finish <  T2.Finish)))) AS M
    WHERE D.ID = M.ID
      AND M.Start  <= D.Start
      AND M.Finish >= D.Finish
    GROUP BY M.ID, M.Start, M.Finish
    ORDER BY M.ID, M.Start;

这给出：

ID  Start        Finish       Amount
01  2008-10-01   2008-10-02   10
01  2008-10-03   2008-10-05   61
02  2008-10-02   2008-10-03   20
02  2008-10-06   2008-10-08   11
03  2008-10-05   2008-10-06   14
03  2008-10-05   2008-11-08   33              -- Here be trouble!
03  2008-10-08   2008-11-08   19

已编辑：这几乎是执行原始问题所要求的 COUNT 和 SUM 聚合的正确数据集，因此最终答案是：

SELECT I.ID, COUNT(*) AS Number, SUM(I.Amount) AS Amount
    FROM (SELECT M.ID, M.Start, M.Finish, SUM(D.Amount) AS Amount
            FROM Data AS D,
                 (SELECT DISTINCT F.ID, F.Start, L.Finish
                      FROM  Data AS F, Data AS L
                      WHERE F.Start < L.Finish
                        AND F.ID = L.ID
                        -- There are no gaps between F.Finish and L.Start
                        AND NOT EXISTS
                            (SELECT *
                                FROM  Data AS M
                                WHERE M.ID = F.ID
                                  AND F.Finish < M.Start
                                  AND M.Start < L.Start
                                  AND NOT EXISTS
                                      (SELECT *
                                          FROM Data AS T1
                                          WHERE T1.ID = F.ID
                                            AND T1.Start <  M.Start
                                            AND M.Start  <= T1.Finish))
                          -- Cannot be extended further
                        AND NOT EXISTS
                            (SELECT *
                                FROM  Data AS T2
                                WHERE T2.ID = F.ID
                                  AND ((T2.Start <  F.Start  AND F.Start  <= T2.Finish) OR
                                       (T2.Start <= L.Finish AND L.Finish <  T2.Finish)))
                 ) AS M
            WHERE D.ID = M.ID
              AND M.Start  <= D.Start
              AND M.Finish >= D.Finish
            GROUP BY M.ID, M.Start, M.Finish
          ) AS I
        GROUP BY I.ID
        ORDER BY I.ID;

id     number  amount
01      2      71
02      2      31
03      3      66

评论：
哦！ 糟糕...3 的条目的“数量”是应有的两倍。 之前“编辑过”的部分表明了问题开始出现的地方。 看起来好像第一个查询有点错误（也许它是针对不同的问题），或者我正在使用的优化器行为不当。 尽管如此，应该有一个与此密切相关的答案，它将给出正确的值。

郑重声明：已在 Solaris 10 上的 IBM Informix Dynamic Server 11.50 上进行测试。但是，在任何其他适度符合标准的 SQL DBMS 上应该可以正常工作。

Answer 2

可能需要创建一个游标并循环遍历结果，跟踪您正在使用的 id 并一路累积数据。 当 id 更改时，您可以将累积的数据插入到临时表中，并在过程结束时返回该表（从中选择全部）。 基于表的函数可能会更好，因为您可以在执行过程中将其插入到返回表中。

Answer 3

我怀疑它可能需要某种迭代，但我不想走这条路。

我认为这是您必须采取的路线，使用光标填充表变量。 如果您有大量记录，您可以使用永久表来存储结果，然后当您需要检索数据时，您可以只处理新数据。

我将向源表添加一个默认值为 0 的位字段，以跟踪已处理的记录。 假设没有人在表上使用 select *，添加具有默认值的列不会影响应用程序的其余部分。

如果您需要帮助编码解决方案，请在本文中添加评论。

Answer 4

好吧，我决定使用连接和游标的混合来走迭代路线。 通过将数据表与自身连接起来，我可以创建一个仅包含那些连续记录的链接列表。

INSERT INTO #CONSEC
  SELECT a.ID, a.Start, b.Finish, b.Amount 
  FROM Data a JOIN Data b 
  ON (a.Finish = b.Start) AND (a.ID = b.ID)

然后我可以通过用游标迭代列表来展开列表，并对数据表进行更新以进行调整（并从数据表中删除现在无关的记录）

DECLARE CCursor  CURSOR FOR
  SELECT ID, Start, Finish, Amount FROM #CONSEC ORDER BY Start DESC

@Total = 0
OPEN CCursor
FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
WHILE @FETCH_STATUS = 0
BEGIN
  @Total = @Total + @Amount
  @Start_Last = @Start
  @Finish_Last = @Finish
  @ID_Last = @ID

  DELETE FROM Data WHERE Start = @Finish
  FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
  IF (@ID_Last<> @ID) OR (@Finish<>@Start_Last)
    BEGIN
      UPDATE Data
        SET Amount = Amount + @Total
        WHERE Start = @Start_Last
      @Total = 0
    END  
END

CLOSE CCursor
DEALLOCATE CCursor

这一切都有效，并且对于我正在使用的典型数据具有可接受的性能。

我确实发现上面的代码有一个小问题。 最初我是通过游标在每个循环上更新数据表。 但这没有用。 似乎您只能对一条记录进行一次更新，而多次更新（为了不断添加数据）将恢复为读取记录的原始内容。