SQL 实战：日期与时间函数 – 统计数据的时间跨度与趋势

在数据分析中，时间维度通常是分析的核心，尤其是当我们需要基于日期和时间进行统计、趋势分析时。SQL 提供了丰富的日期与时间函数，帮助我们从原始数据中提取时间信息，计算时间跨度，进行趋势分析，并最终生成对业务有价值的报告。

本文将通过多个实战案例，展示如何使用 SQL 中的日期和时间函数进行时间分析和统计。

---

一、常用日期与时间函数概览

函数名	说明	示例
NOW()	返回当前的日期和时间	NOW() → 2024-12-27 12:30:00
CURDATE()	返回当前日期（不包含时间）	CURDATE() → 2024-12-27
DATE()	从日期时间中提取日期部分	DATE('2024-12-27 12:30:00') → 2024-12-27
YEAR() / MONTH() / DAY()	提取年份 / 月份 / 日	YEAR('2024-12-27') → 2024
DATEDIFF()	计算两个日期之间的天数差	DATEDIFF('2024-12-27', '2024-12-01') → 26
TIMESTAMPDIFF()	计算两个日期时间之间的差值（以指定单位）	TIMESTAMPDIFF(DAY, '2024-12-01', '2024-12-27') → 26
DATE_ADD() / DATE_SUB()	日期加减指定的时间间隔	DATE_ADD('2024-12-01', INTERVAL 10 DAY) → 2024-12-11
WEEK() / WEEKDAY()	获取星期几或某个日期的星期数	WEEK('2024-12-27') → 52
DATE_FORMAT()	格式化日期为指定的格式	DATE_FORMAT('2024-12-27', '%Y-%m-%d') → 2024-12-27
STR_TO_DATE()	将字符串转换为日期格式	STR_TO_DATE('2024-12-27', '%Y-%m-%d') → 2024-12-27

---

二、实战案例：日期与时间函数应用

案例 1：统计每日订单数量

需求

在电商平台中，我们需要统计每天的订单数量，以了解平台的日活跃情况。

表结构 orders

order_id	order_date
1	2024-12-01 10:15:00
2	2024-12-01 11:30:00
3	2024-12-02 14:45:00
4	2024-12-02 16:00:00
5	2024-12-03 09:00:00

---

SQL 实现

SELECT  DATE(order_date) AS order_day,  COUNT(order_id) AS order_count  
FROM orders  
GROUP BY order_day  
ORDER BY order_day;

---

查询结果

order_day	order_count
2024-12-01	2
2024-12-02	2
2024-12-03	1

解释：

• DATE(order_date) 提取了订单的日期部分，忽略时间。
• 使用 COUNT(order_id) 统计每天的订单数量。
• 通过 GROUP BY 按日期分组，按日期进行统计。

---

案例 2：计算订单完成时间（时间跨度）

需求

在平台上，订单从下单到完成可能需要一定时间。我们需要计算每个订单从下单到完成的时间跨度（以小时为单位）。

表结构 orders

order_id	order_date	completed_date
1	2024-12-01 10:15:00	2024-12-01 14:30:00
2	2024-12-01 11:30:00	2024-12-01 13:00:00
3	2024-12-02 14:45:00	2024-12-02 18:15:00
4	2024-12-02 16:00:00	2024-12-02 19:00:00
5	2024-12-03 09:00:00	2024-12-03 12:00:00

---

SQL 实现

SELECT  order_id,  TIMESTAMPDIFF(HOUR, order_date, completed_date) AS completion_hours  
FROM orders;

---

查询结果

order_id	completion_hours
1	4
2	1.5
3	3.5
4	3
5	3

解释：

• TIMESTAMPDIFF(HOUR, order_date, completed_date) 计算两个时间点之间的差值，并以小时为单位返回结果。
• 该查询帮助我们了解每个订单的完成时间，便于分析订单处理效率。

---

案例 3：按月统计销售总额

需求

我们希望统计每个月的销售总额，以了解不同月份的业务表现。

表结构 sales

sale_id	sale_date	amount
1	2024-12-01 09:00:00	100
2	2024-12-01 10:30:00	150
3	2024-11-15 14:45:00	200
4	2024-11-20 16:00:00	250
5	2024-12-03 11:15:00	180

---

SQL 实现

SELECT  DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,  SUM(amount) AS total_sales  
FROM sales  
GROUP BY sale_month  
ORDER BY sale_month;

---

查询结果

sale_month	total_sales
2024-11	450
2024-12	430

解释：

• DATE_FORMAT(sale_date, '%Y-%m') 提取销售日期的年月部分，以便按月分组。
• 使用 SUM(amount) 统计每个月的销售总额。

---

案例 4：计算每周活跃用户数

需求

我们需要了解每周活跃用户的数量，分析平台的用户活跃趋势。

表结构 user_activity

activity_id	user_id	activity_date
1	1001	2024-12-01 10:15:00
2	1002	2024-12-01 11:30:00
3	1001	2024-12-07 14:45:00
4	1003	2024-12-07 16:00:00
5	1002	2024-12-14 09:00:00

---

SQL 实现

SELECT  YEAR(activity_date) AS year,  WEEK(activity_date) AS week,  COUNT(DISTINCT user_id) AS active_users  
FROM user_activity  
GROUP BY year, week  
ORDER BY year, week;

---

查询结果

year	week	active_users
2024	48	3
2024	49	2
2024	50	1

解释：

• YEAR(activity_date) 和 WEEK(activity_date) 提取活动发生的年份和周数，按周进行

分组。

• COUNT(DISTINCT user_id) 计算每周的独立活跃用户数量。

---

总结

• 日期与时间函数在 SQL 中非常重要，能够帮助我们处理各种基于时间的统计与分析任务。
• 使用这些函数，可以从原始数据中提取时间信息，计算时间差，生成趋势报告等。
• 通过灵活组合使用 DATE()、TIMESTAMPDIFF()、DATE_FORMAT() 等函数，可以轻松实现日期和时间的清洗、分析和展示。

这些日期与时间的处理技巧不仅能提升查询的效率，还能为数据分析和报表生成提供精准支持。