数据开发（三）

一、Hive概述

        Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据表，并提供类似于SQL(HiveSQL)的操作功能。在Hive中，本质上是将SQL转换成为MapReduce程序。

二、

1、建表语句

CREATE TABLE `app.app_test`(
  `data_dt` string COMMENT '时间', 
  `start_city_id` string COMMENT '城市ID', 
  `start_city_name` string COMMENT '城市名称', 
  `predict_list` string COMMENT '序列', 
  `order_num` string COMMENT '单量')
COMMENT '分拣中心预测表--预处理'

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'
LOCATION
  'hdfs://ns14/user/mart_coo/app.db/app_test'
TBLPROPERTIES (
  'transient_lastDdlTime'='1527746321')

（1）STORED AS TEXTFILE：默认的是文本格式，当然也可以通过设置hive.default.fileformat修改默认设置；使用DELIMITED语句解析分割文件，通过ESCAPED BY语句设置分隔符的转义符号，当数据中含有分隔符时，就必须转义；通过NULL DEFINED AS语句可以设置一个自定义的空处理格式，默认情况下NULL='N'

②STORED AS SEQUENCEFILE：压缩序列文件格式

③STORED AS ORC：ORC文件格式

④STORED AS PARQUET

⑤STORED AS AVRO

⑥STORED AS RCFILE：Record Columnar File

⑦STORED BY：non-native table format

⑧INPUTFORMAT and OUTPUTFORMAT

（2）PARTITIONED BY

在创建表时，使用PARTITIONED BY语句，则所创建表就是分区表；分区表可以有一个或多个分区列，给唯一的分区列值创建一个目录。表或分区可以继续通过CLUSTERED BY细分桶，使用SORT BY语句使桶内的数据按升序或降序排序，在某些情况下，可以提升某种查询的性能。

注意点：①分区列式虚拟的，不属于数据本身 ②分区列不能和表结构字段重名，如果需要直接用表结构字段中的名字作为分区列，那必须将表结构中字段重新命名，否则会报错

（3）外部表

创建外部表时，通过指定location语句的路径来覆盖配置文件中指定的文件夹，外部表的特点：当外部表被删除时，其对应的数据并非也从系统中删除。

 2、查询表

三、注意编码格式

1、报错

 

出现这种情况时是格式问题。

重新建个文本，复制粘贴下试试。

四、

1、建表

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'

2、

2.1只删除表中数据，不删除表

truncate table app.app_basic_dashboard_goods_sale_predict_daily_tem

2.2删除分区

ALTER TABLE app_basic_dashboard_goods_sale_predict_daily_tem
DROP partition(dt = '2018-08-16')

 

2.3 按条件删除

2.3删除表及其数据

drop table app.app_basic_dashboard_goods_sale_predict_daily_tem

 显示建表语句

show create app.app_XXXXXXX

 

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.seller_no, --供应商编号
	--c.seller_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 '0.8'
		else d.satisfy_alpha
	end as satisfy_alpha, -- 出仓服务水平c (basis)
	case
		when d.safe_stock_days is null
		then '5'
		else d.safe_stock_days
	end as safe_stock_days, -- 安全库存天数(basis)
	case
		when d.alt is null
		then '10'
		else d.alt
	end as alt, -- 出仓alt(小时)(basis)
    case
		when j.totaltime is null
		then '36'
		else j.totaltime
	end as vlt, --运输时间
	f.in_stock_safety_num as in_stock_safety_num, --安全库存（计划调拨表取数）
	case
		when d.target_stock_days is null
		then '10'
		else d.target_stock_days
	end as target_stock_days, -- 目标库存天数(basis)
	case
		when d.bp is null
		then '20'
		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
	fdm.fdm_eclp_stock1_saleable_warehouse_stock_chain c --可销售库存表
join
	(
		select
			a.dept_no,
			a.goods_id,
			a.warehouse_no,
			max(a.start_date) as start_date
		from
			fdm.fdm_eclp_stock1_saleable_warehouse_stock_chain a
		where
			a.dept_no in
			(
				select dept_no from app.app_basic_dashboard_goods_seller --商户名称表
			)
			and a.dp = 'ACTIVE'
			and a.yn = 1
		group by
			a.dept_no,
			a.warehouse_no,
			a.goods_id
	)
	b
on
	c.dept_no = b.dept_no
	and c.warehouse_no = b.warehouse_no
	and c.goods_id = b.goods_id
	and c.start_date = b.start_date
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
on
	h.loc_city_name = j.startcityname
	and i.loc_city_name = j.endcityname
left join app.app_basic_goods_stock_num_daily_da  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)         
             
"""

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

 4、

#!/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, --安全库存
	k.target_stock_days, -- 目标库存天数(basis)
	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)
		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)