1 引言
在日常查询中，我们经常面临以下两个需求
对如下表单
需求 1
一个数据集中，查询每个月最后一天的数据
也就是 1 月 31 日，2 月 28 日，3 月 31 日 ,4 月 30 日 .... 的数据，对应图中蓝色的数据
需求 2
在一个数据集中，查询每个月的有数据的最后一天的记录，对应图中蓝色和红色的数据
2 查询每个月最后一天的记录
思路 1：抽取每条记录天数
每个与最后一天的数据的下一天一定是 1 号，因此，将日期+1 天，然后使用 extract 函数，获取日期的 “”“天”数据，如果天数是 1 ，那么对应日期就是天。
ex：
with date_result as ( select to_date('2023-01-01','yyyy-MM-dd') as enddate union all select to_date('2023-02-28','yyyy-MM-dd') as enddate union all select to_date('2023-03-01','yyyy-MM-dd') as enddate union all select to_date('2023-04-30','yyyy-MM-dd') as enddate ) select enddate from date_result where extract (day from (enddate+interval '1 day'))=1
复制代码
结果为：
2023-02-28
2023-04-30
思路 1 的方式，需要对 enddate 应用一个日期抽取函数，会导致索引无法命中，在实际生产环境中，如果表的数据量较大，性能可能会存在问题。
思路 2 ：生成每个月的最后一天
使用 日期函数 ，
date_trunc('month', current_date) 可以获取任意日期的当月的第一天，比如
2023-01-04 应用 select date_trunc('month', to_date('2023-01-04','yyyy-MM-dd'))得到的结果是 2023-01-01
基于此，我们的可以得到任意一个月的首日，然后将该日期+1month ，再减一天，即可得到该月的最后一天。SQL 如下：
select date_trunc('month', current_date) + INTERVAL '1 month'- interval '1 day'
复制代码
基于此，可以获取日期区间范围内的每个月的最后一天：
SELECT (date_trunc('month', generate_series(start_date, end_date, INTERVAL '1 month')) + INTERVAL '1 month'- interval '1 day')::date AS last_day_of_monthFROM ( SELECT DATE '2020-01-01' AS start_date, DATE '2020-05-01' AS end_date) AS subquery;
复制代码
结果为
2020-01-31
2020-02-29
2020-03-31
2020-04-30
2020-05-31
因此，要获取一个集合中每个与最后一天的数据，最终 SQL 可以设为
with date_result as (
select to_date('2023-01-01','yyyy-MM-dd') as enddate , 1 as value union all select to_date('2023-02-28','yyyy-MM-dd') as enddate , 2 as value union all select to_date('2023-03-01','yyyy-MM-dd') as enddate ,3 as value union all select to_date('2023-04-30','yyyy-MM-dd') as enddate , 4 as value ) select * from date_result t1 inner join
( SELECT (date_trunc('month', generate_series(start_date, end_date, INTERVAL '1 month')) + INTERVAL '1 month'- interval '1 day')::date AS last_day_of_monthFROM ( SELECT DATE '2023-01-01' AS start_date, DATE '2023-05-01' AS end_date) x ) t2 on t1.enddate = t2.last_day_of_month and t1.enddate>=DATE '2023-01-01' and t1.enddate<= DATE '2023-05-01'
复制代码
结果集为：
这种方法可以命中主表的索引，因此效率高于思路 1
3 每个月的有数据的最后一天的记录
我们可以使用以下语句获取一个日期的年份 和月份
select extract(year from current_date) current_year_num, extract (month from current_date) current_month_num
复制代码
current_date 是 2024-05-13
以上 SQL 的结果集为
然后 按年份和月份进行分组，分组后按照日期降序排列，取第一条数据
SQL 代码如下：
with date_result as (
select to_date('2023-01-01','yyyy-MM-dd') as enddate , 1 as value union all select to_date('2023-01-13','yyyy-MM-dd') as enddate , 13 as value union all select to_date('2023-02-28','yyyy-MM-dd') as enddate , 2 as value union all select to_date('2023-03-13','yyyy-MM-dd') as enddate ,3 as value union all select to_date('2023-03-24','yyyy-MM-dd') as enddate ,3 as value union all select to_date('2023-04-30','yyyy-MM-dd') as enddate , 4 as value ) select enddate , valuefrom ( select enddate , value , row_number() over(partition by current_year_num, current_month_num order by enddate desc ) as rn from ( select enddate, value, extract(year from enddate) current_year_num, extract (month from enddate) current_month_num from date_result ) x ) ywhere y.rn = 1
复制代码
数据结果集如下：
评论