Coding Interview Prep · 课时

截断日期与划分日期区间

Q: 「截断日期与划分日期区间」这节课中我会学到什么？

使用 DATE_TRUNC 及其等价函数按周、月和季度分组。 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用 DATE_TRUNC 及其等价函数按周、月和季度分组。

截断日期与划分日期区间 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 2 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

为什么面试会考日期分桶

“按周显示收入”或“按月显示活跃用户”是分析师面试中的基本题。考查的是将精确时间戳压缩为更粗粒度的分桶，以便将行归到同一组。

初学者常犯的错误是只提取月份编号，这会把不同年份的同一个月份合并。专业的回答是截断：将每个时间戳映射到其所属周期的起点。

按周、月、季度和年份分桶
DATE_TRUNC 和各 SQL 方言的等价函数
正确分组，使图表对齐

DATE_TRUNC：核心工具

在 PostgreSQL 中，DATE_TRUNC(unit, ts) 会将精度高于该单位的部分全部置零。将其截断到 'month' 后，任何三月的时间戳都会变成 2024-03-01 00:00:00。

返回值仍然是时间戳，因此可以按时间顺序排序，也能完美分组。这是报表中最有用的日期函数。

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

按月对收入分组

这是经典的完整示例。先将时间戳截断到月份，然后分组并求和。由于分桶值包含年份，2023 年 1 月和 2024 年 1 月会保持分开。

按截断后的值排序，可得到适合制图的清晰时间序列。

SELECT
  DATE_TRUNC('month', order_ts) AS month,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

EXTRACT 与 DATE_TRUNC

面试官往往会直接追问这个区别。两者都会提取周期信息，但回答的是不同的问题。

EXTRACT(MONTH FROM ts) 对所有年份的三月都会返回数字 3，适合分析季节性。
DATE_TRUNC('month', ts) 返回特定月份的起点，能够区分年份，适合时间序列。

如果为了绘制月度趋势图而按 EXTRACT(MONTH ...) 分组，您会在不知不觉中将不同年份混在一起。

-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

周分桶与星期一问题

按周分组有一个面试官很喜欢考的细节：一周从哪一天开始？PostgreSQL 的 DATE_TRUNC('week', ts) 始终对齐到星期一（ISO 周）。

如果业务需要从星期日开始的周，必须进行偏移。常见做法是先将日期向前移一天，执行截断，再向后移一天。

-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;

-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
  AS sunday_week
FROM orders;

季度分桶

季度报表在金融相关岗位中很常见。DATE_TRUNC('quarter', ts) 会将任何时间戳映射到所属季度的第一天：1 月 1 日、4 月 1 日、7 月 1 日或 10 月 1 日。

如果要将季度标记为数字，可以将 EXTRACT(QUARTER ...) 与年份结合起来。

SELECT
  DATE_TRUNC('quarter', order_ts)                  AS quarter_start,
  EXTRACT(YEAR FROM order_ts) || '-Q'
    || EXTRACT(QUARTER FROM order_ts)              AS quarter_label,
  SUM(amount)                                      AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

MySQL 没有 DATE_TRUNC

这是一个常见的跨方言问题：“MySQL 没有 DATE_TRUNC，如何按月分桶？”通用的回答是将日期格式化到您需要的粒度。

DATE_FORMAT(ts, '%Y-%m-01') 以文本或日期形式给出月份起点。
DATE_FORMAT(ts, '%Y-%m') 给出类似 2024-03 的可排序字符串键。

对于周，MySQL 提供了 YEARWEEK()，其模式参数可以控制一周从哪一天开始。

-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

SQL Server 中的分桶

SQL Server 过去没有直接的截断函数，因此候选人会使用 DATEFROMPARTS 或 DATEADD/DATEDIFF 惯用写法。现代版本（2022 及更高版本）新增了 DATETRUNC。

经典的“计算自纪元以来的单位数，然后再将这些单位加回去”这一惯用写法适用于所有版本，值得掌握。

-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;

填补时间序列中的空缺

仅截断会丢弃没有行的周期：没有订单的月份根本不会出现。面试官会考查您是否注意到这一点。

修复方法是生成完整的周期骨架，并将数据 LEFT JOIN 到骨架上。在 PostgreSQL 中，generate_series 可以生成这个骨架。

SELECT
  cal.month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
                      INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
  ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

更深入的示例：每周活跃用户

将分桶与去重计数结合起来。“每周活跃用户”指的是按周分桶统计不同用户数，这是产品分析中真实存在的需求。

将事件时间戳截断到周，然后使用 COUNT(DISTINCT user_id)。如果提到会连接一个周骨架来显示没有活动的周，您就能获得额外加分。

SELECT
  DATE_TRUNC('week', event_ts) AS week,
  COUNT(DISTINCT user_id)      AS wau
FROM events
GROUP BY 1
ORDER BY 1;

在有索引的列上进行分桶

需要指出一个性能注意事项：在 WHERE 子句中用 DATE_TRUNC 包装日期列，可能会阻止规划器使用该列上的索引。

在 GROUP BY 中这样做没有问题，但进行筛选时，应将原始列与计算出的边界进行比较。我们之前介绍过这种半开区间模式；这里同样适用。

-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

快速检查

请选择适合绘制月度趋势图、并能区分不同年份的工具。

回顾：截断日期并进行分桶

需要记住的内容：

DATE_TRUNC(unit, ts) 会将时间戳映射到周期起点，并区分不同年份，是处理时间序列的正确工具。
EXTRACT 返回一个单独的数字，适合分析季节性，但会合并不同年份。
PostgreSQL 的周从星期一开始；如果需要从星期日开始，请进行偏移。
MySQL 使用 DATE_FORMAT；较旧的 SQL Server 使用 DATEADD(DATEDIFF(...)) 惯用写法；2022 及更高版本提供 DATETRUNC。
使用生成的日期骨架 + LEFT JOIN 来显示空的周期，并避免在 WHERE 中使用 DATE_TRUNC，以保留索引的使用。

常见问题解答

「截断日期与划分日期区间」课时是免费的吗？

是的 — 「截断日期与划分日期区间」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。

「截断日期与划分日期区间」这节课中我会学到什么？

使用 DATE_TRUNC 及其等价函数按周、月和季度分组。你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Coding Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 2 节课，共 4 节。

「截断日期与划分日期区间」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Coding Interview Prep 课中编写并运行代码吗？

能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Coding Interview Prep