第一题
需求：
已知一个表order，有如下字段：date_time，order_id，user_id，amount。数据样例：2020-10-10，1003003981，000000001，1000，请用sql进行统计： （1）2019年每个月的订单数、用户数、总成交金额 （2）2020年10月的新客数（指在2020年10月才有第一笔订单）
实现：
-- date_format(date_time,'yyyy-MM')
select month(date_time),
count(order_id) as order_cnt,
count(distinct user_id) as user_cnt,
sum(amount) as total_amount
from order
where year(date_time)=2019
group by month(date_time);
select *
from order
group by user_id
having date_format(min(date_time),'yyyy-MM')='2020-10';
第二题
需求：
存在如下客户访问商铺的数据，访问日志存储的表名为user_visit，访客的用户id为user_id，被访问的店铺名称为shop_name。 数据如下：
|user_id | shop_name| |——–|———–| | u1|beautiful_a| | u2|beautiful_b| | u1|beautiful_b| | u3|beautiful_c| | u4|beautiful_b| | u1|beautiful_a| | u5|beautiful_b| | u4|beautiful_b| | u6|beautiful_c| | u1|beautiful_b| | u2|beautiful_a| | u5|beautiful_a| （1）统计每个店铺的访问uv； （2）统计每家店铺的前三访客；
实现：
DROP table if EXISTS user_visit;
-- CREATE TABLE user_visit(
-- user_id VARCHAR(20),
-- shop_name VARCHAR(20)
-- );
--
--
-- insert into user_visit values ('u2','beautiful_b');
-- insert into user_visit values ('u1','beautiful_b');
-- insert into user_visit values ('u3','beautiful_c');
-- insert into user_visit values ('u4','beautiful_b');
-- insert into user_visit values ('u1','beautiful_a');
-- insert into user_visit values ('u5','beautiful_b');
-- insert into user_visit values ('u4','beautiful_b');
-- insert into user_visit values ('u6','beautiful_c');
-- insert into user_visit values ('u1','beautiful_b');
-- insert into user_visit values ('u2','beautiful_a');
-- insert into user_visit values ('u5','beautiful_a');
select shop_name,count(distinct user_id) as uv
from user_visit
group by shop_name;
-- postgresql
select *
from
(select shop_name,user_id,cnt,row_number() over (partition by shop_name order by cnt desc) as rank_cnt
from
(select shop_name,user_id,count(*) as cnt
from user_visit
group by shop_name,user_id) as t1
) as t2
where rank_cnt<4;
第三题
需求：
有如下的用户访问数据
| user_id | visit_date | visit_count |
|---|---|---|
| u01 | 2017/1/21 | 5 |
| u02 | 2017/1/23 | 6 |
| u03 | 2017/1/22 | 8 |
| u04 | 2017/1/20 | 3 |
| u01 | 2017/1/23 | 6 |
| u01 | 2017/2/21 | 8 |
| u02 | 2017/1/23 | 6 |
| u01 | 2017/2/22 | 4 |
要求使用SQL统计出每个用户的累积访问次数，如下表所示：
| user_id | visit_month | month_total_visit_cnt | total_visit_cnt |
|---|---|---|---|
| u01 | 2017-01 | 11 | 11 |
| u01 | 2017-02 | 12 | 23 |
| u02 | 2017-01 | 12 | 12 |
| u03 | 2017-01 | 8 | 8 |
| u04 | 2017-01 | 3 | 3 |
实现：
-- CREATE TABLE user_visit_detail(
-- user_id VARCHAR(20),
-- visit_date VARCHAR(20),
-- visit_count int
-- );
--
-- insert into user_visit_detail values ('u01','2017/1/21',5);
-- insert into user_visit_detail values ('u02','2017/1/23',6);
-- insert into user_visit_detail values ('u03','2017/1/22',8);
-- insert into user_visit_detail values ('u04','2017/1/20',3);
-- insert into user_visit_detail values ('u01','2017/1/23',6);
-- insert into user_visit_detail values ('u01','2017/2/21',8);
-- insert into user_visit_detail values ('u02','2017/1/23',6);
-- insert into user_visit_detail values ('u01','2017/2/22',4);
-- postgresql
select user_id,visit_month,month_total_visit_cnt,
sum(month_total_visit_cnt) over (partition by user_id order by visit_month) as total_visit_cnt
from
(select user_id,to_char(visit_date,'yyyy-MM') as visit_month,
sum(visit_count) as month_total_visit_cnt
from user_visit_detail
group by user_id,to_char(visit_date,'yyyy-MM')) as t1
order by user_id,visit_month
;
第四题
需求：
表user(user_id，name，age)记录用户信息，表view_record(user_id，movie_name)记录用户观影信息，请根据年龄段（每10岁为一个年龄段，70以上的单独作为一个年龄段）观看电影的次数进行排序？
实现：
select age_group,count(*) as view_cnt
from view_record as t1
join
(select user_id,
case when age>0 and age<=10 then '0-10岁'
when age>10 and age<=20 then '10-20岁'
when age>20 and age<=30 then '20-30岁'
when age>30 and age<=40 then '30-40岁'
when age>40 and age<=50 then '40-50岁'
when age>50 and age<=60 then '50-60岁'
when age>60 and age<=70 then '60-70岁'
else '70岁以上' end as age_group
from user) as t2
on t1.user_id=t2.user_id
group by age_group;
第五题
需求：
有日志如下，请用SQL求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户） 日期 用户 年龄
| date_time | user_id | age |
|---|---|---|
| 2019-02-12 | 2 | 19 |
| 2019-02-11 | 1 | 23 |
| 2019-02-11 | 3 | 39 |
| 2019-02-11 | 1 | 23 |
| 2019-02-11 | 3 | 39 |
| 2019-02-13 | 1 | 23 |
| 2019-02-15 | 2 | 19 |
| 2019-02-11 | 2 | 19 |
| 2019-02-11 | 1 | 23 |
| 2019-02-16 | 2 | 19 |
实现：
-- create table user_active(
-- date_time timestamp,
-- user_id varchar(20),
-- age int
-- );
--
--
-- insert into user_active values ('2019-02-12','2',19);
-- insert into user_active values ('2019-02-11','1',23);
-- insert into user_active values ('2019-02-11','3',39);
-- insert into user_active values ('2019-02-11','1',23);
-- insert into user_active values ('2019-02-11','3',39);
-- insert into user_active values ('2019-02-13','1',23);
-- insert into user_active values ('2019-02-15','2',19);
-- insert into user_active values ('2019-02-11','2',19);
-- insert into user_active values ('2019-02-11','1',23);
-- insert into user_active values ('2019-02-16','2',19);
-- postgresql
select t4.user_cnt,t4.avg_age,t3.active_user_cnt,t3.active_avg_age
from
(select count(distinct t1.user_id) as active_user_cnt,avg(t1.age) as active_avg_age
from user_active as t1
join user_active as t2
on t1.user_id=t2.user_id
where date_part('day',t2.date_time-t1.date_time)=1) as t3
cross join
(select count(distinct user_id) as user_cnt,avg(age) as avg_age
from user_active) as t4;
第六题
需求：
请用sql写出所有用户中在2020年10月份第一次购买商品的金额，表order字段: 购买用户：user_id，金额：money，购买时间：pay_time(格式：2017-10-01)，订单id：order_id
实现：
-- postgresql
select user_id,pay_time,money,order_id
from
(select user_id,money,pay_time,order_id,
row_number() over (partition by user_id order by pay_time) as rank
from order
where to_char(pay_time,'yyyy-MM')='2020-10') as t1
where rank=1;
第七题
需求：
有一个账号表如下，请写出SQL语句，查询各自区组的money排名前3的账号 dist_id string ‘区组id’, account string ‘账号’, gold_coin int ‘金币’
实现：
-- postgresql
select dist_id,account,coin
from
(select dist_id,account,coin
row_number() over (partition by dist_id order by gold_coin desc) as rank
from account) as t1
where rank<=3;
第八题
需求：
充值日志表credit_log，字段如下：
dist_id int ‘区组id’,
account string ‘账号’,
money int ‘充值金额’,
create_time string ‘订单时间’
请写出SQL语句，查询充值日志表2020年08月08号每个区组下充值额最大的账号，要求结果： 区组id，账号，金额，充值时间
实现：
with temp as (
select dist_id,account,
sum(money) as sum_money
from credit_log
where to_char(create_time,'yyyy-MM-dd')='2020-08-08'
group by dist_id,account
)
select dist_id,account,sum_money
from
(select dist_id,account,sum_money
row_number() over (partition by dist_id order by sum_money desc) as rank
from temp) as t1
where rank=1;
第九题
需求：
有一个线上服务器访问日志格式如下（用sql答题） 时间 接口 IP
| date_time | interface | ip |
|---|---|---|
| 2016-11-09 15:22:05 | /request/user/logout | 110.32.5.23 |
| 2020-09-28 14:23:1 | /api_v1/user/detail | 57.2.1.16 |
| 2020-09-28 14:59:40 | /api_v2/read/buy | 172.6.5.166 |
求2020年9月28号下午14点（14-15点），访问/api_v1/user/detail接口的top10的ip地址
实现：
select ip,count(*) as cnt
from log
where to_char(date_time,'yyyy-MM-dd HH')>='2020-09-28 14'
and to_char(date_time,'yyyy-MM-dd HH')<'2020-09-28 15'
and interface='/api_v1/user/detail'
group by ip
order by cnt desc
limit 10;
第十题
示例数据：
-- create table student(s_id varchar(20), s_name varchar(20), s_birth varchar(20), s_sex varchar(20));
-- create table course(c_id varchar(20), c_name varchar(20), t_id varchar(20)) ;
-- create table teacher(t_id varchar(20), t_name varchar(20)) ;
-- create table score(s_id varchar(20), c_id varchar(20), s_score int);
--
-- insert into student values('01', '赵雷', '1993-01-01', '男');
-- insert into student values('02', '钱电', '1989-12-21', '男');
-- insert into student values('03', '孙雷', '2000-05-20', '男');
-- insert into student values('04', '李云', '1990-08-06', '男');
-- insert into student values('05', '周天', '1978-12-01', '女');
-- insert into student values('06', '吴兰', '1992-03-01', '女');
-- insert into student values('07', '郑竹', '1989-07-01', '男');
-- insert into student values('08', '王霞', '1993-01-20', '女');
--
-- insert into course values('01', '语文', '02');
-- insert into course values('02', '数学', '01');
-- insert into course values('03', '英语', '03');
--
-- insert into teacher values('01', '张三');
-- insert into teacher values('02', '李四');
-- insert into teacher values('03', '王五');
--
-- insert into score values('01', '01', '80');
-- insert into score values('01', '02', '90');
-- insert into score values('01', '03', '99');
-- insert into score values('02', '01', '70');
-- insert into score values('02', '02', '60');
-- insert into score values('02', '03', '80');
-- insert into score values('03', '01', '80');
-- insert into score values('03', '02', '80');
-- insert into score values('03', '03', '80');
需求：
- 查询”01”课程比”02”课程成绩高的学生的信息及课程分数
select student.*,t1.s_score,t2.s_score from student join score as t1 on t1.c_id='01' join score as t2 on t2.c_id='02' where t1.s_id=student.s_id and t2.s_id=student.s_id and t1.s_score>t2.s_score; - 查询”01”课程比”02”课程成绩低的学生的信息及课程分数
select student.*,t1.s_score,t2.s_score from student join score as t1 on t1.c_id='01' join score as t2 on t2.c_id='02' where t1.s_id=student.s_id and t2.s_id=student.s_id and t1.s_score<t2.s_score; - 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 ```sql select student.s_id,s_name,avg_score from student join (select s_id,avg(s_score) as avg_score from score group by s_id) as t1 on student.s_id=t1.s_id where avg_score>=60;
– 或 SELECT student.s_id,student.s_name, round(avg (score.s_score),1) as 平均成绩 FROM student JOIN score ON student.s_id = score.s_id GROUP BY student.s_id,student.s_name HAVING avg(score.s_score) >= 60;
4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
```sql
-- 包括有成绩和无成绩的
select student.s_id,s_name,avg_score
from student
join
(select s_id,avg(s_score) as avg_score
from score
group by s_id) as t1
on student.s_id=t1.s_id
where avg_score<60
union all
select student.s_id,s_name,0 as avg_score
from student
where s_id not in (select distinct s_id from score);
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.s_id,student.s_name, (count(score.c_id) )as total_count, sum(score.s_score)as total_score from student left join score on student.s_id = score.s_id group by student.s_id,student.s_name; - 查询”李”姓老师的数量
select t_name,count(1) from teacher where t_name like '李%' group by t_name; - 查询学过”张三”老师授课的同学的信息
select student.* from student join score on student.s_id = score.s_id join course on course.c_id = score.c_id join teacher on course.t_id = teacher.t_id and t_name = '张三';