时间戳

首页 > 解决方案 > 合并 T-SQL 中的连续行组，并对每组中的值求和

sql - 合并 T-SQL 中的连续行组，并对每组中的值求和

问题描述

2019 年 10 月 8 日更新：

@Gordon Linoff：我尝试应用您的解决方案，但我意识到它没有按预期工作。我在此处添加了一个带有预期结果的示例（https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1b486476d6aeab25997f25e66ee455e9），如果您能帮助我，我将不胜感激。

--

我有一个带有架构的事务表：

CREATE TABLE Transactions (Id int IDENTITY, SessionId int, TransactionType varchar(50), DateTimeEnd datetime, DateStart datetime, Rank int);

以下是一些行示例：

INSERT INTO Transactions (Id, SessionId, TransactionType, DateTimeEnd, DateStart, Rank)
VALUES
 (1, 1, 'Deposit',    '2017-01-20T11:16:33Z', '2017-01-20T11:16:33Z', 600),
 (2, 1, 'Withdrawal', '2017-01-21T11:16:33Z', '2017-01-20T11:16:33Z', 100),
 (3, 2, 'Deposit',    '2017-02-23T11:16:33Z', '2017-02-23T11:16:33Z', 500),
 (4, 1, 'Withdrawal', '2017-01-24T11:16:33Z', '2017-01-21T11:16:33Z', 150),
 (5, 1, 'Withdrawal', '2017-01-26T11:16:33Z', '2017-01-24T11:16:33Z', 150),
 (6, 2, 'Withdrawal', '2017-02-27T11:16:33Z', '2017-02-23T11:16:33Z', 200),
 (7, 1, 'Withdrawal', '2017-01-28T11:16:33Z', '2017-01-26T11:16:33Z', 10),
 (8, 1, 'Withdrawal', '2017-01-30T11:16:33Z', '2017-01-28T11:16:33Z', 10),
 (9, 1, 'Withdrawal', '2017-01-31T11:16:33Z', '2017-01-30T11:16:33Z', 10);

我想要的是一个 T-SQL 查询，用于按 SessionId、TransactionType 和每个组合并连续行组，以仅保留具有最小 DateTimeEnd 的行。此外，保留的行的 Rank 值必须是来自组的行的 Rank 值的总和。T-SQL 查询需要在 Microsoft Azure SQL 数据仓库的 MS SQL Server 中运行。

期望的结果：

|    Id    |     SessionId    | Transaction |       DateTimeEnd  |      DateStart     |   Rank  |
|----------|------------------|-------------|--------------------|--------------------|---------|
|    1     |         1        |      Deposit|2017-01-20T11:16:33Z|2017-01-20T11:16:33Z|   600   |
|    2     |         1        |   Withdrawal|2017-01-21T11:16:33Z|2017-01-20T11:16:33Z|   100   |
|  4       |         1        |   Withdrawal|2017-01-24T11:16:33Z|2017-01-21T11:16:33Z|   300   |
|  7       |         1        |   Withdrawal|2017-01-28T11:16:33Z|2017-01-26T11:16:33Z|    30   |
|    3     |         2        |      Deposit|2017-02-23T11:16:33Z|2017-02-23T11:16:33Z|   500   |
|    6     |         2        |   Withdrawal|2017-02-27T11:16:33Z|2017-02-23T11:16:33Z|   200   |

我尝试了很多方法，但无法实现。

标签： sqlsql-servertsqlazure-sql-data-warehouse

解决方案

正如 GMB 指出的那样，这是一个孤岛问题。因为您想保留第一行，所以我将建议一种lag()方法，而不是行号的差异：

SELECT SessionId, TransactionType, DateTimeEnd,DateStart, sumRank
FROM (SELECT t.*,
             SUM(Rank) OVER (PARTITION BY SessionId, TransactionType, grp) as sumRank
      FROM (SELECT t.*,
                   SUM(CASE WHEN prev_st_id = prev_id THEN 0 ELSE 1 END) OVER (ORDER BY id) as grp
            FROM (SELECT t.*,
                         LAG(id) OVER (PARTITION BY SessionId, TransactionType ORDER BY id) as prev_st_id,
                         LAG(id) OVER (PARTITION BY SessionId ORDER BY id) as prev_id
                  FROM Transactions t
                 ) t
           ) t
     ) t
WHERE prev_st_id <> prev_id OR prev_st_id IS NULL;

这是做什么的？

  • 最内层的子查询计算 id 的整体和会话/事务类型的滞后。这id是因为它看起来比日期/时间更稳定（其中一列中有重复的日期/时间值）。
  • 当 id 不同时，就会识别出一个新岛。累积和标识组。
  • 然后grp使用窗口函数计算整个组的值。
  • 然后，外部查询只过滤到每个组中的第一行。

这是一个 db<>fiddle。

推荐阅读