PostgreSQL中如何统计各ID首次state_review转state_review_2的次数

原始数据表 #

需求说明 #

需要统计所有ID中首次出现从state_review转换到state_review_2的次数，每个ID只要存在一次这样的转换就计为1次，示例预期结果如下：

尝试的错误查询 #

尝试使用以下查询，但无法得到正确结果，会统计所有转换而非按ID去重的首次转换：

SELECT
   COUNT(DISTINCT (
   CASE
      WHEN
         (
            q.state = 'state_review' 
            AND 'state_review' != 'state_review_2'
         )
      THEN
         ID 
   END
)) AS amount 
FROM
   (
      SELECT
         id,
         state
      FROM
         states_table
      WHERE
         updatedate >= 1668603529 
         AND updatedate <= 1671599849
         AND 
         (
            state = 'state_review' 
            OR state = 'state_review_2'
         )
      ORDER BY
         id, updatedate DESC
   )
   AS q

正确解决方案 #

可以通过**窗口函数LAG()**实现需求，核心思路是按ID分组、时间升序排序，获取每条记录的上一个状态，筛选出符合转换条件的记录后按ID去重统计：

方法1：统计存在转换的ID数量 #

SELECT COUNT(DISTINCT id) AS amount
FROM (
    SELECT
        id,
        state,
        -- 按ID分组、时间升序，获取上一条记录的状态
        LAG(state) OVER (PARTITION BY id ORDER BY updatedate ASC) AS prev_state
    FROM
        states_table
    WHERE
        -- 仅保留目标状态
        state IN ('state_review', 'state_review_2')
        -- 注意：原查询的时间范围会过滤掉ID1的state_review_2记录，导致结果不符
        -- 若需要保留原时间范围，请确认是否符合业务需求，否则可注释或调整
        -- AND updatedate >= 1668603529 
        -- AND updatedate <= 1671599849
) AS transitions
-- 筛选出从state_review转换到state_review_2的记录
WHERE prev_state = 'state_review' AND state = 'state_review_2';

方法2：严格获取每个ID的首次转换并统计 #

如果需要确保只统计每个ID的第一次转换事件，可结合ROW_NUMBER()窗口函数：

WITH state_transitions AS (
    SELECT
        id,
        state,
        updatedate,
        LAG(state) OVER (PARTITION BY id ORDER BY updatedate ASC) AS prev_state,
        -- 给每个ID的转换事件按时间排序编号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY updatedate ASC) AS transition_rank
    FROM
        states_table
    WHERE
        state IN ('state_review', 'state_review_2')
)
-- 仅统计每个ID的第一次转换
SELECT COUNT(id) AS amount
FROM state_transitions
WHERE prev_state = 'state_review' AND state = 'state_review_2'
AND transition_rank = 1;

逻辑说明 #

1. LAG()函数：按ID分组，按时间从旧到新排序，获取每条记录的上一个状态，实现当前行与历史行的状态对比。
2. 筛选转换事件：找出上一个状态为state_review且当前状态为state_review_2的记录，这些就是符合要求的转换事件。
3. 按ID去重统计：通过COUNT(DISTINCT id)或ROW_NUMBER()确保每个ID仅被统计一次，得到首次转换的总次数。

注意：原查询中的时间范围会过滤掉ID1的state_review_2记录（其时间戳1668601821小于1668603529），导致结果为2而非预期的3。请根据实际业务需求调整时间条件。

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