数据库 课后习题

一、实战

① 每天交易金额、订单数量、顾客数

select date_format(create_time,'%y-%m-%d') as every_day
        ,sum(order_money)
        ,sum(status)
        ,count(distinct member_id)
from hy_order_20170410
group by date_format(create_time,'%y-%m-%d')

结果：

②每天交易平均客单价

select every_day,all_money/all_id
from(select sum(order_money) all_money
        ,count(distinct member_id) all_id
        ,date_format(create_time,'%y-%m-%d') as every_day
from hy_order_20170410
group by date_format(create_time,"%y-%m-%d")) as T

结果：

 ③每天订单客单价分布（按最大，最小分成5个等分区间）

 不懂

④查询本周金额最大、最小的订单号及对应的顾客（不会一次性输出）

select id,member_id,max(all_money)
from (select member_id
        ,id
        ,sum(order_money) as all_money
from hy_order_20170410
where create_time>date_add(now(),interval -2 week)
group by member_id) T

⑤删除金额小于0元

delete from hy_order_20170410
where order_money<1

结果：

⑥把金额小于2元都修改为1

update hy_order_20170410
set order_money =1
where order_money<2

结果：

⑦统计XX日期到XX日期会员购买金额，去掉金额大于1000会员

select *
from(select member_id,sum(order_money) all_money,create_time
from hy_order_20170410
where create_time between "2016-12-16" and "2019-09-26" 
group by member_id) T
where T.all_money<1000

结果：

⑧计算过去一周平均客单价，平均每个顾客购买金额，计算购买平均复购次数

最内层查询代码为：

select sum(order_money) as all_money
        ,count(distinct member_id) as num_id
        ,sum(status) num_st
from hy_order_20170410
where create_time>date_add(now(),interval -2 week)
group by (member_id)

结果：

 ①平均每个顾客购买金额

select sum(all_money)/sum(num_id) 平均每个顾客购买金额
from(select sum(order_money) as all_money
        ,count(distinct member_id) as num_id
        ,sum(status) num_st
from hy_order_20170410
where create_time>date_add(now(),interval -2 week)
group by (member_id)) T

②平均客单价

select all_money/num_st 平均客单价
from(select sum(order_money) as all_money
        ,count(distinct member_id) as num_id
        ,sum(status) num_st
from hy_order_20170410
where create_time>date_add(now(),interval -2 week)
group by (member_id)) T

③购买平均复购次数

select sum(num_st)/sum(num_id) 购买平均复购次数
from(select sum(order_money) as all_money
        ,count(distinct member_id) as num_id
        ,sum(status) num_st
from hy_order_20170410
where create_time>date_add(now(),interval -2 week)
group by (member_id)) T