一、
1、建立原始表
drop table dev.temp_app_eclp_coo_cx_store_detail_2;
create table dev.temp_app_eclp_coo_cx_store_detail_2 as
select
b.so_no as so_no, --ECLP单号（主键1）
c.sp_so_no, --销售平台单号
c.create_time, --创建时间
c.goods_no as goods_no, --商品SKU编号（主键2）
c.goods_name as goods_name, --商品名称
c.store_name as store_name, --仓库名称
c.store_cate_name as store_cate_name, --仓库分类名称
c.store_subd_name as store_subd_name, --仓库分公司名称
c.store_delv_center_name as store_delv_center_name, --仓库配送中心维名称
c.store_region_name as store_region_name, --仓库所在区域名称
c.store_settleame as store_settleame, --仓库机构名称
c.store_cate_new_name as store_cate_new_name, --仓库分类名称(new)
c.store_garden_name as store_garden_name, --仓库园区名称
c.org_name, --发货区域名称
c.distribution_network, --配送网络（大件、中小件B网、中小件C网)(新加)
c.cate1_name, --商品一级分类名称
c.cate2_name, --商品二级分类名称
c.cate3_name, --商品三级分类名称
c.brand_name, --品牌名称
c.wms_rec_qtty, --商品件数
c.sale_ord_dt, --销售订单订购日期
c.jit_tm, --波次时间
c.ord_end_tm, --订单生产截止时间
c.ord_complete_tm, --订单完成时间
c.pre_sorting_tm, --预分拣时间
c.wms_rec_tm, --WMS接收时间
substr(b.wms_rec_tm, 1, 10) as wms_rec_dt, --WMS接收日期
c.print_tm, --打印时间
b.pickup_tm, --拣货完成时间
c.recheck_tm, --复核时间
b.package_tm, --打包时间
case
when c.package_tm is null
then '0'
when c.wms_rec_tm is null
then '0'
else cast(hour(cast(b.pickup_tm as timestamp) - cast(b.wms_rec_tm as timestamp)) * 60 + minute(cast(b.pickup_tm as timestamp) - cast(b.wms_rec_tm as timestamp)) + second(cast(b.pickup_tm as timestamp) - cast(b.wms_rec_tm as timestamp)) / 60 as bigint)
end as pickup_wms_minute, -- WMS—拣货完成 分钟数 系统时间
case
when c.package_tm is null
then '0'
when c.pickup_tm is null
then '0'
else cast(hour(cast(b.package_tm as timestamp) - cast(b.pickup_tm as timestamp)) * 60 + minute(cast(b.package_tm as timestamp) - cast(b.pickup_tm as timestamp)) + second(cast(b.package_tm as timestamp) - cast(b.pickup_tm as timestamp)) / 60 as bigint)
end as package_pickup_minute, -- 拣货-打包完成 分钟数 生产时间
case
when c.pickup_tm is not null
then '1'
else '0'
end as is_pickup, --是否拣货 0订单无拣货 1订单有拣货
case
when c.recheck_tm is not null
then '1'
else '0'
end as is_recheck, --是否复核 0订单无复核 1订单有复核
case
when c.package_tm is not null
then '1'
else '0'
end as is_package--是否打包 0订单无打包 1订单有打包
from
(
select
a.so_no as so_no, --订单号
min(wms_rec_tm) as wms_rec_tm, --最小接收时间
min(pickup_tm) as pickup_tm, --最小拣货时间
max(package_tm) as package_tm --最大打包时间
from
dev.temp_app_eclp_coo_cx_store_detail a
group by
so_no --去重方式
)
b
left join dev.temp_app_eclp_coo_cx_store_detail c
on
c.so_no = b.so_no
and c.wms_rec_tm = b.wms_rec_tm
--and c.pickup_tm = b.pickup_tm
--and c.package_tm = b.package_tm
至今已grpup by 的去重方式。
2、多重group by的建表语句
一定注意，group by 要么分类里的字段能用，要么就得用聚合函数计算的值才能用。
create table dev.temp_app_eclp_coo_cx_store_detail_3 as --建表
select
substr(create_time, 1, 7) as mm, --年月
case
when substr(create_time, 7, 1) = '6'
then substr(create_time, 1, 10) --6月的话，年月日
else substr(create_time, 1, 7) --非6月，年月（默认都为该月的1号）
end as dd,
store_name,
store_cate_name,
store_subd_name,
store_delv_center_name, --仓库配送中心维名称
store_region_name, --仓库所在区域名称
store_settleame, --仓库机构名称
store_cate_new_name, --仓库分类名称(new)
store_garden_name, --仓库园区名称
org_name, --发货区域名称
distribution_network, --配送网络（大件、中小件B网、中小件C网)(新加)
sum(nvl(pickup_wms_minute, 0)) as pickup_wms_minute, --NVL(ARG,VALUE)达标如果前面的ARG值为NULL那么返回的值为后面的VALUE。
sum(nvl(package_pickup_minute, 0)) as package_pickup_minute,
count(DISTINCT so_no) as cnt
from
dev.temp_app_eclp_coo_cx_store_detail_2
group by
substr(create_time, 1, 7),
case
when substr(create_time, 7, 1) = '6'
then substr(create_time, 1, 10)
else substr(create_time, 1, 7)
end,
store_name,
store_cate_name,
store_subd_name,
store_delv_center_name, --仓库配送中心维名称
store_region_name, --仓库所在区域名称
store_settleame, --仓库机构名称
store_cate_new_name, --仓库分类名称(new)
store_garden_name, --仓库园区名称
org_name, --发货区域名称
distribution_network
4月数据
6月数据
3、618程序
################################################################################# dev.temp_app_eclp_coo_cx_store_detail 建表 ###############
#难点计算时间差，取出原始订单数据
drop table dev.temp_app_eclp_coo_cx_store_detail;
create table dev.temp_app_eclp_coo_cx_store_detail as
select
a.sale_ord_ob_id as so_no, --ECLP单号（主键1）
b.sp_so_no, --销售平台单号
b.create_time, --创建时间
a.item_sku_id as goods_no, --商品SKU编号（主键2）
a.item_name as goods_name, --商品名称
a.dim_store_name as store_name, --仓库名称
a.wh_cate_desc as store_cate_name, --仓库分类名称
a.dim_subd_name as store_subd_name, --仓库分公司名称
a.dim_delv_center_name as store_delv_center_name, --仓库配送中心维名称
a.region_name as store_region_name, --仓库所在区域名称
a.settleame as store_settleame, --仓库机构名称
a.wh_cate_desc_new as store_cate_new_name, --仓库分类名称(new)
a.garden_name as store_garden_name, --仓库园区名称
c.org_name, --发货区域名称
c.distribution_network, --配送网络（大件、中小件B网、中小件C网)(新加)
b.cate1_name, --商品一级分类名称
b.cate2_name, --商品二级分类名称
b.cate3_name, --商品三级分类名称
b.brand_name, --品牌名称
a.wms_rec_qtty, --商品件数
a.sale_ord_dt, --销售订单订购日期
a.jit_tm, --波次时间
a.ord_end_tm, --订单生产截止时间
a.ord_complete_tm, --订单完成时间
a.pre_sorting_tm, --预分拣时间
a.wms_rec_tm, --WMS接收时间
substr(a.wms_rec_tm, 1, 10) as wms_rec_dt, --WMS接收日期
a.print_tm, --打印时间
a.pickup_tm, --拣货完成时间
a.recheck_tm, --复核时间
a.package_tm, --打包时间
case
when a.package_tm is null
then '0'
when a.wms_rec_tm is null
then '0'
else cast(hour(cast(a.pickup_tm as timestamp) - cast(a.wms_rec_tm as timestamp)) * 60 + minute(cast(a.pickup_tm as timestamp) - cast(a.wms_rec_tm as timestamp)) + second(cast(a.pickup_tm as timestamp) - cast(a.wms_rec_tm as timestamp)) / 60 as bigint)
end as pickup_wms_minute, -- WMS—拣货完成 分钟数 系统时间
case
when a.package_tm is null
then '0'
when a.pickup_tm is null
then '0'
else cast(hour(cast(a.package_tm as timestamp) - cast(a.pickup_tm as timestamp)) * 60 + minute(cast(a.package_tm as timestamp) - cast(a.pickup_tm as timestamp)) + second(cast(a.package_tm as timestamp) - cast(a.pickup_tm as timestamp)) / 60 as bigint)
end as package_pickup_minute, -- 拣货-打包完成 分钟数 生产时间
case
when a.pickup_tm is not null
then '1'
else '0'
end as is_pickup, --是否拣货 0订单无拣货 1订单有拣货
case
when a.recheck_tm is not null
then '1'
else '0'
end as is_recheck, --是否复核 0订单无复核 1订单有复核
case
when a.package_tm is not null
then '1'
else '0'
end as is_package--是否打包 0订单无打包 1订单有打包
from
dev.temp_app_eclp_coo_cx_store a
left join dev.temp_app_eclp_coo_cx_order_det b --胡文博表
on
a.sale_ord_ob_id = b.so_no
and a.item_sku_id = b.goods_no
left join dev.temp_app_eclp_coo_cx_waybill c --刘银苹表
on
a.sale_ord_ob_id = c.so_no ;
##################################################################################### dev.temp_app_eclp_coo_cx_store_detail_2建表############################
#找出最小的接收时间，最小的拣货时间，最大的打包时间，但是由于是自连接join，所以有重复的情况。
drop table dev.temp_app_eclp_coo_cx_store_detail_2;
create table dev.temp_app_eclp_coo_cx_store_detail_2 as
select
b.so_no as so_no, --ECLP单号（主键1）
c.sp_so_no, --销售平台单号
c.create_time, --创建时间
c.goods_no as goods_no, --商品SKU编号（主键2）
c.goods_name as goods_name, --商品名称
c.store_name as store_name, --仓库名称
c.store_cate_name as store_cate_name, --仓库分类名称
c.store_subd_name as store_subd_name, --仓库分公司名称
c.store_delv_center_name as store_delv_center_name, --仓库配送中心维名称
c.store_region_name as store_region_name, --仓库所在区域名称
c.store_settleame as store_settleame, --仓库机构名称
c.store_cate_new_name as store_cate_new_name, --仓库分类名称(new)
c.store_garden_name as store_garden_name, --仓库园区名称
c.org_name, --发货区域名称
c.distribution_network, --配送网络（大件、中小件B网、中小件C网)(新加)
c.cate1_name, --商品一级分类名称
c.cate2_name, --商品二级分类名称
c.cate3_name, --商品三级分类名称
c.brand_name, --品牌名称
c.wms_rec_qtty, --商品件数
c.sale_ord_dt, --销售订单订购日期
c.jit_tm, --波次时间
c.ord_end_tm, --订单生产截止时间
c.ord_complete_tm, --订单完成时间
c.pre_sorting_tm, --预分拣时间
c.wms_rec_tm, --WMS接收时间
substr(b.wms_rec_tm, 1, 10) as wms_rec_dt, --WMS接收日期
c.print_tm, --打印时间
b.pickup_tm, --拣货完成时间
c.recheck_tm, --复核时间
b.package_tm, --打包时间
case
when c.package_tm is null
then '0'
when c.wms_rec_tm is null
then '0'
else cast(hour(cast(b.pickup_tm as timestamp) - cast(b.wms_rec_tm as timestamp)) * 60 + minute(cast(b.pickup_tm as timestamp) - cast(b.wms_rec_tm as timestamp)) + second(cast(b.pickup_tm as timestamp) - cast(b.wms_rec_tm as timestamp)) / 60 as bigint)
end as pickup_wms_minute, -- WMS—拣货完成 分钟数 系统时间
case
when c.package_tm is null
then '0'
when c.pickup_tm is null
then '0'
else cast(hour(cast(b.package_tm as timestamp) - cast(b.pickup_tm as timestamp)) * 60 + minute(cast(b.package_tm as timestamp) - cast(b.pickup_tm as timestamp)) + second(cast(b.package_tm as timestamp) - cast(b.pickup_tm as timestamp)) / 60 as bigint)
end as package_pickup_minute, -- 拣货-打包完成 分钟数 生产时间
case
when c.pickup_tm is not null
then '1'
else '0'
end as is_pickup, --是否拣货 0订单无拣货 1订单有拣货
case
when c.recheck_tm is not null
then '1'
else '0'
end as is_recheck, --是否复核 0订单无复核 1订单有复核
case
when c.package_tm is not null
then '1'
else '0'
end as is_package--是否打包 0订单无打包 1订单有打包
from
(
select
a.so_no as so_no, --订单号
min(wms_rec_tm) as wms_rec_tm, --最小接收时间
min(pickup_tm) as pickup_tm, --最小拣货时间
max(package_tm) as package_tm --最大打包时间
from
dev.temp_app_eclp_coo_cx_store_detail a
group by
so_no
)
b
left join dev.temp_app_eclp_coo_cx_store_detail c
on
c.so_no = b.so_no
and c.wms_rec_tm = b.wms_rec_tm
--and c.pickup_tm = b.pickup_tm
--and c.package_tm = b.package_tm
#################################################################################### dev.temp_app_eclp_coo_cx_store_detail_3 建表##################################
#group by 去重，把所有的数据找出来，放到一张Excel表中。
create table dev.temp_app_eclp_coo_cx_store_detail_3 as
select
substr(create_time, 1, 7) as mm,
case
when substr(create_time, 7, 1) = '6'
then substr(create_time, 1, 10)
else substr(create_time, 1, 7)
end as dd,
store_name,
store_cate_name,
store_subd_name,
store_delv_center_name, --仓库配送中心维名称
store_region_name, --仓库所在区域名称
store_settleame, --仓库机构名称
store_cate_new_name, --仓库分类名称(new)
store_garden_name, --仓库园区名称
org_name, --发货区域名称
distribution_network, --配送网络（大件、中小件B网、中小件C网)(新加)
sum(nvl(pickup_wms_minute, 0)) as pickup_wms_minute,
sum(nvl(package_pickup_minute, 0)) as package_pickup_minute,
count(DISTINCT so_no) as cnt
from
dev.temp_app_eclp_coo_cx_store_detail_2
group by
substr(create_time, 1, 7),
case
when substr(create_time, 7, 1) = '6'
then substr(create_time, 1, 10)
else substr(create_time, 1, 7)
end,
store_name,
store_cate_name,
store_subd_name,
store_delv_center_name, --仓库配送中心维名称
store_region_name, --仓库所在区域名称
store_settleame, --仓库机构名称
store_cate_new_name, --仓库分类名称(new)
store_garden_name, --仓库园区名称
org_name, --发货区域名称
distribution_network
二、
1、
select * from lyp_dev_yuce_qtty
初始数据这样，有时间，标签，区域，库存值，现在需要按区域统计所有月份某一种标签的值，及其和。
2、
select
substr(stat_dt, 1, 7) as stat_dt,
band_name,
loc_region,
stock_qtty
from
lyp_dev_yuce_qtty
where
loc_region = '华东'
将华东区域数据找出来。并没有每个月汇总的数据
3、每个月的汇总数据
select
substr(stat_dt, 1, 7) as stat_dt,
'#汇总' as band_name,
'华东' as loc_region,
sum(stock_qtty) as stock_qtty
from
lyp_dev_yuce_qtty
where
loc_region = '华东'
group by
stat_dt
增加了个新标签，汇总值。
4、合并所有的值
select
substr(stat_dt, 1, 7) as stat_dt,
band_name,
loc_region,
stock_qtty
from
lyp_dev_yuce_qtty
where
loc_region = '华东'
union all
select
substr(stat_dt, 1, 7) as stat_dt,
'#汇总' as band_name,
'华东' as loc_region,
sum(stock_qtty) as stock_qtty
from
lyp_dev_yuce_qtty
where
loc_region = '华东'
group by
stat_dt
5047
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
