溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“SQL的日期與期間怎么使用”，在日常操作中，相信很多人在SQL的日期與期間怎么使用問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”SQL的日期與期間怎么使用”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
日期與時間段的篩選在工作中是經常被用到的，因為在拉取報表、儀表板和各種分析時，周、月、季度、年度的表現往往是分析需要考量的重點。
-- field可以是day、hour、minute, month, quarter等等
-- source可以是date、timestamp類型
extract(field FROM source)
SELECT extract(year FROM '2020-08-05 09:30:08'); -- 結果為 2020
SELECT extract(quarter FROM '2020-08-05 09:30:08'); -- 結果為 3
SELECT extract(month FROM '2020-08-05 09:30:08'); -- 結果為 8
SELECT extract(week FROM '2020-08-05 09:30:08'); -- 結果為 31,一年中的第幾周
SELECT extract(day FROM '2020-08-05 09:30:08'); -- 結果為 5
SELECT extract(hour FROM '2020-08-05 09:30:08'); -- 結果為 9
SELECT extract(minute FROM '2020-08-05 09:30:08'); -- 結果為 30
SELECT extract(second FROM '2020-08-05 09:30:08'); -- 結果為 8
上面可供提取的字段，不同的數據庫存在些許的差異。以Hive為例，支持day, dayofweek, hour, minute, month, quarter, second, week 和 year。其中周、月、年使用最為廣泛，因為無論是公司內部產品，還是商用的產品所提供的數據后臺統計，周報和月報(比如近7天、近30天)最注重表現的周期。
注意：
impala支持：YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, EPOCH
Hive支持：day, dayofweek, hour, minute, month, quarter, second, week 和 year
Hive是從Hive2.2.0版本開始引入該函數
在按照周的區間進行統計時，需要識別出周一的日期與周日的日期，這個時候經常會用到下面的函數：
next_day(STRING start_date, STRING day_of_week)
-- 返回當前日期對應的下一個周幾對應的日期
-- 2020-08-05為周三
SELECT next_day('2020-08-05','MO') -- 下一個周一對應的日期：2020-08-10
SELECT next_day('2020-08-05','TU') -- 下一個周二對應的日期：2020-08-11
SELECT next_day('2020-08-05','WE') -- 下一個周三對應的日期：2020-08-12
SELECT next_day('2020-08-05','TH') -- 下一個周四對應的日期：2020-08-06，即為本周四
SELECT next_day('2020-08-05','FR') -- 下一個周五對應的日期：2020-08-07，即為本周五
SELECT next_day('2020-08-05','SA') -- 下一個周六對應的日期：2020-08-08，即為本周六
SELECT next_day('2020-08-05','SU') -- 下一個周日對應的日期：2020-08-09，即為本周日
-- 星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
那么該如何獲取當前日期所在周的周一對應的日期呢？只需要先獲取當前日期的下周一對應的日期，然后減去7天，即可獲得：
SELECT date_add(next_day('2020-08-05','MO'),-7);
同理，獲取當前日期所在周的周日對應的日期，只需要先獲取當前日期的下周一對應的日期，然后減去1天，即可獲得：
select date_add(next_day('2020-08-05','MO'),-1)
-- 2020-08-09 至于怎么將月份從單一日期提取出來呢，LAST_DAY這個函數可以將每個月中的日期變成該月的最后一天(28號，29號，30號或31號)，如下：
last_day(STRING date)
SELECT last_day('2020-08-05'); -- 2020-08-31
除了上面的方式，也可以使用date_format函數，比如：
SELECT date_format('2020-08-05','yyyy-MM');
-- 2020-08
月的Window：使用add_months加上trunc()的應用
-- 返回加減月份之后對應的日期
-- 2020-07-05
select add_months('2020-08-05', -1)
-- 返回當前日期的月初日期
-- 2020-08-01
select trunc("2020-08-05",'MM')
由上面范例可見，單純使用add_months，減N個月的用法，可以剛好取到整數月的數據，但如果加上trunc()函數，則會從前N個月的一號開始取值。
-- 選取2020-07-05到2020-08-05所有數據
BETWEEN add_months('2020-08-05', -1) AND '2020-08-05'
-- 選取2020-07-01到2020-08-05之間所有數據
BETWEEN add_months(trunc("2020-08-05",'MM'),-1) AND '2020-08-05'
這兩種方法是日常工作中經常被使用到，對于一些比較復雜的計算任務，為了避免過多的JOIN，通常會先把一些需要提取的部分數據使用臨時表或是CTE的形式在主要查詢區塊前進行提取。
臨時表的作法：
CREATE TEMPORARY TABLE table_1 AS
SELECT
columns
FROM table A;
CREATE TEMPORARY table_2 AS
SELECT
columns
FROM table B;
SELECT
table_1.columns,
table_2.columns,
c.columns
FROM table C JOIN table_1
JOIN table_2;
CTE的作法：
-- 注意Hive、Impala支持這種語法，低版本的MySQL不支持(高版本支持)
WITH employee_by_title_count AS (
SELECT
t.name as job_title
, COUNT(e.id) as amount_of_employees
FROM employees e
JOIN job_titles t on e.job_title_id = t.id
GROUP BY 1
),
salaries_by_title AS (
SELECT
name as job_title
, salary
FROM job_titles
)
SELECT *
FROM employee_by_title_count e
JOIN salaries_by_title s ON s.job_title = e.job_title
可以看到TEMP TABLE和CTE WITH的用法其實非常類似，目的都是為了讓你的Query更加一目了然且優雅簡潔。很多人習慣將所有的Query寫在單一的區塊里面，用過多的JOIN或SUBQUERY，導致最后邏輯丟失且自己也搞不清楚寫到哪里，適時的使用TEMP TABLE和CTE作為輔助，絕對是很加分的。
將Aggregation function (SUM/COUNT/COUNT DISTINCT/MIN/MAX) 結合CASE WHEN是最強大且最有趣的使用方式。這樣的使用創造出一種類似EXCEL中SUMIF/COUNTIF的效果，可以用這個方式做出很多高效的分析。
CREATE TABLE order(
register_date string,
order_date string,
user_id string,
country string,
order_sales decimal(10,2),
order_id string);
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","001",'c0',210,"o1");
INSERT INTO TABLE order VALUES("2020-06-08","2020-06-09","002",'c1',220,"o2");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-10","003",'c2',230,"o3");
INSERT INTO TABLE order VALUES("2020-06-09","2020-06-10","004",'c3',200,"o4");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-20","005",'c4',300,"o5");
INSERT INTO TABLE order VALUES("2020-06-10","2020-06-23","006",'c5',400,"o6");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-19","007",'c6',600,"o7");
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-18","008",'c7',700,"o8");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","009",'c8',100,"o9");
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-18","0010",'c9',200,"o10");
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-19","0011",'c10',250,"o11");
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-29","0012",'c11',270,"o12");
INSERT INTO TABLE order VALUES("2020-06-16","2020-06-19","0013",'c12',230,"o13");
INSERT INTO TABLE order VALUES("2020-06-17","2020-06-20","0014",'c13',290,"o14");
INSERT INTO TABLE order VALUES("2020-06-20","2020-06-29","0015",'c14',203,"o15");
-- 允許多列去重
set hive.groupby.skewindata = false
-- 允許使用位置編號分組或排序
set hive.groupby.orderby.position.alias = true
SELECT
date_add(Next_day(register_date, 'MO'),-1) AS week_end,
COUNT(DISTINCT CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN user_id END) AS first_week_order,
COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN user_id END) AS sencod_week_order,
COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,14) AND date_add(register_date,20) THEN user_id END) as third_week_order
FROM order
GROUP BY 1
上面的示例可以得知到用戶在注冊之后，有沒有創建訂單的行為。比如注冊后的第一周，第二周，第三周分別有多少下單用戶，這樣可以分析出用戶的使用情況和留存情況。
注意：上面的使用方式，需要配置兩個參數：
hive.groupby.skewindata = false：允許多列去重，否則報錯：
SemanticException [Error 10022]: DISTINCT on different columns not supported with skew in datahive.groupby.orderby.position.alias = true：允許使用位置編號分組或排序,否則報錯：
SemanticException [Error 10025]: line 79:13 Expression not in GROUP BY key ''MO''
SELECT
user_id,
SUM (CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN order_sales END) AS first_week_amount,
SUM (CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN order_sales END) AS second_week_amount
FROM order
GROUP BY 1
通過篩選出注冊與消費的日期，并且進行消費金額統計，每個用戶在每段時間段(注冊后第一周、第二周…以此類推)的消費金額，可以觀察用戶是否有持續維持消費習慣或是消費金額變低等分析。
SELECT
user_id,
COUNT(DISTINCT CASE WHEN order_sales >= 100 THEN order_id END) AS count_of_order_greateer_than_100
FROM order
GROUP BY 1
上面的示例就是類似countif的用法，針對每個用戶，統計其訂單金額大于某個值的訂單數量，分析去篩選出高價值的顧客。
SELECT
user_id,
MIN(CASE WHEN order_sales > 100 THEN order_date END) AS first_order_date_over1000,
MAX(CASE WHEN order_sales > 100 THEN order_date END) AS recent_order_date_over100
FROM order
GROUP BY 1
CASE WHEN加上MIN/MAX時間，可以得出該用戶在其整個使用過程中，首次購買超過一定金額的訂單日期，以及最近一次購買超過一定金額的訂單日期。
Window Function既是工作中經常使用的函數，也是面試時經常被問到的問題。常見的使用場景是分組取topN。本文介紹的另外一個用法，使用開窗函數進行用戶訪問session分析。
session是指在指定的時間段內用戶在網站上發生的一系列互動。例如，一次session可以包含多個網頁瀏覽、事件、社交互動和電子商務交易。session就相當于一個容器，其中包含了用戶在網站上執行的操作。
session具有一個過期時間，比如30分鐘，即不活動狀態超過 30 分鐘，該session就會過時。
假設張三訪問了網站，從他到達網站的那一刻開始，就開始計時。如果過了 30 分鐘，而張三仍然沒有進行任何形式的互動，則視為本次session結束。但是，只要張三與某個元素進行了互動（例如發生了某個事件、社交互動或打開了新網頁），就會在該次互動的時間基礎上再增加 30 分鐘，從而重置過期時間。
CREATE TABLE user_visit_action(
user_id string,
session_id string,
page_url string,
action_time string);
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://a.com","2020-08-06 13:34:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://b.com","2020-08-06 13:35:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://c.com","2020-08-06 13:36:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://a.com","2020-08-06 14:30:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://b.com","2020-08-06 14:31:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://e.com","2020-08-06 14:33:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://f.com","2020-08-06 14:35:11.478");
INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://u.com","2020-08-06 18:34:11.478");
INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://k.com","2020-08-06 18:38:11.478");
范例的資料表如上，有使用者、訪次和頁面的連結和時間。以下則使用partition by來表達每個使用者在不同訪次之間的瀏覽行為。
SELECT
user_id,
session_id,
page_url,
DENSE_RANK() OVER (PARTITION BY user_id, session_id ORDER BY action_time ASC) AS page_order,
MIN(action_time) OVER (PARTITION BY user_id, session_id) AS session_start_time,
MAX(action_time) OVER (PARTITION BY user_id, session_id) AS session_finisht_time
FROM user_visit_action 上面的查詢會返回針對每個用戶、每次的到訪，瀏覽頁面行為的先后次序，以及該session開始與結束的時間，以此為基礎就可以將這個結果存入TEMP TABLE或是CTE ，進行更進一步的分析。
到此，關于“SQL的日期與期間怎么使用”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
