在 SQL 中处理日期

我们将介绍与SQL中的日期一起工作的三个常见场景。我们将使用Metabase附带的数据库示例，让您能够跟上，并坚持使用适用于许多数据库的常见SQL函数和技术。我们假设这不是您的第一个SQL查询，您正在寻求提升水平。但即使您刚刚开始，您也应该能够获得一些提示。

按时间段分组结果

我们经常想要提出问题，例如：每个月有多少客户注册？或者每周有多少订单被下？在这里，我们将通过结果表，计算行数，并将这些计数按时间段分组。

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

我们希望返回两列

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

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

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

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

DATE_TRUNC函数允许您将时间戳“截断”到您关心的粒度：周、月等等。DATE_TRUNC接受两个参数：文本和时间戳，并返回一个时间戳。第一个文本参数是时间段，在本例中是“周”，但我们可以指定不同的粒度，如月、季度或年（请检查数据库的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）中。
• 通过偏移量将CTE与自身连接，偏移量为1周
• 从每周的总计数中减去前一周的订单计数总数，以获取周与周的变化

我们将使用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'）。这“对齐”了行，但偏离了一周（注意第一行上方第二个组中周/订单数的缺失）。

我们现在有一个包含我们计算每周变化所需所有信息的表（在每个行中）。现在我们只需要修改我们的选择语句，以返回我们正在寻找的列。

• 订单放置的周数
• 该周订单数
• 周同比增长（即本周与上周之间的差异）。

以下是完整的查询：

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函数接受一个时间段（如“天”、“周”、“月”），并返回两个时间戳之间的时间段数。

（鉴于示例数据库是随机的，我们的响应与现实差距很大——人们在账户设置和购买之间等待173天的频率有多高？）

进一步阅读

我们希望这些查询演练给您自己的问题提供了一些想法，但请记住，不同的数据库支持不同的SQL函数，因此在处理查询时，请养成查阅数据库文档的习惯。您还可以查看编写SQL查询的最佳实践。如果您对连接的工作原理有些模糊，请查看Metabase中的连接。