计算每周第一天和最后一天

最新推荐文章于 2022-09-12 20:50:59 发布

翟工的分享空间

最新推荐文章于 2022-09-12 20:50:59 发布

阅读量876

点赞数

分类专栏：数据仓库

本文链接：https://blog.csdn.net/weixin_39471936/article/details/105487737

版权

数据仓库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

计算每周第一天和最后一天

select
	day
	,dayofweek(day)                                                                    as dw1
	,date_add(day,1 - dayofweek(day))                                                  as Su_s -- 周日_start
	,date_add(day,7 - dayofweek(day))                                                  as Sa_e -- 周六_end
	,case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end                   as dw2
	,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as Mo_s -- 周一_start
	,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as Su_e -- 周日_end
	,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01'))          as season_first_day -- 当季第一天
	,trunc(day,'MM')                                                                   as month_first_day  -- 当月第一天
	,trunc(day,'YY')                                                                   as year_first_day   -- 当年第一天
	,last_day(add_months(trunc(day,'YY'),12))                                          as year_last_day    -- 当年最后一天
	,last_day(day)                                                                     as month_last_day   -- 当月最后一天
	,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01')))   as season_last_day  -- 当季最后一天
	,weekofyear(day)                                                                   as weekofyear1       -- 当年第几周
	,weekofyear('2019-08-07')  - weekofyear(trunc('2019-08-07','MM')) +1               as weekofmonth1   --当月第几周
	,second(day)                                                                       as second           -- 秒钟
	,minute(day)                                                                       as minute           -- 分钟
	,hour(day)                                                                         as hour             -- 小时
	,day(day)                                                                          as day              -- 日期
	,month(day)                                                                        as month            -- 月份
	,lpad(ceil(month(day)/3),2,0)                                                      as season           -- 季度
	,year(day)                                                                         as year             -- 年份
 from (
     select '2020-04-01' as day union all
     select '2020-04-02' as day union all
     select '2020-04-03' as day union all
     select '2020-04-04' as day union all
     select '2020-04-05' as day union all
     select '2020-04-06' as day union all
     select '2020-04-07' as day union all
     select '2020-04-08' as day union all
     select '2020-04-09' as day union all
     select '2020-04-10' as day union all
     select '2020-04-11' as day union all
     select '2020-04-12' as day union all
     select '2020-04-13' as day union all
     select '2020-04-14' as day union all
     select '2020-04-15' as day union all
     select '2020-04-16' as day union all
     select '2020-04-17' as day union all
     select '2020-04-18' as day union all
     select '2020-04-19' as day union all
     select '2020-04-20' as day union all
     select '2020-04-21' as day union all
     select '2020-04-22' as day
 ) t1