问题描述:
这是一个古老的问题,
supplier 有多个products,
需求,为每个product 找属于同一个supplier 的3个兄弟products(不能是自己).
如果少于3个,有几个就找几个。
my senior的做法:
对每个product, 从头开始找3个product(非自己就可以),
出现问题:因为每个product 每次都要从头开始找,每次找到,还要与自己比较3次,
当product足够多的时候,程序就崩溃了。
解决办法:
利用SQL/PLUS,开cursor, go through 一遍,每个product的兄弟用它前面的三个;最前面的那个三个呢,
第一个的兄弟是最后的三个;第二个的是,最后两个和第一个;第三个的是最后一个和第1,2。
如果是少于三个,同理类推。
code 如下:(只有一个brother的情况)
SET VERIFY OFF
DECLARE
preSuppId NUMBER DEFAULT 0;
pre_ppId1 NUMBER DEFAULT 0;
pre_ppId2 NUMBER DEFAULT 0;
pre_ppId3 NUMBER DEFAULT 0;
first_ppId NUMBER DEFAULT 0;
second_ppId NUMBER DEFAULT 0;
third_ppId NUMBER DEFAULT 0;
cur_ppId NUMBER DEFAULT 0;
cur_suppId NUMBER DEFAULT 0;
num NUMBER DEFAULT 0;
CURSOR cur IS
SELECT SUPPLIER_ID cur_suppId,PRODUCT_ID cur_ppId FROM product_funnel WHERE SUPPLIER_PDT_CNT =2 order by supplier_id ;
BEGIN
for rec in cur LOOP
IF preSuppId<>rec.cur_suppId THEN
--num<>0 means need to process last supplier's first 3 record update
IF num<>0 THEN
UPDATE product_funnel_ranking pfr
SET pfr.related_product_id1 = pre_ppId1
WHERE pfr.product_id = first_ppId;
num := 0;
END IF;
--num = 0, means, start one new supplier
IF num=0 THEN
pre_ppId1 := rec.cur_ppId;
num := 1;
preSuppId := rec.cur_suppId;
first_ppId := rec.cur_ppId;
END IF;
-- the same supplier ,need to check num.if num <3 ,means processing the first 3 record,
-- if num =3, update the current record
ELSE
IF num=1 THEN
UPDATE product_funnel_ranking pfr
SET pfr.related_product_id1 = pre_ppId1
WHERE pfr.product_id = rec.cur_ppId;
pre_ppId1 := rec.cur_ppId;
END IF;
END IF;
END LOOP;
--update the last second rec
UPDATE product_funnel_ranking pfr
SET pfr.related_product_id1 = pre_ppId1
WHERE pfr.product_id = first_ppId;
commit;
END;
/
EXIT;
效果:20W数据,最快可以跑4分钟。
relatePDT1,relatePDT2,relatePDT3;分别建立index (原来就有的, 原因??)
下图给出两个例子:
note:
1. this method is better than old one , can be a smart way to resolve currnet problem.
2.可以考虑function ,but 逻辑简单,考虑到function占用oracle server 空间,没用
这是一个古老的问题,
supplier 有多个products,
需求,为每个product 找属于同一个supplier 的3个兄弟products(不能是自己).
如果少于3个,有几个就找几个。
my senior的做法:
对每个product, 从头开始找3个product(非自己就可以),
出现问题:因为每个product 每次都要从头开始找,每次找到,还要与自己比较3次,
当product足够多的时候,程序就崩溃了。
解决办法:
利用SQL/PLUS,开cursor, go through 一遍,每个product的兄弟用它前面的三个;最前面的那个三个呢,
第一个的兄弟是最后的三个;第二个的是,最后两个和第一个;第三个的是最后一个和第1,2。
如果是少于三个,同理类推。
code 如下:(只有一个brother的情况)
SET VERIFY OFF
DECLARE
preSuppId NUMBER DEFAULT 0;
pre_ppId1 NUMBER DEFAULT 0;
pre_ppId2 NUMBER DEFAULT 0;
pre_ppId3 NUMBER DEFAULT 0;
first_ppId NUMBER DEFAULT 0;
second_ppId NUMBER DEFAULT 0;
third_ppId NUMBER DEFAULT 0;
cur_ppId NUMBER DEFAULT 0;
cur_suppId NUMBER DEFAULT 0;
num NUMBER DEFAULT 0;
CURSOR cur IS
SELECT SUPPLIER_ID cur_suppId,PRODUCT_ID cur_ppId FROM product_funnel WHERE SUPPLIER_PDT_CNT =2 order by supplier_id ;
BEGIN
for rec in cur LOOP
IF preSuppId<>rec.cur_suppId THEN
--num<>0 means need to process last supplier's first 3 record update
IF num<>0 THEN
UPDATE product_funnel_ranking pfr
SET pfr.related_product_id1 = pre_ppId1
WHERE pfr.product_id = first_ppId;
num := 0;
END IF;
--num = 0, means, start one new supplier
IF num=0 THEN
pre_ppId1 := rec.cur_ppId;
num := 1;
preSuppId := rec.cur_suppId;
first_ppId := rec.cur_ppId;
END IF;
-- the same supplier ,need to check num.if num <3 ,means processing the first 3 record,
-- if num =3, update the current record
ELSE
IF num=1 THEN
UPDATE product_funnel_ranking pfr
SET pfr.related_product_id1 = pre_ppId1
WHERE pfr.product_id = rec.cur_ppId;
pre_ppId1 := rec.cur_ppId;
END IF;
END IF;
END LOOP;
--update the last second rec
UPDATE product_funnel_ranking pfr
SET pfr.related_product_id1 = pre_ppId1
WHERE pfr.product_id = first_ppId;
commit;
END;
/
EXIT;
效果:20W数据,最快可以跑4分钟。
relatePDT1,relatePDT2,relatePDT3;分别建立index (原来就有的, 原因??)
下图给出两个例子:
note:
1. this method is better than old one , can be a smart way to resolve currnet problem.
2.可以考虑function ,but 逻辑简单,考虑到function占用oracle server 空间,没用