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

俊红的数据分析之路

共 2968字,需浏览 6分钟

 ·

2021-10-01 18:36

今天来深入探讨 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 在进行分组分析时是必要的,你通常按月对用户进行分组。

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


 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 :假设你想获取包裹发货时间和包裹送达时间之间的时间:


 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 天的订单:


 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:假设你想获取过去一周内发货的所有订单:


 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())


点击下方卡片进行关注,获取更多内容
点分享
点收藏
点点赞
点在看
浏览 20
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报