SQL中SUM(1) OVER与SUM(COUNT(*)) OVER的结果差异原因解析

表结构与测试数据 #

CREATE TABLE IF NOT EXISTS `games` (
  `date` DATE,
  `item_id` varchar(40),
  `player_id` varchar(40)
);
INSERT INTO `games` (`date`, `item_id`, `player_id`) VALUES
  ('2023-01-03', 'raven', 'david'),
  ('2023-01-04', 'folly', 'david'),
  ('2023-01-05', 'syd', 'david'),
  ('2023-01-05', 'syd', 'ire'),
  ('2023-01-01', 'raven', 'jane'),
  ('2023-01-03', 'syd', 'jane'),
  ('2023-01-03', 'folly', 'harry'),
  ('2023-01-10', 'syd', 'harry'),
  ('2023-01-10', 'syd', 'yvette')
;

两个查询及结果对比 #

查询1 #

SELECT
  g.date,
  g.item_id,
  COUNT(*) AS item_games,
  SUM(1) OVER (PARTITION BY g.date) AS total_matches
FROM games g
GROUP BY g.date, g.item_id
ORDER BY g.date, g.item_id;

查询结果：

2023-01-01  raven   1   1
2023-01-03  folly   1   3
2023-01-03  raven   1   3
2023-01-03  syd     1   3
2023-01-04  folly   1   1
2023-01-05  syd     2   1
2023-01-10  syd     2   1

查询2 #

SELECT
  g.date,
  g.item_id,
  COUNT(*) AS item_games,
  SUM(count(*)) OVER (PARTITION BY g.date) AS item_game_ratio
FROM games g
GROUP BY g.date, g.item_id
ORDER BY g.date, g.item_id;

查询结果：

2023-01-01  raven   1   1
2023-01-03  folly   1   3
2023-01-03  raven   1   3
2023-01-03  syd     1   3
2023-01-04  folly   1   1
2023-01-05  syd     2   2
2023-01-10  syd     2   2

差异原因解析 #

核心区别在于窗口函数计算的对象不同，且窗口函数是在GROUP BY执行之后运行的：

1. 查询1的SUM(1) OVER (PARTITION BY g.date)

   • GROUP BY g.date, g.item_id执行后，生成按「日期+物品ID」分组的结果行：2023-01-05只有1行分组结果（该日期所有行都属于syd），2023-01-10同样只有1行分组结果。
   • 窗口函数SUM(1)是对每个日期下的分组行逐行计数，每一行贡献1，所以这两个日期的总和都是1。

2. 查询2的SUM(count(*)) OVER (PARTITION BY g.date)

   • 这里的count(*)是每个分组内的原始行数：2023-01-05的分组对应2条原始数据，count(*)=2；2023-01-10的分组同样count(*)=2。
   • 窗口函数是对每个日期下所有分组的count(*)值求和，所以这两个日期的总和都是2。

简单总结：

• 查询1统计的是「每个日期下有多少个不同的物品分组」
• 查询2统计的是「每个日期下的原始数据总行数」

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