备份下早期写的存储过程
这个存储过程的作用是对一份数据进行汇总、过滤、输出
CREATE OR REPLACE PROCEDURE up_hb_henanyongle
(
av_return OUT VARCHAR2 -- 成功标识
)
AS
-- ## Define Variable(eternal)
n_mark_flag NUMBER(3) := 0; -- DUBEG: MARK Flag
-- flag NUMBER; -- 标识
BEGIN
--v_sql := 'truncate table err_hb_henanyongle';
--EXECUTE IMMEDIATE v_sql;
--v_sql := 'truncate table mid_hb_henanyongle';
--EXECUTE IMMEDIATE v_sql;
-- 1. 根据会员卡号统计品类购买情况
n_mark_flag := 20;
for obj in (
select
MEMBER_CARD_NUMBER ,
sum(CASE when trim(PURCHASE_DEPT)='彩电' then commodity_count end) as caidian,
sum(CASE when trim(PURCHASE_DEPT)='冰箱' then commodity_count end) as bingxiang,
sum(CASE when trim(PURCHASE_DEPT)='冰柜' then commodity_count end) as binggui,
sum(CASE when trim(PURCHASE_DEPT)='洗衣机' then commodity_count end) as xiyiji,
sum(CASE when trim(PURCHASE_DEPT)='空调' then commodity_count end) as kongtiao,
sum(CASE when trim(PURCHASE_DEPT)='手机' then commodity_count end) as shouji,
sum(CASE when trim(PURCHASE_DEPT)='电脑' then commodity_count end) as diannao,
sum(CASE when trim(PURCHASE_DEPT)='照相机' then commodity_count end) as zhaoxiangji,
sum(CASE when trim(PURCHASE_DEPT)='摄像机' then commodity_count end) as shexiangji,
sum(CASE when trim(PURCHASE_DEPT)='白色小件' then commodity_count end) as baisexiaojian,
sum(CASE when trim(PURCHASE_DEPT)='黑色小件' then commodity_count end) as heisexiaojian,
sum(CASE when trim(PURCHASE_DEPT)='微波炉' then commodity_count end) as weibolu,
sum(CASE when trim(PURCHASE_DEPT)='热水器' then commodity_count end) as reshuiqi,
sum(CASE when trim(PURCHASE_DEPT)='消毒柜' then commodity_count end) as xiaodugui,
sum(CASE when trim(PURCHASE_DEPT)='抽油烟机' then commodity_count end) as chouyouyanji,
sum(CASE when trim(PURCHASE_DEPT)='灶具' then commodity_count end) as zaoju,
sum(CASE when trim(PURCHASE_DEPT)='洗碗机' then commodity_count end) as xiwanji,
sum(CASE when trim(PURCHASE_DEPT)='音响、碟机'
or trim(PURCHASE_DEPT)='碟机'
or trim(PURCHASE_DEPT)='音响'
then commodity_count end) as yinxiangdieji,
sum(CASE when trim(PURCHASE_DEPT)='OA'
OR trim(PURCHASE_DEPT)='点钞机'
OR trim(PURCHASE_DEPT)='保管箱'
OR trim(PURCHASE_DEPT)='保险柜'
OR trim(PURCHASE_DEPT)='打印机'
OR trim(PURCHASE_DEPT)='复印机'
OR trim(PURCHASE_DEPT)='传真机'
OR trim(PURCHASE_DEPT)='扫描仪'
OR trim(PURCHASE_DEPT)='碎纸机'
OR trim(PURCHASE_DEPT)='一体机'
OR trim(PURCHASE_DEPT)='碎纸机'
OR trim(PURCHASE_DEPT)='墨盒'
OR trim(PURCHASE_DEPT)='硒鼓'
OR trim(PURCHASE_DEPT)='电话机'
OR trim(PURCHASE_DEPT)='有线电话'
OR trim(PURCHASE_DEPT)='办公家具'
OR trim(PURCHASE_DEPT)='计算器'
OR trim(PURCHASE_DEPT)='其他耗材'
then commodity_count end) as OA,
sum(CASE when trim(PURCHASE_DEPT)='会员商品'
or trim(PURCHASE_DEPT)='会员礼品'
or trim(PURCHASE_DEPT)='会员定制品'
then commodity_count end) as huiyuanshangpin,
sum(CASE when trim(PURCHASE_DEPT)='医疗保健器材' then commodity_count end) as yiliaobaojianqicai,
sum(CASE when trim(PURCHASE_DEPT)='厨卫小件' then commodity_count end) as chuweixiaojian,
sum(CASE when trim(PURCHASE_DEPT)='数码黑小' then commodity_count end) as shumaheixiao,
sum(CASE when trim(PURCHASE_DEPT)='传统配件'
OR trim(PURCHASE_DEPT)='彩电配件'
OR trim(PURCHASE_DEPT)='空调配件'
OR trim(PURCHASE_DEPT)='音响配件'
OR trim(PURCHASE_DEPT)='冰洗配件'
then commodity_count end) as chuangtongpeijian,
sum(CASE when trim(PURCHASE_DEPT)='摄照配件' then commodity_count end) as shexiangpeijian,
sum(CASE when trim(PURCHASE_DEPT)='电脑配件' then commodity_count end) as diannaopeijian,
sum(CASE when trim(PURCHASE_DEPT)='手机配件' then commodity_count end) as shoujipeijian,
sum(CASE when trim(PURCHASE_DEPT)='延保' then commodity_count end) as yanbao,
sum(CASE when trim(PURCHASE_DEPT)='运营商' then commodity_count end) as yunyingshang
from bi.mid_hb_qingdao where member_card_number is not null group by MEMBER_CARD_NUMBER
) loop
insert into ODS_HB_QINGDAO(
MEMBER_CARD_NUMBER ,
TV ,
FRIDGE ,
FREEZER ,
WASHING_MACHINE ,
AIR_CONDITIONING ,
MOBILE ,
PC ,
CAMERA ,
VIDEO_CAMERA ,
WHITE_SMALL ,
BLACK_SMALL ,
MICROWAVE_OVENS ,
WATER_HEATER ,
DISINFECTION_CABINET ,
HOOD ,
STOVE ,
DISHWASHER ,
AUDIO_CHANGER ,
OA ,
MEMBER_COMMODITY ,
HEALTH_CARE_EQUIPMENT ,
SMALL_KITCHEN ,
DIGITAL_BLACK_SMALL , ,
CHUANTONG_ACCESSORIES ,
CAMERA_PHOTO_ACCESSORIES ,
COMPUTER_ACCESSORIES ,
MOBILE_ACCESSORIES ,
WARRANTY ,
OPERATORS
) values(
obj.MEMBER_CARD_NUMBER ,
obj.caidian ,
obj.bingxiang ,
obj.binggui ,
obj.xiyiji ,
obj.kongtiao ,
obj.shouji ,
obj.diannao ,
obj.zhaoxiangji ,
obj.shexiangji ,
obj.baisexiaojian ,
obj.heisexiaojian ,
obj.weibolu ,
obj.reshuiqi ,
obj.xiaodugui ,
obj.chouyouyanji ,
obj.zaoju ,
obj.xiwanji ,
obj.yinxiangdieji ,
obj.OA ,
obj.huiyuanshangpin ,
obj.yiliaobaojianqicai ,
obj.chuweixiaojian ,
obj.shumaheixiao ,
obj.chuangtongpeijian ,
obj.shexiangpeijian ,
obj.diannaopeijian ,
obj.shoujipeijian ,
obj.yanbao ,
obj.yunyingshang
);
END LOOP;
COMMIT;
-- 2. 单品类购买情况 标签
n_mark_flag := 21;
-- 2.1 彩电，冰箱，冰柜，洗衣机，电脑，照相机>5 为异常1
UPDATE bi.ods_hb_qingdao SET
SINGLE_CATEGORY_NUMBER = '异常①'
WHERE TV>5
OR FRIDGE>5
OR FREEZER>5
OR WASHING_MACHINE >5
OR PC>5
OR CAMERA>5;
--2.2 空调>10次，为异常2
UPDATE bi.ods_hb_qingdao SET
SINGLE_CATEGORY_NUMBER = '异常②'
WHERE AIR_CONDITIONING >10 AND SINGLE_CATEGORY_NUMBER is null;
--2.2 两者都符合，标记异常1,2
UPDATE ods_hb_qingdao SET
SINGLE_CATEGORY_NUMBER = '异常①②'
WHERE AIR_CONDITIONING >10 AND SINGLE_CATEGORY_NUMBER ='异常①';
COMMIT;
-- 3. 购买次数大约20次的，标记违规 标签更新
n_mark_flag := 22;
UPDATE bi.ods_hb_qingdao SET CATEGORY_TOTAL_NUMBER = '违规'
WHERE
NVL(TV,0)+
NVL(FRIDGE,0)+
NVL(FREEZER,0)+
NVL(WASHING_MACHINE,0)+
NVL(AIR_CONDITIONING,0)+
NVL(MOBILE,0)+
NVL(PC,0)+
NVL(CAMERA,0)+
NVL(VIDEO_CAMERA,0)+
NVL(WHITE_SMALL,0)+
NVL(BLACK_SMALL,0)+
NVL(MICROWAVE_OVENS,0)+
NVL(WATER_HEATER,0)+
NVL(DISINFECTION_CABINET,0)+
NVL(HOOD,0)+
NVL(STOVE,0)+
NVL(DISHWASHER,0)+
NVL(AUDIO_CHANGER,0)+
NVL(OA,0)+
NVL(MEMBER_COMMODITY,0)+
NVL(HEALTH_CARE_EQUIPMENT,0)+
NVL(SMALL_KITCHEN,0)+
NVL(DIGITAL_BLACK_SMALL,0)+
NVL(CHUANTONG_ACCESSORIES,0)+
NVL(CAMERA_PHOTO_ACCESSORIES,0)+
NVL(COMPUTER_ACCESSORIES,0)+
NVL(MOBILE_ACCESSORIES,0)+
NVL(WARRANTY,0)+
NVL(OPERATORS,0) > 20;
COMMIT;
-- 4. 更新客户购买频度标签 (频度=购买的总数量/最后消费日期-登记日期(月))
n_mark_flag := 23;
UPDATE bi.ods_hb_qingdao c SET
FREQUENCY_CUSTOMER_PURCHASES=ROUND(c.CONSUME_ALL_DEGREE / MONTHS_BETWEEN(c.END_CONSUME_DATE,c.ENTER_DATE),2)
WHERE END_CONSUME_DATE IS NOT NULL AND ENTER_DATE IS NOT NULL
AND MONTHS_BETWEEN(END_CONSUME_DATE,ENTER_DATE) <> 0;
COMMIT;
-- 5. 根据会员卡号，更新会员基本信息
n_mark_flag := 24;
for obj in (
select
MEMBER_CARD_NUMBER ,
OLD_MEMBER_CARD_NUMBER ,
MEMBER_TYPE ,
NAME ,
CONSUME_ALL_MONEY ,
LEAVE_ALL_INTEGRAL ,
CONSUME_ALL_INTEGRAL ,
CONSUME_ALL_DEGREE ,
SEX ,
BRITHDAY ,
CREDENTIALS_ID ,
IMPORTANT_PHONE ,
HOME_PHONE ,
OTHER_PHONE ,
MEMBER_AREA ,
ADDRESS ,
POSTALCODE ,
REMARK ,
ENTER_DATE ,
BUILD_CARD_SHOP ,
END_CONSUME_DATE ,
ATTESTATION_DATE ,
V_OLD_CLASS
from(select
MEMBER_CARD_NUMBER ,
OLD_MEMBER_CARD_NUMBER ,
MEMBER_TYPE ,
NAME ,
CONSUME_ALL_MONEY ,
LEAVE_ALL_INTEGRAL ,
CONSUME_ALL_INTEGRAL ,
CONSUME_ALL_DEGREE ,
SEX ,
BRITHDAY ,
CREDENTIALS_ID ,
IMPORTANT_PHONE ,
HOME_PHONE ,
OTHER_PHONE ,
MEMBER_AREA ,
ADDRESS ,
POSTALCODE ,
REMARK ,
ENTER_DATE ,
BUILD_CARD_SHOP ,
END_CONSUME_DATE ,
ATTESTATION_DATE ,
V_OLD_CLASS,
row_number() over (PARTITION BY member_card_number ORDER BY end_consume_date) id
FROM bi.mid_hb_qingdao)
WHERE id<=1;
) LOOP
UPDATE bi.ods_hb_qingdao m
SET
m.OLD_MEMBER_CARD_NUMBER = obj.OLD_MEMBER_CARD_NUMBER ,
m.MEMBER_TYPE = obj.MEMBER_TYPE ,
m.NAME = obj.NAME ,
m.CONSUME_ALL_MONEY = obj.CONSUME_ALL_MONEY ,
m.LEAVE_ALL_INTEGRAL = obj.LEAVE_ALL_INTEGRAL ,
m.CONSUME_ALL_INTEGRAL = obj.CONSUME_ALL_INTEGRAL ,
m.CONSUME_ALL_DEGREE = obj.CONSUME_ALL_DEGREE ,
m.SEX = obj.SEX ,
m.BRITHDAY = obj.BRITHDAY ,
m.CREDENTIALS_ID = obj.CREDENTIALS_ID ,
m.IMPORTANT_PHONE = obj.IMPORTANT_PHONE ,
m.HOME_PHONE = obj.HOME_PHONE ,
m.OTHER_PHONE = obj.OTHER_PHONE ,
m.MEMBER_AREA = obj.MEMBER_AREA ,
m.ADDRESS = obj.ADDRESS ,
m.POSTALCODE = obj.POSTALCODE ,
m.REMARK = obj.REMARK ,
m.ENTER_DATE = to_date(substr(obj.ENTER_DATE,1,10),'YYYY-mm-dd') ,
m.BUILD_CARD_SHOP = obj.BUILD_CARD_SHOP ,
m.END_CONSUME_DATE = to_date(substr(obj.END_CONSUME_DATE ,1,10),'YYYY-mm-dd') ,
m.ATTESTATION_DATE = obj.ATTESTATION_DATE ,
m.age = obj.V_OLD_CLASS
where m.MEMBER_CARD_NUMBER = obj.MEMBER_CARD_NUMBER;
END LOOP;
COMMIT;
-- 6. 服务请求 标签
n_mark_flag := 25;
for obj in (
SELECT
phone,
zixun_numbers
FROM bi.mid_zixun;
) LOOP
-- 更新
UPDATE bi.ods_hb_qingdao m
SET
m.CONSULTATION_REQUEST = a.zixun_numbers
WHERE m.IMPORTANT_PHONE = a.phone
OR m.HOME_PHONE = a.phone
OR m.OTHER_PHONE = a.phone;
END LOOP;
COMMIT;
-- 7. 投诉 标签
n_mark_flag := 26;
for obj in (
SELECT
phone,
tousu_numbers
FROM mid_tousu;
) LOOP
--更新
UPDATE ods_hb_qingdao m
SET
m.COMPLAINTS = a.tousu_numbers
WHRER m.IMPORTANT_PHONE = a.phone
OR m.HOME_PHONE = a.phone
OR m.OTHER_PHONE = a.phone;
END LOOP;
COMMIT;
-- 8 退货标签
n_mark_flag := 26;
for obj in (
SELECT
phone,
TUIHUANHUO_NUMBER,TYPE
FROM tuihuanhuo_temp WHERE trim(TYPE) = '退货';
) LOOP
--更新
UPDATE ods_hb_qingdao m
SET
m.RETURNS = a.tuihuanhuo_number
where m.IMPORTANT_PHONE = a.phone
OR m.home_phone = a.phone
OR m.other_phone = a.phone;
END LOOP;
COMMIT;
-- 9. 换货标签
n_mark_flag := 26;
for obj in (
SELECT
phone,
TUIHUANHUO_NUMBER,TYPE
FROM tuihuanhuo_temp WHERE trim(TYPE) = '换货';
) LOOP
--更新
UPDATE ods_hb_qingdao m
SET
m.REPLACEMENT = a.tuihuanhuo_number
WHERE m.IMPORTANT_PHONE = a.phone
OR m.home_phone = a.phone
OR m.other_phone = a.phone;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line( n_mark_flag );
EXCEPTION
-- Capture Oracle SQL Abort.(固定)
WHEN OTHERS THEN
ROLLBACK;
av_return := '-1';
COMMIT;
RETURN;
END;
工作方向: 大数据、数据仓库、
hadoop、hive、Hbase、
python、ad-hoc、scala、数据工具研发
邮 箱 ：zhangkai081@gmail.com
邮 箱 ：zhangkai081@gmail.com
