PostgreSQL月度客户留存率计算分组报错求助

我正在开展个人项目，使用PGAdmin 4（v6.19，Windows 10桌面版）计算年度（1-12月）月度客户留存率，按客户首次产生收入的月份分组统计留存百分比以分析用户行为。当前执行SQL时出现分组相关报错，无法得到预期的群组留存百分比结果。

数据表结构 #

CREATE TABLE customer_revenue (
    customer_name varchar (100),
    revenue_jan DECIMAL(10,0),
    revenue_feb DECIMAL(10,0),
    revenue_mar DECIMAL(10,0),
    revenue_april DECIMAL(10,0),
    revenue_may DECIMAL(10,0),
    revenue_june DECIMAL(10,0),
    revenue_july DECIMAL(10,0),
    revenue_aug DECIMAL(10,0),
    revenue_sept DECIMAL(10,0),
    revenue_oct DECIMAL(10,0),
    revenue_nov DECIMAL(10,0),
    revenue_dec DECIMAL(10,0));

数据导入语句 #

COPY customer_revenue (customer_name, revenue_jan, revenue_feb, revenue_mar, revenue_april, revenue_may, revenue_june, revenue_july, revenue_aug, revenue_sept, revenue_oct, revenue_nov, revenue_Dec)
-- 替换为实际文件路径
FROM '[MY DIRECTORY]' 
WITH (FORMAT CSV, HEADER);

当前使用的SQL代码 #

WITH cohort_items AS (
  SELECT
    customer_name,
    CASE
      WHEN revenue_jan > 0 THEN 'January'
      WHEN revenue_feb > 0 THEN 'February'
      WHEN revenue_mar > 0 THEN 'March'
      WHEN revenue_april > 0 THEN 'April'
      WHEN revenue_may > 0 THEN 'May'
      WHEN revenue_june > 0 THEN 'June'
      WHEN revenue_july > 0 THEN 'July'
      WHEN revenue_aug > 0 THEN 'August'
      WHEN revenue_sept > 0 THEN 'September'
      WHEN revenue_oct > 0 THEN 'October'
      WHEN revenue_nov > 0 THEN 'November'
      WHEN revenue_dec > 0 THEN 'December'
    END AS cohort_month
  FROM customer_revenue
),
cohort_size AS (
  SELECT cohort_month, COUNT(DISTINCT customer_name) AS num_customers
  FROM cohort_items
  GROUP BY 1
  ORDER BY MIN(CASE cohort_month
                  WHEN 'January' THEN 1
                  WHEN 'February' THEN 2
                  WHEN 'March' THEN 3
                  WHEN 'April' THEN 4
                  WHEN 'May' THEN 5
                  WHEN 'June' THEN 6
                  WHEN 'July' THEN 7
                  WHEN 'August' THEN 8
                  WHEN 'September' THEN 9
                  WHEN 'October' THEN 10
                  WHEN 'November' THEN 11
                  WHEN 'December' THEN 12
              END)
),
B AS (
  SELECT
    C.cohort_month,
    COUNT(DISTINCT C.customer_name) AS num_customers
  FROM cohort_items C
  WHERE EXISTS (
    SELECT 1
    FROM cohort_items
    WHERE cohort_month = C.cohort_month
      AND customer_name = C.customer_name  )
  GROUP BY C.cohort_month
)
SELECT
  B.cohort_month,
  S.num_customers AS total_customers,
  (B.num_customers::float / S.num_customers::float) * 100 AS percentage
FROM B
LEFT JOIN cohort_size S ON B.cohort_month = S.cohort_month
WHERE B.cohort_month IS NOT NULL
GROUP BY b.cohort_month
ORDER BY MIN(CASE B.cohort_month
                WHEN 'January' THEN 1
                WHEN 'February' THEN 2
                WHEN 'March' THEN 3
                WHEN 'April' THEN 4
                WHEN 'May' THEN 5
                WHEN 'June' THEN 6
                WHEN 'July' THEN 7
                WHEN 'August' THEN 8
                WHEN 'September' THEN 9
                WHEN 'October' THEN 10
                WHEN 'November' THEN 11
                WHEN 'December' THEN 12
            END);

数据示例 #

问题分析 #

原SQL存在两个核心问题：

1. 分组规则违反：最终SELECT语句中GROUP BY b.cohort_month，但查询字段包含S.num_customers，该字段未在GROUP BY中，也未使用聚合函数，触发PostgreSQL分组报错。
2. 逻辑错误：CTE B 的存在查询完全冗余，仅重复统计了每个群组的初始客户数，和cohort_size结果一致，根本没有计算"留存"——留存率需要统计群组客户在后续月份仍有收入的数量，而非仅统计群组本身的客户数。

修正后的SQL代码 #

WITH customer_monthly_revenue AS (
    -- 将宽表转为长表，简化留存计算逻辑
    SELECT customer_name, 'January' AS month, revenue_jan AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'February' AS month, revenue_feb AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'March' AS month, revenue_mar AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'April' AS month, revenue_april AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'May' AS month, revenue_may AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'June' AS month, revenue_june AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'July' AS month, revenue_july AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'August' AS month, revenue_aug AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'September' AS month, revenue_sept AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'October' AS month, revenue_oct AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'November' AS month, revenue_nov AS revenue FROM customer_revenue
    UNION ALL
    SELECT customer_name, 'December' AS month, revenue_dec AS revenue FROM customer_revenue
),
cohort_assignments AS (
    -- 标记每个客户的首次收入月份（所属群组）
    SELECT customer_name, MIN(month) AS cohort_month
    FROM customer_monthly_revenue
    WHERE revenue > 0
    GROUP BY customer_name
),
cohort_size AS (
    -- 统计每个群组的初始客户总数
    SELECT cohort_month, COUNT(DISTINCT customer_name) AS total_customers
    FROM cohort_assignments
    GROUP BY cohort_month
),
customer_retention AS (
    -- 统计每个群组在各月份的留存客户数
    SELECT
        ca.cohort_month,
        cmr.month AS retention_month,
        COUNT(DISTINCT cmr.customer_name) AS retained_customers
    FROM customer_monthly_revenue cmr
    JOIN cohort_assignments ca ON cmr.customer_name = ca.customer_name
    WHERE cmr.revenue > 0
    GROUP BY ca.cohort_month, cmr.month
)
-- 计算各群组在各月份的留存百分比
SELECT
    cr.cohort_month,
    cr.retention_month,
    cs.total_customers,
    cr.retained_customers,
    ROUND((cr.retained_customers::FLOAT / cs.total_customers::FLOAT) * 100, 2) AS retention_percentage
FROM customer_retention cr
JOIN cohort_size cs ON cr.cohort_month = cs.cohort_month
ORDER BY
    -- 按群组月份顺序排序
    CASE cr.cohort_month
        WHEN 'January' THEN 1 WHEN 'February' THEN 2 WHEN 'March' THEN 3
        WHEN 'April' THEN 4 WHEN 'May' THEN 5 WHEN 'June' THEN 6
        WHEN 'July' THEN 7 WHEN 'August' THEN 8 WHEN 'September' THEN 9
        WHEN 'October' THEN 10 WHEN 'November' THEN 11 WHEN 'December' THEN 12
    END,
    -- 按留存月份顺序排序
    CASE cr.retention_month
        WHEN 'January' THEN 1 WHEN 'February' THEN 2 WHEN 'March' THEN 3
        WHEN 'April' THEN 4 WHEN 'May' THEN 5 WHEN 'June' THEN 6
        WHEN 'July' THEN 7 WHEN 'August' THEN 8 WHEN 'September' THEN 9
        WHEN 'October' THEN 10 WHEN 'November' THEN 11 WHEN 'December' THEN 12
    END;

代码说明 #

1. 宽表转长表：通过UNION ALL将每个月份的收入数据转为行记录，避免原宽表结构带来的逻辑复杂度。
2. 确定群组归属：为每个客户标记首次产生收入的月份，作为其所属群组。
3. 统计群组规模：计算每个初始群组的客户总数，作为留存率计算的分母。
4. 统计留存客户：按群组和月份统计仍有收入的客户数量，作为留存率计算的分子。
5. 计算留存百分比：用留存客户数除以群组初始客户数，得到留存率并保留两位小数，结果按群组和月份顺序排序。

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