题
create table product (
zid varchar(20),
category_id int,
`operator` varchar(20),
sellerid int,
shop_name varchar(64)
);
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('A1', 2, '张三', 4, '服装1');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('A2', 2, '张三', 4, '服装1');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('A3', 4, '李四', 2, '服装4');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('c1', 4, '赵二', 8, '服装3');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('c2', 6, '赵二', 6, '服装6');
insert into product(zid,category_id,`operator`,sellerid,shop_name) values('c3', 8, '李二', 10, '服装4');
create table category_info (
id int,
fist_name varchar(20),
second_name varchar(64)
);
insert into category_info(id,fist_name,second_name) values(2,'女装','女裙');
insert into category_info(id,fist_name,second_name) values(4,'女装','短袖');
insert into category_info(id,fist_name,second_name) values(6,'男装','上衣');
insert into category_info(id,fist_name,second_name) values(8,'男装','短裤');
create table `order` (
orderid varchar(20),
zid varchar(20),
quantity int,
`date` date,
gmv double,
buyerid varchar(20)
);
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-001','A1',1,'2018-01-01', 156.7,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-002','A2',1,'2018-06-01', 196.7,'u_002');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-003','A2',1,'2018-06-02', 176.7,'u_005');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-004','A3',1,'2018-06-17', 199.9,'u_006');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-005','A3',1,'2018-06-20', 1188.7,'u_007');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-006','c1',1,'2018-07-09', 166.8,'u_002');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-007','c2',1,'2018-07-10', 136.5,'u_005');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-008','A3',1,'2018-07-29', 123.8,'u_006');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-009','A2',1,'2018-08-10', 123.8,'u_007');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-010','A3',50,'2018-04-01', 2600.0,'u_003');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-011','A1',20,'2018-04-05', 2000.0,'u_004');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-020','A1',1,'2018-04-07', 20.0,'u_099');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c1',1,'2018-04-07', 50.0,'u_098');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c2',1,'2018-04-09', 30.0,'u_097');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c3',20,'2018-04-09', 1999.0,'u_096');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-021','c3',2,'2018-04-09', 90.0,'u_095');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-012','c1',1,'2019-01-01', 19.5,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-013','c2',1,'2019-02-01', 25.6,'u_011');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-014','c3',1,'2019-03-01', 126.7,'u_012');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-015','c1',1,'2019-04-01', 555.5,'u_013');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-033','A2',1,'2019-02-01', 10.9,'u_011');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-034','A3',1,'2019-03-01', 99.9,'u_012');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-035','A1',1,'2019-04-01', 299.9,'u_013');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-016','A1',8,'2020-01-01', 653.7,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-017','A1',7,'2020-01-01', 328.7,'u_001');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-018','A3',6,'2020-02-01', 428.7,'u_008');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','A3',3,'2020-12-01', 25.7,'u_009');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','c2',2,'2020-12-01', 100.0,'u_021');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','c3',5,'2020-12-01', 100.0,'u_022');
insert into `order`(orderid,zid,quantity,`date`,gmv,buyerid) values('111-223-019','c3',5,'2020-12-01', 100.0,'u_022');
1.导出女装类目2020年整体销额，订单数，销量
-- 先求女装
select t2.zid
from category_info t1 left join product t2 on t1.id = t2.category_id
where t1.fist_name='女装'
;
-- 关联求聚合，注意：double会损失精度，替代使用decimal
select sum(gmv) as total_gmv,
count(orderid) as order_cnt,
sum(quantity) as total_quantity
from `order` t3
where t3.date >= '2020-01-01'
and t3.date <= '2021-12-31'
and t3.zid in (
select t2.zid
from category_info t1
left join product t2 on t1.id = t2.category_id
where t1.fist_name = '女装'
);
2.导出2018年4月总销额大于2000元的商品，整年的订单数，销量，购买用户数，总金额
-- 先求销售额大于2000的商品id(zid)
select zid,
sum(gmv) as zid_total_gmv
from `order`
where year(date)='2018'
and month(date)='04'
group by zid
having zid_total_gmv > 2000;
-- 求 整年 的订单数，销量，购买用户数，总金额
select count(orderid) as total_order,
sum(quantity) as total_quantity,
count(distinct (buyerid)) as total_user,
sum(gmv) as total_gmv
from `order`
where year(date) = '2018'
and zid in (
select zid,
sum(gmv) as zid_total_gmv
from `order`
where year(date) = '2018'
and month(date) = '04'
group by zid
having zid_total_gmv > 2000
)
;
3.导出2019年订单单价在如下价格阶段下商品的 总销量，总订单，总销额(0-20,20-50,50-100,100-200,200-500,500+)
-- 根据商品id分组聚合(这道题有点歧义，也就是是否要根据商品id分组）
select zid,
sum(quantity) as total_quantity,
count(orderid) as total_order,
sum(gmv) as total_gmv,
'0-20'
from `order`
where year(date)='2019'
group by zid
having total_gmv < 20
union all
select zid,
sum(quantity) as total_quantity,
count(orderid) as total_order,
sum(gmv) as total_gmv,
'20-50'
from `order`
where year(date)='2019'
group by zid
having total_gmv >= 20 and total_gmv < 50
union all
select zid,
sum(quantity) as total_quantity,
count(orderid) as total_order,
sum(gmv) as total_gmv,
'50-100'
from `order`
where year(date)='2019'
group by zid
having total_gmv >= 50 and total_gmv < 100
union all
select zid,
sum(quantity) as total_quantity,
count(orderid) as total_order,
sum(gmv) as total_gmv,
'100-200'
from `order`
where year(date)='2019'
group by zid
having total_gmv >= 100 and total_gmv < 200
union all
select zid,
sum(quantity) as total_quantity,
count(orderid) as total_order,
sum(gmv) as total_gmv,
'200-500'
from `order`
where year(date)='2019'
group by zid
having total_gmv >= 200 and total_gmv < 500
union all
select zid,
sum(quantity) as total_quantity,
count(orderid) as total_order,
sum(gmv) as total_gmv,
'200-500'
from `order`
where year(date)='2019'
group by zid
having total_gmv >= 500
;
4.导出2020年每个店铺销量前3名商品，并输出一级类目，运营，店铺等基本信息
-- 写法1
select t2.zid,
t4.fist_name,
t3.operator,
t3.sellerid,
t3.shop_name
from (
select zid,
sum(quantity) as zid_total_quantity
from `order`
where year(date) = '2020'
group by zid
order by zid_total_quantity desc
limit 3
) t2
left join product t3 on t2.zid = t3.zid
left join category_info t4 on t3.category_id = t4.id
;
-- 写法2
-- 定义变量
set @crank = 0;
select t2.zid,
t4.fist_name,
t3.operator,
t3.sellerid,
t3.shop_name
from(
select t1.zid,
t1.zid_total_quantity,
@crank := @crank + 1 as crank
from (
select zid,
sum(quantity) as zid_total_quantity
from `order`
where year(date) = '2020'
group by zid
order by zid_total_quantity desc
)t1
)t2 left join product t3 on t2.zid = t3.zid
left join category_info t4 on t3.category_id = t4.id
where t2.crank <= 3
;
5.导出在2018年6月下单的用户，分别在7月，8月，9月下单的购买的用户数
select buyerid,
'07'
from `order` t2
where year (t2.date)='2018'
and month (t2.date)='07'
and buyerid in (
select t1.buyerid
from `order` t1
where year (t1.date)='2018'
and month (t1.date)='06'
)
union all
select buyerid,
'08'
from `order` t2
where year (t2.date)='2018'
and month (t2.date)='08'
and buyerid in (
select t1.buyerid
from `order` t1
where year (t1.date)='2018'
and month (t1.date)='06'
)
union all
select buyerid,
'09'
from `order` t2
where year (t2.date)='2018'
and month (t2.date)='09'
and buyerid in (
select t1.buyerid
from `order` t1
where year (t1.date)='2018'
and month (t1.date)='06'
)
;
- 延伸，用户漏斗分析，也叫用户留存，如：6月下单的用户，7月还剩哪些，8月还剩哪些，9月还剩哪些
