带特殊条件的SQL分组计数:按维度统计组内0值与最大值匹配记录数
原始表结构与基础数据
存储表为add_patient,包含字段num、p3、Value1、p22,原始数据如下:
| num | p3 | Value1 | p22 |
|---|---|---|---|
| 1 | Developer1 | 0 | process |
| 2 | Developer1 | 0 | process |
| 3 | programmer | 0 | process1 |
| 4 | programmer | 0 | process1 |
| 5 | programmer | 30 | process1 |
| 6 | programmer | 20 | process1 |
| 7 | programmer | 10 | process1 |
核心统计需求
按p3维度做分组统计,每个(p3, p22)细分组内,仅对两类记录做计数统计:
- 细分组内
Value1 = 0的记录 - 细分组内
Value1取到最大值的记录
其余非0、非组内最大值的冗余记录,不计入统计值。
原有查询的偏差
原有查询语句为:
SELECT p3, SUM(CASE WHEN p22 IS NOT NULL and p22 ='process1' THEN 1 ELSE 0 END) AS process1, SUM(CASE WHEN p22 IS NOT NULL and p22 ='process' THEN 1 ELSE 0 END) AS process FROM add_patient group by (p3)
该查询的执行结果为:
| p3 | process1 | process |
|---|---|---|
| Developer1 | 0 | 2 |
| programmer | 5 | 0 |
偏差点在于:原有查询直接统计了所有匹配p22值的记录数量,没有过滤冗余记录。例如programmer分组下的process1类别,总共有5条记录,按规则仅需统计2条0值记录+1条Value1最大值(30)对应的记录,合计3条,与实际输出的5条不符。
符合需求的实现方案
通过窗口函数先计算每个(p3, p22)细分组的最大Value1值,再筛选出符合统计规则的有效记录,最后做行转列计数,SQL语句如下:
WITH record_with_group_max AS ( -- 按p3、p22拆分细分组,计算每个细分组的Value1最大值 SELECT *, MAX(Value1) OVER (PARTITION BY p3, p22) AS group_max_val FROM add_patient ) SELECT p3, COUNT(CASE WHEN p22 = 'process1' THEN 1 END) AS process1, COUNT(CASE WHEN p22 = 'process' THEN 1 END) AS process FROM record_with_group_max -- 仅保留符合统计规则的有效记录 WHERE Value1 = 0 OR Value1 = group_max_val GROUP BY p3
该语句执行后的预期结果为:
| p3 | process1 | process |
|---|---|---|
| Developer1 | 0 | 2 |
| programmer | 3 | 0 |
完全匹配预期统计规则:programmer分组下process1类别排除了Value1为10、20的冗余记录,仅统计3条有效记录。
内容的提问来源于stack exchange,提问作者mandarin software
相关产品推荐
相关产品推荐

