用户累计销量面试题-千锋教育

用户累计销量面试题

发布时间:2022-06-07 13:35:00

发布人:wjy

今天给大家准备的面试题解答的题目如下：

我们同样先根据需求构造表

create table sale_order(
id int primary key auto_increment comment '订单编号',
sale_dt date comment '订单日期',
user_id int comment '用户id',
sku_id int comment '商品id',
sale_count int comment '销售数量',
price int comment '商品单价',
amount int comment '销售金额'
);

insert into sale_order values
(1, '2019-01-01', 1, 1001, 2, 100, 200),
(2, '2019-01-02', 2, 1001, 1, 100, 100),
(3, '2019-02-10', 3, 1002, 2, 50, 100),
(4, '2019-02-11', 2, 1001, 2, 100, 200),
(5, '2019-03-01', 3, 1001, 1, 100, 100),
(6, '2019-03-01', 3, 1002, 1, 50, 50),
(7, '2019-03-02', 3, 1003, 4, 100, 400);
select * from sale_order;

create table products(
sku_id int primary key comment '商品id',
sku_name varchar(100) comment '商品名称'
);

insert into products values(1001, '商品1'), (1002, '商品2'),(1003, '商品3');

按照需求完成代码书写

/*
需求1：求每个月份的销量销售额
  月份销量销售额
2019-01    3        300
2019-02    4        300
2019-03    6        550
*/
-- 根据月份分组对销量销售额求和这里需要时间函数对时间格式化
select date_format(sale_dt, '%Y-%m') `月份`,
sum(sale_count) `销量`,
sum(amount) `销售额`
from sale_order group by date_format(sale_dt, '%Y-%m');

/*
需求2：每个用户各个月份的销售额
user_id 2019-01      2019-02    2019-03
  1           200           0          0
  2           100           200        0
  3           0             100        550
*/
-- 先判断月份对应的数据是否有销售额有就设置为销售额没有就设置为0 最后根据用户归类销售额求和
select user_id,
sum(if(date_format(sale_dt, '%Y-%m') = '2019-01', amount, 0)) `2019-01`,
sum(if(date_format(sale_dt, '%Y-%m') = '2019-02', amount, 0)) `2019-02`,
sum(if(date_format(sale_dt, '%Y-%m') = '2019-03', amount, 0)) `2019-03`
from sale_order
group by user_id;

/*
需求3: 月份之间的累计销量与累计销售额
  月份累计销量累计销售额
2019-01    3        300
2019-02    7        600
2019-03    13       1150
*/
-- 关于累计的问题我们需要知道sum的应用
/*
1. 不在分组中使用获取指定字段所有行的求和
2. 在分组中是按照分类字段的值将统计到的行中对应的字段的值求和【分组会折叠把折叠的行中指定字段的数据求和】
3. sum还可以在窗口函数来用
sum(字段名) over(partition by 归类字段)
--- 如果是这样使用的话在查询中增加一列根据归类字段把数据整合并行排放的多行中
显示的是根据归类字段对字段的求和
不会折叠数据这个因为没有排序只有归类求的就是归类完成之后该字段的对应的所有行求和的结果
4. sum还可以在窗口函数来用
sum(字段名) over(partition by 归类字段 order by 排序字段)
--- 如果是这样使用的话在查询中增加一列根据归类字段把数据整合在并行排放的多行中
并按照排序字段把数据排序好，完成的是该归类字段下当前行以上数据的累加和
*/
-- 在需求1的基础上按照年归类按照月份升序排序使用sum窗口函数进行累计
select `月份`,
sum(`销量`) over(partition by substr(`月份`, 1, 4) order by substr(`月份`, 6)) as 累计销量,
sum(`销售额`) over(partition by substr(`月份`, 1, 4) order by substr(`月份`, 6)) as 累计销售额
from (select date_format(sale_dt, '%Y-%m') `月份`,
sum(sale_count) `销量`,
sum(amount) `销售额`
from sale_order group by date_format(sale_dt, '%Y-%m')) as t;

/*
需求4: 每个产品的销量与销售额并给出排名
*/
-- 连接两表按照商品分组求和
select products.*, sum(sale_count) 销量, sum(amount) 销售额
from sale_order join products
on sale_order.sku_id = products.sku_id
group by sku_id;