课程 11.2 · 阅读时间：~11分钟

本课程重点介绍在SQL中用于分析的日期和时间函数的实际应用。您将学习如何从时间字段中提取时间段，按天和月聚合数据，计算事件之间的时间间隔，并基于时间指标构建工作报告。到本课程结束时，您将能够自信地分析Sakila数据库中的基于时间的动态。

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

在上一课中，我们讨论了实用的字符串处理。现在我们转向几乎在每个实际任务中都会出现的另一种数据类型：日期和时间。

在分析中，仅仅显示 payment_date 或 rental_date 是不够的。实际上，您需要回答诸如：活动如何按天变化，哪些小时的交易最多，从租赁到归还需要多长时间，以及是否存在季节性高峰等问题。

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

几乎每个报告都有时间维度。即使商业问题是“销售多少”或“客户有多少”，在实践中，您通常需要时间上下文：按天、周、月、季度或特定时期。

日期和时间函数帮助您：

• 提取所需的粒度（天、月、小时）；
• 随时间聚合指标；
• 比较不同时间段；
• 计算过程持续时间；
• 检测异常峰值和下降。

最常用的核心函数

在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 涵盖了大多数实际任务。
• 时间粒度直接影响指标的解释方式。
• 事件间隔分析有助于衡量过程效率。
• 即使是简单的时期比较也能提供有价值的决策信号。

常见问题

为什么 >= start 和 < end 通常比 BETWEEN 更好？

因为这种格式提供了清晰、不重叠的区间，特别是对于 DATETIME。它减少了在边界处重复计数的风险。

何时使用 DATE_FORMAT 而不是 YEAR() 和 MONTH()？

DATE_FORMAT 适用于现成的报告键（例如，2025-08）。YEAR() 和 MONTH() 在您需要单独的年/月逻辑或额外计算时很有用。

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

典型问题包括混合时区、错误的粒度、不完整的记录（return_date 中的 NULL）和不清晰的时期边界。

面试问题

您如何解释 DATEDIFF() 和 TIMESTAMPDIFF() 之间的区别？

DATEDIFF() 返回日期之间的天数差。TIMESTAMPDIFF() 允许您选择单位（小时、分钟、天等），更适合更精确的间隔分析。

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

因为粒度定义了解释：每日分析突出操作波动，而每月分析突出趋势。错误的聚合级别可能会隐藏重要模式。

您如何在发布前验证基于时间的报告？

我会验证时期边界、时区假设、NULL 处理、间隔重叠的缺失，然后将总数与原始数据的控制样本进行核对。

在下一课中，我们将转向数据转换技术以进行分析，并查看如何在一个查询中结合基于时间和条件的计算。