SQL 时间范围和时间粒度 - 萌猫他爸
source link: https://www.cnblogs.com/yurunmiao/p/16712173.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL 时间范围和时间粒度
使用 SQL 进行业务数据计算时,经常会遇到两个概念:时间范围 和 时间粒度 。以 最近一天的每小时的用户访问人数 为例:
- 最近一天 是时间范围
- 每小时 是时间粒度
常见的时间范围:最近五分钟、最近一小时、最近一天、最近一周、最近一月、最近一年、截止到今天、截止到本周、截止到本月、截止到今年。
常见的时间粒度:五分钟、小时、天、周、月、年。
大多数情况下,我们需要根据计算时间和时间范围,计算出业务数据的开始时间和结束时间,用于过滤业务数据;然后再根据业务数据的业务时间和时间粒度,计算出业务时间点,用于分组统计业务数据。
假设用户访问表(user_visit)记录如下:
id | uid | timestamp |
---|---|---|
1 | u1 | 2022-09-19 15:10:58 |
2 | u2 | 2022-09-19 16:24:19 |
3 | u1 | 2022-09-20 01:04:03 |
4 | u2 | 2022-09-20 02:12:36 |
5 | u1 | 2022-09-20 02:35:03 |
6 | u1 | 2022-09-20 03:10:27 |
使用 最近一天 过滤数据,开始时间:2022-09-20 00:00:00,结束时间:2022-09-21 00:00:00,SQL 伪代码:
SELECT
*
FROM
user_visit
WHERE
timestamp >= "2022-09-20 00:00:00"
AND timestamp < "2022-09-21 00:00:00"
过滤结果:
id | uid | timestamp |
---|---|---|
3 | u1 | 2022-09-20 01:04:03 |
4 | u2 | 2022-09-20 02:12:36 |
5 | u1 | 2022-09-20 02:35:03 |
6 | u1 | 2022-09-20 03:10:27 |
过滤后的业务数据,使用 小时 将业务时间转换成业务时间点,转换结果:
id | uid | timestamp |
---|---|---|
3 | u1 | 2022-09-20 01:00:00 |
4 | u2 | 2022-09-20 02:00:00 |
5 | u1 | 2022-09-20 02:00:00 |
6 | u1 | 2022-09-20 03:00:00 |
按小时分组统计用户访问人数,SQL 伪代码:
SELECT
timestamp, COUNT(DISTINCT(uid)) AS uids
FROM
user_visit
GROUP BY
timestamp
统计结果:
timestamp | uids |
---|---|
2022-09-20 01:00:00 | 1 |
2022-09-20 02:00:00 | 2 |
2022-09-20 03:00:00 | 1 |
整个过程涉及两个关键的时间计算:
- 根据计算时间和时间范围,计算业务数据开始时间和结束时间
- 根据业务时间和时间粒度,计算业务时间点
这两个时间的计算均需要通过 SQL 的 日期时间函数 实现。然而不同的数据库对于日期时间函数的支持程度差异很大,实际的计算过程可能比较繁琐。
本文以阿里云 ODPS 和 RDS 为例,详细说明日期时间函数关于时间范围和时间粒度的计算方法。
时间范围的开始时间是闭区间,结束时间是开区间。
阿里云的 ODPS 和 RDS 都是支持日期时间(DATETIME)类型的,业务数据可以直接使用 DATETIME 存储业务时间;也可以使用其它数据类型存储业务时间,常见的有日期时间字符串(STRING)和 Unix 时间戳(INT)。
我们建议将业务时间统一转换成 DATETIME 类型之后再进行时间计算。
日期时间字符串
以字符串 2022-09-20 15:10:58 例,将其转换成 DATETIME。
ODPS
TO_DATE('2022-09-20 15:10:58', 'yyyy-mm-dd hh:mi:ss')
RDS
STR_TO_DATE('2022-09-20 15:10:58', '%Y-%m-%d %H:%i:%s')
Unix 时间戳
以时间戳 1663657859 为例,将其转换成 DATETIME。
ODPS
FROM_UNIXTIME(1663657859)
RDS
FROM_UNIXTIME(1663657859)
我们使用 当前时间 指代 计算时间,获取当前时间(DATETIME):
ODPS
GETDATE()
RDS
NOW()
最近五分钟
以计算时间:2022-09-20 17:07:33 为例,最近五分钟的业务开始时间应为:2022-09-20 17:00:00,业务结束时间应为:2022-09-20 17:05:00。
ODPS
// 开始时间
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(GETDATE()) / 300 - 1) * 300)
// 结束时间
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(GETDATE()) / 300) * 300)
RDS
// 开始时间
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(NOW()) / 300 - 1) * 300)
// 结束时间
FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(NOW()) / 300) * 300)
300 表示 5 分钟,即:300 秒。
最近一小时
以计算时间 2022-09-20 17:19:57 为例,最近一小时的业务开始时间应为 2022-09-20 16:00:00,业务结束时间应为 2022-09-20 17:00:00。
ODPS
// 开始时间
DATETRUNC(DATEADD(GETDATE(), -1, 'hh'), 'hh')
// 结束时间
DATETRUNC(GETDATE(), 'hh')
RDS
// 开始时间
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 HOUR), '%Y-%m-%d %H:00:00')
// 结束时间
DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
以计算时间 2022-09-20 17:31:06 为例,最近一天的业务开始时间应为 2022-09-19 00:00:00,业务结束时间应为 2022-09-20 00:00:00。
ODPS
// 开始时间
DATETRUNC(DATEADD(GETDATE(), -1, 'dd'), 'dd')
// 结束时间
DATETRUNC(GETDATE(), 'dd')
RDS
// 开始时间
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 DAY), '%Y-%m-%d 00:00:00')
// 结束时间
DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
以计算时间 2022-09-20 17:48:10 为例,最近一周的业务开始时间应为 2022-09-12 00:00:00,业务结束时间应为 2022-09-19 00:00:00。
ODPS
// 开始时间
DATETRUNC(DATEADD(GETDATE(), - WEEKDAY(GETDATE()) - 7 , 'dd'), 'dd')
// 结束时间
DATETRUNC(DATEADD(GETDATE(), - WEEKDAY(GETDATE()), 'dd'), 'dd')
RDS
// 开始时间
DATE_FORMAT(ADDDATE(NOW(), - 7 - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
// 结束时间
DATE_FORMAT(ADDDATE(NOW(), - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
以计算时间 2022-09-20 17:57:05 为例,最近一月的业务开始时间应为 2022-08-01 00:00:00,业务结束时间应为 2022-09-01 00:00:00。
ODPS
// 开始时间
DATETRUNC(DATEADD(GETDATE(), -1, 'mm'), 'mm')
// 结束时间
DATETRUNC(GETDATE(), 'mm')
RDS
// 开始时间
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 MONTH), '%Y-%m-01 00:00:00')
// 结束时间
DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
以计算时间 2022-09-20 18:03:00 为例,最近一年的业务开始时间应为 2021-01-01 00:00:00,业务结束时间应为 2022-01-01 00:00:00。
ODPS
// 开始时间
DATETRUNC(DATEADD(GETDATE(), -1, 'yyyy'), 'yyyy')
// 结束时间
DATETRUNC(GETDATE(), 'yyyy')
RDS
// 开始时间
DATE_FORMAT(DATE_ADD(NOW(), INTERVAL - 1 YEAR), '%Y-01-01 00:00:00')
// 结束时间
DATE_FORMAT(NOW(), '%Y-01-01 00:00:00')
截止到今天
以计算时间 2022-09-20 18:12:31 为例,截止到今天的业务开始时间应为 2022-09-20 00:00:00,业务结束时间应为 2022-09-21 00:00:00。
ODPS
// 开始时间
DATETRUNC(GETDATE(), 'dd')
// 结束时间
DATETRUNC(DATEADD(GETDATE(), 1, 'dd'), 'dd')
RDS
// 开始时间
DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
// 结束时间
DATE_FORMAT(ADDDATE(NOW(), 1), '%Y-%m-%d 00:00:00')
截止到本周
以计算时间 2022-09-20 18:16:20 为例,截止到本周的业务开始时间应为 2022-09-19 00:00:00,业务结束时间应为 2022-09-26 00:00:00。
ODPS
// 开始时间
DATETRUNC(DATEADD(GETDATE(), - WEEKDAY(GETDATE()), 'dd'), 'dd')
// 结束时间
DATETRUNC(DATEADD(GETDATE(), 7 - WEEKDAY(GETDATE()), 'dd'), 'dd')
RDS
// 开始时间
DATE_FORMAT(ADDDATE(NOW(), - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
// 结束时间
DATE_FORMAT(ADDDATE(NOW(), 7 - WEEKDAY(NOW())), '%Y-%m-%d 00:00:00')
截止到本月
以计算时间 2022-09-20 18:19:15 为例,截止到本月的业务开始时间为 2022-09-01 00:00:00,业务结束时间应为 2022-10-01 00:00:00。
ODPS
// 开始时间
DATETRUNC(GETDATE(), 'mm')
// 结束时间
DATETRUNC(DATEADD(GETDATE(), 1, 'mm'), 'mm')
RDS
// 开始时间
DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
// 结束时间
DATE_FORMAT(ADDDATE(NOW(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
截止到今年
以计算时间 2022-09-20 18:21:09 为例,截止到今年的业务开始时间为 2022-01-01 00:00:00,业务结束时间应为 2023-01-01 00:00:00。
ODPS
// 开始时间
DATETRUNC(GETDATE(), 'yyyy')
// 结束时间
DATETRUNC(DATEADD(GETDATE(), 1, 'yyyy'), 'yyyy')
RDS
// 开始时间
DATE_FORMAT(NOW(), '%Y-01-01 00:00:00')
// 结束时间
DATE_FORMAT(ADDDATE(NOW(), INTERVAL 1 YEAR), '%Y-01-01 00:00:00')
参考时间范围为最近五分钟的结束时间的计算方法。
参考时间范围为最近一小时的结束时间的计算方法。
参考时间范围为最近一天的结束时间的计算方法。
参考时间范围为最近一周的结束时间的计算方法。
参考时间范围为最近一月的结束时间的计算方法。
参考时间范围为最近一年的结束时间的计算方法。
时间范围和时间粒度的计算虽然不是什么技术难点,却是数据分析 SQL 语句中极其重要的组成部分。不同数据库之间的日期时间函数的支持程度差异较大,具体使用时很容易混淆,如果平时可以多记录多总结,则可以幅度提升开发效率。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK