sql 查询每个季度最后一周的第一天

1 dim_pss_calendar 原始表

2 sql 语句

-- 精炼字段
with origin as (
select fiscal_year as year,fiscal_quarter as quarter,SPLIT_PART(fiscal_week,'-',1) as week,date from dim_pss_calendar where 1=1 group by fiscal_year,fiscal_quarter,fiscal_week,date order by fiscal_year,fiscal_quarter,fiscal_week,date
),
-- 查询 每周最早的一天
 minday as (
select year,quarter,week,min(date) as day from origin  group by year,quarter,week order by year,quarter,week
)
,

-- 查询 每个季度最后的一周
maxweek as (
select year,quarter,max(week) as week from origin group by year,quarter order by year,quarter
)
select minday.* from maxweek inner JOIN minday on maxweek.year=minday.year and maxweek.quarter=minday.quarter and maxweek.week=minday.week
order by minday.year,minday.quarter,minday.week

3 查询结果