MySQL同表两日期分组计数相减结果异常求助

原SQL的核心错误 #

你的SQL里减号的位置完全错了——它被塞在了第一个子查询的WHERE条件末尾，数据库会把'2022-10-14' - (select count(...))当成一个数值计算，而不是两个用户计数的差值。比如第二个子查询返回的count是X，数据库会把日期'2022-10-14'转成数值后减去X，得到一个完全无效的日期值，导致第一个子查询匹配不到任何数据，最终结果自然是0。

修正后的SQL写法 #

写法1：子查询正确相减 #

把两个独立的子查询用括号包裹后再做减法，逻辑清晰：

select 
    (select count(user_id)
     from weekly_customer_RFM_TABLE
     where SEGMENT = 'champion'
       and SEGMENTED_DATE = '2022-10-14')
    -
    (select count(user_id)
     from weekly_customer_RFM_TABLE
     where SEGMENT = 'champion'
       and SEGMENTED_DATE = '2022-10-07') as total_changes

写法2：单表扫描更高效（推荐） #

只扫一次表，用CASE语句分别统计两个日期的用户数再计算差值，性能比两次子查询更好：

select 
    sum(case when SEGMENTED_DATE = '2022-10-14' then 1 else 0 end)
    -
    sum(case when SEGMENTED_DATE = '2022-10-07' then 1 else 0 end) as total_changes
from weekly_customer_RFM_TABLE
where SEGMENT = 'champion'
  and SEGMENTED_DATE in ('2022-10-14', '2022-10-07')

额外验证步骤 #

先单独跑两个子查询，确认各自的用户数：

-- 查2022-10-14的champion用户数
select count(user_id) from weekly_customer_RFM_TABLE where SEGMENT='champion' and SEGMENTED_DATE='2022-10-14';
-- 查2022-10-07的champion用户数
select count(user_id) from weekly_customer_RFM_TABLE where SEGMENT='champion' and SEGMENTED_DATE='2022-10-07';

手动算一下两者的差值，确认是不是45，排除数据本身的问题。

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