在 SQL 中处理日期
使用 SQL 按时间段分组结果,比较周环比总数,并查找两个日期之间的持续时间。
我们将介绍在 SQL 中处理日期的三个常见场景。我们将使用 Metabase 中包含的示例数据库,以便您可以跟着操作,并坚持使用许多数据库通用的 SQL 函数和技术。我们假设这并非您的第一个 SQL 查询,并且您希望提升技能。但即使您刚刚入门,也应该能学到一些技巧。
本文依赖于预定义的示例数据集,但您也可以使用AI 数据集生成器生成自己的练习数据。
场景 | 示例 |
---|---|
按时间段分组结果 | 每周有多少人创建账户? |
比较周环比总数 | 本周的订单数量与上周相比如何? |
查找两个日期之间的持续时间 | 客户创建账户到下第一笔订单之间有多少天? |
按时间段分组结果
我们经常想问这样的问题:每个月有多少客户注册?或者每周下了多少订单?在这里,我们将遍历结果表,计算行数,并按时间段对这些计数进行分组。
示例:每周有多少人创建账户?
在这里,我们希望返回两列
| WEEK | ACCOUNTS CREATED |
|------|------------------|
| ... | ... |
让我们看一下我们的People
表。我们可以SELECT * FROM people LIMIT 1
来查看字段列表,或者直接点击书本图标来查看我们正在处理的数据库中表的元数据。
由于我们对客户何时注册账户感兴趣,我们需要created_at
字段,根据我们的数据参考,该字段是“用户记录创建的日期。也称为用户的‘加入日期’”。
我们需要对这些账户创建进行分组,但不是按日期分组,而是按周分组。为了查看每个created_at
日期属于哪一周,我们将使用DATE_TRUNC
函数。
DATE_TRUNC
允许您将时间戳舍入(“截断”)到您关心的粒度:周、月等等。DATE_TRUNC
接受两个参数:文本和时间戳,并返回一个时间戳。第一个文本参数是时间段,在本例中是“week”,但我们可以指定不同的粒度,如month、quarter或year(请查阅您的数据库关于DATE_TRUNC
的文档以查看选项)。为了我们的目的,我们将写入DATE_TRUNC('week', created_at)
,这将返回每周的星期一的日期。顺便说一句,SQL不区分大小写,所以您可以根据自己的喜好对代码进行大小写处理(date_trunc
也可以,如果您是讽刺性地查询,DaTe_TrUnc
也可以)。
我们还将对结果使用别名,为列提供更具体的名称。例如,使用AS
关键字,我们将把Count(*)
显示为accounts_created
。
SELECT
DATE_TRUNC('week', created_at) AS week,
COUNT(*) AS accounts_created
FROM
people
GROUP BY
week
ORDER BY
week
它返回
| WEEK | ACCOUNTS_CREATED |
|---------|------------------|
| 4/18/16 | 13 |
| 4/25/16 | 17 |
| 5/2/16 | 17 |
| ... | ... |
我们可以将此结果可视化为折线图
这与我们对随机数据集的预期非常一致。
比较周环比总数
您通常希望查看计数从一周到下一周的变化,您可以通过将表与自身连接,并将每一周与其前一周进行比较来计算。
示例:订单与上周相比如何?
我们在这里寻找的是星期、该星期的订单数量以及周环比变化(订单是增加了、减少了还是保持不变?)
| WEEK | COUNT_OF_ORDERS | WOW_CHANGE |
|---------|-----------------|------------|
| ... | ... | ... |
为了获取这些数据,我们首先需要一个按周列出订单数量的表。我们将基本执行与People
表相同的操作,但这次针对Orders
表:我们将使用DATE_TRUNC
按周对订单数量进行分组。
SELECT
DATE_TRUNC('week', orders.created_at) AS week,
COUNT(*) AS order_count
FROM
orders
GROUP BY
week
这给了我们
| WEEK | ORDER_COUNT |
|----------|-------------|
| 7/1/2019 | 115 |
| 7/2/2018 | 119 |
| 7/3/2017 | 78 |
| ... | ... |
我们将使用这些结果来构建查询的其余部分。现在我们需要做的是获取每周的订单计数(我们称之为w1
),并将其减去前一周的计数(我们称之为w2
)。这里的挑战是,为了执行减法,我们需要以某种方式将每周的计数与前一周的计数放在同一行中。
我们会这样做
- 将我们的结果封装在公共表表达式(CTE)中。
- 通过将连接偏移 1 周来将该 CTE 与自身连接
- 从每周的总数中减去前一周的订单总数,以获取周环比变化
我们将使用WITH
关键字将上述查询转换为一个公共表表达式 (CTE)。本质上,CTE 是一种将变量分配给中间结果的方法,然后我们可以将这些结果视为数据库中的实际表(如Orders
或Table
)。我们将结果表命名为order_count_by_week
。然后,我们将使用此表并将其与自身连接,但带有偏移量:其行将偏移一周。
这是带偏移连接的查询
WITH order_count_by_week AS (
SELECT
DATE_TRUNC('week', orders.created_at) AS week,
COUNT(*) AS order_count
FROM
orders
GROUP BY
week
)
SELECT
*
FROM
order_count_by_week w1
LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)
ORDER BY
w1.week
此查询产生
| WEEK | ORDER_COUNT | WEEK | ORDER_COUNT |
|-----------|-------------|-----------|-------------|
| 4/25/2016 | 1 | | |
| 5/2/2016 | 3 | 4/25/2016 | 1 |
| 5/9/2016 | 3 | 5/2/2016 | 3 |
| ... | ... | ... | ... |
让我们来分析一下这里发生了什么。我们将order_count_by_week
CTE 别名为w1
,然后再次别名为w2
。接下来,我们将这两个 CTE 左连接。这里的关键是DATEADD
函数,我们用它给每个w2.week
值添加一周,以偏移连接的列。
LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)
DATEADD
函数接受一个时间段(WEEK)、要应用的周数(在本例中为 1,因为我们想知道与一周前的差异)以及要应用加法的日期列(w2.week
)。(请注意,有些数据库使用INTERVAL
而不是DATEADD
,例如w2.week + INTERVAL '1 week'
)。这会“对齐”行,但会偏移一周(请注意上面第一行中第二组周/订单计数缺少值)。
我们现在有一个表格,其中包含了计算每行中周环比变化所需的一切。现在我们所要做的就是修改我们的 SELECT 语句,以返回我们正在寻找的列。
- 下订单的周
- 该周的订单数量
- 周环比变化(即本周计数与上周计数之间的差值)。
这是完整的查询
WITH order_count_by_week AS (
SELECT
DATE_TRUNC('week', orders.created_at) AS week,
COUNT(*) AS order_count
FROM
orders
GROUP BY
week
)
SELECT
w1.week,
w1.order_count AS count_of_orders,
w1.order_count - w2.order_count AS wow_change
FROM
order_count_by_week w1
LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)
ORDER BY
w1.week
它返回
| WEEK | COUNT_OF_ORDERS | WOW_CHANGE |
|---------|-----------------|------------|
| 4/25/16 | 1 | |
| 5/2/16 | 3 | 2 |
| 5/9/16 | 3 | 0 |
| ... | ... | ... |
查找两个日期之间的持续时间
您通常会想知道两个事件之间的时间量:注册和结账之间的秒数,或者结账和送达之间的天数。
示例:客户创建账户和下第一笔订单之间有多少天?
为了回答这个问题,我们返回四列
- 客户ID
- 客户创建账户的日期
- 该客户下第一笔订单的日期
- 这两个日期之间的差异
现在,为了获取这些信息,我们需要从People
和Orders
表中获取数据。但是我们不想连接这两个表,因为我们只需要每个客户下的第一笔订单。
我们首先找出每个客户何时下了他们的第一笔订单。
SELECT
user_id,
MIN(created_at) as first_order_date
FROM
orders
GROUP BY
user_id
在这里,我们按客户分组订单(GROUP BY user_id
),并使用MIN
函数查找最早的订单日期。我们将这些结果存储为first_orders
,然后继续我们的查询。
WITH first_orders AS (
SELECT
user_id,
MIN(created_at) as first_order_date
FROM
orders
GROUP BY
user_id
)
SELECT
people.id,
people.created_at AS account_creation,
first_orders.first_order_date,
DATEDIFF(
'day', people.created_at, first_orders.first_order_date
) AS days_before_first_order
FROM
PEOPLE
JOIN first_orders ON first_orders.user_id = people.id
ORDER BY
account_creation
这给了我们
| ID | ACCOUNT_CREATION | FIRST_ORDER_DATE | DAYS_BEFORE_FIRST_ORDER |
|------|------------------|------------------|-------------------------|
| 915 | 4/19/16 21:35 | 10/9/16 8:42 | 173 |
| 1712 | 4/21/16 23:46 | 8/15/16 4:01 | 116 |
| 2379 | 4/22/16 4:07 | 5/22/16 3:56 | 30 |
| ... | ... | ... | ... |
总结一下:我们获取了客户的created_at
日期,并将其连接到我们的 CTE。我们使用DATEDIFF
函数来查找账户创建与其第一笔订单之间的天数,然后将结果存储为days_before_first_order
。DATEDIFF
接受一个时间段(如“day”、“week”、“month”),并返回两个时间戳之间的周期数。
(鉴于示例数据库是随机的,我们的响应与现实不符——人们多久会等待 173 天才在账户设置和购买之间进行操作?)
延伸阅读
我们希望这些查询演练能为您的提问提供一些思路,但请记住,不同的数据库支持不同的 SQL 函数,因此在处理查询时,请养成查阅数据库文档的习惯。您还可以查看编写 SQL 查询的最佳实践。如果您对连接的工作原理有些模糊,请查看Metabase 中的连接。