如何将用户付款次数统计转换为累计用户数统计（SQL实现）

原始统计数据 #

字段定义 #

• instances：用户数
• product：套餐类型
• payments：已发生的付款次数

转换逻辑 #

将「完成X次付款的用户数」转换为累计统计（至少完成X次付款的用户数）——例如完成2次付款的用户，需同时被计入完成1次付款的统计中。

预期结果 #

完善后的SQL代码 #

with pay_counts as (
    {{#102}} -- 此处替换为你的原始数据查询（如按用户+套餐统计付款次数的逻辑）
),
plan_pay_counts as (
    select
        plan as product,
        payments,
        count(plan) as instances
    from 
        pay_counts
    group by
        plan, payments
),
-- 生成全局付款次数序列（覆盖所有套餐的最大付款次数）
payment_series as (
    SELECT generate_series(1, max(payments)) as payments
    FROM pay_counts
),
-- 提取所有唯一套餐类型
product_list as (
    SELECT DISTINCT product
    FROM plan_pay_counts
),
-- 生成套餐+付款次数的全量组合（避免遗漏无数据的付款次数行）
product_payment_combinations as (
    SELECT pl.product, ps.payments
    FROM product_list pl
    CROSS JOIN payment_series ps
)
select
    ppc.product,
    ppc.payments,
    -- 累计求和：当前付款次数及以上的用户总数，无数据时显示0
    COALESCE(SUM(ppc2.instances), 0) as instances
from product_payment_combinations ppc
left join plan_pay_counts ppc2
    on ppc.product = ppc2.product
    and ppc2.payments >= ppc.payments
group by ppc.product, ppc.payments
order by ppc.product, ppc.payments;

代码关键逻辑 #

1. 用CROSS JOIN生成每个套餐对应的所有付款次数行，确保不会遗漏像「Professional的1、2次付款」这类原始数据中不存在的行；
2. 左连接原始统计数据，筛选出付款次数≥当前行的记录，求和得到累计用户数；
3. 通过COALESCE将无匹配数据的情况转为0，对齐预期结果格式。

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