sql需求处理篇-统计指定某年中有多少个周一至周日

需求描述

统计某一年中周一至周日分别有多少天

with date_list as(
    select date_add("2020-01-01", a.pos) as dates
    from (select posexplode(split(repeat("m", datediff("2020-12-31", "2020-01-01")), "m"))) a
)

select dates
from date_list 
;

----
2020-01-01
2020-01-02
2020-01-03
2020-01-04
2020-01-05
...
2020-12-31

-- 最终sql
with date_list as(
    select date_add("2020-01-01", a.pos) as dates
    from (select posexplode(split(repeat("m", datediff("2020-12-31", "2020-01-01")), "m"))) a
)

select 
     num,
     count(1)
from (
select 
      dates ,
      dayofweek(dates) -1 as num
from date_list
)res
group by num
order by num

--- 0表示周天 1-6表示周一到周六
星期      天数
 0	   52
 1	   52
 2	   52
 3	   53
 4	   53
 5	   52
 6	   52

抽象代码

with date_list as(
    select date_add("${start_date}", a.pos) as dates
    from (select posexplode(split(repeat("${anyDelimiter}", datediff("${end_date}", "${start_date}")), "${anyDelimiter}"))) a
)

select 
     num,
     count(1)
from (
select 
      dates ,
      dayofweek(dates) -1 as num
from date_list
)res
group by num
order by num