实习周记（五）：SQL的练习

SQL练习

根据上周出现的问题进行针对性练习

题目链接

https://blog.csdn.net/fashion2014/article/details/78826299

https://www.nowcoder.com/ta/sql

练习过程中比较重要的知识点

连接函数

https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc

窗口函数

https://zhuanlan.zhihu.com/p/92654574

行列转换

case ？？（字段）

​ when ？（字段符合的条件）

​ then ？（满足后的展示效果）

​ else ？（不满足的展示效果）

end as ？（别名）

成果

yk出的一道题目，计算间隔1，3，7，15，30，45天新增用户的充值流水

CREATE TABLE `stat_user_retain` (
  `id` int NOT NULL AUTO_INCREMENT,
  `create_date` date DEFAULT NULL,
  `cid` int DEFAULT NULL,
  `game_id` int DEFAULT NULL,
  `master_id` int DEFAULT NULL,
  `reg_date` date DEFAULT NULL,
  `counts` int DEFAULT '0',
  `new_users` int DEFAULT '0',
  `diff_date` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date_cid` (`create_date`,`cid`,`reg_date`) USING BTREE,
  KEY `createDate` (`create_date`) USING BTREE,
  KEY `cid` (`cid`) USING BTREE,
  KEY `regDate` (`reg_date`) USING BTREE,
  KEY `diffDate` (`diff_date`) USING BTREE,
  KEY `gameId` (`game_id`) USING BTREE,
  KEY `masterId` (`master_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


delimiter $$
drop procedure if exists insert_dept;
create procedure insert_dept(in max_num int(10))
begin
declare i int default 0;
declare z int default 0;
repeat
set i=i+1;

	repeat
		set z=z+1;
			insert into stat_user_retain (create_date,cid,game_id,master_id,reg_date,counts,new_users,diff_date) values(DATE_ADD(DATE('2021-06-01'),INTERVAL z-1 DAY),111,1,1,DATE_ADD(DATE('2021-06-01'),INTERVAL i-1 DAY),floor(100+rand()*100),100,
datediff(DATE_ADD(DATE('2021-06-01'),INTERVAL z-1 DAY),DATE_ADD(DATE('2021-06-01'),INTERVAL i-1 DAY)));
		until z=max_num
	end repeat;
set z=i;
until i=max_num
end repeat;
commit;
end $$


call insert_dept(71);


#执行脚本时，修改cid、game_id、master_id的值

SELECT create_date,
case diff_date 
	when 1 then round((counts*1.0/new_users),2) 
	else 0
end as oneLtv,
case diff_date 
	when 3 then round((counts*1.0/new_users),2) 
	else 0
end as threeLtv,
case diff_date 
	when 7 then round((counts*1.0/new_users),2) 
	else 0
end as sevenLtv,
case diff_date 
	when 15 then round((counts*1.0/new_users),2) 
	else 0
end as fifteenLtv,
case diff_date 
	when 30 then round((counts*1.0/new_users),2) 
	else 0
end as thirtyLtv,
case diff_date 
	when 45 then round((counts*1.0/new_users),2) 
	else 0
end as fortyFiveLtv
FROM stat_user_retain
GROUP BY create_date
ORDER BY create_date;

以上为上周主要进行内容，开会决定的新项目需求分析等正式启动再一起总结