基于Timestamp优化SQL中Group By分组的实现方案

要实现这种以column3 != 'A'（即column3 = 'A'为FALSE）的行起始、后跟连续column3 = 'A'（TRUE）行的子分组统计，核心是用窗口函数生成子分组的唯一标识，具体步骤如下：

1. 生成子分组唯一ID #

先在原表数据基础上，按column1、column2做一级分组，每个一级分组内按timestamp从旧到新排序。通过累加窗口函数，每遇到一行column3 != 'A'的记录，就为当前及后续连续的TRUE行分配一个新的子分组ID：

SELECT
  column1,
  column2,
  column3,
  timestamp,
  -- 每遇到column3 != 'A'的行，累加计数+1，生成子分组ID
  SUM(CASE WHEN column3 != 'A' THEN 1 ELSE 0 END) OVER (
    PARTITION BY column1, column2
    ORDER BY timestamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS sub_group_id
FROM master

比如你给出的示例数据，三个子分组的sub_group_id会分别是1、2、3，每个子分组内的行共享同一个ID。

2. 基于子分组统计数量 #

有了子分组ID后，将column1、column2、sub_group_id作为联合分组依据，就能实现你需要的细化分组统计：

SELECT
  column1,
  column2,
  sub_group_id,
  COUNT(*) AS CNT
FROM (
  SELECT
    column1,
    column2,
    SUM(CASE WHEN column3 != 'A' THEN 1 ELSE 0 END) OVER (
      PARTITION BY column1, column2
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sub_group_id
  FROM master
) AS sub_grouped_data
GROUP BY column1, column2, sub_group_id
ORDER BY column1, column2, sub_group_id

逻辑说明 #

窗口函数SUM(...) OVER (...)会在每个column1+column2的一级分组内，按时间顺序累计column3 != 'A'的出现次数。每出现一次FALSE行，累计值加1，后续的TRUE行都会继承这个累计值，直到下一个FALSE行出现才会生成新的累计值，以此实现将连续的FALSE+TRUE块拆分为独立子分组的效果。

内容的提问来源于stack exchange，提问作者Bipolar Minds