容易题
每一行代表了这个订单的id、完成这个订单司机的id，这个订单的金额，订单完成时间，想要一个sql：如果某一天中，任何一个司机完成了5单及以上，且5单的总金额大于50元，把这天和对应的司机id输出。
输出列名：date,drier_id
知识点：子查询或者用having做筛选。
中等题
每一行代表这个用户是否在当天活跃过，如果一个用户在当天活跃过，且在未来的第2到第30天又活跃过，则称其为当天的活跃30天留存用户，比如表中a和b用户都在2019/1/1活跃了，a在2019/1/3活跃了，所以满足在2019/1/1的活跃30天留存条件，b没有在第2到第30天活跃过，因此不满足活跃30天留存。我想要每天的活跃用户数和活跃30天留存用户数
上表正确输出
知识点：留存的自连接写法，日期加减写法。
下面是我写的：
-- CREATE TABLE `test`.`Activate` (
-- `User_id` INT NULL,
-- `date` DATETIME NOT NULL);
-- * from test.Activate
-- insert into test.Activate values ('1','2019/01/01');
-- insert into test.Activate values ('1','2019/01/03');
-- insert into test.Activate values ('2','2019/01/01');
-- insert into test.Activate values ('2','2019/05/01');
-- -- insert into test.Activate values ('3','2019/01/01');
-- -- insert into test.Activate values ('3','2019/01/30');
-- select * from Activate
select a.date,count(a.user_id)as '每天活跃用户数',count(b.user_id) as'活跃30天留存用户数' from
(select User_id,date from Activate)a
left join (select User_id,date from Activate)b
on a.User_id=b.User_id
and date_add(a.date,interval 1 day)<=b.date and b.date<=date_add(a.date,interval 29 day)
group by 1
这里之前出了几次错，一个是用了inner join以后只有一条 01-01的数据
一个是on and我一开始用的是where，那也只有一条01-01的数据了
因为不满足的直接被筛选掉了。
另外查了这个用法 date_add(a.date,interval 1 day)是说加一天
然后这个双边好像写成<=b<=会出错
困难题：
里面第二行的结束时间应该为1:30
每行代表司机开始玩游戏的时间（start_time）和游戏结束时间（end_time），请问每个司机结束一场游戏后，平均多久时间内会开始下场游戏？如果司机只玩过一次游戏，就不计算该司机。
CREATE TABLE `test`.`game`
( `drier_id` INT NULL,
`start_time` DATETIME NOT NULL,
`end_time` DATETIME NOT NULL
);
insert into game values ('1','2019/01/01 00:00:00','2019/01/01 00:20:00');
insert into game values ('1','2019/01/01 01:15:00','2019/01/01 01:30:00');
insert into game values ('1','2019/01/01 02:00:00','2019/01/01 02:15:00');
insert into game values ('2','2019/01/01 01:15:00','2019/01/01 00:30:00');
一开始写错的，我还改了好多次，真的是完挂的节奏：
-- select a.drier_id,a.start_time,a.end_time,b.start_time,b.end_time
select a.drier_id,avg(datediff(b.start_time,a.end_time))as '平均游戏间隔' from 这里datediff是天的所以求出来0了，要用timestampdiff（minute/hour/day，开始时间小的，结束时间大的）
(select drier_id,start_time,end_time,rank() over (partition by start_time order by start_time) as rank_ from game)a 这里写错了 partition by starttime的话每一次都不一样，会有多个rank1，要换成id以后针对一个id分组，多个记录才会rank 123
left join (select drier_id,start_time,end_time,rank() over (partition by start_time order by start_time) as rank_ from game)b 写错了同理
on a.drier_id=b.drier_id
where a.rank_+1=b.rank_ and count(a.drier_id)>1 这里count应该是要放到having去
group by 1
-- having count(a.drier_id)>1但是因为where里面a必须要有2个及以上，所以这个不用写了。
最后对的：
select a.drier_id,avg(timestampdiff(minute,a.end_time,b.start_time))as '平均游戏间隔'
from
(select drier_id,start_time,end_time,rank() over (partition by drier_id order by start_time) as rank_ from game)a
left join (select drier_id,start_time,end_time,rank() over (partition by drier_id order by start_time) as rank_ from game)b
on a.drier_id=b.drier_id
where a.rank_+1=b.rank_
group by 1
上表正确输出
知识点：窗口函数row_number用法，时间加减写法。
关于窗口函数的题：解一下TMD几道热门数据分析面试题。
网友评论