题目描述
已知表格:
daily_sales:2021年每日销量表,有date,shop_id,order_id三列
1)算出2021-12-01销量前3的shop_id、销量quantity、排名rank
2)求每shop_id达到全年销量80%的日期:shop_id、date_80p
3)求每天每shop_id的历史30天销量:date、shop_id、quantity_30d
SELECT
shop_id,
COUNT(order_id) quantity,
(@i:=@i+1) 'rank'
from daily_sales,(SELECT @i:=0) AS t
where date = '2021-12-01'
group by shop_id order by COUNT(order_id) desc limit 3
select t1.shop_id,
t1.date as date_80p
from
(select date,
shop_id,
count(order_id) daily_quantity
from daily_sales
group by shop_id,date
)t1
left join
(select
shop_id,
count(order_id) full_quantity
from daily_sales
group by shop_id
)t2
on t1.shop_id=t2.shop_id
where t1.daily_quantity >= 0.8*t2.full_quantity
select
date,
shop_id,
(select count(1) from daily_sales where date>date_sub(d.date),interval 30 day) and date<=d.date
) quantity_30d
from daily_sales d