如何关联表并编写SQL统计活跃/非活跃用户及非活跃占比

核心问题分析 #

你的现有查询存在三个关键问题：

1. 重复计数：直接用COUNT(orders.customer_id)会重复统计同一客户的多个订单项，导致用户数虚高。
2. 交叉连接产生冗余数据：将两个子查询用逗号拼接会触发笛卡尔积，生成大量重复行。
3. 占比计算逻辑错误：未处理SQL整数除法的精度问题，也未明确总用户数的统计范围。

优化后的SQL查询 #

采用条件聚合方式，在单查询内完成所有统计，彻底避免连接带来的重复问题：

WITH customer_product_activity AS (
    SELECT 
        oi.product_id,
        o.customer_id,
        MAX(o.order_date) AS last_order_date
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY oi.product_id, o.customer_id
)
SELECT 
    product_id,
    COUNT(CASE WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 day' THEN 1 END) AS active_cust,
    COUNT(CASE WHEN last_order_date < CURRENT_DATE - INTERVAL '90 day' THEN 1 END) AS inactive_cust,
    ROUND(
        COUNT(CASE WHEN last_order_date < CURRENT_DATE - INTERVAL '90 day' THEN 1 END)::NUMERIC 
        / COUNT(*) * 100, 
        2
    ) AS inactive_percentage
FROM customer_product_activity
GROUP BY product_id
ORDER BY product_id;

关键逻辑说明 #

1. 去重统计客户：通过CTEcustomer_product_activity按产品和客户分组，获取每个客户对应产品的最后下单日期，确保每个客户在单个产品下仅被统计一次。
2. 条件聚合区分活跃/非活跃：
   • active_cust：统计最近90天内有该产品下单记录的客户数。
   • inactive_cust：统计最近90天内无该产品下单记录，但历史有过下单的客户数。
3. 非活跃占比计算：
   • 用::NUMERIC将整数转换为数值类型，避免SQL整数除法导致的精度丢失。
   • 计算非活跃用户数占该产品总用户数的百分比，并保留两位小数。

额外说明 #

• 若你对"非活跃用户"的定义是最近90天未下单任何产品的客户（而非针对单个产品），则需调整逻辑为基于全量订单的客户活跃状态，而非按产品分组。
• 若存在独立的customers表，可加入查询以包含从未下单的客户（你的问题中未提及该表，故未纳入）。

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