物资项目数据处理
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'SIMPLE_CALC' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE simple_calc';
END IF;
END;
/
--计算平均值、方差、离散系数
create table simple_calc as
select t.matlcode,
avg(t.taxprice) as junz,
STDDEV(t.taxprice) biaozc,
STDDEV(t.taxprice) / avg(t.taxprice) lisxs
from T_WLCG_ZZCL_130W_X t
group by t.matlcode;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'BEYOND_YEAR_OF_16' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE beyond_year_of_16';
END IF;
END;
/
--2016年以后还有数据的表
create table beyond_year_of_16 as (
select t1.*
from T_WLCG_ZZCL_130W_X t1
right join (select t.matlcode
from T_WLCG_ZZCL_130W_X t
group by t.matlcode
having max(to_char(t.deliverydate,'yyyy')) >= 2016) t2 on t1.matlcode =
t2.matlcode
);
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'NOT_BEYOND_YEAR_OF_16' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE not_beyond_year_of_16';
END IF;
END;
/
--2016年以后没有数据的表
create table not_beyond_year_of_16 as (
select t1.*
from T_WLCG_ZZCL_130W_X t1 right join
(select t.matlcode
from T_WLCG_ZZCL_130W_X t
group by t.matlcode
having max(to_char(t.deliverydate,'yyyy')) < 2016) t2
on t1.matlcode = t2.matlcode
);
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'BEYOND_YEAR_OF_16_2RD' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE beyond_year_of_16_2rd';
END IF;
END;
/
--两次筛选之后的数据（物料日单价异常值过滤：
--1、物料单价：(物料均价-3倍标准差，物料均价+3倍标准差)
--3、物料日单价的离散系数<0.5）
create table beyond_year_of_16_2rd as
select t1.*
from (select t1.*
from beyond_year_of_16 t1
right join simple_calc t2 on t1.matlcode = t2.matlcode and
t1.taxprice between
t2.junz - 3 * t2.biaozc and
t2.junz + 3 * t2.biaozc and
t2.lisxs < 0.5) t1
right join (select t.matlcode,
avg(t.taxprice),
STDDEV(t.taxprice) biaozc2,
STDDEV(t.taxprice) / avg(t.taxprice) lisxs2
from (select t1.*
from beyond_year_of_16 t1
right join simple_calc t2 on (t1.matlcode = t2.matlcode and
t1.taxprice between
t2.junz - 3 * t2.biaozc and
t2.junz + 3 * t2.biaozc and
t2.lisxs < 0.5)) t
group by t.matlcode) t2 on (t1.matlcode = t2.matlcode
and t1.taxprice between
t1.taxprice - 3 * t2.biaozc2 and
t1.taxprice + 3 * t2.biaozc2);
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'BEYOND_YEAR_OF_16_2RD_BIG15' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE beyond_year_of_16_2rd_big15';
END IF;
END;
/
--历史采购数量大于15条的种类所有字段建表语句
create table beyond_year_of_16_2rd_big15 as
select t2.*
from beyond_year_of_16_2rd t2
right join (select t1.matlcode
from (select t.matlcode, t.deliverydate
from beyond_year_of_16_2rd t
group by t.matlcode, t.deliverydate) t1
group by t1.matlcode
having count(t1.matlcode) >=15) t3 on t2.matlcode =
t3.matlcode;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'g_model_data' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE g_model_data';
END IF;
END;
/
--包含平均价格
create table g_model_data as
select t1.*,t2.avg_price
from beyond_year_of_16_2rd_big15 t1 left join (select avg(taxprice) avg_price,matlcode from beyond_year_of_16_2rd_big15 group by matlcode) t2 on t1.matlcode=t2.matlcode;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'g_jstt' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE g_jstt';
END IF;
END;
/
-- 技术淘汰物料
create table g_jstt as
select t.big_class_name,
t.mid_class_name,
t.small_class_name,
t.mat_desc,
t.unit_name,
t.bidplancode,
t.compname,
t.matlcode,
t.deliverydate,
avg(t.taxprice) taxprice
from not_beyond_year_of_16 t
group by t.big_class_name,
t.mid_class_name,
t.small_class_name,
t.matlcode,
t.mat_desc,
t.unit_name,
t.bidplancode,
t.compname,
t.matlcode,
t.deliverydate;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'NOT_MODEL_DATA_BASE' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE not_model_data_base';
END IF;
END;
/
--非建模数据
--基础表(日价格)
create table not_model_data_base as
select t.BIG_CLASS_NAME,
t.MID_CLASS_NAME,
t.SMALL_CLASS_NAME,
t.MATLCODE,
t.MAT_DESC,
t.UNIT_NAME,
avg(t.TAXPRICE) TAXPRICE,
t.DELIVERYDATE
from (select *
from beyond_year_of_16 t1
where t1.matlcode not in
(select t2.matlcode from beyond_year_of_16_2rd_big15 t2)) t
group by t.BIG_CLASS_NAME,
t.MID_CLASS_NAME,
t.SMALL_CLASS_NAME,
t.MATLCODE,
t.MAT_DESC,
t.UNIT_NAME,
t.DELIVERYDATE;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'CALC_TJ' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE calc_tj';
END IF;
END;
/
--计算平均值、方差、中位数
create table calc_tj as
select t.matlcode,
avg(t.taxprice) as junz,
STDDEV(t.taxprice) biaozc,
MEDIAN(t.taxprice) mid_dailyprice
from not_model_data_base t
group by t.matlcode;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'NOT_MODEL_NO_YIC' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE not_model_no_yic';
END IF;
END;
/
-- 异常值过滤
create table not_model_no_yic as
select t1.*
from not_model_data_base t1
right join calc_tj t2 on t1.matlcode = t2.matlcode
and t1.taxprice between t2.junz - 3 * t2.biaozc and
t2.junz + 3 * t2.biaozc
and t1.taxprice between t2.mid_dailyprice / 5 and
t2.mid_dailyprice * 5;
--插入最新日期
insert into not_model_no_yic
(BIG_CLASS_NAME,
MID_CLASS_NAME,
SMALL_CLASS_NAME,
MATLCODE,
MAT_DESC,
UNIT_NAME,
TAXPRICE,
DELIVERYDATE)
select BIG_CLASS_NAME,
MID_CLASS_NAME,
SMALL_CLASS_NAME,
MATLCODE,
MAT_DESC,
UNIT_NAME,
TAXPRICE,
DELIVERYDATE
from (select t1.*,
null taxprice,
to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') DELIVERYDATE
from (select distinct t.big_class_name,
t.mid_class_name,
t.small_class_name,
t.matlcode,
t.mat_desc,
t.unit_name
from not_model_no_yic t) t1);
commit;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'BEFORE_3RD_DATA' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE before_3rd_data';
END IF;
END;
/
--3次前的数据
create table before_3rd_data as
select t.matlcode,t.taxprice taxprice,
lag(t.taxprice, 1, null) over(partition by t.matlcode order by t.deliverydate) before_1p,
lag(t.taxprice, 2, null) over(partition by t.matlcode order by t.deliverydate) before_2p,
lag(t.taxprice, 3, null) over(partition by t.matlcode order by t.deliverydate) before_3p,
lag(t.deliverydate, 1, null) over(partition by t.matlcode order by t.deliverydate)-t.deliverydate before_1t,
lag(t.deliverydate, 2, null) over(partition by t.matlcode order by t.deliverydate)-t.deliverydate before_2t,
lag(t.deliverydate, 3, null) over(partition by t.matlcode order by t.deliverydate)-t.deliverydate before_3t,
t.deliverydate,t.big_class_name,t.mid_class_name,t.small_class_name,t.mat_desc,t.unit_name
from not_model_no_yic t
order by t.deliverydate asc;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'NOT_MODEL_WITH_PRIDICT' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE not_model_with_pridict';
END IF;
END;
/
--带有预测价格的数据
create table not_model_with_pridict as
select t1.big_class_name,t1.mid_class_name,t1.small_class_name,t1.matlcode,t1.mat_desc,t1.unit_name,t1.taxprice,
(case
when t1.before_1p is null then
t1.taxprice
when t1.before_2p is null then
t1.before_1p
when t1.before_3p is null then
(1 / t1.before_2t * t1.before_2p + 1 / t1.before_1t * t1.before_1p) /
(1 / t1.before_2t + 1 / t1.before_1t)
else
(1 / t1.before_2t * t1.before_2p + 1 / t1.before_1t * t1.before_1p +
1 / t1.before_3t * t1.before_3p) /
(1 / t1.before_2t + 1 / t1.before_1t + 1 / t1.before_3t)
end) as predict_price,t1.deliverydate
from before_3rd_data t1 order by t1.matlcode;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'g_not_model' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE g_not_model';
END IF;
END;
/
-- 非建模数据最终版
create table g_not_model as
select a.*,
least(a.predict_price + 3 * b.taxprice_std, b.taxprice_max) taxprice_up,
GREATEST(a.predict_price - 3 * b.taxprice_std, b.taxprice_min) taxprice_low,
1 - abs((a.taxprice - a.predict_price) / a.taxprice) percision
from (select * from not_model_with_pridict) a
left join (select t1.matlcode,
min(t1.taxprice) TAXPRICE_MIN,
max(t1.taxprice) TAXPRICE_MAX,
stddev(t1.taxprice) TAXPRICE_STD
from not_model_with_pridict t1
group by t1.matlcode) b on a.matlcode = b.matlcode;
alter table g_not_model add if_predict varchar2(10) default '拟合';
update g_not_model set if_predict='预测' where taxprice is null;
commit;
--创建日期维度表脚本
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1)
INTO NUM
FROM ALL_TABLES
WHERE TABLE_NAME = 'TIME_DIMENSION';
IF NUM = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE TIME_DIMENSION';
END IF;
END;
/
create table TIME_DIMENSION(the_date varchar2(10),
the_year varchar2(4),
the_quarter VARCHAR2(10),
the_month varchar2(2));
DECLARE
dDate date;
v_the_date varchar2(10);
v_the_year varchar2(4);
v_the_quarter varchar2(10);
v_the_month varchar2(2);
begin_date varchar2(10);
end_date varchar2(10);
adddays int;
BEGIN
select to_char(max(DATE_DAY), 'yyyymmdd') into end_date from T_YSJS;
select to_char(min(DATE_DAY), 'yyyymmdd') into begin_date from T_YSJS;
adddays := 1;
dDate := to_date(begin_date, 'yyyymmdd');
WHILE (dDate <= to_date(end_date, 'yyyymmdd')) loop
v_the_date := to_char(dDate, 'yyyymmdd'); --key值
v_the_year := to_char(dDate, 'yyyy'); --年
v_the_quarter := to_char(dDate, 'q'); --季度
v_the_month := to_char(dDate, 'mm'); --月份(字符型)
insert into time_dimension
(the_date, the_year, the_quarter, the_month)
values
(v_the_date, v_the_year, v_the_quarter, v_the_month);
dDate := dDate + adddays;
END loop;
end;
/
commit;
DECLARE
NUM NUMBER;
BEGIN
SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = 'G_OUTERDATA' ;
IF NUM=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE G_OUTERDATA';
END IF;
END;
/
create table G_OUTERDATA as
select t11.*, t12.SNZS
from (select t9.*, t10.CU, t10.AL, t10.PB, t10.ZN, t10.Q
from (select t7.*, t8.PPI
from (select t5.*, t6.PMI
from (select t3.*, t4.gdp_gy
from (select t1.*, t2.salary
from TIME_DIMENSION t1
left join T_SALARY t2 on t1.the_year =
t2.year) t3
left join T_GDP_GY t4 on t3.the_year =
t4.year
and t3.the_quarter =
t4.quarter) t5
left join T_PMI t6 on t5.the_year =
to_char(t6.date_day, 'yyyy')
and t5.the_month =
to_char(t6.date_day, 'mm')) t7
left join T_PPI t8 on t7.the_year =
to_char(t8.date_day, 'yyyy')
and t7.the_month =
to_char(t8.date_day, 'mm')) t9
left join T_YSJS t10 on t9.the_date =
to_char(t10.date_day, 'yyyymmdd')) t11
left join T_SNZS t12 on t11.the_date = to_char(t12.date_day, 'yyyymmdd');
We only live once, and time just goes by.
浙公网安备 33010602011771号