在數(shù)據(jù)分析從數(shù)倉取數(shù)的過程中，我們經(jīng)常需要根據(jù)不同的時(shí)間段對數(shù)據(jù)指標(biāo)進(jìn)行篩選和查詢，比如今日、昨日、前日、本周、上周、本月、上月、本季、上季、本年、上年等。
接下來，分享我日常常用的SQL語句。
為了避免時(shí)間的跨年情況，我在文章的4.本周部分列舉了一個(gè)網(wǎng)上能搜索到的其他寫法，如果寫定期SQL腳本提取數(shù)據(jù)，不建議！
什么是時(shí)間的跨年情況？
比如現(xiàn)在是2024-01-18，獲取本周的函數(shù)用WEEKOFYEAR，得到的結(jié)果是3，2023-01-18得到的結(jié)果也是3，那么在一個(gè)時(shí)間范圍比較大或者含歷史全量數(shù)據(jù)的表中取本周的數(shù)據(jù)，可能將之前年份的周數(shù)據(jù)也都取了，導(dǎo)致數(shù)據(jù)不準(zhǔn)確。
假設(shè)表中的日期字段為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)));
-- 網(wǎng)絡(luò)常搜到以下寫法，但我不建議，會(huì)遇到文章開頭說的跨年的數(shù)據(jù)問題
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');
-- 方式二，將一周的日期都轉(zhuǎn)化為該日期對應(yīng)周的第一天
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函數(shù)很好用，適用于PostgreSQL或者一些企業(yè)自研的云數(shù)據(jù)庫，比如華為云的GAUSSDB等。但是MySQL不支持。所以使用MySQL的小伙伴可以試試上述的方式二。
以上就是如何通過SQL語句來篩選和查詢不同時(shí)間段的數(shù)據(jù)，希望對你的數(shù)據(jù)分析工作有所幫助，也歡迎各位一起交流，探討更高效、簡潔的實(shí)現(xiàn)上述效果的SQL寫法。
該文章在 2024/1/23 12:51:45 編輯過
400 186 1886
