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

以你之姓@ 2022-11-13 00:56 216阅读 0赞

### 1 dim\_pss\_calendar 原始表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70]

### 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 查询结果 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70]: /images/20221022/4eedd49fb0ac41a895f6da91a022ae6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210324203635556.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlaW5taW5uYQ==,size_16,color_FFFFFF,t_70