PostgreSQL统计2023年12月各工单组提报数关联最近组ID

我有一个PostgreSQL表my_tbl，用于记录工单提报历史，现需统计2023年12月各assigned_to_group的工单提报数量，结果需展示不同的组ID及对应提报数。组ID需取自同一entity_id下、提报发生前的最近记录。

表结构及示例数据 #

CREATE TABLE my_tbl (
    entity_id INT,
    nb_raise_c VARCHAR(255),
    assigned_to_group VARCHAR(255),
    time_c TIMESTAMP
);

INSERT INTO my_tbl (entity_id, nb_raise_c, assigned_to_group, time_c)
VALUES
(1, NULL, '10086',  '2023-01-11 15:51:20.021+01:00'),
(1, '0',  '10086',  '2023-01-11 15:51:20.021+01:00'),
(1, NULL, '435334', '2023-01-11 15:54:47.471+01:00'),
(1, NULL, '435334', '2023-01-11 15:54:47.471+01:00'),
(1, '1',  NULL,     '2023-08-29 16:01:39.135+02:00'),
(1, '2',  NULL,     '2023-09-26 15:02:00.968+02:00'),
(1, '3',  NULL,     '2023-12-19 10:52:31.812+01:00'),
(2, NULL, '45457',  '2023-11-28 15:51:20.021+01:00'),
(2, '0',  NULL,     '2023-11-28 16:53:20.021+01:00'),
(2, '1',  NULL,     '2023-11-30 18:58:20.021+01:00'),
(2, '2',  NULL,     '2023-12-02 07:10:28.021+01:00'),
(2, NULL, '36781',  '2023-12-04 07:10:28.021+01:00'),
(2, '3',  NULL,     '2023-12-05 07:10:28.021+01:00'),
(2, NULL, '45457',  '2023-12-06 07:10:28.021+01:00'),
(2, '4',  NULL,     '2023-12-07 07:10:28.021+01:00');

尝试的SQL及问题 #

我尝试了以下查询，但返回结果中assigned_to_group为null，计数为4，不符合预期：

WITH ranked_groups AS (
    SELECT
        entity_id,
        nb_raise_c,
        assigned_to_group,
        time_c,
        RANK() OVER (PARTITION BY entity_id, nb_raise_c ORDER BY time_c DESC) AS rnk
    FROM
        my_tbl
    WHERE
        nb_raise_c IS NOT NULL
)
SELECT
    assigned_to_group,
    COUNT(nb_raise_c) AS num_relance
FROM
    ranked_groups
WHERE
    EXTRACT(MONTH FROM time_c) = 12
    AND rnk = 1
GROUP BY
    assigned_to_group;

预期结果 #

解决方案 #

方法一：使用LATERAL JOIN关联最近组记录 #

WITH raise_records AS (
    SELECT 
        entity_id,
        nb_raise_c,
        time_c AS raise_time
    FROM my_tbl
    WHERE 
        nb_raise_c IS NOT NULL
        AND time_c >= '2023-12-01'::TIMESTAMP
        AND time_c < '2024-01-01'::TIMESTAMP
)
SELECT 
    g.assigned_to_group,
    COUNT(r.nb_raise_c) AS num_relance
FROM raise_records r
LEFT JOIN LATERAL (
    SELECT assigned_to_group
    FROM my_tbl
    WHERE 
        entity_id = r.entity_id
        AND assigned_to_group IS NOT NULL
        AND time_c <= r.raise_time
    ORDER BY time_c DESC
    LIMIT 1
) g ON true
GROUP BY g.assigned_to_group
ORDER BY num_relance DESC;

逻辑说明：

1. 先筛选出2023年12月的所有工单提报记录（nb_raise_c非空）。
2. 通过LATERAL JOIN为每条提报记录，查询同一entity_id下、时间不晚于提报时间的最新组分配记录（取最近的一条）。
3. 最后按组ID分组统计提报数量。

方法二：使用窗口函数填充最近组ID #

WITH all_records AS (
    SELECT 
        entity_id,
        nb_raise_c,
        time_c,
        -- 填充到当前记录为止最近的非空组ID
        LAST_VALUE(assigned_to_group) OVER (
            PARTITION BY entity_id 
            ORDER BY time_c 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS latest_group
    FROM my_tbl
)
SELECT 
    latest_group AS assigned_to_group,
    COUNT(nb_raise_c) AS num_relance
FROM all_records
WHERE 
    nb_raise_c IS NOT NULL
    AND time_c >= '2023-12-01'::TIMESTAMP
    AND time_c < '2024-01-01'::TIMESTAMP
GROUP BY latest_group
ORDER BY num_relance DESC;

逻辑说明：

1. 对所有记录按entity_id和时间排序，使用LAST_VALUE结合IGNORE NULLS，为每条记录填充到当前时间为止最近的非空assigned_to_group值。
2. 筛选出2023年12月的提报记录，按填充后的组ID分组统计数量。

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