SQL count(condition) over(partition by)用法:统计每日非封禁用户取消订单数
代码问题定位
- 窗口函数执行顺序与聚合逻辑错位:SQL的标准执行顺序中,窗口函数是在
GROUP BY阶段完成后才会运行的。当代码按天完成分组聚合后,每个天的分组只会返回1行汇总数据,此时再基于天做分区窗口计算,每个分区仅包含1行数据,无论这行对应什么订单状态,COUNT()的结果都会固定为1,本质上根本没有实现对当天所有订单的计数逻辑,只是刚好2013-10-01、2013-10-03当天存在取消订单,结果巧合符合预期。 - 计数写法错误:即便不考虑执行顺序的问题,
COUNT(Status != 'completed')也无法实现未完成订单的计数。COUNT()仅忽略参数值为NULL的行,Status != 'completed'的判断结果只会是0或1,均为非空值,因此所有满足条件的行——无论订单是已完成还是已取消——都会被计入总数,根本没有过滤掉已完成订单。 - 异常结果的根因:2013-10-02统计结果为1的偏差,本质是当天存在1条满足“乘客未封禁、司机未封禁”条件的已完成订单。因为代码在按天分组后,仅基于这一行数据做窗口计数,无论这行的状态是什么,都会返回统计值1,刚好和当天无取消订单的实际情况形成误差。
修正后可正常运行的代码
SELECT t.Request_at AS 'Day', -- 仅对非完成状态的订单累计计数,已完成订单计0不影响统计 SUM(CASE WHEN t.Status != 'completed' THEN 1 ELSE 0 END) AS 'Cancellation Num' FROM Trips t INNER JOIN Users u1 ON t.Client_Id = u1.Users_Id AND u1.Banned = 'No' INNER JOIN Users u2 ON t.Driver_Id = u2.Users_Id AND u2.Banned = 'No' WHERE t.Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY t.Request_at
优化说明
- 去掉冗余的窗口函数逻辑,完全依靠
GROUP BY按天做粒度清晰的聚合,计算逻辑无交叉,不会出现行级统计错位。 - 用条件求和替代错误的计数写法,精准匹配“仅统计未完成订单”的需求,已完成的订单不会被纳入统计值。
- 如果需要满足“当天没有任何有效订单时,取消数默认显示为0”的需求,可以预先生成覆盖全统计周期的日期列表,左连接订单聚合结果,避免因当天无匹配数据导致查询结果缺行。
内容的提问来源于stack exchange,提问作者Yihao Mao
相关产品推荐
相关产品推荐

