SQLite使用CASE语句做交易频次分箱统计时如何避免两层CTE

原有的两层嵌套CTE实现存在无意义的冗余聚合，完全可以通过逻辑下钻、去掉重复计算的方式压缩为更短、执行效率更高的结构。

原实现的冗余问题 #

• 第一层内层CTE已经完成了单用户维度的交易数统计
• 第二层CTE基于交易数做CASE分箱后，已经按分箱维度完成了首次聚合，得到了每个分箱对应的用户计数
• 最外层再嵌套一层查询对分箱结果做GROUP BY和SUM求和，本质是对已经聚合完成的单值行做二次汇总——同一分箱值在上一步输出中仅会对应一行，求和结果和原计数值完全一致，属于完全多余的计算步骤

优化后的实现方案 #

我们可以把分箱逻辑直接整合到用户交易数统计的环节，去掉所有冗余嵌套，整体仅需一层CTE即可完成全部逻辑，代码完全适配SQLite执行环境：

WITH user_trx_bins AS (
  SELECT
    -- 对单个用户的交易数直接做分箱映射
    CASE
      WHEN COUNT(DISTINCT id) < 11 THEN CAST(COUNT(DISTINCT id) AS TEXT)
      ELSE '+10'
    END AS n_trx
  FROM trx
  GROUP BY p_id
)
-- 直接统计每个分箱下的用户总数即可
SELECT n_trx, COUNT(*) AS bin_size
FROM user_trx_bins
GROUP BY n_trx;

如果追求更极致的简洁，也可以去掉CTE改用内联子查询实现，逻辑完全等价：

SELECT
  CASE
    WHEN trx_cnt < 11 THEN CAST(trx_cnt AS TEXT)
    ELSE '+10'
  END AS n_trx,
  COUNT(*) AS bin_size
FROM (
  SELECT p_id, COUNT(DISTINCT id) AS trx_cnt
  FROM trx
  GROUP BY p_id
) user_stats
GROUP BY n_trx;

落地注意细节 #

• 适配SQLite弱类型特性：分箱输出的n_trx字段需要把数字类型的交易笔数统一转为文本类型，避免数字、字符串类型混存导致分组逻辑出错
• 直方图展示需要按分箱值从小到大排序，可以在查询末尾补充排序规则，避免默认字符串排序把+10分箱排到最靠前的位置：

ORDER BY CASE WHEN n_trx = '+10' THEN 999 ELSE CAST(n_trx AS INTEGER) END

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