KA 接口表

一、建表

1、年日均销量表

drop table app.app_basic_dashboard_goods_avg_year_sellnum;
CREATE TABLE app.app_basic_dashboard_goods_avg_year_sellnum (
  seller_id bigint COMMENT '商家id', 
  seller_name string COMMENT '商家名称', 
  dept_id bigint COMMENT '事业部门id', 
  dept_name string COMMENT '事业部名字', 
  warehouse_id bigint COMMENT '入仓id', 
  warehouse_name string COMMENT '入仓名称',
  goods_id bigint COMMENT '商品id', 
  goods_no string COMMENT '商品序号', 
  goods_name string COMMENT '商品名称', 
  avg_year_sellnum float COMMENT '年日均销量',
  yn int COMMENT '删除标识 1为没删,0为删除', 
  create_pin string COMMENT '创建人', 
  update_pin string COMMENT '更新人', 
  create_time timestamp COMMENT '创建时间', 
  update_time timestamp COMMENT '更新时间', 
  ts timestamp COMMENT '时间戳')
COMMENT 'KA商品销量预测年日均销量'
PARTITIONED BY ( 
  dt string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

 2、商家参数表

CREATE EXTERNAL TABLE `app_basic_dashboard_goods_seller`(
  `seller_id` string COMMENT '商家id', 
  `seller_no` string COMMENT '商家编号', 
  `seller_name` string COMMENT '商家名称', 
  `dept_id` string COMMENT '部门id', 
  `dept_no` string COMMENT '部门编号', 
  `vlt` string COMMENT 'VLT',
  `alt` string COMMENT 'ALT',
  `satisfyAlpha` string COMMENT '出仓服务水平C',
  `safetyDays` int COMMENT '安全库存天数',
  `targetDays` int COMMENT '目标库存天数',
  `bp` string COMMENT 'BP',
  `task_exec_date` string COMMENT '任务执行时的时间,保存格式为:2018-03-31', 
  `yn` string COMMENT '删除标识 1为没删,0为删除', 
  `create_time` timestamp COMMENT '创建时间', 
  `create_pin` string COMMENT '创建人', 
  `update_pin` string COMMENT '更新人', 
  `update_time` timestamp COMMENT '更新时间', 
  `ts` timestamp COMMENT '时间戳')
COMMENT '商家表'
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

3、KA 临时中转表

drop table app.app_basic_dashboard_goods_sale_predict_daily_tem;
CREATE TABLE app.app_basic_dashboard_goods_sale_predict_daily_tem (
  seller_id bigint COMMENT '商家id', 
  seller_name string COMMENT '商家名称', 
  dept_id bigint COMMENT '事业部门id', 
  dept_no string COMMENT '事业部门编号',  
  dept_name string COMMENT '事业部名字', 
  goods_id bigint COMMENT '商品id', 
  goods_no string COMMENT '商品序号', 
  goods_name string COMMENT '商品名称', 
  warehouse_id bigint COMMENT '入仓id', 
  warehouse_no string COMMENT '入仓编号', 
  warehouse_name string COMMENT '入仓名称', 
  in_warehouse_city string COMMENT '入仓城市',
  satisfy_alpha string COMMENT '入仓服务水平C', 
  safe_stock_days string COMMENT '安全库存天数', 
  alt string COMMENT '出仓ALT', 
  vlt string COMMENT '出仓VLT', 
  in_stock_safety_num int COMMENT '安全库存', 
  target_stock_days string COMMENT '目标库存天数', 
  bp string COMMENT 'BP', 
  out_warehouse_no string COMMENT '出仓编号', 
  out_warehouse_name string COMMENT '出仓名称', 
  out_warehouse_city string COMMENT '出仓城市', 
  in_stock_max_num int COMMENT '目标库存', 
  bef_sales_1d string COMMENT 'T-1日销量', 
  bef_sales_2d string COMMENT 'T-2日销量', 
  bef_sales_3d string COMMENT 'T-3日销量',
  bef_sales_4d string COMMENT 'T-4日销量', 
  bef_sales_5d string COMMENT 'T-5日销量', 
  bef_sales_6d string COMMENT 'T-6日销量',
  bef_sales_7d string COMMENT 'T-7日销量', 
  in_stock_sales_14d string COMMENT '历史14日销量和', 
  in_stock_sales_28d string COMMENT '历史28日销量和',
  predict_sales_1d string COMMENT 'T+1日预测销量',
  predict_sales_2d string COMMENT 'T+2日预测销量', 
  predict_sales_3d string COMMENT 'T+3日预测销量',
  predict_sales_4d string COMMENT 'T+4日预测销量',
  predict_sales_5d string COMMENT 'T+5日预测销量', 
  predict_sales_6d string COMMENT 'T+6日预测销量',
  predict_sales_7d string COMMENT 'T+7日预测销量',
  in_stock_predict_sales_14d string COMMENT '14日预测销量和', 
  in_stock_predict_sales_28d string COMMENT '28日预测销量和',
  yn int COMMENT '删除标识 1为没删,0为删除', 
  create_pin string COMMENT '创建人', 
  update_pin string COMMENT '更新人', 
  create_time timestamp COMMENT '创建时间', 
  update_time timestamp COMMENT '更新时间', 
  ts timestamp COMMENT '时间戳')
COMMENT 'KA商品销量预测接口中转表'
PARTITIONED BY ( 
  dt string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

 4、KA 接口表

drop table app.app_basic_dashboard_goods_sale_predict_daily;
CREATE TABLE app.app_basic_dashboard_goods_sale_predict_daily (
  seller_id bigint COMMENT '商家id', 
  seller_name string COMMENT '商家名称', 
  dept_id bigint COMMENT '事业部门id', 
  dept_no string COMMENT '事业部门编号',  
  dept_name string COMMENT '事业部名字', 
  goods_id bigint COMMENT '商品id', 
  goods_no string COMMENT '商品序号', 
  goods_name string COMMENT '商品名称', 
  warehouse_id bigint COMMENT '入仓id', 
  warehouse_no string COMMENT '入仓编号', 
  warehouse_name string COMMENT '入仓名称', 
  in_warehouse_city string COMMENT '入仓城市',
  satisfy_alpha string COMMENT '入仓服务水平C', 
  safe_stock_days string COMMENT '安全库存天数', 
  alt string COMMENT '出仓ALT', 
  vlt string COMMENT '出仓VLT', 
  in_stock_safety_num int COMMENT '安全库存', 
  target_stock_days string COMMENT '目标库存天数', 
  bp string COMMENT 'BP', 
  out_warehouse_no string COMMENT '出仓编号', 
  out_warehouse_name string COMMENT '出仓名称', 
  out_warehouse_city string COMMENT '出仓城市', 
  in_stock_max_num int COMMENT '目标库存', 
  bef_sales_1d string COMMENT 'T-1日销量', 
  bef_sales_2d string COMMENT 'T-2日销量', 
  bef_sales_3d string COMMENT 'T-3日销量',
  bef_sales_4d string COMMENT 'T-4日销量', 
  bef_sales_5d string COMMENT 'T-5日销量', 
  bef_sales_6d string COMMENT 'T-6日销量',
  bef_sales_7d string COMMENT 'T-7日销量', 
  in_stock_sales_14d string COMMENT '历史14日销量和', 
  in_stock_sales_28d string COMMENT '历史28日销量和',
  predict_sales_1d string COMMENT 'T+1日预测销量',
  predict_sales_2d string COMMENT 'T+2日预测销量', 
  predict_sales_3d string COMMENT 'T+3日预测销量',
  predict_sales_4d string COMMENT 'T+4日预测销量',
  predict_sales_5d string COMMENT 'T+5日预测销量', 
  predict_sales_6d string COMMENT 'T+6日预测销量',
  predict_sales_7d string COMMENT 'T+7日预测销量',
  in_stock_predict_sales_14d string COMMENT '14日预测销量和', 
  in_stock_predict_sales_28d string COMMENT '28日预测销量和',
  yn int COMMENT '删除标识 1为没删,0为删除', 
  create_pin string COMMENT '创建人', 
  update_pin string COMMENT '更新人', 
  create_time timestamp COMMENT '创建时间', 
  update_time timestamp COMMENT '更新时间', 
  ts timestamp COMMENT '时间戳')
COMMENT 'KA商品销量预测接口表'
PARTITIONED BY ( 
  dt string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

二、插值

1、商家参数表

insert overwrite table app.app_basic_dashboard_goods_seller
select distinct
	seller_id,
	seller_no,
	seller_name,
	dept_id,
	dept_no,
    '36' as vlt,  --VLT
    '10' as alt,  --ALT
    '0.8' as satisfyAlpha,  --出仓服务水平C
	case
		when seller_no = 'ECP0020000003619'  --安利
		then 32
		when seller_no = 'ECP0020000014466'  --住友
		then 120
		else 0
	end as safetyDays,  --安全库存天数
	case
		when seller_no = 'ECP0020000003619'  
		then 40
		when seller_no = 'ECP0020000014466'  
		then 132
		else 0
	end as targetDays,  --目标库存天数
    '20' as bp,  --BP
	'"""+yesterday+"""' AS task_exec_date,
	1 AS yn,
	current_timestamp AS create_time,
	'plumber' AS create_pin,
	'plumber' AS update_pin,
	current_timestamp AS update_time,
	current_timestamp AS ts
from
	fdm.fdm_eclp_so1_so_main_chain
WHERE
	start_date <= '"""+yesterday+"""'
	and end_date > '"""+yesterday+"""'
	and seller_no in('ECP0020000003619', 'ECP0020000014466') ;
   

2、年日均销量表

#!/usr/bin/env python3
################################################################
# AUTHOR:         wn
# CREATED TIME:   2018-08-09
# MODIFIED BY:    
# MODIFTED TIME:  
# REVIEWED BY:    
# REVIEWED TIME:  
# COMMENTS: goods

################################################################
#===============================================================================
#  FILE: exe_app_basic_dashboard_goods_avg_year_sellnum.py
#  USAGE: ./exe_app_basic_dashboard_goods_avg_year_sellnum.py
#  SRC_TABLE: 
#  TGT_TABLE: app.app_basic_dashboard_goods_avg_year_sellnum
#===============================================================================
import sys
import os
import time
import datetime
import logging 
import calendar

sys.path.append(os.getenv('HIVE_TASK'))
from HiveTask import HiveTask
ht = HiveTask()
today = ht.oneday(1)[0:10]
yesterday  = ht.oneday(0)[0:10] 


sql1 = """
use app;
	insert overwrite table app.app_basic_dashboard_goods_avg_year_sellnum partition
	(
	   dt = '"""+yesterday+"""'
	) 
	SELECT
	seller_id, --商家id
	seller_name, 
	dept_id,
	dept_name,
	warehouse_id,
	warehouse_name,
	goods_id,
	goods_no,
	goods_name,
	round((
			case
				WHEN saletime >= 365
				THEN yreal_outtore_qty / 365
				WHEN saletime < 365
				THEN real_outtore_qty / datediff(sysdate( - 1), oldsaletime)
				ELSE 0
			end), 2) AS avg_year_sellnum, --年日均销量
	1 AS yn, --删除标识 1为没删,0为删除
	'plumber' AS create_pin, --创建人
	'plumber' AS update_pin, --更新人
	current_timestamp AS create_time, --创建时间
	current_timestamp AS update_time, --更新时间
	current_timestamp AS ts --时间戳
FROM
	(
		SELECT
			seller_id,
			seller_name,
			dept_id,
			dept_name,
			warehouse_id,
			warehouse_name,
			goods_id,
			goods_no,
			goods_name,
			datediff(sysdate( - 1), min(to_date(create_time))) AS saletime, --
			min(to_date(create_time)) AS oldsaletime, --
			sum(real_outtore_qty) AS real_outtore_qty, --
			sum(
				case
					WHEN create_time >= date_sub(sysdate( - 1), 365)
					THEN real_outtore_qty
					ELSE 0
				end) AS yreal_outtore_qty --
		FROM
			(
				SELECT
					main.seller_id,
					main.seller_name,
					main.dept_id,
					main.dept_name,
					main.warehouse_id,
					case
						when warehouse.warehouse_name is not null
						then warehouse.warehouse_name
						else main.warehouse_name
					end warehouse_name,
					item.create_time, --
					item.goods_id,
					item.goods_no,
					item.goods_name,
					item.real_outtore_qty AS real_outtore_qty
				FROM
					(
						SELECT
							so_no,
							seller_id,
							seller_no,
							seller_name,
							dept_id,
							dept_no,
							trim(dept_name) as dept_name,
							warehouse_id,
							warehouse_no,
							warehouse_name
						FROM
							fdm.fdm_eclp_so1_so_main_chain
						WHERE
							start_date <= sysdate( - 1)
							and end_date > sysdate( - 1)
							AND seller_no in
							(
								select seller_no from app.app_basic_dashboard_goods_seller
							)
							AND
							(
								parent_id = cast(substring(so_no, 4) AS bigint)
								OR parent_id is NULL
							)
							AND so_status <> '10056'
							AND so_status <> '10009'
							AND so_status <> '10028'
							AND so_status <> '10060'
					)
					main
				JOIN
					(
						SELECT
							so_id,
							goods_id,
							goods_no,
							goods_name,
							dept_id,
							sum(nvl(apply_outstore_qty, 0)) AS apply_outstore_qty,
							sum(
								case
									WHEN nvl(real_outtore_qty, 0) = 0
										and nvl(apply_outstore_qty, 0) > 0
									THEN nvl(apply_outstore_qty, 0)
									ELSE nvl(real_outtore_qty, 0)
								end) AS real_outtore_qty,
							min(create_time) as create_time
						FROM
							fdm.fdm_eclp_so1_so_item_chain
						WHERE
							dt >= date_sub(sysdate( - 1), 365)
						GROUP BY
							so_id,
							goods_id,
							goods_no,
							goods_name,
							dept_id
					)
					item
				ON
					substring(main.so_no, 4) = item.so_id
				LEFT JOIN
					(
						SELECT
							warehouse_no,
							warehouse_name
						from
							app.app_log_scm_ka_warehouse wh1
						left join
							(
								select distinct
									dim_area_id,
									dim_area_name
								from
									dim.dim_supp_report_area_province
							)
							wh2
						on
							trim(wh1.org_name) = trim(wh2.dim_area_name)
						GROUP BY
							org_id,
							wh2.dim_area_id,
							org_name,
							warehouse_no,
							warehouse_name,
							province_id,
							province_name,
							city_id,
							city_name
					)
					warehouse ON main.warehouse_no = warehouse.warehouse_no
			)
			p
		GROUP BY
			seller_id,
			seller_name,
			dept_id,
			dept_name,
			warehouse_id,
			warehouse_name,
			goods_id,
			goods_no,
			goods_name
	)
	q
    
             
"""

ht.exec_sql(schema_name = 'app', table_name = 'app_basic_dashboard_goods_avg_year_sellnum', sql = sql1, merge_flag = True) 

3、中转表

#!/usr/bin/env python3
################################################################
# AUTHOR:         wn
# CREATED TIME:   2018-08-09
# MODIFIED BY:    
# MODIFTED TIME:  
# REVIEWED BY:    
# REVIEWED TIME:  
# COMMENTS: goods

################################################################
#===============================================================================
#  FILE: exe_app_basic_goods_stock_num_daily_da_d.py
#  USAGE: ./exe_app_basic_goods_stock_num_daily_da_d.py
#  SRC_TABLE: 
#  TGT_TABLE: app_basic_goods_stock_num_daily_da
#===============================================================================
import sys
import os
import time
import datetime
import logging 
import calendar

sys.path.append(os.getenv('HIVE_TASK'))
from HiveTask import HiveTask
ht = HiveTask()
today = ht.oneday(1)[0:10]
yesterday  = ht.oneday(0)[0:10] 


sql1 = """
use app;
	insert overwrite table app.app_basic_dashboard_goods_sale_predict_daily_tem partition
	(
	   dt = '"""+yesterday+"""'
	) 
	select
	c.seller_id, --商家编号
	c.seller_name, --商家名称
	c.dept_id, --事业部id
	c.dept_no, --事业部编号
	c.dept_name, --事业部名称
	c.goods_id, --商品id
	c.goods_no, --商品编号
	c.goods_name, --商品名称
	c.warehouse_id, --入仓id
	c.warehouse_no, --入仓编号
	c.warehouse_name, --入仓名称
	h.loc_city_name as in_warehouse_city, --入仓城市
	case
		when d.satisfy_alpha is null
		then n.satisfyalpha
		else d.satisfy_alpha
	end as satisfy_alpha, -- 出仓服务水平c (basis)
	case
		when d.safe_stock_days is null
		then n.safetydays
		else d.safe_stock_days
	end as safe_stock_days, -- 安全库存天数(basis)
	case
		when d.alt is null
		then n.alt
		else d.alt
	end as alt, -- 出仓alt(小时)(basis)
	case
		when j.totaltime is null
		then n.vlt
		else j.totaltime
	end as vlt, --运输时间(basis)
	f.in_stock_safety_num as in_stock_safety_num, --安全库存(计划调拨表取数)
	case
		when d.target_stock_days is null
		then n.targetdays
		else d.target_stock_days
	end as target_stock_days, -- 目标库存天数(basis)
	case
		when d.bp is null
		then n.bp
		else d.bp
	end as BP, -- BP
	f.out_warehouse_no as out_warehouse_no, --配出仓编码
	f.out_warehouse_name as out_warehouse_name, --配出仓名称
	i.loc_city_name as out_warehouse_city, --入仓城市
	f.in_stock_max_num as in_stock_max_num, --目标库存(计划调拨表取数)
	case
		when split(g.sales_week_detail, ',') [6] is NULL
		then '0'
		else split(g.sales_week_detail, ',') [6]
	end as bef_sales_1d, --T-1日销量(逆向)
	case
		when split(g.sales_week_detail, ',') [5] is NULL
		then '0'
		else split(g.sales_week_detail, ',') [5]
	end as bef_sales_2d, --T-2日销量
	case
		when split(g.sales_week_detail, ',') [4] is NULL
		then '0'
		else split(g.sales_week_detail, ',') [4]
	end as bef_sales_3d, --T-3日销量
	case
		when split(g.sales_week_detail, ',') [3] is NULL
		then '0'
		else split(g.sales_week_detail, ',') [3]
	end as bef_sales_4d, --T-4日销量
	case
		when split(g.sales_week_detail, ',') [2] is NULL
		then '0'
		else split(g.sales_week_detail, ',') [2]
	end as bef_sales_5d, --T-5日销量
	case
		when split(g.sales_week_detail, ',') [1] is NULL
		then '0'
		else split(g.sales_week_detail, ',') [1]
	end as bef_sales_6d, --T-6日销量
	case
		when split(g.sales_week_detail, ',') [0] is NULL
		then '0'
		else split(g.sales_week_detail, ',') [0]
	end as bef_sales_7d, --T-7日销量
	case
		when g.sales_14d is NULL
		then '0'
		else g.sales_14d
	end as in_stock_sales_14d, --14日销量
	case
		when g.sales_28d is NULL
		then '0'
		else g.sales_28d
	end as in_stock_sales_28d, --28日销量
	case
		when split(g.predict_sales_week_detail, ',') [0] is NULL
			and m.avg_year_sellnum is NULL --都空为0
		then '0'
		when split(g.predict_sales_week_detail, ',') [0] is NULL
			and m.avg_year_sellnum is not NULL --没有预测值,按销量平均值计
		then round(m.avg_year_sellnum)
		else split(g.predict_sales_week_detail, ',') [0] --预测值计
	end as predict_sales_1d, --T+1日预测销量(正向)
	case
		when split(g.predict_sales_week_detail, ',') [1] is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when split(g.predict_sales_week_detail, ',') [1] is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum)
		else split(g.predict_sales_week_detail, ',') [1]
	end as predict_sales_2d, --T+2日预测销量
	case
		when split(g.predict_sales_week_detail, ',') [2] is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when split(g.predict_sales_week_detail, ',') [2] is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum)
		else split(g.predict_sales_week_detail, ',') [2]
	end as predict_sales_3d, --T+3日预测销量
	case
		when split(g.predict_sales_week_detail, ',') [3] is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when split(g.predict_sales_week_detail, ',') [3] is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum)
		else split(g.predict_sales_week_detail, ',') [3]
	end as predict_sales_4d, --T+4日预测销量
	case
		when split(g.predict_sales_week_detail, ',') [4] is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when split(g.predict_sales_week_detail, ',') [4] is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum)
		else split(g.predict_sales_week_detail, ',') [4]
	end as predict_sales_5d, --T+5日预测销量
	case
		when split(g.predict_sales_week_detail, ',') [5] is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when split(g.predict_sales_week_detail, ',') [5] is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum)
		else split(g.predict_sales_week_detail, ',') [5]
	end as predict_sales_6d, --T+6日预测销量
	case
		when split(g.predict_sales_week_detail, ',') [6] is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when split(g.predict_sales_week_detail, ',') [6] is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum)
		else split(g.predict_sales_week_detail, ',') [6]
	end as predict_sales_7d, --T+7日预测销量
	case
		when g.predict_sales_14d is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when g.predict_sales_14d is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum * 14)
		else g.predict_sales_14d
	end as in_stock_predict_sales_14d, --14日预测销量
	case
		when g.predict_sales_28d is NULL
			and m.avg_year_sellnum is NULL
		then '0'
		when g.predict_sales_28d is NULL
			and m.avg_year_sellnum is not NULL
		then round(m.avg_year_sellnum * 28)
		else g.predict_sales_28d
	end as in_stock_predict_sales_28d, --28日预测销量
	1 AS yn, --删除标识 1为没删,0为删除
	'plumber' AS create_pin, --创建人
	'plumber' AS update_pin, --更新人
	current_timestamp AS create_time, --创建时间
	current_timestamp AS update_time, --更新时间
	current_timestamp AS ts --时间戳
from
	(
		SELECT
			*
		FROM
			(
				SELECT
					id,
					goods_id,
					goods_no,
					goods_name,
					seller_id,
					seller_no,
					trim(seller_name) as seller_name, --去除空格
					dept_id,
					dept_no,
					trim(dept_name) as dept_name,
					warehouse_no,
					warehouse_id,
					warehouse_name,
					update_time,
					create_time,
					row_number() over(partition by goods_id, seller_no, warehouse_no ORDER BY update_time desc, create_time desc) AS num --去重
				FROM
					fdm.fdm_eclp_stock1_saleable_warehouse_stock_chain
				WHERE
					dp = 'ACTIVE'
					AND yn = 1
					AND seller_no in
					(
						select seller_no from app.app_basic_dashboard_goods_seller
					)
			)
			s1
		WHERE
			s1.num = 1
	)
	c
left join fdm.fdm_log_scm_ka_allot_sys_pre_allot d --参数表
on
	c.dept_no = d.dept_no
	and c.warehouse_no = d.warehouse_no
	and c.goods_no = d.goods_id
	and d.yn = 1
	and d.dt = sysdate( - 1)
left join
	(
		select
			*
		from
			fdm.fdm_log_scm_ka_allot_allot_plan --调拨计划表 (注意去重)
		where
			id in
			(
				select
					max(e.id)
				from
					fdm.fdm_log_scm_ka_allot_allot_plan e
				where
					e.dt = sysdate( - 1)
				group by
					e.dept_name,
					e.in_warehouse_no,
					e.goods_no
			)
			and dt = sysdate( - 1)
	)
	f 
on
	f.dept_no = c.dept_no
	and f.in_warehouse_no = c.warehouse_no
	and f.goods_no = c.goods_no
left join app.app_ka_predict_sales_da g ---线下门店销量预测表
on
	c.dept_no = g.dept_no
	and c.goods_no = g.goods_no
	and c.warehouse_no = g.warehouse_no
	and g.dt = sysdate( - 1)
left join dim.dim_wms_store h --库房维表,取出入仓所属城市
on
	c.warehouse_name = h.dim_store_name
left join dim.dim_wms_store i --库房维表,取出仓所属城市
on
	f.out_warehouse_name = i.dim_store_name
left join
	(
		select
			startcityid,
			startcityname,
			endcityid,
			endcityname,
			totalaging,
			totaltime,
			yn
		from
			(
				select
					case
						when
							(
								startprovinceid in(1, 2, 3, 4)
							)
						then startprovinceid
						else startcityid
					end as startcityid,
					case
						when
							(
								startprovinceid in(1, 2, 3, 4)
							)
						then concat(startprovincename, '市')
						else startcityname
					end as startcityname,
					case
						when
							(
								endprovinceid in(1, 2, 3, 4)
							)
						then endprovinceid
						else endcityid
					end as endcityid,
					case
						when
							(
								endprovinceid in(1, 2, 3, 4)
							)
						then concat(endprovincename, '市')
						else endcityname
					end as endcityname,
					ceil(avg(totalaging)) as totalaging,
					ceil(avg(totaltime)) as totaltime,
					max(1) as yn
				from
					fdm.fdm_staticroutebatchgenerate_staticroutebatchgenerate_chain
				where
					dp = 'ACTIVE'
					and yn = 1
				group by
					case
						when
							(
								startprovinceid in(1, 2, 3, 4)
							)
						then startprovinceid
						else startcityid
					end,
					case
						when
							(
								startprovinceid in(1, 2, 3, 4)
							)
						then concat(startprovincename, '市')
						else startcityname
					end,
					case
						when
							(
								endprovinceid in(1, 2, 3, 4)
							)
						then endprovinceid
						else endcityid
					end,
					case
						when
							(
								endprovinceid in(1, 2, 3, 4)
							)
						then concat(endprovincename, '市')
						else endcityname
					end
			)
			route
	)
	j --vlt (青龙路由,通过城市名称,获取vlt)
on
	h.loc_city_name = j.startcityname
	and i.loc_city_name = j.endcityname
left join
	(
		select
			*
		from
			(
				SELECT
					seller_id,
					warehouse_id,
					goods_id,
					avg_year_sellnum,
					dt,
					row_number() over(partition by goods_id, seller_id, warehouse_id ORDER BY avg_year_sellnum desc) AS num
				FROM
					app.app_basic_dashboard_goods_avg_year_sellnum --KA商品销量年日均值(去重)
				WHERE
					dt = sysdate( - 1)
			)
			s2
		where
			s2.num = 1
	)
	m --获取销量年日均值
on
	c.seller_id = m.seller_id
	and c.warehouse_id = m.warehouse_id
	and c.goods_id = m.goods_id
	and m.dt = sysdate( - 1)
join app.app_basic_dashboard_goods_seller n
on
	c.seller_id = n.seller_id
             
"""

ht.exec_sql(schema_name = 'app', table_name = 'app_basic_dashboard_goods_sale_predict_daily_tem', sql = sql1, merge_flag = True) 

 4、KA接口表

#!/usr/bin/env python3
################################################################
# AUTHOR:         wn
# CREATED TIME:   2018-08-09
# MODIFIED BY:    
# MODIFTED TIME:  
# REVIEWED BY:    
# REVIEWED TIME:  
# COMMENTS: goods

################################################################
#===============================================================================
#  FILE: exe_app_basic_dashboard_goods_sale_predict_daily.py
#  USAGE: ./exe_app_basic_dashboard_goods_sale_predict_daily.py
#  SRC_TABLE: 
#  TGT_TABLE: app.app_basic_dashboard_goods_sale_predict_daily
#===============================================================================
import sys
import os
import time
import datetime
import logging 
import calendar

sys.path.append(os.getenv('HIVE_TASK'))
from HiveTask import HiveTask
ht = HiveTask()
today = ht.oneday(1)[0:10]
yesterday  = ht.oneday(0)[0:10] 


sql1 = """
use app;
	insert overwrite table app.app_basic_dashboard_goods_sale_predict_daily partition
	(
	   dt = '"""+yesterday+"""'
	) 
	select
	k.seller_id, --商家编号
	k.seller_name, --商家名称
	k.dept_id, --事业部id
	k.dept_no, --事业部编号
	k.dept_name, --事业部名称
	k.goods_id, --商品id
	k.goods_no, --商品编号
	k.goods_name, --商品名称
	k.warehouse_id, --入仓id
	k.warehouse_no, --入仓编号
	k.warehouse_name, --入仓名称
	k.in_warehouse_city, --入仓城市
	k.satisfy_alpha, -- 出仓服务水平c (basis)
	k.safe_stock_days, -- 安全库存天数(basis)
	k.alt, -- 出仓alt(小时)(basis)
	k.vlt, --运输时间
	case
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			>= 0
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 1
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) * m.predict_sales_1d)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 1
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 2
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.predict_sales_1d + m.predict_sales_2d) / 2)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 2
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 3
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.predict_sales_1d + m.predict_sales_2d + m.predict_sales_3d) / 3)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 3
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 4
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.predict_sales_1d + m.predict_sales_2d + m.predict_sales_3d + m.predict_sales_4d) / 4)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 4
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 5
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.predict_sales_1d + m.predict_sales_2d + m.predict_sales_3d + m.predict_sales_4d + m.predict_sales_5d) / 5)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 5
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 6
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.predict_sales_1d + m.predict_sales_2d + m.predict_sales_3d + m.predict_sales_4d + m.predict_sales_5d + m.predict_sales_6d) / 6)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 6
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 7
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.predict_sales_1d + m.predict_sales_2d + m.predict_sales_3d + m.predict_sales_4d + m.predict_sales_5d + m.predict_sales_6d + m.predict_sales_7d) / 7)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 7
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			<= 14
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.in_stock_predict_sales_14d) / 14)
		when k.in_stock_safety_num is null
			and
			(
				m.safe_stock_days + m.alt / 24 + m.vlt / 24
			)
			> 14
		then round(m.satisfy_alpha *(m.safe_stock_days + m.alt / 24 + m.vlt / 24) *(m.in_stock_predict_sales_28d) / 28)
		else round(k.in_stock_safety_num)
	end as in_stock_safety_num, --安全库存(复杂公式见prd)
	k.target_stock_days, -- 目标库存天数
	k.BP, -- BP
	k.out_warehouse_no, --配出仓编码
	k.out_warehouse_name, --配出仓名称
	k.out_warehouse_city, --出仓城市
	case
		when k.in_stock_max_num is null
		then round(m.satisfy_alpha * m.target_stock_days * m.in_stock_predict_sales_14d / 14 + m.BP) --目标库存天数10,所以没有判断条件
		else round(k.in_stock_max_num)
	end as in_stock_max_num, --目标库存
	k.bef_sales_1d, --T-1日销量(逆向)
	k.bef_sales_2d, --T-2日销量
	k.bef_sales_3d, --T-3日销量
	k.bef_sales_4d, --T-4日销量
	k.bef_sales_5d, --T-5日销量
	k.bef_sales_6d, --T-6日销量
	k.bef_sales_7d, --T-7日销量
	k.in_stock_sales_14d, --14日销量
	k.in_stock_sales_28d, --28日销量
	k.predict_sales_1d, --T+1日预测销量(正向)
	k.predict_sales_2d, --T+2日预测销量
	k.predict_sales_3d, --T+3日预测销量
	k.predict_sales_4d, --T+4日预测销量
	k.predict_sales_5d, --T+5日预测销量
	k.predict_sales_6d, --T+6日预测销量
	k.predict_sales_7d, --T+7日预测销量
	k.in_stock_predict_sales_14d, --14日预测销量
	k.in_stock_predict_sales_28d, --28日预测销量
	1 AS yn, --删除标识 1为没删,0为删除
	'plumber' AS create_pin, --创建人
	'plumber' AS update_pin, --更新人
	current_timestamp AS create_time, --创建时间
	current_timestamp AS update_time, --更新时间
	current_timestamp AS ts --时间戳
from
	(
		select
			*
		from
			app.app_basic_dashboard_goods_sale_predict_daily_tem
		where
			dt = sysdate( - 1)
	)
	k
join
	(
		select
			*
		from
			app.app_basic_dashboard_goods_sale_predict_daily_tem
		where
			dt = sysdate( - 1)
	)
	m
on
	k.dept_no = m.dept_no
	and k.goods_no = m.goods_no
	and k.warehouse_no = m.warehouse_no      
             
"""

ht.exec_sql(schema_name = 'app', table_name = 'app_basic_dashboard_goods_sale_predict_daily', sql = sql1, merge_flag = True) 

 

posted on 2018-08-20 10:24  箬笠蓑衣  阅读(542)  评论(0编辑  收藏  举报