日期时间函数在数据分析中的实际应用

Q: 为什么 >= 开始 加 < 结束 往往比 BETWEEN 更好？

因为这种写法给出的区间清晰且互不重叠，对 DATETIME 尤其如此。它能降低在边界处重复计数的风险。

Q: 什么时候用 DATE_FORMAT，什么时候用 YEAR() 和 MONTH()？

DATE_FORMAT 便于生成可直接用于报表的键（例如 2025-08）。当需要分别处理年和月，或者做额外计算时，YEAR() 和 MONTH() 更有用。

Q: 什么最容易破坏基于时间的分析？

典型问题包括时区混用、粒度选择错误、记录不完整（return_date 中有 NULL）以及时段边界不清晰。

第 11.2 课 · 阅读时间：约 11 分钟 · 练习题 ↓

本课聚焦 SQL 日期时间函数在分析中的实际应用。你将学习如何从时间字段中提取时间段、按天和按月聚合数据、计算事件之间的间隔，以及基于时间指标构建实用的报表。学完本课后，你将能够熟练地分析 Sakila 数据库中随时间变化的动态。

在上一课中，我们介绍了实用的字符串处理。现在来看另一种几乎出现在每个实际任务中的数据类型：日期和时间。

在分析中，仅仅显示 payment_date 或 rental_date 是不够的。实际上，你需要回答这样的问题：活跃度每天如何变化、哪些时段交易最多、从租借到归还需要多长时间，以及是否存在季节性高峰。

使用 SQL 日期时间函数进行实际数据分析

为什么日期时间函数在分析中很重要

几乎每份报表都有时间维度。即使业务问题是“销售了多少”或“有多少客户”，实际上通常也需要时间背景：按天、周、月、季度或某个特定时段。

日期时间函数可以帮助你：

提取所需的粒度（天、月、小时）；
按时间聚合指标；
对不同时段进行比较；
计算流程的持续时间；
发现异常的高峰和低谷。

最常用的核心函数

在 MySQL 中，以下函数在实际分析中特别有用：

DATE()：从 DATETIME 中只取日期；
YEAR()、MONTH()、DAY()：提取日期的各个部分；
HOUR()：分析每小时的活跃度；
DATE_FORMAT()：构建方便使用的时间键；
TIMESTAMPDIFF()：计算两个时刻之间的间隔；
DATEDIFF()：计算相差的天数。

下面用 Sakila 的数据来看实际场景。

按天汇总付款

第一个实际场景是查看付款量每天如何变化。

SELECT
   DATE(payment_date) AS payment_day,
   COUNT(*) AS payments_count,
   SUM(amount) AS total_amount
FROM payment
GROUP BY DATE(payment_date)
ORDER BY payment_day;

结果：得到付款笔数和收入金额的每日变化。

这份报表可以作为监控活跃度和发现突然变化的基础。

用 DATE_FORMAT 按月比较

当需要更紧凑的视图时，可以按月汇总数据。

SELECT
   DATE_FORMAT(payment_date, '%Y-%m') AS year_month,
   COUNT(*) AS payments_count,
   ROUND(SUM(amount), 2) AS revenue
FROM payment
GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
ORDER BY year_month;

说明：%Y-%m 既便于排序，也便于在 BI 工具中可视化。

如果只保留月份而不保留年份，不同年份的同一个月就会合并到同一组中。

按小时分析活跃度

一个常见的实际问题是：用户在哪些时段操作最多？

SELECT
   HOUR(payment_date) AS payment_hour,
   COUNT(*) AS payments_count,
   ROUND(SUM(amount), 2) AS total_amount
FROM payment
GROUP BY HOUR(payment_date)
ORDER BY payment_hour;

结果：得到活跃度在一天中各小时的分布。

这有助于规划负载、安排营销活动的时间以及制定运营排班。

计算租赁时长

时间函数常用于分析事件的生命周期。在 Sakila 中，可以计算从租借到归还经过了多少小时。

SELECT
   rental_id,
   rental_date,
   return_date,
   TIMESTAMPDIFF(HOUR, rental_date, return_date) AS rental_duration_hours
FROM rental
WHERE return_date IS NOT NULL
ORDER BY rental_duration_hours DESC
LIMIT 10;

结果：查询显示时间最长的已完成租赁。

如果需要汇总视图，可以按平均值和中位数（如果你的 DBMS 支持中位数函数）汇总时长。

实用报表：按星期几统计平均归还时间

现在把时间函数和聚合组合到一条实际查询中。

SELECT
   DAYOFWEEK(rental_date) AS week_day,
   COUNT(*) AS rentals_count,
   ROUND(AVG(TIMESTAMPDIFF(HOUR, rental_date, return_date)), 2) AS avg_return_hours
FROM rental
WHERE return_date IS NOT NULL
GROUP BY DAYOFWEEK(rental_date)
ORDER BY week_day;

结果：得到按星期几统计的平均租赁时长。

这份报表有助于发现行为规律，并按天调整运营规则。

比较当前时段和上一时段

在实际分析中，不仅要计算指标，还要比较不同时段。即使是简单地比较两个时间范围，也能提供有用的信号。

SELECT
   CASE
      WHEN payment_date >= '2005-07-01' AND payment_date < '2005-08-01' THEN 'period_1'
      WHEN payment_date >= '2005-08-01' AND payment_date < '2005-09-01' THEN 'period_2'
   END AS period_label,
   COUNT(*) AS payments_count,
   ROUND(SUM(amount), 2) AS revenue
FROM payment
WHERE payment_date >= '2005-07-01'
  AND payment_date < '2005-09-01'
GROUP BY period_label
ORDER BY period_label;

说明：这种模式很容易扩展到周环比、月环比和季度环比。

实用建议

事先确定所需的粒度：天、周、月还是小时。
为了让时段排序稳定，使用可按字典序排序的格式（YYYY-MM）。
计算间隔时，显式过滤掉未完成的事件（return_date IS NOT NULL）。
分析每小时活跃度时，检查源数据的时区。
比较时段时使用清晰的 >= 和 < 边界，避免重叠。

本课要点：

SQL 日期时间函数是分析趋势和季节性的必备工具。
DATE、DATE_FORMAT、HOUR、TIMESTAMPDIFF 和 DATEDIFF 能完成大多数实际任务。
时间粒度直接影响指标的解读方式。
分析事件之间的间隔有助于衡量流程效率。
即使是简单的时段比较，也能为决策提供有价值的信号。

常见问题

为什么 `>= 开始` 加 `< 结束` 往往比 `BETWEEN` 更好？

因为这种写法给出的区间清晰且互不重叠，对 DATETIME 尤其如此。它能降低在边界处重复计数的风险。

什么时候用 `DATE_FORMAT`，什么时候用 `YEAR()` 和 `MONTH()`？

DATE_FORMAT 便于生成可直接用于报表的键（例如 2025-08）。当需要分别处理年和月，或者做额外计算时，YEAR() 和 MONTH() 更有用。

什么最容易破坏基于时间的分析？

典型问题包括时区混用、粒度选择错误、记录不完整（return_date 中有 NULL）以及时段边界不清晰。

面试问题

你会如何解释 `DATEDIFF()` 和 `TIMESTAMPDIFF()` 的区别？

DATEDIFF() 返回两个日期之间相差的天数。TIMESTAMPDIFF() 可以选择单位（小时、分钟、天等），更适合进行精确的间隔分析。

为什么在报表中选择正确的时间粒度很重要？

因为粒度决定了解读方式：按天的分析突出运营上的波动，而按月的分析突出趋势。错误的聚合层级可能会掩盖重要的规律。

在发布基于时间的报表之前，你会如何验证它？

我会检查时段边界、时区假设、NULL 的处理以及区间是否重叠，然后用原始数据中的抽样核对合计值。