在 SQL 中处理日期

使用 Metabase 学习 SQL

免费下载 Metabase，或注册 Metabase 云版免费试用

我们将介绍在 SQL 中处理日期的三种常见场景。我们将使用 Metabase 中包含的示例数据库，以便您可以跟着操作，并坚持使用适用于许多数据库的一些常见 SQL 函数和技术。我们假设这不是您的第一个 SQL 查询，并且您希望提升技能。但即使您刚开始学习，也应该能学到一些技巧。

按时间段分组结果

我们经常想问这样的问题：每个月有多少客户注册？或者每周下了多少订单？在这里，我们将遍历结果表，统计行数，并按时间段对这些计数进行分组。

示例：每周有多少人创建了账户？

这里我们希望返回两列

| WEEK | ACCOUNTS CREATED |
|------|------------------|
| ...  | ...              |

让我们看看我们的 People 表。我们可以 SELECT * FROM people LIMIT 1 来查看字段列表，或者直接点击**书本图标**来查看我们正在使用的数据库中表的元数据。

由于我们关心客户何时注册账户，因此需要 created_at 字段，根据我们的数据参考，该字段是“用户记录创建的日期。也称为用户的‘加入日期’”。

我们需要对这些账户创建进行分组，但不是按日期分组，而是按周分组。要查看每个 created_at 日期属于哪一周，我们将使用 DATE_TRUNC 函数。

DATE_TRUNC 允许您将时间戳舍入（“截断”）到您关心的粒度：周、月等等。DATE_TRUNC 接受两个参数：文本和时间戳，并返回一个时间戳。第一个文本参数是时间段，在本例中是“week”，但我们可以指定不同的粒度，例如月、季度或年（查看您的数据库关于 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 中的联接》。