如何按全量、近4/8/12周多日期范围汇总客户订单数据？

前提说明 #

你的数据表必须包含交易日期字段（假设名为trans_date），否则无法计算近N周的时间范围。以下方案均基于该字段存在的前提。

方案1：Union All 分查询汇总（直观易写） #

这是你最初考虑的方案，写法简单，适合新手或小数据量场景。核心是分别计算四个时间范围的汇总结果，再用UNION ALL合并（用ALL避免去重，提升性能）。

-- 全量数据汇总
SELECT 
  cus_id,
  '全量' AS date_range,
  SUM(`order`) AS sum_order,
  SUM(order_value) AS sum_order_value,
  SUM(refunds) AS sum_refunds
FROM your_table
GROUP BY cus_id

UNION ALL

-- 近4周数据汇总
SELECT 
  cus_id,
  '近4周' AS date_range,
  SUM(`order`) AS sum_order,
  SUM(order_value) AS sum_order_value,
  SUM(refunds) AS sum_refunds
FROM your_table
WHERE trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK)
GROUP BY cus_id

UNION ALL

-- 近8周数据汇总
SELECT 
  cus_id,
  '近8周' AS date_range,
  SUM(`order`) AS sum_order,
  SUM(order_value) AS sum_order_value,
  SUM(refunds) AS sum_refunds
FROM your_table
WHERE trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK)
GROUP BY cus_id

UNION ALL

-- 近12周数据汇总
SELECT 
  cus_id,
  '近12周' AS date_range,
  SUM(`order`) AS sum_order,
  SUM(order_value) AS sum_order_value,
  SUM(refunds) AS sum_refunds
FROM your_table
WHERE trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
GROUP BY cus_id;

注意：

• order是SQL关键字，需用反引号/双引号转义（不同引擎语法不同：MySQL用`，PostgreSQL用"）。
• 若需按自然周（而非纯天数）统计，需调整日期过滤逻辑，比如PostgreSQL用trans_date >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '3 weeks'（近4周自然周）。
• 缺点：大表场景下会扫描4次原始表，性能开销较高。

方案2：条件聚合+行转列（大表最优） #

这个方案仅扫描一次原始表，先通过条件聚合计算出每个客户所有时间范围的汇总值，再将列转为行，性能远优于方案1，适合大型数据表。

步骤1：条件聚合计算所有汇总值 #

WITH aggregated_data AS (
  SELECT 
    cus_id,
    -- 全量汇总
    SUM(`order`) AS total_order,
    SUM(order_value) AS total_order_value,
    SUM(refunds) AS total_refunds,
    -- 近4周汇总
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) THEN `order` ELSE 0 END) AS last4w_order,
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) THEN order_value ELSE 0 END) AS last4w_order_value,
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) THEN refunds ELSE 0 END) AS last4w_refunds,
    -- 近8周汇总
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK) THEN `order` ELSE 0 END) AS last8w_order,
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK) THEN order_value ELSE 0 END) AS last8w_order_value,
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK) THEN refunds ELSE 0 END) AS last8w_refunds,
    -- 近12周汇总
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK) THEN `order` ELSE 0 END) AS last12w_order,
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK) THEN order_value ELSE 0 END) AS last12w_order_value,
    SUM(CASE WHEN trans_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK) THEN refunds ELSE 0 END) AS last12w_refunds
  FROM your_table
  GROUP BY cus_id
)

步骤2：将列转为行（适配不同SQL引擎） #

MySQL 8.0+/MariaDB

用UNION ALL行转列：

SELECT cus_id, '全量' AS date_range, total_order AS sum_order, total_order_value AS sum_order_value, total_refunds AS sum_refunds FROM aggregated_data
UNION ALL
SELECT cus_id, '近4周' AS date_range, last4w_order, last4w_order_value, last4w_refunds FROM aggregated_data
UNION ALL
SELECT cus_id, '近8周' AS date_range, last8w_order, last8w_order_value, last8w_refunds FROM aggregated_data
UNION ALL
SELECT cus_id, '近12周' AS date_range, last12w_order, last12w_order_value, last12w_refunds FROM aggregated_data;

PostgreSQL

用UNNEST数组批量行转列：

SELECT 
  cus_id,
  UNNEST(ARRAY['全量', '近4周', '近8周', '近12周']) AS date_range,
  UNNEST(ARRAY[total_order, last4w_order, last8w_order, last12w_order]) AS sum_order,
  UNNEST(ARRAY[total_order_value, last4w_order_value, last8w_order_value, last12w_order_value]) AS sum_order_value,
  UNNEST(ARRAY[total_refunds, last4w_refunds, last8w_refunds, last12w_refunds]) AS sum_refunds
FROM aggregated_data;

SQL Server

用CROSS APPLY VALUES行转列（最灵活）：

SELECT 
  cus_id,
  date_range,
  sum_order,
  sum_order_value,
  sum_refunds
FROM aggregated_data
CROSS APPLY (
  VALUES
    ('全量', total_order, total_order_value, total_refunds),
    ('近4周', last4w_order, last4w_order_value, last4w_refunds),
    ('近8周', last8w_order, last8w_order_value, last8w_refunds),
    ('近12周', last12w_order, last12w_order_value, last12w_refunds)
) AS vals(date_range, sum_order, sum_order_value, sum_refunds);

优势：

• 仅扫描一次原始表，大表场景下性能提升显著。
• 自动处理无数据场景，返回对应sum为0的行（若不需要可调整CASE逻辑）。

性能优化建议 #

无论用哪种方案，给your_table创建(cus_id, trans_date)的联合索引，能大幅加速过滤和分组操作。

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