|
- DROP TABLE IF EXISTS inventory_wm.tmp_vsc_sales_forecast_warehouse_ratio_d
- ------------------------------------------------------------------
- CREATE TABLE inventory_wm.tmp_vsc_sales_forecast_warehouse_ratio_d AS
- SELECT
- seller_no,
- dept_no,
- wh_no,
- sku_id,
- system_goods_id,
- system_goods_type,
- dt,
- split_type,
- sku_no_virtual_flag,
- create_time,
- ratio
- FROM
- inventory_wm.app_vsc_sales_forecast_warehouse_ratio_d
- WHERE
- dt = FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd')
- -------------------------------------------------------------
- DELETE FROM perfect_warehouse_ratio where dt=curdate()
- -------------------------------------------------------------
- {
- "tables":["inventory_wm.tmp_vsc_sales_forecast_warehouse_ratio_d"],
- "sql":"select * from inventory_wm.tmp_vsc_sales_forecast_warehouse_ratio_d",
- "databases":{
- "inventory_wm":{
- "target":"vsc_forecast_wm",
- "tables":{
- "tmp_vsc_sales_forecast_warehouse_ratio_d":{
- "target":"perfect_warehouse_ratio",
- "sql":"select * from inventory_wm.tmp_vsc_sales_forecast_warehouse_ratio_d"
- }
- }
- }
- }
- }
- --------------------------------------------------------------
- CREATE TABLE perfect.tmp_perfect_warehouse_ratio AS
- SELECT
- wh_level.seller_no,
- wh_level.dept_no,
- wh_level.sku_id,
- wh_level.wh_no,
- round(nvl(wh_level.sku_qtty / nation_level.sku_qtty, 0), 4) AS ratio,
- date_format(from_utc_timestamp(current_timestamp,'PRC'),'yyyy-MM-dd') AS dt
- FROM
- (
- SELECT
- seller_no,
- dept_no,
- wh_no,
- sku_id,
- SUM(sku_qtty) AS sku_qtty,
- SUM(sku_qtty_pprsed) AS sku_qtty_pprsed,
- COUNT(1) cnt
- FROM
- perfect.app_vsc_sales_forecast_preprocess_res_to_doris_d
- WHERE
- dt = date_format(from_utc_timestamp(current_timestamp,'PRC'),'yyyy-MM-dd')
- AND order_date >= DATE_SUB(date_format(from_utc_timestamp(current_timestamp,'PRC'),'yyyy-MM-dd'), 180) -- 使用最近180天的数据计算分仓比
- GROUP BY
- seller_no,
- dept_no,
- wh_no,
- sku_id
- )
- wh_level
- JOIN
- (
- SELECT
- seller_no,
- dept_no,
- sku_id,
- SUM(sku_qtty) AS sku_qtty,
- SUM(sku_qtty_pprsed) AS sku_qtty_pprsed,
- COUNT(1) cnt
- FROM
- perfect.app_vsc_sales_forecast_preprocess_res_to_doris_d
- WHERE
- dt = date_format(from_utc_timestamp(current_timestamp,'PRC'),'yyyy-MM-dd')
- AND order_date >= DATE_SUB(date_format(from_utc_timestamp(current_timestamp,'PRC'),'yyyy-MM-dd'), 180) -- 使用最近180天的数据计算分仓比
- GROUP BY
- seller_no,
- dept_no,
- sku_id
- )
- nation_level
- ON
- wh_level.dept_no = nation_level.dept_no
- AND wh_level.seller_no = nation_level.seller_no
- AND wh_level.sku_id = nation_level.sku_id
|