数据开发（二）

一、

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