SQL的 SUM函数没有对数值进行求和

编程语言 2026-07-11

我正尝试使用子查询，把下面的数据整理成4 个临时的、可引用的列。

events_key	person_name	person_key	event_percentile	event_level
1	A	1	50	composite
2	A	1	75	composite
3	A	1	25	growth
4	B	2	10	composite
5	B	2	9	composite
6	B	2	15	composite
7	B	2	-1	growth
8	B	2	6	growth
9	B	2	5	growth
10	C	3	4	composite

SELECT 
    dtbl_person.person_name AS "person"
    ,ftbl_event_norms.Achievement_exercise AS "achievement sum"
    ,ftbl_event_norms.total_Achievement_exercise AS "achievement count"
    ,ftbl_event_norms.Growth_exercise AS "growth sum"
    ,ftbl_event_norms.total_Growth_exercise AS "growth count"
FROM
    (
        SELECT
            ftbl_event_norms.person_key
            ,ftbl_event_norms.events_key
            ,SUM(CASE WHEN dtbl_events.event_company = 'exercise' AND dtbl_events.event_level = 'Growth' THEN ftbl_event_norms.event_percentile ELSE 0 END) Growth_exercise
            ,SUM(CASE WHEN dtbl_events.event_company = 'exercise' AND dtbl_events.event_level = 'Composite' THEN ftbl_event_norms.event_percentile ELSE 0 END) Achievement_exercise
            ,SUM(CASE WHEN dtbl_events.event_company = 'exercise' AND dtbl_events.event_level = 'Growth' THEN 1 ELSE 0 END) total_Growth_exercise
            ,SUM(CASE WHEN dtbl_events.event_company = 'exercise' AND dtbl_events.event_level = 'Composite' THEN 1 ELSE 0 END) total_Achievement_exercise
        FROM
            datawarehouse.ftbl_event_norms
            INNER JOIN datawarehouse.dtbl_events ON dtbl_events.events_key = ftbl_event_norms.events_key
        GROUP BY
            ftbl_event_norms.person_key
            ,ftbl_event_norms.events_key
    ) 
    ftbl_event_norms
    INNER JOIN datawarehouse.dtbl_persons ON ftbl_event_norms.person_key = dtbl_persons.person_key
    INNER JOIN datawarehouse.dtbl_events ON dtbl_events.events_key = ftbl_event_norms.events_key
WHERE
    (
    dtbl_events.event_topic = 'quantitative'
    )
ORDER BY
    dtbl_persons.person_name

我得到的结果是

姓名	成就总和	成就计数	增长总和	增长计数
A	50	0	0	0
A	0	0	0	0
A	0	1	0	0
A	0	0	25	0
A	75	0	0	0
A	0	0	0	1
A	0	1	0	0

我想要

姓名	成就总和	成就计数	增长总和	增长计数
A	125	2	25	1
B	34	3	10	3
C	4	1	0	0

显然的问题是这些列没有进行求和，但我看不出显示的行数与表中符合筛选条件的信息之间有怎样的相关性。举例来说，对于人员A，总共有45行的event_topic = 'quantitative'，或者总共有16行的event_level = 'growth' 或event_level = 'composite'，而表格却显示了30行。

解决方案

结果中全为0 的行应该来自于非匹配行。具有和但计数为0 的行，按照你展示的代码是不可能的。你之所以没有得到每个人一行结果，是因为在你的 GROUP BY 子句中，明确要求每个人和每个事件只有一行结果。

解决方案：直接对连接后的行进行过滤并按个人分组。如果你想包含没有任意匹配数据的人员，需要将此结果做外连接到persons表；否则就执行内连接。

SELECT 
  p.person_name,
  COALESCE(x.gr_pct, 0) AS growth_percent,
  COALESCE(x.gr_cnt, 0) AS growth_count,
  COALESCE(x.co_pct, 0) AS composite_percent,
  COALESCE(x.co_cnt, 0) AS composite_count
FROM datawarehouse.dtbl_persons p
LEFT JOIN
(
  SELECT
    en.person_key,
    SUM(CASE WHEN e.event_level = 'Growth' THEN en.event_percentile END) AS gr_pct,
    SUM(CASE WHEN e.event_level = 'Growth' THEN 1 END) AS gr_cnt,
    SUM(CASE WHEN e.event_level = 'Composite' THEN en.event_percentile END) AS co_pct,
    SUM(CASE WHEN e.event_level = 'Composite' THEN 1 END) AS co_cnt
  FROM datawarehouse.ftbl_event_norms en
  JOIN datawarehouse.dtbl_events e ON e.events_key = en.events_key
  WHERE e.event_company = 'exercise'
    AND e.event_topic = 'quantitative'
    AND e.event_level IN ('Composite', 'Growth')
  GROUP BY en.person_key
 ) x ON x.person_key = p.person_key
ORDER BY p.person_name;

SQL