如何分析数据

www.themegallery.com
按品牌查找上一年度订单成本总额占区域订单成本 20% 以上的城市,每个城市所占的订单成本比例 select s.*,100 * round(sales / brand_sales, 2) || '%' Percent from (select brand,city, sum(sale* cost) sales, sum(sum(sale* cost)) over(partition by brand) brand_sales from cux_tran_oh group by brand,city ) s where sales > brand_sales * 0.2
www.themegallery.com
空值放最后 NULLS LAST/FIRST select brand, city, sum(sale* cost) sales, sum(sum(sale* cost)) over(partition by brand) brand_sales, rank() over(partition by brand order by sum(sale* cost) desc NULLS LAST) rank from cux_tran_oh group by brand,city
www.themegallery.com
分区后排名
select brand,city, sum(sale* cost) sales, rank() over(partition by brand order by sum(sale* cost) desc) rank, dense_rank() over(partition by brand order by sum(sale* cost) desc) dense_rank, row_number() over(partition by brand order by sum(sale* cost) desc) row_number from cux_tran_oh group by brand,city
www.themegallery.com
平均周转率 select sum(PICK * cost * PICK/(og+po+org_tran))/sum(PICK* cost) from cux_tran_oh where og+po+org_tran > 0
www.themegallery.com
LOGO
如何分析数据
2013-01-11 simonliu
分析数据
提升公司、部门、个人的价值
www.themegallery.com
为何分析数据
发现公司营运异常，减少损失 表扬好的，警告差的 为持续改进做数据支持 资讯就是数据，EBIS需要分析数据
www.themegallery.com
www.themegallery.com
比较同一个相邻的记录集 select YM, sum(sale* cost) sales, lag(sum(sale* cost),1) over(order by YM ) prev1_sales, lag(sum(sale* cost),2) over(order by YM ) prev2_sales from cux_tran_oh group by YM
www.themegallery.com
总结 Over函数指明在那些字段上做分析，其内跟 Partition by表示对数据进行分组。注意 Partition by可以有多个字段 Over函数可以和其它聚集函数、分析函数搭配， 起到不同的作用。例如这里的SUM，还有诸如 Rank，Dense_rank等。
www.themegallery.com
滚动统计(累积/均值) select YM, sum(sale* cost) sales, sum(sum(sale* cost)) over(order by YM rows between unbounded preceding and current row) total_sales from cux_tran_oh group by YM
www.themegallery.com
RATIO_TO_REPORT占比函数
select brand, city, sum(sale* cost) sales, sum(sum(sale* cost)) over(partition by brand) brand_sales, round(ratio_to_report(sum(sale* cost)) over (partition by brand), 2) percent_of_brand from cux_tran_oh group by brand,city
www.themegallery.com
partition 按品牌对数据进行分区，然后累积每个品牌每个 城市的订单成本 select brand,city, sum(sale* cost) sales, sum(sum(sale* cost)) over(partition by brand) brand_sales from cux_tran_oh group by brand,city
www.themegallery.com
全统计 select YM, sum(sale* cost) sales, sum(sum(sale* cost)) over(order by YM rows between unbounded preceding and unbounded following) total_sales, sum(sum(sale* cost)) over() t_sales from cux_tran_oh group by YM
www.themegallery.com
排名 Rank, Dense_rank, row_number select brand,city, sum(sale* cost) sales, rank() over(order by sum(sale* cost) desc) rank, dense_rank() over(order by sum(sale* cost) desc) dense_rank, row_number() over(order by sum(sale* cost) desc) row_number from cux_tran_oh group by brand,city
www.themegallery.com
First/Last排名查询 select brand,sum(sale* cost) sales from cux_tran_oh group by brand order by sales desc select min(brand) keep (dense_rank first order by sum(sale* cost) desc) first, min(brand) keep (dense_rank last order by sum(sale* cost) desc) last from cux_tran_oh group by brand
www.themegallery.com
Top/Bottom N查询 按品牌查找订单成本前3名的城市 select * from (select brand,city, sum(sale* cost) sales, sum(sum(sale* cost)) over(partition by brand) brand_sales, rank() over(partition by brand order by sum(sale* cost) desc NULLS LAST) rank from cux_tran_oh group by brand,city) where rank < 3
www.themegallery.com
总结 ①假如客户就只需要指定数目的记录，那么采用 row_number是最简单的，但有漏掉的记录的 危险 ②假如客户需要所有达到排名水平的记录，那么 采用rank或dense_rank是不错的选择。至于 选择哪一种则看客户的需要，选择dense_rank 或得到最大的记录
应该分析哪些数据
查找公司、部门、个人工作中的可能问题
库存周转率太低，明明库存很大，销量很低，还源自文库购 库存残损品太多，快要过期，还不减价销售 天天有出差报销，有可能吗 亏损的生意，都有审批吗 操作失误，能发现吗
反省公司、部门、个人的目标
做这笔生意有多少钱赚，学会分析品牌 库存少点，如何提高周转率 应收少点，学会与客户谈判 客户满意了吗，意见收集了吗，改进了吗
www.themegallery.com
first_value/last_value 相邻三个月的平均值
select YM, sum(sale* cost) sales, first_value(sum(sale* cost)) over(order by YM rows between 1 preceding and 1 following) first_sales, last_value(sum(sale* cost)) over(order by YM rows between 1 preceding and 1 following) last_sales, avg(sum(sale* cost)) over(order by YM rows between 1 preceding and 1 following) avg_sales from cux_tran_oh group by YM
品牌周转率 select rownum,r.* from ( select brand,sum(PICK * cost * PICK/(og+po+org_tran))/sum(PICK* cost) rate from cux_tran_oh tran where og+po+org_tran > 0 group by brand having sum(PICK* cost) > 0 order by rate desc) r
www.themegallery.com
如何分析数据
找平均值，排名 ABC分类 横向，纵向对比
Excel Oracle 分析函数 BI工具
www.themegallery.com
数据表 cux_tran_oh OH 现有量 OG完好量 OS 发货区 PO本月采购 ORG_TRAN 本月中转 SALE本月销售 COST单位成本 pick 挑库数量 brand 品牌 city 城市
www.themegallery.com
按层次查询 ABC分类 ntile select brand,city, sum(sale* cost) sales, sum(sum(sale* cost)) over(partition by brand order by sum(sale* cost) desc) brand_sales, ntile(4) over(partition by brand order by sum(sale* cost) desc) til from cux_tran_oh group by brand,city