SQL中累积群组（Cumulated Cohorts）的累积订单百分比自动计算方案问询

架构老林

2026-4-29

动态计算Cohort累积订单百分比的解决方案

当然有更优雅的方法！完全不用手动写一堆CASE语句，我们可以用SQL窗口函数来动态获取每个cohort的初始订单数（month=0时的cohort orders），然后自动计算累积订单的百分比。

核心思路

每个cohort的基准值是它在month=0时的cohort orders，我们需要为每一行数据匹配对应cohort的这个基准值，然后用cumulated orders除以基准值，转换为百分比格式。窗口函数可以帮我们按cohort分组，自动提取每个组的基准值。

实现代码

假设你的原始表名为cohort_analytics，下面两种写法都可以实现需求：

方法1：使用`FIRST_VALUE`窗口函数

FIRST_VALUE会按指定排序规则取分组内的第一个值，这里我们按month升序排序，就能拿到每个cohort的month=0的订单数：

SELECT
    cohort,
    month,
    cohort_orders,
    cumulated_orders,
    -- 计算百分比并格式化为整数百分形式
    CONCAT(ROUND((cumulated_orders * 100.0) / FIRST_VALUE(cohort_orders) OVER (PARTITION BY cohort ORDER BY month), 0), '%') AS cumulated_in_%
FROM cohort_analytics;

方法2：使用`MAX(CASE...)`窗口函数

如果你的SQL方言对FIRST_VALUE支持有限，也可以用这种方式明确筛选month=0的订单数作为基准：

SELECT
    cohort,
    month,
    cohort_orders,
    cumulated_orders,
    CONCAT(ROUND((cumulated_orders * 100.0) / MAX(CASE WHEN month = 0 THEN cohort_orders END) OVER (PARTITION BY cohort), 0), '%') AS cumulated_in_%
FROM cohort_analytics;