ORA-00937错误排查：SQL连续登录占比查询原语句报错原因

问题场景 #

需求是计算Activity表（主键为player_id和event_date）中，首次登录次日又登录的玩家占比，结果保留两位小数。编写的原SQL触发ORA-00937错误，改用WITH子句重构后运行正常，需明确报错原因。

原报错SQL #

select 
    round(count(a1.player_id) / (select count(distinct player_id) as cnt from activity a3), 2)
from
    (select 
         activity.*, 
         row_number() over (partition by player_id order by event_date) as rn
     from 
         activity) a1 
join 
    activity a2 on a1.rn = 1 
                and a2.event_date - a1.event_date = 1 
                and a1.player_id = a2.player_id;

正确SQL #

with retained as (
  select count(*) as ret
  from
  (
      select activity.*, row_number() over(partition by player_id order by event_date) as rn
      from activity
  ) a1 join activity a2
  on
  a1.rn = 1 and
  a2.event_date - a1.event_date = 1 and
  a1.player_id = a2.player_id
),
total as (
  select count(distinct player_id) as cnt
  from activity
)
select distinct round(ret/cnt, 2) as fraction from retained, total;

报错原因 #

ORA-00937错误的核心是非单组分组函数使用违规，具体到原SQL的问题：

1. 原SQL的FROM子句通过子查询+表连接返回的是多行结果集（所有满足“首次登录次日又登录”的玩家记录，若玩家次日多次登录会对应多行）。
2. SELECT子句中使用了聚合函数count(a1.player_id)对上述多行结果集进行计数，但未指定GROUP BY子句。Oracle语法规则要求：当SELECT列表包含聚合函数，且FROM子句返回多行数据时，必须通过GROUP BY明确分组依据——哪怕逻辑上是对整个结果集做单组聚合，Oracle也需要明确的分组声明（比如GROUP BY NULL）。

为什么正确SQL能运行 #

正确SQL通过WITH子句拆分了计算逻辑：

• retained子句先计算满足条件的玩家数（count(*)返回单行结果）；
• total子句计算总玩家数（同样返回单行结果）；
• 最后将两个单行表做交叉连接，此时SELECT子句只是对两个单行的列进行数值运算，不需要聚合函数或GROUP BY，完全符合Oracle的语法要求。

原SQL的修复方案 #

如果不想用WITH子句，只需在原SQL末尾添加GROUP BY NULL即可解决报错：

select 
    round(count(a1.player_id) / (select count(distinct player_id) as cnt from activity a3), 2)
from
    (select 
         activity.*, 
         row_number() over (partition by player_id order by event_date) as rn
     from 
         activity) a1 
join 
    activity a2 on a1.rn = 1 
                and a2.event_date - a1.event_date = 1 
                and a1.player_id = a2.player_id
group by null;

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