PostgreSQL按月年统计符合条件的轮次（含跨月场景）

原始数据表 #

del_no  |   Pkt   | direction  |  Env  | start_datetimestamp |
---------+---------+------------+-------+---------------------+
 H_00002 |  02     |  SOUTH     | PROD  | 2022-10-29 16:20:57 |
 E20     |  20     |  NORTH     | PROD  | 2022-10-30 16:41:37 |
 H_00002 |  02     |  NORTH     | TEST  | 2022-10-30 17:21:17 |
 E20     |  20     |  SOUTH     | DEV   | 2022-10-30 17:30:24 |
 H_00004 |  02     |  NORTH     | PROD  | 2022-10-30 16:52:48 |
 H_00004 |  02     |  SOUTH     | PROD  | 2022-10-30 19:03:36 |
 H_00007 |  02     |  NORTH     | PROD  | 2022-10-30 20:52:48 |
 H_00007 |  02     |  SOUTH     | PROD  | 2022-10-30 21:03:36 |
 H_00015 |  02     |  SOUTH     | TEST  | 2022-11-13 19:11:10 |
 L 0013  |  13     |  NORTH     | PROD  | 2022-11-14 20:06:46 |
 H_00015 |  02     |  NORTH     | TEST  | 2022-11-15 20:17:40 |
 L0021   |  21     |  SOUTH     | TEST  | 2022-11-15 20:56:18 |
 H_00015 |  02     |  NORTH     | PROD  | 2022-11-15 20:17:40 |
 L0027   |  21     |  SOUTH     | DEV   | 2022-11-30 20:56:18 |
 H_00019 |  02     |  NORTH     | PROD  | 2022-11-30 20:17:40 |
 L0023   |  21     |  SOUTH     | TEST  | 2022-11-30 20:56:18 |
 H_00019 |  02     |  SOUTH     | TEST  | 2022-11-30 20:17:40 |
 L0025   |  21     |  SOUTH     | TEST  | 2022-11-30 20:56:18 |
 H_00019 |  02     |  SOUTH     | DEV   | 2022-11-30 20:17:40 |
 H_00018 |  02     |  SOUTH     | PROD  | 2023-10-31 20:17:40 |
 H_00018 |  02     |  NORTH     | PROD  | 2023-11-02 03:17:40 |
 H_00033 |  02     |  SOUTH     | PROD  | 2023-10-31 20:17:40 |
 H_00033 |  02     |  NORTH     | DEV   | 2023-11-02 03:17:40 |

统计要求 #

• 仅筛选Pkt=02的数据；
• 按del_no分组（NORTH和SOUTH方向各一次为一个完整轮次），再按年份和月份统计轮次总数；
• 按Env维度拆分统计（例如同一del_no的轮次包含PROD和TEST环境，则该计数归为PROD/TEST类别）；
• 若同一del_no的轮次起始于某月、结束于次月，计数需归至起始月份。

期望输出结果 #

Month   |       Env     | Counts |
--------+---------------+--------+
2022-10 | PROD/TEST     |  1     |
2022-10 | PROD          |  2     |
2022-11 | PROD/TEST     |  1     |
2022-11 | PROD/TEST/DEV |  1     |
2023-10 | PROD          |  1     |
2023-10 | PROD/DEV      |  1     |

当前查询的问题 #

现有查询按单条记录的start_datetimestamp月份分组，无法处理跨月轮次的统计，导致跨月的轮次会被拆分到不同月份，不符合要求。

select "Month",sum(rounds)"Counts","Env" from (
    select del_no,
           to_char(start_datetimestamp,'YYYY-MM') "Month",
           least( count(*)filter(where direction='SOUTH')
                 ,count(*)filter(where direction='NORTH')) rounds,
           string_agg(distinct env,'/' order by env) "Env"
    from tablename where pkt='2' group by del_no,2)
group by "Month","Env";

优化后的查询 #

SELECT
    "Month",
    SUM(rounds) AS "Counts",
    "Env"
FROM (
    SELECT
        del_no,
        -- 取当前del_no最早的时间作为轮次起始月份
        TO_CHAR(MIN(start_datetimestamp), 'YYYY-MM') AS "Month",
        -- 计算完整轮次数量：NORTH和SOUTH的最小出现次数
        LEAST(
            COUNT(*) FILTER (WHERE direction = 'SOUTH'),
            COUNT(*) FILTER (WHERE direction = 'NORTH')
        ) AS rounds,
        -- 聚合去重后的Env，按顺序拼接
        STRING_AGG(DISTINCT env, '/' ORDER BY env) AS "Env"
    FROM tablename
    WHERE pkt = '02' -- 匹配原始数据中的Pkt值
    GROUP BY del_no
) AS del_no_summary
GROUP BY "Month", "Env"
ORDER BY "Month", "Env";

关键改动说明 #

1. 内层查询不再按单条记录的月份分组，而是按del_no分组后，用MIN(start_datetimestamp)获取该轮次的最早时间，以此确定轮次归属的月份，满足跨月轮次归至起始月的要求；
2. 保留原有的轮次数量计算逻辑（取NORTH和SOUTH出现次数的最小值）；
3. 维持按Env维度聚合拼接的逻辑，确保同一del_no的多环境被正确归类。

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