SQL问题：如何基于最新LAST_DATE统计指定Level 2 Manager的员工部门数据

需求说明 #

需要按以下条件统计员工数量：

• 仅获取最新LAST_DATE的记录
• 筛选Level_2_MNGR为'L2M__B'的记录
• 仅统计ACK_STATUS为'N'的记录
• 按Level_1_MNGR、ASSIGN_DATE分组，统计各部门（DIV_A/DIV_B/DIV_C）的员工数

原始数据表 #

table : tbl_name
+-------+-----------+--------------+---------------+--------------+-------------------+-------------------+----------------+------------+---------------+
|    id |    name   | Level_1_MNGR |  Level_2_MNGR | Level_3_MNGR | FORM_NAME         | ASSIGN_DATE       | LAST_DATE      | ACK_STATUS | DIVISION_NAME |
+-------+-----------+--------------+---------------+--------------+-------------------+-------------------+----------------+------------+---------------+
|    1  |    EMP_1  | L1M_NAME_A   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |
|    2  |    EMP_2  | L1M_NAME_A   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | N          | DIV_B         |
|    3  |    EMP_3  | L1M_NAME_A   | L2M__B        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |
|    4  |    EMP_4  | L1M_NAME_B   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |
|    5  |    EMP_5  | L1M_NAME_B   | L2M__B        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | N          | DIV_B         |
|    6  |    EMP_6  | L1M_NAME_B   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | N          | DIV_A         |
|    7  |    EMP_7  | L1M_NAME_B   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |   
|    8  |    EMP_8  | L1M_NAME_C   | L2M__B        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_B         |
|    9  |    EMP_9  | L1M_NAME_C   | L2M__B        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         |
|    10 |    EMP_10 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         |
|    11 |    EMP_11 | L1M_NAME_A   | L2M__A        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | Y          | DIV_A         |
|    12 |    EMP_12 | L1M_NAME_A   | L2M__A        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         | 
|    13 |    EMP_13 | L1M_NAME_A   | L2M__B        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_A         |
|    14 |    EMP_14 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | Y          | DIV_A         |
|    15 |    EMP_15 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_01_jan_2024  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         |
|    16 |    EMP_16 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_01_jan_2024  | JAN 1st, 2024     | FEB 1st, 2024  | N          | DIV_A         |
|    17 |    EMP_17 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | Y          | DIV_A         |
|    18 |    EMP_18 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | Y          | DIV_B         |
|    19 |    EMP_19 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | N          | DIV_C         | 
|    20 |    EMP_20 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | N          | DIV_B         |
+-------+-----------+--------------+---------------+--------------+-------------------+-------------------+-------------------+------------+------------+

原始SQL问题 #

原始SQL存在以下问题：

1. 使用COUNT统计匹配条件的记录数错误，COUNT会把非NULL值都计为1，无法准确统计目标部门数量
2. 分组维度错误，预期结果需要按Level_1_MNGR和ASSIGN_DATE分组，而非仅按Level_1_MNGR
3. 模糊匹配ILIKE无必要，部门名称为固定值，精确匹配更高效

SELECT MAX("ASSIGN_DATE") AS assignDate, 
MAX("LAST_DATE") AS lastDate, 
MAX("Level_1_MNGR") AS lvl_one_mngr_name,
COUNT("DIVISION_NAME" ILIKE '%DIV_A%') as DIV_A,
COUNT("DIVISION_NAME" ILIKE '%DIV_B%') as DIV_B,
COUNT("DIVISION_NAME" ILIKE '%DIV_C%') as DIV_C
FROM tbl_name
INNER JOIN (SELECT MAX("LAST_DATE") AS lastDate FROM tbl_name) subq 
ON "tbl_name"."LAST_DATE" = subq.lastDate
WHERE "ACK_STATUS" = "N"
AND "Level_2_MNGR" = 'L2M__B'
GROUP BY "Level_1_MNGR"

修正后的SQL #

SELECT 
    "Level_1_MNGR" AS lvl_one_mngr_name,
    "ASSIGN_DATE",
    "LAST_DATE",
    SUM(CASE WHEN "DIVISION_NAME" = 'DIV_A' THEN 1 ELSE 0 END) AS div_a,
    SUM(CASE WHEN "DIVISION_NAME" = 'DIV_B' THEN 1 ELSE 0 END) AS div_b,
    SUM(CASE WHEN "DIVISION_NAME" = 'DIV_C' THEN 1 ELSE 0 END) AS div_c
FROM tbl_name
WHERE 
    "Level_2_MNGR" = 'L2M__B'
    AND "ACK_STATUS" = 'N'
    AND "LAST_DATE" = (SELECT MAX("LAST_DATE") FROM tbl_name)
GROUP BY "Level_1_MNGR", "ASSIGN_DATE", "LAST_DATE"
ORDER BY "Level_1_MNGR", "ASSIGN_DATE"

关键修正点 #

1. 用SUM(CASE ...)替代COUNT：通过CASE语句对匹配的部门返回1，否则返回0，SUM可准确统计对应部门的员工数
2. 调整分组字段：按Level_1_MNGR、ASSIGN_DATE、LAST_DATE分组，确保同一主管同一分配日期的记录单独统计
3. 简化最新日期过滤逻辑：直接在WHERE子句中匹配全局最大LAST_DATE，无需子查询关联
4. 改用精确匹配：部门名称为固定值，用=替代ILIKE提升查询效率

预期输出 #

"levelTwoManager": "L2M__B" desired data
+-------------------+--------------+------------+-------+-------+-------+
| lvl_one_mngr_name |  ASSIGN_DATE | LAST_DATE  | div_a | div_b | div_c |
+-------------------+--------------+------------+-------+-------+-------+
| L1M_NAME_A        | DEC 1st, 2023| FEB 1st, 2024|   1   |   0   |   0   |
| L1M_NAME_C        | DEC 1st, 2023| FEB 1st, 2024|   0   |   2   |   0   |
| L1M_NAME_C        | JAN 15th, 2024| FEB 1st, 2024|   0   |   1   |   1   |
+-------------------+--------------+------------+-------+-------+-------+

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