数据分析和数仓建设中周对周的环比和同比涉及到跨年的是个头疼的问题，
说这个跨年的时候，周不好计算，不知道1月1日算去年的最后一周还是算今年的第一周，我们在这里使用mysql
为例子进行说明，帮助大家如何解决这个问题
此时就必须要熟悉了解一下这MySql 中 week 函数 下面是介绍
WEEK(date, mode);有两个参数
date是日期，mode是模式，模式说明如下
模式 一周的第一天 范围 说明
weekofyear(日期)
这个是取日期是年的哪一个周，类似上面的week函数的 mode=3的情况。
2023-01-01 是星期天 我们按照我们的习惯，每周从周一开始，那么改天应该属于2022年的第52周
select weekofyear("2023-01-01") ,week("2023-01-01",3);
模式0 则属于2023年第一周
select week("2023-01-01",0)
我们可以采用week模式3 或 weekofyear 来进行数据处理，这样保证每周都是固定的7天，另一个问题是解决年初的日期到底是属于那一年的问题，我们可以创建一个时间的维度表来先将这些数据初始化好了，这样计算的时候直接使用时间维度表就可以了，在计算周的同比和环比时候直接按照码表走就可以了，
创建时间码表 dim_date
CREATE TABLE `dim_date` (
`date_id` bigint(20) NOT NULL,
`the_date` date DEFAULT NULL,
`the_day` int(11) DEFAULT NULL,
`the_month` int(11) DEFAULT NULL,
`the_year` int(11) DEFAULT NULL,
`day_of_month` int(11) DEFAULT NULL,
`week_of_year` int(11) DEFAULT NULL,
`week_year` varchar(7) DEFAULT NULL COMMENT '周是那年的字符串格式',
`week_year_y` varchar(7) DEFAULT NULL COMMENT 'week_year 同比',
`week_year_c` varchar(7) DEFAULT NULL COMMENT 'week_year 环比',
`weekday` varchar(3) DEFAULT NULL,
`quarter` int(11) DEFAULT NULL,
PRIMARY KEY (`date_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
创建辅助表 nums
create table nums(id int primary key)
insert into nums values(1),(2),(3),(4),(5),(6);
运行多次，保证nums中数据到达1万即可
insert into nums
select id +(select count(*) from nums) as id from nums;
产生dim_date最终表数据：
--插入数据
INSERT INTO dim_date
(`date_id`,
`the_date`,
`the_day`,
`the_month`,
`the_year`,
`day_of_month`,
`week_of_year`,
`week_year`,
`weekday`,
`quarter`)
select date_format(d,'%Y%m%d') as date_id ,
d as the_date,
dayofyear(d) as the_day,
month(d) as the_month,
year(d) as the_year,
dayofmonth(d) as day_of_moth,
week(d,3) as week_of_year,
case when week(d,3)>51 and dayofyear(d)<7 then concat(year(d)-1,'-', week(d,3))
when week(d,3)=1 and dayofyear(d)>360 then concat(year(d)+1,'-', week(d,3))
else concat(year(d),'-', week(d,3)) end as week_year,
case when dayofweek(d)=1 then '星期天'
when dayofweek(d)=2 then '星期一'
when dayofweek(d)=3 then '星期二'
when dayofweek(d)=4 then '星期三'
when dayofweek(d)=5 then '星期四'
when dayofweek(d)=6 then '星期五'
when dayofweek(d)=7 then '星期六'
end as weekday,
quarter(d) as quarter
from
(
select d from(
select date_add( '2015-12-01', interval id day ) as d
from nums) as re
where d >= '2016-01-01' and d< '2037-01-01'
) as res;
--更新week_year_c
update dim_date as a
left join dim_date as b on a.the_date= date_add( b.the_date , interval 7 day)
set a.week_year_c=b.week_year
# 如果超时 就使用下面where 语句分配更新即可
# where a.date_id between 20350101 and 20400101;
--更新week_year_y
update dim_date
set week_year_y =concat( left(week_year,4)-1, substring(week_year,5,3))
查询数据：
select *from dim_date
where the_day<4 or the_day>364
order by date_id;
这样我们就可以利用dim_date 来进行周的同比和环比计算了
网友评论