在数据分析从数仓取数的过程中，我们经常需要根据不同的时间段对数据指标进行筛选和查询，比如今日、昨日、前日、本周、上周、本月、上月、本季、上季、本年、上年等。
接下来，分享我日常常用的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
