在數據分析從數倉取數的過程中，我們經常需要根據不同的時間段對數據指標進行篩選和查詢，比如今日、昨日、前日、本周、上周、本月、上月、本季、上季、本年、上年等。
接下來，分享我日常常用的SQL語句。
為了避免時間的跨年情況，我在文章的4.本周部分列舉了一個網上能搜索到的其他寫法，如果寫定期SQL腳本提取數據，不建議！
什么是時間的跨年情況？
比如現在是2024-01-18，獲取本周的函數用WEEKOFYEAR，得到的結果是3，2023-01-18得到的結果也是3，那么在一個時間范圍比較大或者含歷史全量數據的表中取本周的數據，可能將之前年份的周數據也都取了，導致數據不準確。
假設表中的日期字段為day_key，且格式為YYYY-MM-DD：
1.今日
SELECT * FROM table WHERE DATE(time_column) = current_date;
2.昨日
SELECT * FROM table WHERE DATE(time_column) = current_date - 1;
3.前日
SELECT * FROM table WHERE DATE(time_column) = current_date - 2;
4.本周
-- 方式一
SELECT * FROM table WHERE date_trunc('week',date(day_key)) = date_trunc('week',current_date);
-- 方式二
SELECT * FROM table WHERE subdate(date(day_key),weekday(date(day_key))) = subdate(date(current_date),weekday(date(current_date)));
-- 網絡常搜到以下寫法，但我不建議，會遇到文章開頭說的跨年的數據問題
SELECT * FROM table WHERE WEEKOFYEAR(day_key) = WEEKOFYEAR(current_date);
5.上周
-- 方式一
SELECT * FROM table WHERE date_trunc('week',date(day_key)) = (date_trunc('week',current_date) - interval '1 week');
-- 方式二，將一周的日期都轉化為該日期對應周的第一天
SELECT * FROM table WHERE subdate(date(day_key),weekday(date(day_key))) = subdate(date(current_date),weekday(date(current_date))) - INTERVAL '1' week;
6.本月
-- 方式一
SELECT * FROM table WHERE date_trunc('month',date(day_key)) = date_trunc('month',current_date);
-- 方式二
SELECT * FROM table WHERE substring(day_key,0,8) = substring(current_date,0,8);
7.上月
-- 方式一
SELECT * FROM table WHERE date_trunc('month',date(day_key)) = (date_trunc('month',current_date) - interval '1 month');
-- 方式二
SELECT * FROM table WHERE substring(day_key,0,8) = substring(DATE_SUB(NOW(), INTERVAL '1' MONTH),0,8);
8.本季
-- 方式一
SELECT * FROM table WHERE date_trunc('quarter',date(day_key)) = date_trunc('quarter',current_date);
-- 方式二
SELECT * FROM table WHERE concat(year(day_key),'-Q',quarter(day_key)) = concat(year(current_date),'-Q',quarter(current_date));
SELECT * FROM table WHERE concat(year(day_key),'-Q',quarter(day_key)) = concat(year(current_date),'-Q',quarter(current_date));
9.上季
-- 方式一
SELECT * FROM table WHERE date_trunc('quarter',date(day_key)) = (date_trunc('quarter',current_date) - interval '3 month');
-- 方式二
SELECT * FROM table WHERE concat(year(day_key),'-Q',quarter(day_key)) = concat(year(current_date - INTERVAL '1' quarter),'-Q',quarter(current_date - INTERVAL '1' quarter));
10.本年
-- 方式一
SELECT * FROM table WHERE extract( year from date(day_key)) = extract( year from current_date);
-- 方式二
SELECT * FROM table WHERE YEAR(time_column) = YEAR(NOW());
11.上年
-- 方式一
SELECT * FROM table WHERE extract( year from date(day_key)) = extract( year from current_date);
-- 方式二
SELECT * FROM table WHERE YEAR(time_column) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR));
date_trunc函數很好用，適用于PostgreSQL或者一些企業自研的云數據庫，比如華為云的GAUSSDB等。但是MySQL不支持。所以使用MySQL的小伙伴可以試試上述的方式二。
以上就是如何通過SQL語句來篩選和查詢不同時間段的數據，希望對你的數據分析工作有所幫助，也歡迎各位一起交流，探討更高效、簡潔的實現上述效果的SQL寫法。
該文章在 2024/1/23 12:51:45 編輯過
400 186 1886
