在SQL中如何通过嵌套子查询实现动态按周聚合销售额？-SQL-PHP中文网

用 DATE_TRUNC('week', ...) 配合子查询做周聚合最稳妥：先在子查询中统一计算周起始日并下推时间过滤，再外层分组；跨系统需对齐周首定义，补空周时须确保周计算逻辑一致。

在sql中如何通过嵌套子查询实现动态按周聚合销售额？

用 `DATE_TRUNC('week', ...)` 配合子查询做周聚合最稳妥

PostgreSQL 和 BigQuery 支持 DATE_TRUNC('week', order_date) 直接截取到周一（或周日，取决于系统设置），这是动态按周分组的基石。MySQL 和 SQL Server 没有原生 DATE_TRUNC，得靠表达式模拟——但别在主查询里硬写，容易出错且不可复用。

推荐把“计算周起始日”的逻辑封装进子查询，既清晰又避免重复计算：

SELECT 
  week_start,
  SUM(sales_amount) AS weekly_sales
FROM (
  SELECT 
    sales_amount,
    DATE_TRUNC('week', order_date) AS week_start
  FROM orders
  WHERE order_date >= '2024-01-01'
) AS weekly_data
GROUP BY week_start
ORDER BY week_start;

注意：DATE_TRUNC('week', ...) 在 PostgreSQL 默认以周一为周首，在 BigQuery 可指定 DATE_TRUNC(order_date, WEEK(MONDAY))；若你所在环境以周日为起点，得统一调整，否则跨年/跨月时周对齐会错位。

MySQL 用户必须用 `STR_TO_DATE` + `WEEKDAY` 手动算周起始日

MySQL 没有 DATE_TRUNC，WEEK() 函数返回的是“年第几周”，不能直接用于分组——它不带年份上下文，2023-12-31 和 2024-01-01 可能同属 WEEK=52，但显然不属于同一自然周。

正确做法是：先算出当前日期所属周的周一（或周日）日期，再分组。子查询里必须显式计算：

SELECT 
  week_start,
  SUM(sales_amount) AS weekly_sales
FROM (
  SELECT 
    sales_amount,
    DATE_SUB(order_date, INTERVAL WEEKDAY(order_date) DAY) AS week_start
  FROM orders
  WHERE order_date >= '2024-01-01'
) AS weekly_data
GROUP BY week_start
ORDER BY week_start;

关键点：WEEKDAY(order_date) 返回 0（周一）到 6（周日），所以 DATE_SUB(... INTERVAL WEEKDAY(...) DAY) 总是得到当周周一。别用 YEARWEEK() 做分组键——它返回整数，202352 和 202401 看似连续，但中间缺了 7 天，无法排序或做时间差计算。

子查询里漏加 `WHERE` 过滤会导致聚合结果膨胀

嵌套子查询不是万能缓冲区。如果业务只关心近 6 个月数据，但子查询没写 WHERE order_date >= ...，那数据库就得先扫描全表、生成所有周记录，再在外层 GROUP BY——不仅慢，还可能因临时空间不足报错 ERROR: out of memory for query result。

务必把时间过滤下推到子查询内层，原则就一条：能早筛绝不晚筛。常见错误写法：

-- ❌ 错误：过滤放在外层，子查询仍处理全量
SELECT week_start, SUM(sales_amount)
FROM (SELECT ..., order_date FROM orders) AS t
WHERE order_date >= '2024-01-01'  -- 这里无效！order_date 不在子查询 SELECT 列表中
GROUP BY week_start;

正确写法必须让过滤条件作用于子查询的原始表：

子查询的 SELECT 列只需包含聚合所需字段 + 计算出的周字段，别拖全表字段
WHERE 必须出现在子查询的 FROM orders 后面
如果要排除测试订单，WHERE status != 'test' 也得放子查询里

聚合后补 `COALESCE` 处理空周比在应用层拼更可靠

真实销售数据常有“某周没订单”，导致查询结果跳过该周。想补全连续周（比如画折线图），不能指望前端循环生成日期再左连接——数据库端补更稳。

用子查询生成完整周序列，再 LEFT JOIN 销售聚合结果：

SELECT 
  all_weeks.week_start,
  COALESCE(s.weekly_sales, 0) AS weekly_sales
FROM (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '7 days'
  )::date AS week_start
) AS all_weeks
LEFT JOIN (
  SELECT 
    DATE_TRUNC('week', order_date) AS week_start,
    SUM(sales_amount) AS weekly_sales
  FROM orders
  WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
  GROUP BY DATE_TRUNC('week', order_date)
) AS s ON all_weeks.week_start = s.week_start
ORDER BY all_weeks.week_start;

注意：generate_series 是 PostgreSQL 特性；MySQL 需用递归 CTE 或数字表模拟。重点在于：补周逻辑必须和聚合逻辑用同一套“周起始日”定义，否则 JOIN 会失败——比如一个用周一截断，另一个用周日截断，永远对不上。

真正难的不是写子查询，而是确保所有环节对“一周从哪天开始、如何跨年”有一致理解。哪怕只改一行 INTERVAL WEEKDAY(...) DAY 里的数字，都可能让整张周报偏移 7 天。