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 |

统计要求 #

• 仅筛选Pkt = 02的数据；
• 按del_no分组，同时包含NORTH和SOUTH方向视为一个完整轮次，再按月份统计轮次总数；
• 按Env维度区分统计结果（例：第1、3行属于一个完整轮次，Env包含PROD和TEST，该轮次计入PROD/TEST分类）。

预期输出 #

Month   |       Env     | Counts |
--------+---------------+--------+
 Oct    | PROD/TEST     |  1     |
 Oct    | PROD          |  2     |
 Nov    | PROD/TEST     |  1     |
 Nov    | PROD/TEST/DEV |  1     |

实现SQL语句 #

WITH valid_rotations AS (
    SELECT 
        del_no,
        TO_CHAR(start_datetimestamp, 'Mon') AS Month,
        STRING_AGG(DISTINCT Env, '/' ORDER BY Env) AS Env_group,
        COUNT(DISTINCT direction) AS dir_count
    FROM your_table
    WHERE Pkt = '02'
    GROUP BY del_no, TO_CHAR(start_datetimestamp, 'Mon')
    HAVING COUNT(DISTINCT direction) = 2
)
SELECT 
    Month,
    Env_group AS Env,
    COUNT(*) AS Counts
FROM valid_rotations
GROUP BY Month, Env_group
ORDER BY 
    CASE Month 
        WHEN 'Oct' THEN 1 
        WHEN 'Nov' THEN 2 
    END,
    Env;

逻辑说明 #

1. 通过CTE筛选出Pkt=02的记录，按del_no和月份分组，统计每个组内的不同方向数量，仅保留同时包含NORTH和SOUTH两个方向的有效轮次，并将组内的Env值去重后按顺序拼接成分类字符串；
2. 基于CTE的结果，再按月份和Env分类分组，统计每个分类下的轮次总数，最后按月份排序输出。

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