Python全系列 教程
3567个小节阅读:5930.7k
目录
鸿蒙应用开发
C语言快速入门
JAVA全系列 教程
面向对象的程序设计语言
Python全系列 教程
Python3.x版本,未来主流的版本
人工智能 教程
顺势而为,AI创新未来
大厂算法 教程
算法,程序员自我提升必经之路
C++ 教程
一门通用计算机编程语言
微服务 教程
目前业界流行的框架组合
web前端全系列 教程
通向WEB技术世界的钥匙
大数据全系列 教程
站在云端操控万千数据
AIGC全能工具班
A A
White Night
通过年月日计算对应的周数和星期几:
xxxxxxxxxx
select weekofyear('2030-01-01');
select pmod(datediff("2030-01-01","1970-01-01") - 3,7);
select concat('aa', 100, 5.5);
select unix_timestamp('2030-01-01','yyyy-MM-dd');
从中间临时表取数据进行分析计算:
xxxxxxxxxx
select user_id,cat_id,brand_id,item_id,seller_id,time_year,time_month,time_day,
unix_timestamp(concat(time_year,"-",if(time_month>9,time_month,concat("0",time_month)),"-",
if(time_day>9,time_day,concat("0",time_day))),'yyyy-MM-dd') time_stamp,
weekofyear(concat(time_year,"-",if(time_month>9,time_month,concat("0",time_month)),"-",
if(time_day>9,time_day,concat("0",time_day)))) week_year,
pmod(datediff(concat(time_year,"-",if(time_month>9,time_month,concat("0",time_month)),"-",
if(time_day>9,time_day,concat("0",time_day))),'1970-01-01')-3,7) week_day
from temp_user_log
limit 50;
创建结果表:
xxxxxxxxxx
create table if not exists td_userlog_year_month_day_week(
user_id int comment "买家id",
cat_id int comment "分类id",
brand_id int comment "品牌id",
item_id int comment "产品id",
seller_id int comment "卖家id",
time_year int comment "年",
time_month int comment "月",
time_day int comment "日",
time_stamp bigint comment "时间戳 单位秒",
week_year int comment "一年中的第几周",
week_day int comment "星期几"
)
row format delimited
fields terminated by ","
lines terminated by "\n";
将查询结果插入结果表中:
xxxxxxxxxx
from temp_user_log
insert into td_userlog_year_month_day_week
select user_id,cat_id,brand_id,item_id,seller_id,time_year,time_month,time_day,
unix_timestamp(concat(time_year,"-",if(time_month>9,time_month,concat("0",time_month)),"-",
if(time_day>9,time_day,concat("0",time_day))),'yyyy-MM-dd') time_stamp,
weekofyear(concat(time_year,"-",if(time_month>9,time_month,concat("0",time_month)),"-",
if(time_day>9,time_day,concat("0",time_day)))) week_year,
pmod(datediff(concat(time_year,"-",if(time_month>9,time_month,concat("0",time_month)),"-",
if(time_day>9,time_day,concat("0",time_day))),
"1970-01-01") - 3,7) week_day;
注意:0表示星期天。