今天來(lái)深入探討 SQL 中 5 個(gè)最重要和最有用的 DATE 函數(shù)以及一些可以使用它們的實(shí)際業(yè)務(wù)案例。



正文




來(lái)源：DeepHub IMBA

一個(gè)優(yōu)秀的 SQL 開(kāi)發(fā)人員是能夠以他們喜歡的任何方式操作數(shù)據(jù)的——其中很大一部分是能夠操作日期。因?yàn)槿掌诜浅Ｖ匾?，比如企業(yè)喜歡比較和評(píng)估不同時(shí)間段的業(yè)務(wù)績(jī)效，統(tǒng)計(jì)一個(gè)時(shí)段的指標(biāo)，這些都離不開(kāi)日期函數(shù)，能夠操縱日期對(duì)于頂級(jí)業(yè)務(wù)運(yùn)營(yíng)和業(yè)務(wù)報(bào)告至關(guān)重要。

在本文中，我們將深入探討 SQL 中 5 個(gè)最重要和最有用的 DATE 函數(shù)以及一些可以使用它們的實(shí)際業(yè)務(wù)案例。

01

DATE_TRUNC()

 DATE_TRUNC(date_expression, date_part)

DATE_TRUNC() 將日期縮短為指定的日期部分。

在下面的示例中，由于我們指定了 date_part = MONTH，因此 date_trunc() 將截?cái)嗳掌谝垣@取該月的第一天：


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

如果我們指定 date_part = YEAR，那么我們將得到指定日期的年份的第一天：

什么時(shí)候使用？

DATE_TRUNC 在你希望定期（例如每周、每月或每年）匯總數(shù)字時(shí)非常有用。
DATE_TRUNC 在進(jìn)行分組分析時(shí)是必要的，你通常按月對(duì)用戶進(jìn)行分組。

示例：假設(shè)你想從下表中獲得每周的銷售額總和：


 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() 比較兩個(gè)日期并返回兩個(gè)日期之間日期部分的差異。

例如，如果 date_part = DAY，則 DATE_DIFF() 返回兩個(gè)日期之間的天數(shù)。如果 date_part = MONTH，則 DATE_DIFF() 返回兩個(gè)日期之間的 MONTH 數(shù)。


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

什么時(shí)候使用？

DATE_DIFF() 在你想要比較兩個(gè)日期時(shí)很有用，例如，包裹何時(shí)發(fā)貨和包裹何時(shí)交付，或者用戶何時(shí)注冊(cè)和何時(shí)取消。
DATE_DIFF() 在 WHERE 子句中也很有用，如果你想過(guò)濾 X 周期前發(fā)生的日期（例如 5 天前、2 周前、上個(gè)月）。

示例1 ：假設(shè)你想獲取包裹發(fā)貨時(shí)間和包裹送達(dá)時(shí)間之間的時(shí)間：


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

示例 2：使用上表，假設(shè)你想獲取所有發(fā)貨時(shí)間少于 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() 將指定數(shù)量添加到日期部分。相反，DATE_SUB 將日期指定減去數(shù)量。

 
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'

什么時(shí)候使用？

DATE_ADD() 和 DATE_SUB() 可以像 WHERE 子句中的 DATE_DIFF() 一樣使用，以過(guò)濾 X 周期前或?qū)?lái) X 周期發(fā)生的日期。

示例 1：假設(shè)你想獲取所有發(fā)貨時(shí)間少于 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() 返回與指定日期部分對(duì)應(yīng)的值。


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

什么時(shí)候使用？

使用 EXTRACT() 是獲取日期的特定部份的一種簡(jiǎn)單方法。
例如需要按周數(shù)報(bào)告，可以使用 EXTRACT() 獲取給定記錄的給定日期的周數(shù)。
EXTRACT() 允許你從日期中獲取月份數(shù)或年份，可用作機(jī)器學(xué)習(xí)模型的特征。

05

CURRENT_DATE()


 CURRENT_DATE([time_zone])

CURRENT_DATE() 返回在指定時(shí)區(qū)執(zhí)行查詢的當(dāng)前日期。注意時(shí)區(qū)參數(shù)是可選的，不需要指定。

什么時(shí)候使用？

使用 CURRENT_DATE() 是引用今天日期的一種更簡(jiǎn)單的方法，而不是硬編碼的日期，如果它是在 Airflow 上固化的查詢或你經(jīng)常使用的查詢，這尤其有用。

示例 1：假設(shè)你想獲取過(guò)去一周內(nèi)發(fā)貨的所有訂單：


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

示例 2：假設(shè)你想獲取與今天日期同月發(fā)貨的任何年份的所有訂單：

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

點(diǎn)擊下方卡片進(jìn)行關(guān)注，獲取更多內(nèi)容

點(diǎn)分享

點(diǎn)收藏

點(diǎn)點(diǎn)贊

點(diǎn)在看

數(shù)據(jù)科學(xué)面試中應(yīng)該知道的5個(gè)SQL日期函數(shù)

01

02

03

04

05