如何用SQL/Tableau每日统计近400天内有2+订单的活跃客户数？

SQL实现方案 #

核心逻辑是先生成需要统计的每日日期序列，再对每个日期计算其往前400天窗口内的用户下单次数，最后筛选出下单次数≥2的用户并按日计数。

步骤与示例代码 #

1. 生成日期序列：先覆盖销售表中所有订单日期的区间（如果需要扩展到未来日期，可调整序列的结束值）。不同SQL方言的日期生成方式略有差异：

   • PostgreSQL示例：

     WITH date_series AS (
         SELECT generate_series(
             (SELECT MIN(order_date) FROM sales),
             (SELECT MAX(order_date) FROM sales),
             INTERVAL '1 day'
         )::DATE AS stat_date
     )
     

   • MySQL示例（递归CTE）：

     WITH RECURSIVE date_series AS (
         SELECT (SELECT MIN(order_date) FROM sales) AS stat_date
         UNION ALL
         SELECT DATE_ADD(stat_date, INTERVAL 1 DAY) FROM date_series
         WHERE stat_date < (SELECT MAX(order_date) FROM sales)
     )
     

2. 计算窗口内用户下单次数并统计活跃客户：
   结合日期序列与销售表，统计每个日期窗口内的用户下单次数，再过滤出符合条件的用户：

   -- 承接上面的date_series CTE
   , user_order_stats AS (
       SELECT
           ds.stat_date,
           s.email,
           COUNT(s.order_id) AS order_count
       FROM date_series ds
       LEFT JOIN sales s
           ON s.order_date BETWEEN DATE_SUB(ds.stat_date, INTERVAL 400 DAY) AND ds.stat_date
       GROUP BY ds.stat_date, s.email
   )
   SELECT
       stat_date,
       COUNT(DISTINCT email) AS active_customers
   FROM user_order_stats
   WHERE order_count >= 2
   GROUP BY stat_date
   ORDER BY stat_date;
   

   注：如果销售表数据量较大，可通过添加order_date和email的联合索引来优化查询性能。

Tableau实现方案 #

通过LOD表达式或表计算，实现每日400天窗口内的用户下单次数统计，最终得到活跃客户数。

步骤 #

1. 准备数据：连接销售表，将order date设置为日期维度。若需要统计无订单的日期，可通过「数据 > 创建数据 > 日期范围」生成完整的日期序列，再与销售表左连接。

2. 创建计算字段：
   创建名为400天内下单次数的计算字段，用INCLUDE LOD表达式计算每个用户在当前日期往前400天内的下单数：

   {INCLUDE [email] : COUNT(IF DATEDIFF('day', [order date], [统计日期]) BETWEEN 0 AND 400 THEN [order id] END)}
   

   （其中[统计日期]可使用视图中的日期维度，或创建参数来指定日期范围）

3. 筛选与可视化：

   • 添加筛选器，将400天内下单次数设置为≥2；
   • 将统计日期拖到行功能区，将email拖到标记卡的「计数（不同）」选项，即可得到每日活跃客户数的可视化结果。

   也可使用表计算替代LOD：将email和order date拖到视图，统计每个用户的下单数后，添加「运行总和」表计算，设置计算依据为email，范围为「过去400天」，再筛选总和≥2的记录。

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