CDA117556

2022-01-23   阅读量: 344

Mysql

荣威销售额面试题代码总结

扫码加入数据分析学习群

荣威销售额面试题

partition by 与 group by 的联合使用, partition在计算单度量值时可以对group取得的表格进行分区,这种情况一般group by 多个字段;

老师的:

select *

from

(select

store_id,

item_id,

sum(sales) as 商品销售额,

sum(sum(sales)) over(partition by store_id order by sum(sales) desc) as 累积销售额,

sum(sum(sales)) over(partition by store_id) as 门店总销售,

sum(sum(sales)) over(partition by store_id order by sum(sales) desc)/sum(sum(sales)) over(partition by store_id) as 累积销售额占比

from store_sales_info

group by store_id,item_id) as t

where 累积销售额占比<=0.7;

我的是错的:

select store_id, item_id

from

(select store_id, item_id,

sum(sales) over(partition by store_id order by sales desc) 各门店累计销售额,

sum(sales) over(partition by store_id) 各门店总销售额,

sum(sales) over(partition by store_id order by sales desc)/ sum(sales) over(partition by store_id) 累计占比

from store_sales_info) t

where 累计占比<0.7; -- 此做法错误,忽略了产品类别与金额累计之间的关系,累计应该是以类目的金额累计的;


40.1344 7 0 关注作者 收藏

评论(0)


暂无数据

推荐课程

推荐帖子