數(shù)據(jù)科學(xué)面試中應(yīng)該知道的5個SQL日期函數(shù)
來源:DeepHub IMBA
一個優(yōu)秀的 SQL 開發(fā)人員是能夠以他們喜歡的任何方式操作數(shù)據(jù)的——其中很大一部分是能夠操作日期。因為日期非常重要,比如企業(yè)喜歡比較和評估不同時間段的業(yè)務(wù)績效,統(tǒng)計一個時段的指標,這些都離不開日期函數(shù),能夠操縱日期對于頂級業(yè)務(wù)運營和業(yè)務(wù)報告至關(guān)重要。
在本文中,我們將深入探討 SQL 中 5 個最重要和最有用的 DATE 函數(shù)以及一些可以使用它們的實際業(yè)務(wù)案例。
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,那么我們將得到指定日期的年份的第一天:
什么時候使用?
DATE_TRUNC 在你希望定期(例如每周、每月或每年)匯總數(shù)字時非常有用。
DATE_TRUNC 在進行分組分析時是必要的,你通常按月對用戶進行分組。
示例 :假設(shè)你想從下表中獲得每周的銷售額總和:
with sales_data 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
DATE_DIFF()
DATE_DIFF(date_expression_1, date_expression_2, date_part)
DATE_DIFF() 比較兩個日期并返回兩個日期之間日期部分的差異。
例如,如果 date_part = DAY,則 DATE_DIFF() 返回兩個日期之間的天數(shù)。如果 date_part = MONTH,則 DATE_DIFF() 返回兩個日期之間的 MONTH 數(shù)。
DATE_DIFF('2021-01-02', '2021-01-01', DAY) = 1
什么時候使用?
DATE_DIFF() 在你想要比較兩個日期時很有用,例如,包裹何時發(fā)貨和包裹何時交付,或者用戶何時注冊和何時取消。
DATE_DIFF() 在 WHERE 子句中也很有用,如果你想過濾 X 周期前發(fā)生的日期(例如 5 天前、2 周前、上個月)。
示例1 :假設(shè)你想獲取包裹發(fā)貨時間和包裹送達時間之間的時間:
SELECT order_id , DATE_DIFF(date_received, date_shipped, DAY) as shipping_time FROM orders
示例 2:使用上表,假設(shè)你想獲取所有發(fā)貨時間少于 10 天的訂單:
SELECT order_id , amount FROM orders WHERE DATE_DIFF(date_received, date_shipped, DAY) < 10
DATE_ADD() / DATE_SUB()
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'
什么時候使用?
DATE_ADD() 和 DATE_SUB() 可以像 WHERE 子句中的 DATE_DIFF() 一樣使用,以過濾 X 周期前或?qū)?X 周期發(fā)生的日期。
示例 1:假設(shè)你想獲取所有發(fā)貨時間少于 10 天的訂單:
SELECT order_id , amount FROM orders WHERE DATE_ADD(date_shipped, INTERVAL 10 DAY) > date_received
EXTRACT()
EXTRACT(part FROM date_expression)
EXTRACT() 返回與指定日期部分對應(yīng)的值。
EXTRACT(DAY FROM '2021-01-03') = 3 EXTRACT(MONTH FROM '2021-01-03') = 1 EXTRACT(YEAR FROM '2021-01-03') = 2021
什么時候使用?
使用 EXTRACT() 是獲取日期的特定部份的一種簡單方法。
例如需要按周數(shù)報告,可以使用 EXTRACT() 獲取給定記錄的給定日期的周數(shù)。
EXTRACT() 允許你從日期中獲取月份數(shù)或年份,可用作機器學(xué)習(xí)模型的特征。
CURRENT_DATE()
CURRENT_DATE([time_zone])
CURRENT_DATE() 返回在指定時區(qū)執(zhí)行查詢的當(dāng)前日期。注意時區(qū)參數(shù)是可選的,不需要指定。
什么時候使用?
使用 CURRENT_DATE() 是引用今天日期的一種更簡單的方法,而不是硬編碼的日期,如果它是在 Airflow 上固化的查詢或你經(jīng)常使用的查詢,這尤其有用。
示例 1:假設(shè)你想獲取過去一周內(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())
編輯:于騰凱
校對:汪雨晴
*博客內(nèi)容為網(wǎng)友個人發(fā)布,僅代表博主個人觀點,如有侵權(quán)請聯(lián)系工作人員刪除。