ERP数仓的十个需求

最新推荐文章于 2023-12-07 16:27:49 发布

panxuej_shenx

最新推荐文章于 2023-12-07 16:27:49 发布

阅读量357

点赞数

本文链接：https://blog.csdn.net/xwcj3/article/details/104151204

版权

表结构

--1.维度表
CREATE DATABASE pxj;

CREATE TABLE IF NOT EXISTS pxj.dwd_ruozedata_depot ( 
id BIGINT, name STRING, address STRING, warehousing DOUBLE, truckage DOUBLE, 
type INT, sort STRING, remark STRING, principal BIGINT, tenant_id BIGINT, 
delete_Flag STRING, is_default BOOLEAN, 
UpdateTime STRING,
StartDay string comment '数据起始时间',
EndDay string comment '数据结束时间'
) COMMENT '仓库维度表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

create table  pxj.dwd_dim_time(
time_id string,
y string,
m string,
d string,
h string,
hour_period string,
week_of_year string,
week_of_month string,
week_day  string,
ten_days string,
quarter_of_year string,
is_holiday BOOLEAN,
is_festival BOOLEAN
)COMMENT '时间维度表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

--2.事实大宽明细表
CREATE TABLE pxj.dws_ruozedata_bill (
id bigint,
itemid bigint,
DepotId bigint,
depotName string,
`type` string,
subtype string,
`number` string,
MaterialId bigint,
materialName string,
OperNumber double,
UnitPrice double,
AllPrice double,
SafetyStock double,
SecondCategory string,
firstCategory string,
OrganId bigint ,
supplierOrCustomer string, 
supplierOrCustomerType string,
CreateTime string
)COMMENT '订单轻度汇总表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

数据

209	223	16	上海仓库01	其它	采购订单	CGDD00000000345	588	澳洲西冷牛排套餐	100.0	48.0	4800.0	100.0	牛肉	精选肉类	74	供应商1	供应商	2019-12-22 19:17:50
210	224	16	上海仓库01	入库	采购	CGRK00000000346	588	澳洲西冷牛排套餐	100.0	48.0	4800.0	100.0	牛肉	精选肉类	74	供应商1	供应商	2019-12-22 19:18:26
211	225	18	北京仓库01	入库	其它	QTRK00000000347	588	澳洲西冷牛排套餐	555.0	48.0	26640.0	100.0	牛肉	精选肉类	74	供应商1	供应商	2019-12-22 19:22:13
212	226	16	上海仓库01	其它	销售订单	XSDD00000000348	588	澳洲西冷牛排套餐	999.0	72.0	71928.0	100.0	牛肉	精选肉类	75	客户1	客户	2019-12-22 19:28:20
216	227	16	上海仓库01	出库	销售	XSCK00000000350	588	澳洲西冷牛排套餐	90.0	72.0	6480.0	100.0	牛肉	精选肉类	75	客户1	客户	2019-12-22 19:29:44
218	228	16	上海仓库01	出库	其它	QTCK00000000353	588	澳洲西冷牛排套餐	2.0	72.0	144.0	100.0	牛肉	精选肉类	75	客户1	客户	2019-12-22 19:32:02
220	229	16	上海仓库01	出库	零售	LSCK00000000357	588	澳洲西冷牛排套餐	8.0	72.0	576.0	100.0	牛肉	精选肉类	76	女神	会员	2019-12-22 19:38:35
221	230	16	上海仓库01	入库	零售退货	LSTH00000000359	588	澳洲西冷牛排套餐	5.0	72.0	360.0	100.0	牛肉	精选肉类	76	女神	会员	2019-12-22 19:41:04
222	231	19	广州仓库01	其它	采购订单	CGDD00000000365	589	散养柴鸡500天走地鸡	300.0	30.0	9000.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2019-12-22 21:42:13
222	232	16	上海仓库01	其它	采购订单	CGDD00000000365	589	散养柴鸡500天走地鸡	150.0	30.0	4500.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2019-12-22 21:42:13
222	233	18	北京仓库01	其它	采购订单	CGDD00000000365	589	散养柴鸡500天走地鸡	3000.0	30.0	90000.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2019-12-22 21:42:13
223	234	19	广州仓库01	入库	采购	CGRK00000000367	589	散养柴鸡500天走地鸡	300.0	30.0	9000.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2019-12-22 21:48:20
223	235	16	上海仓库01	入库	采购	CGRK00000000367	589	散养柴鸡500天走地鸡	150.0	30.0	4500.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2019-12-22 21:48:20
223	236	18	北京仓库01	入库	采购	CGRK00000000367	589	散养柴鸡500天走地鸡	3000.0	30.0	90000.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2019-12-22 21:48:20
224	237	16	上海仓库01	入库	其它	QTRK00000000369	589	散养柴鸡500天走地鸡	888.0	30.0	26640.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2019-12-22 22:01:35
225	238	18	北京仓库01	其它	销售订单	XSDD00000000373	589	散养柴鸡500天走地鸡	200.0	55.0	11000.0	1000.0	老母鸡	精选肉类	78	客户2	客户	2019-12-22 22:03:42
227	239	18	北京仓库01	出库	销售	XSCK00000000375	589	散养柴鸡500天走地鸡	2990.0	55.0	164450.0	1000.0	老母鸡	精选肉类	78	客户2	客户	2019-12-22 22:11:13
228	240	18	北京仓库01	出库	其它	QTCK00000000376	589	散养柴鸡500天走地鸡	4.0	55.0	220.0	1000.0	老母鸡	精选肉类	78	客户2	客户	2019-12-22 22:14:14
230	241	18	北京仓库01	出库	零售	LSCK00000000377	589	散养柴鸡500天走地鸡	6.0	80.0	480.0	1000.0	老母鸡	精选肉类	79	女神2号	会员	2019-12-22 22:16:21
231	242	18	北京仓库01	入库	零售退货	LSTH00000000378	589	散养柴鸡500天走地鸡	4.0	80.0	320.0	1000.0	老母鸡	精选肉类	79	女神2号	会员	2019-12-22 22:17:10
232	243	17	上海仓库02	其它	采购订单	CGDD00000000379	587	智利进口冷冻三文鱼	1000.0	80.0	80000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-23 11:17:43
233	244	17	上海仓库02	入库	采购	CGRK00000000380	587	智利进口冷冻三文鱼	1000.0	80.0	80000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-23 11:18:08
234	245	17	上海仓库02	其它	采购订单	CGDD00000000382	587	智利进口冷冻三文鱼	200.0	80.0	16000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-25 12:04:40
234	246	18	北京仓库01	其它	采购订单	CGDD00000000382	587	智利进口冷冻三文鱼	300.0	80.0	24000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-25 12:04:40
234	247	19	广州仓库01	其它	采购订单	CGDD00000000382	587	智利进口冷冻三文鱼	500.0	80.0	40000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-25 12:04:40
235	248	17	上海仓库02	入库	采购	CGRK00000000384	587	智利进口冷冻三文鱼	200.0	80.0	16000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-25 12:05:05
235	249	18	北京仓库01	入库	采购	CGRK00000000384	587	智利进口冷冻三文鱼	300.0	80.0	24000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-25 12:05:05
235	250	19	广州仓库01	入库	采购	CGRK00000000384	587	智利进口冷冻三文鱼	500.0	80.0	40000.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-25 12:05:05
236	251	17	上海仓库02	其它	销售订单	XSDD00000000385	587	智利进口冷冻三文鱼	100.0	140.0	14000.0	2000.0	鱼类	海鲜水产	75	客户1	客户	2019-12-25 12:10:22
236	252	18	北京仓库01	其它	销售订单	XSDD00000000385	587	智利进口冷冻三文鱼	90.0	140.0	12600.0	2000.0	鱼类	海鲜水产	75	客户1	客户	2019-12-25 12:10:22
236	253	19	广州仓库01	其它	销售订单	XSDD00000000385	587	智利进口冷冻三文鱼	400.0	140.0	56000.0	2000.0	鱼类	海鲜水产	75	客户1	客户	2019-12-25 12:10:22
237	254	17	上海仓库02	出库	销售	XSCK00000000386	587	智利进口冷冻三文鱼	100.0	140.0	14000.0	2000.0	鱼类	海鲜水产	75	客户1	客户	2019-12-25 12:10:43
237	255	18	北京仓库01	出库	销售	XSCK00000000386	587	智利进口冷冻三文鱼	90.0	140.0	12600.0	2000.0	鱼类	海鲜水产	75	客户1	客户	2019-12-25 12:10:43
237	256	19	广州仓库01	出库	销售	XSCK00000000386	587	智利进口冷冻三文鱼	400.0	140.0	56000.0	2000.0	鱼类	海鲜水产	75	客户1	客户	2019-12-25 12:10:43
240	257	18	北京仓库01	出库	销售	XSCK00000000387	589	散养柴鸡500天走地鸡	1.0	55.0	55.0	1000.0	老母鸡	精选肉类	78	客户2	客户	2019-12-25 13:05:53
241	258	17	上海仓库02	出库	采购退货	CGTH00000000389	587	智利进口冷冻三文鱼	99.0	140.0	13860.0	2000.0	鱼类	海鲜水产	74	供应商1	供应商	2019-12-25 13:34:38
242	259	17	上海仓库02	入库	销售退货	XSTH00000000390	587	智利进口冷冻三文鱼	100.0	80.0	8000.0	2000.0	鱼类	海鲜水产	75	客户1	客户	2019-12-25 13:35:17
244	260	17	上海仓库02	其它	采购订单	CGDD00000000398	590	WECOOK味库 超大阿根廷红虾	999.0	80.0	79920.0	1000.0	虾类	海鲜水产	74	供应商1	供应商	2019-12-28 09:14:23
244	261	18	北京仓库01	其它	采购订单	CGDD00000000398	589	散养柴鸡500天走地鸡	998.0	30.0	29940.0	1000.0	老母鸡	精选肉类	74	供应商1	供应商	2019-12-28 09:14:23
244	262	19	广州仓库01	其它	采购订单	CGDD00000000398	586	新疆阿克苏苹果	997.0	8.0	7976.0	100.0	热销水果	新鲜水果	74	供应商1	供应商	2019-12-28 09:14:23
244	263	17	上海仓库02	其它	采购订单	CGDD00000000398	588	澳洲西冷牛排套餐	1000.0	48.0	48000.0	100.0	牛肉	精选肉类	74	供应商1	供应商	2019-12-28 09:14:23
244	264	19	广州仓库01	其它	采购订单	CGDD00000000398	588	澳洲西冷牛排套餐	500.0	48.0	24000.0	100.0	牛肉	精选肉类	74	供应商1	供应商	2019-12-28 09:14:23
245	265	17	上海仓库02	入库	采购	CGRK00000000399	590	WECOOK味库 超大阿根廷红虾	999.0	80.0	79920.0	1000.0	虾类	海鲜水产	74	供应商1	供应商	2019-12-28 09:14:38
245	266	18	北京仓库01	入库	采购	CGRK00000000399	589	散养柴鸡500天走地鸡	998.0	30.0	29940.0	1000.0	老母鸡	精选肉类	74	供应商1	供应商	2019-12-28 09:14:38
245	267	19	广州仓库01	入库	采购	CGRK00000000399	586	新疆阿克苏苹果	997.0	8.0	7976.0	100.0	热销水果	新鲜水果	74	供应商1	供应商	2019-12-28 09:14:38
245	268	17	上海仓库02	入库	采购	CGRK00000000399	588	澳洲西冷牛排套餐	1000.0	48.0	48000.0	100.0	牛肉	精选肉类	74	供应商1	供应商	2019-12-28 09:14:38
245	269	19	广州仓库01	入库	采购	CGRK00000000399	588	澳洲西冷牛排套餐	500.0	48.0	24000.0	100.0	牛肉	精选肉类	74	供应商1	供应商	2019-12-28 09:14:38
246	270	16	上海仓库01	其它	采购订单	CGDD00000000401	590	WECOOK味库 超大阿根廷红虾	100.0	80.0	8000.0	1000.0	虾类	海鲜水产	74	供应商1	供应商	2019-12-28 23:09:30
246	271	18	北京仓库01	其它	采购订单	CGDD00000000401	590	WECOOK味库 超大阿根廷红虾	200.0	80.0	16000.0	1000.0	虾类	海鲜水产	74	供应商1	供应商	2019-12-28 23:09:30
247	272	16	上海仓库01	入库	采购	CGRK00000000402	590	WECOOK味库 超大阿根廷红虾	100.0	80.0	8000.0	1000.0	虾类	海鲜水产	74	供应商1	供应商	2019-12-28 23:09:57
247	273	18	北京仓库01	入库	采购	CGRK00000000402	590	WECOOK味库 超大阿根廷红虾	200.0	80.0	16000.0	1000.0	虾类	海鲜水产	74	供应商1	供应商	2019-12-28 23:09:57
248	274	16	上海仓库01	其它	采购订单	CGDD00000000403	588	澳洲西冷牛排套餐	100.0	48.0	4800.0	100.0	牛肉	精选肉类	77	供应商2	供应商	2019-12-29 10:12:38
248	275	19	广州仓库01	其它	采购订单	CGDD00000000403	588	澳洲西冷牛排套餐	5000.0	48.0	240000.0	100.0	牛肉	精选肉类	77	供应商2	供应商	2019-12-29 10:12:38
249	276	16	上海仓库01	入库	采购	CGRK00000000404	588	澳洲西冷牛排套餐	100.0	48.0	4800.0	100.0	牛肉	精选肉类	77	供应商2	供应商	2019-12-29 10:13:02
249	277	19	广州仓库01	入库	采购	CGRK00000000404	588	澳洲西冷牛排套餐	5000.0	48.0	240000.0	100.0	牛肉	精选肉类	77	供应商2	供应商	2019-12-29 10:13:02
250	278	16	上海仓库01	入库	其它	QTRK00000000407	590	WECOOK味库 超大阿根廷红虾	1.0	80.0	80.0	1000.0	虾类	海鲜水产	77	供应商2	供应商	2020-01-03 11:32:13
251	279	16	上海仓库01	其它	采购订单	CGDD00000000431	590	WECOOK味库 超大阿根廷红虾	100.0	80.0	8000.0	1000.0	虾类	海鲜水产	77	供应商2	供应商	2020-01-15 11:09:31
251	280	18	北京仓库01	其它	采购订单	CGDD00000000431	589	散养柴鸡500天走地鸡	100.0	30.0	3000.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2020-01-15 11:09:31
251	281	19	广州仓库01	其它	采购订单	CGDD00000000431	586	新疆阿克苏苹果	10000.0	8.0	80000.0	100.0	热销水果	新鲜水果	77	供应商2	供应商	2020-01-15 11:09:31
252	282	16	上海仓库01	入库	采购	CGRK00000000432	590	WECOOK味库 超大阿根廷红虾	100.0	80.0	8000.0	1000.0	虾类	海鲜水产	77	供应商2	供应商	2020-01-15 11:09:48
252	283	18	北京仓库01	入库	采购	CGRK00000000432	589	散养柴鸡500天走地鸡	100.0	30.0	3000.0	1000.0	老母鸡	精选肉类	77	供应商2	供应商	2020-01-15 11:09:48
252	284	19	广州仓库01	入库	采购	CGRK00000000432	586	新疆阿克苏苹果	10000.0	8.0	80000.0	100.0	热销水果	新鲜水果	77	供应商2	供应商	2020-01-15 11:09:48
253	285	16	上海仓库01	出库	其它	QTCK00000000435	590	WECOOK味库 超大阿根廷红虾	109.0	140.0	15260.0	1000.0	虾类	海鲜水产	78	客户2	客户	2020-01-15 11:13:13
253	286	17	上海仓库02	出库	其它	QTCK00000000435	588	澳洲西冷牛排套餐	100.0	72.0	7200.0	100.0	牛肉	精选肉类	78	客户2	客户	2020-01-15 11:13:13
253	287	19	广州仓库01	出库	其它	QTCK00000000435	588	澳洲西冷牛排套餐	1000.0	72.0	72000.0	100.0	牛肉	精选肉类	78	客户2	客户	2020-01-15 11:13:13
254	288	16	上海仓库01	出库	其它	QTCK00000000436	588	澳洲西冷牛排套餐	10.0	72.0	720.0	100.0	牛肉	精选肉类	78	客户2	客户	2020-01-15 13:44:50
254	289	18	北京仓库01	出库	其它	QTCK00000000436	587	智利进口冷冻三文鱼	100.0	140.0	14000.0	2000.0	鱼类	海鲜水产	78	客户2	客户	2020-01-15 13:44:50
254	290	19	广州仓库01	出库	其它	QTCK00000000436	586	新疆阿克苏苹果	10000.0	12.0	120000.0	100.0	热销水果	新鲜水果	78	客户2	客户	2020-01-15 13:44:50
255	291	17	上海仓库02	其它	销售订单	XSDD00000000437	588	澳洲西冷牛排套餐	100.0	72.0	7200.0	100.0	牛肉	精选肉类	75	客户1	客户	2020-01-15 13:46:15
255	292	18	北京仓库01	其它	销售订单	XSDD00000000437	589	散养柴鸡500天走地鸡	1000.0	55.0	55000.0	1000.0	老母鸡	精选肉类	75	客户1	客户	2020-01-15 13:46:15
255	293	19	广州仓库01	其它	销售订单	XSDD00000000437	588	澳洲西冷牛排套餐	1000.0	72.0	72000.0	100.0	牛肉	精选肉类	75	客户1	客户	2020-01-15 13:46:15
256	294	17	上海仓库02	出库	销售	XSCK00000000438	588	澳洲西冷牛排套餐	100.0	72.0	7200.0	100.0	牛肉	精选肉类	75	客户1	客户	2020-01-15 13:46:40
256	295	18	北京仓库01	出库	销售	XSCK00000000438	589	散养柴鸡500天走地鸡	1000.0	55.0	55000.0	1000.0	老母鸡	精选肉类	75	客户1	客户	2020-01-15 13:46:40
256	296	19	广州仓库01	出库	销售	XSCK00000000438	588	澳洲西冷牛排套餐	1000.0	72.0	72000.0	100.0	牛肉	精选肉类	75	客户1	客户	2020-01-15 13:46:40

16	上海仓库01	上海浦东	5.0	\N	0			\N	1	0	\N	2019-12-23 19:12:16.0	2019-12-28	9999-99-99
17	上海仓库02	上海奉贤	3.0	\N	0			\N	1	0	\N	2019-12-23 19:12:16.0	2019-12-28	9999-99-99
18	北京仓库01	北京西二旗	8.0	\N	0			\N	1	0	\N	2019-12-23 19:12:16.0	2019-12-28	9999-99-99
19	广州仓库01	广州xxx	4.0	\N	0			120	1	0	\N	2019-12-23 19:12:16.0	2019-12-28	9999-99-99

1.GMV销售成交总金额

selet  round(sum(AllPrice),2) from dws_ruozedata_bill  where CONCAT_WS('-',type,subtype) in ('出库-销售','出库-其它','出库-零售')

2.最近6个月每天采购总金额、商品SKU总量

select 
n.day,
m.bill_count,
m.bill_sum
from (
select 
            SUBSTRING(createtime,1,10) as day,
            count(materialname) as bill_count ,
            sum(allprice) bill_sum,
            materialname
        from ruozedata_erp.dws_ruozedata_bill
        where CONCAT_WS('-',type,subtype) in  ('入库-采购','入库-其它')
        group by SUBSTRING(createtime,1,10),materialname
)m
left join 
(
select concat_ws('-',y,m,d) day 
from dwd_dim_time 
where floor(months_between(date_format(from_unixtime(unix_timestamp()),'yyyy-MM-dd') ,concat_ws('-',y,m,d)))<=6
)n
on m.day=n.day

3.最近6个月每天销售总金额、商品SKU总量

select 
materialname,
m.bill_count,
m.bill_sum,
n.day
from (
select 
            SUBSTRING(createtime,1,10) as day,
            count(materialname) as bill_count ,
            sum(allprice) bill_sum,
            materialname
        from ruozedata_erp.dws_ruozedata_bill
        where CONCAT_WS('-',type,subtype) in ('出库-销售','出库-其它','出库-零售')
        group by SUBSTRING(createtime,1,10),materialname
)m
left join 
(
select concat_ws('-',y,m,d) day 
from dwd_dim_time 
where months_between(date_format(from_unixtime(unix_timestamp()),'yyyy-MM-dd') ,concat_ws('-',y,m,d))<=6
)n
on m.day=n.day

4.销售总价Top 3的商品是哪些

SELECT allprice ,materialname
from 
(
select sum(allprice) allprice ,materialname,row_number() over(partition by materialid ) rn 
from dws_ruozedata_bill
group by  materialname,materialid
)m
where rn<=3

5.一级类别的哪个商品是最畅销的

SELECT
n.firstcategory,n.materialname,n.sell_sku_allprice
from (
        SELECT
        m.*,
        row_number() over ( partition by firstcategory order by sell_sku_allprice desc ) num 
        from 
        (
            select 
            firstcategory,materialname,
            round(sum(allprice),2) as sell_sku_allprice
            from ruozedata_erp.dws_ruozedata_bill 
            where CONCAT_WS('-',type,subtype) in ('出库-销售','出库-其它','出库-零售')
            group by firstcategory,materialname
        ) m
) n 
where n.num=1;

6.各个供应商供应的货款最大的是哪个商品

select 
supplierorcustomer,materialname, supplier_sku_allprice
from (
select 
supplierorcustomer,materialname, supplier_sku_allprice,
row_number() over(partition by supplierorcustomer order by supplier_sku_allprice desc) rn 
from (
select 
                        supplierorcustomer,materialname,
                        round(sum(allprice),2) as supplier_sku_allprice
                        from ruozedata_erp.dws_ruozedata_bill 
                        where CONCAT_WS('-',type,subtype) in ('入库-采购','入库-其它')
                        and supplierorcustomertype='供应商'
                        group by supplierorcustomer,materialname
)m
)n
where rn=1

7.各个客户最喜欢买的商品是哪个

select 
supplierorcustomer,materialname, supplier_sku_allprice
from (
select 
supplierorcustomer,materialname, supplier_sku_allprice,
row_number() over(partition by materialname order by supplier_sku_allprice desc) rn 
from (
select 
                        supplierorcustomer,materialname,
                        round(sum(allprice),2) as supplier_sku_allprice
                        from ruozedata_erp.dws_ruozedata_bill 
                        where CONCAT_WS('-',type,subtype) in ('出库-销售','出库-其它','出库-零售')
                        and supplierorcustomertype='客户'
                        group by supplierorcustomer,materialname
)m
)n
where rn=1

8.2019每个月的各商品各仓库的采购、销售的数量

SELECT
m.materialname,m.depotname,m.billtype,
'2019' as currentyear,
max(case when m.mon='2019-01' then m.sumqty else 0 end)  m01,
max(case when m.mon='2019-02' then m.sumqty else 0 end)  m02,
max(case when m.mon='2019-03' then m.sumqty else 0 end)  m03,
max(case when m.mon='2019-04' then m.sumqty else 0 end)  m04,
max(case when m.mon='2019-05' then m.sumqty else 0 end)  m05,
max(case when m.mon='2019-06' then m.sumqty else 0 end)  m06,
max(case when m.mon='2019-07' then m.sumqty else 0 end)  m07,
max(case when m.mon='2019-08' then m.sumqty else 0 end)  m08,
max(case when m.mon='2019-09' then m.sumqty else 0 end)  m09,
max(case when m.mon='2019-10' then m.sumqty else 0 end)  m10,
max(case when m.mon='2019-11' then m.sumqty else 0 end)  m11,
max(case when m.mon='2019-12' then m.sumqty else 0 end)  m12
from    
(select 
    '采购' as billtype,
    substr(createtime,0,7) as mon,
    materialname,depotname,
    sum(opernumber) as sumqty
    from ruozedata_erp.dws_ruozedata_bill 
    where CONCAT_WS('-',`type`,subtype) in  ('入库-采购','入库-其它')
    and year(createtime)='2019' 
    group by substr(createtime,0,7),materialname,depotname
union all
select 
    '销售' as billtype,
    substr(createtime,0,7) as mon,
    materialname,depotname,
    sum(opernumber) as sumqty
    from ruozedata_erp.dws_ruozedata_bill 
    where CONCAT_WS('-',`type`,subtype) in ('出库-销售','出库-其它','出库-零售')
    and year(createtime)='2019' 
    group by substr(createtime,0,7),materialname,depotname
) m group by m.materialname,m.depotname,m.billtype;

9.最近30天的每天每小时订单总量(含退货)、退货订单总量

 select
   concat(n.chour,':00:00') chour
   ,case when m.bill_count is null then 0
         else  m.bill_count
    end bill_count
   ,case when g.return_bill_count is null then 0
         else g.return_bill_count
     end return_bill_count
     from
   (select 
            SUBSTRING(createtime,1,13) as chour,
            count(DISTINCT number) as bill_count 
        from ruozedata_erp.dws_ruozedata_bill
        group by SUBSTRING(createtime,1,13)
    )m
    left join 
    (
       
      select concat_ws(' ',concat_ws('-',y,m,d),h) as chour 
        from dwd_dim_time 
        where 
        datediff(CURRENT_TIMESTAMP,concat_ws('-',y,m,d))<=30 and  datediff(CURRENT_TIMESTAMP,concat_ws('-',y,m,d))>=0
    
    )n
    on m.chour=n.chour
    left join(
    select 
            SUBSTRING(createtime,1,13) as chour,
            count(DISTINCT number) as return_bill_count 
        from ruozedata_erp.dws_ruozedata_bill 
        where subtype like '%退货%'
        group by SUBSTRING(createtime,1,13)
    )g
    on g.chour=m.chour

10.每天各仓库的盘点时，订单总量(含退货)、退货订单总量、商品总量、总价

  select 
d.name as depotname,
b.bill_count,
r.return_bill_count,
oa.opernumber,
oa.allprice,
b.day
from ruozedata_erp.dwd_ruozedata_depot as d 
left JOIN
    (
    select 
    depotname,      
    count(DISTINCT `number`)  as bill_count,
    substr(createtime,0,10) as day
    from ruozedata_erp.dws_ruozedata_bill
    group by depotname,substr(createtime,0,10)
    ) as b on d.name=b.depotname
left join 
    (
    select 
    depotname,
    count(DISTINCT `number`)  as return_bill_count,
    substr(createtime,0,10) as day
    from ruozedata_erp.dws_ruozedata_bill where subtype like '%退货%'
    group by depotname ,substr(createtime,0,10) 
    ) as r on d.name=r.depotname
left join
    (select 
    depotname,
    substr(createtime,0,10) as day,
    sum(if(CONCAT_WS('-',`type`,subtype) in ('出库-采购退货','出库-销售','出库-其它','出库-零售'),-opernumber,opernumber)) as opernumber,
    sum(if(CONCAT_WS('-',`type`,subtype) in ('出库-采购退货','出库-销售','出库-其它','出库-零售'),-allprice,allprice)) as allprice
    from ruozedata_erp.dws_ruozedata_bill 
    where CONCAT_WS('-',`type`,subtype) in ('入库-采购','出库-采购退货','入库-其它',
    '出库-销售','入库-销售退货','出库-其它','入库-零售退货','出库-零售')
    group by depotname ,substr(createtime,0,10) 
    ) as oa on d.name =oa.depotname;

说明:

type是一级类别，subtype是二级类别
需要拼接作为筛选条件:
CONCAT_WS('-',`type`,subtype)

状态:
进库:
'入库-采购','出库-采购退货','入库-其它','入库-零售退货'

出库:
'出库-销售','入库-销售退货','出库-其它','出库-零售'

作者：pxj （潘陈）
日期：2020-02-03 0:22

panxuej_shenx

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ERP数仓的十个需求

表结构--1.维度表CREATE DATABASE pxj;CREATE TABLE IF NOT EXISTS pxj.dwd_ruozedata_depot ( id BIGINT, name STRING, address STRING, warehousing DOUBLE, truckage DOUBLE, type INT, sort STRING, remark STRI...
复制链接

扫一扫