数据科学面试中应该知道的5个SQL日期函数

我不是女神ヾ 2022-09-13 13:26 156阅读 0赞

今天来深入探讨 SQL 中 5 个最重要和最有用的 DATE 函数以及一些可以使用它们的实际业务案例。

正文

**来源：DeepHub IMBA**

一个优秀的 SQL 开发人员是能够以他们喜欢的任何方式操作数据的——其中很大一部分是能够操作日期。因为日期非常重要，比如企业喜欢比较和评估不同时间段的业务绩效，统计一个时段的指标，这些都离不开日期函数，能够操纵日期对于顶级业务运营和业务报告至关重要。

在本文中，我们将深入探讨 SQL 中 5 个最重要和最有用的 DATE 函数以及一些可以使用它们的实际业务案例。

##     ##

# 01 #

**DATE\_TRUNC()**

DATE_TRUNC(date_expression, date_part)

DATE\_TRUNC() 将日期缩短为指定的日期部分。

在下面的示例中，由于我们指定了 date\_part = MONTH，因此 date\_trunc() 将截断日期以获取该月的第一天：

DATE_TRUNC('2021-06-28', MONTH) = '2021-06-01'
     DATE_TRUNC('2021-06-28', YEAR) = '2021-01-01'

如果我们指定 date\_part = YEAR，那么我们将得到指定日期的年份的第一天：

什么时候使用？

1.  DATE\_TRUNC 在你希望定期（例如每周、每月或每年）汇总数字时非常有用。
2.  DATE\_TRUNC 在进行分组分析时是必要的，你通常按月对用户进行分组。

示例 ：假设你想从下表中获得每周的销售额总和：

![edbc85be80ccee2165a9897089be4de2.png][]

withDTE_ADD() / DATE_SUB()as (
        SELECT DATE_TRUNC(date, WEEK) as week_date,
                daily_sales
        FROM sales
     )
     SELECT week_date,
            SUM(daily_sales) as weekly_sales
     FROM sales_data
     GROUP BY week_date

##     ##

# 02 #

**DATE\_DIFF()**

DATE_DIFF(date_expression_1, date_expression_2, date_part)

DATE\_DIFF() 比较两个日期并返回两个日期之间日期部分的差异。

例如，如果 date\_part = DAY，则 DATE\_DIFF() 返回两个日期之间的天数。如果 date\_part = MONTH，则 DATE\_DIFF() 返回两个日期之间的 MONTH 数。

DATE_DIFF('2021-01-02', '2021-01-01', DAY) = 1

什么时候使用？

1.  DATE\_DIFF() 在你想要比较两个日期时很有用，例如，包裹何时发货和包裹何时交付，或者用户何时注册和何时取消。
2.  DATE\_DIFF() 在 WHERE 子句中也很有用，如果你想过滤 X 周期前发生的日期（例如 5 天前、2 周前、上个月）。

示例1 ：假设你想获取包裹发货时间和包裹送达时间之间的时间：

![0323b893e58d12a6d51e7c37e0aef1e5.png][]

SELECT order_id
          , DATE_DIFF(date_received, date_shipped, DAY) as shipping_time
     FROM orders

示例 2：使用上表，假设你想获取所有发货时间少于 10 天的订单：

SELECT order_id
          , amount
     FROM orders
     WHERE DATE_DIFF(date_received, date_shipped, DAY) < 10

# 03 #

DTE\_ADD() / DATE\_SUM()

DATE_ADD(date_expression, INTERVAL int64 date_part)
     DATE_SUB(date_expression, INTERVAL int64 date_part)

DATE\_ADD() 将指定数量添加到日期部分。相反，DATE\_SUB 将日期指定减去数量。

DATE_ADD('2021-01-01', INTERVAL 3 DAY) = '2021-01-04'
     DATE_SUB('2021-01-04', INTERVAL 3 DAY) = '2021-01-01'
     DATE_ADD('2021-01-01', INTERVAL 1 MONTH) = '2021-02-01'

什么时候使用？

1.  DATE\_ADD() 和 DATE\_SUB() 可以像 WHERE 子句中的 DATE\_DIFF() 一样使用，以过滤 X 周期前或将来 X 周期发生的日期。

示例 1：假设你想获取所有发货时间少于 10 天的订单：

![56417cf36143aa4c8f3cadf8a29f85ed.png][]

SELECT order_id
          , amount
     FROM orders
     WHERE DATE_ADD(date_shipped, INTERVAL 10 DAY) > date_received

# 04 #

EXTRACT()

EXTRACT(part FROM date_expression)

EXTRACT() 返回与指定日期部分对应的值。

EXTRACT(DAY FROM '2021-01-03') = 3
     EXTRACT(MONTH FROM '2021-01-03') = 1
     EXTRACT(YEAR FROM '2021-01-03') = 2021

什么时候使用？

1.  使用 EXTRACT() 是获取日期的特定部份的一种简单方法。
2.  例如需要按周数报告，可以使用 EXTRACT() 获取给定记录的给定日期的周数。
3.  EXTRACT() 允许你从日期中获取月份数或年份，可用作机器学习模型的特征。

##     ##

# 05 #

CURRENT\_DATE()

CURRENT_DATE([time_zone])

CURRENT\_DATE() 返回在指定时区执行查询的当前日期。注意时区参数是可选的，不需要指定。

什么时候使用？

使用 CURRENT\_DATE() 是引用今天日期的一种更简单的方法，而不是硬编码的日期，如果它是在 Airflow 上固化的查询或你经常使用的查询，这尤其有用。

示例 1：假设你想获取过去一周内发货的所有订单：

![be61f770ad56ff31ae113378bef32317.png][]

SELECT order_id
          , amount
     FROM orders
     WHERE DATE_DIFF(CURRENT_DATE(), date_shipped, DAY) < 7

示例 2：假设你想获取与今天日期同月发货的任何年份的所有订单：

SELECT order_id
          , amount
     FROM orders
     WHERE EXTRACT(MONTH FROM date_shipped) = EXTRACT(MONTH FROM CURRENT_DATE())

![f4ab7e0571cd67138c18e9f48b74f8b3.gif][]

点击下方卡片进行关注，获取更多内容

![fd81f887023988688f1c099c420d32a2.png][]

点分享

![5e56aa694445f7ee48f228f71d4b3226.png][]

点收藏

![db2ffdbfcb50cc85d8eec68bff342eb6.png][]

点点赞

![bbf0ff30fabf0adc6c88f9d74cfdd2ff.png][]

点在看

[edbc85be80ccee2165a9897089be4de2.png]: /images/20220828/c226168ae0fa414ab60abb682c100d5a.png
[0323b893e58d12a6d51e7c37e0aef1e5.png]: /images/20220828/7a756120bb3948838f72769fd3cc7310.png
[56417cf36143aa4c8f3cadf8a29f85ed.png]: /images/20220828/b20cdee0683948a88de6952200bab9cf.png
[be61f770ad56ff31ae113378bef32317.png]: /images/20220828/4ab8b3b6f54e4ee08e2df133f6741707.png
[f4ab7e0571cd67138c18e9f48b74f8b3.gif]: /images/20220828/d3b805ecc0974d368479cfe4fb93367a.png
[fd81f887023988688f1c099c420d32a2.png]: /images/20220828/454e328459c94bc983df0110547b12d4.png
[5e56aa694445f7ee48f228f71d4b3226.png]: /images/20220828/675426faab654e91bdf7da56a3e92ca3.png
[db2ffdbfcb50cc85d8eec68bff342eb6.png]: /images/20220828/7dcf30facdb34990acab772d39e8936a.png
[bbf0ff30fabf0adc6c88f9d74cfdd2ff.png]: /images/20220828/ab7234e8396b436b8959f4ac0777b34c.png