SQL迴圈實作 -5.關聯分析的處理工廠3

2019鐵人賽

張小馬~

2018-10-25 08:16:55

4743 瀏覽

分享至

4.ithelp_asso_t4_loopfunction

t4要做的事

將t3那串SQL丟進迴圈裡，很明顯的，t3中的編號2和編號6，是t4語法裡，迴圈的變數。在t3中被我們寫死了，丟進迴圈後，記得要去改。

方程語法架構

建資料，是create table；
建方程/建迴圈，是create function。

----------以下開始ithelp_asso_fun1(r int, s int)的設定----------
DROP FUNCTION IF EXISTS    ithelp_asso_fun1(r int, s int);
CREATE OR REPLACE FUNCTION ithelp_asso_fun1(r int, s int) 
RETURNS SETOF ithelp_asso_t3_setof AS
$BODY$
BEGIN
RETURN query
--------------------以下開始SQL語法的設定--------------------
--------------------以上結束SQL語法的設定--------------------
;
END
$BODY$
LANGUAGE 'plpgsql';
----------以上結束ithelp_asso_fun1(r int, s int)的設定----------

drop function: 砍掉這個function

create function: 馬上再建立剛剛被砍掉的function

return: 這function要做啥事呢？

setof: 原來是要做和某table一樣的欄位啊！

as: 就像是...如下

$BODY$~$BODY$: function的身體

BEGIN~END: 這身體，從頭到腳

return query (SQL);
把SQL塞進去，最後別忘了加上分號【;】。

LANGUAGE: 最後附上我用的SQL語言

ithelp_asso_fun1

清楚瞭解上述function架構後，我現在要建立【ithelp_asso_fun1】這個function，而且要把t3寫好的SQL丟進去了，完整如下：

----------以下開始ithelp_asso_fun1(r int, s int)的設定----------
DROP FUNCTION IF EXISTS    ithelp_asso_fun1(r int, s int);
CREATE OR REPLACE FUNCTION ithelp_asso_fun1(r int, s int) 
RETURNS SETOF ithelp_asso_t3_setof AS
$BODY$
BEGIN
RETURN query
--------------------以下開始SQL語法的設定--------------------
select
C.共選次數,
p1.category 商品1名稱, 
p2.category 商品2名稱, 
p1.category||'_'||p2.category 組合名稱,
C.商品1編號, 
C.商品2編號, 
C.組合編號

from(
select
SUM(1) 共選次數, 
r "商品1編號", 
s "商品2編號", 
r||'_'||s 組合編號
from(
select A.* 
from(
select
name, 
SUM(1) cnt
from ithelp_asso_t2_source_no
where category_no in (r,s) 
group by name 
) A 
where cnt =2 
) B 
group by cnt
) C 
LEFT JOIN ithelp_asso_t1_category p1 
on C.商品1編號 = p1.category_no
LEFT JOIN ithelp_asso_t1_category p2 
on C.商品2編號 = p2.category_no
--------------------以上結束SQL語法的設定--------------------
;
END
$BODY$
LANGUAGE 'plpgsql';
----------以上結束ithelp_asso_fun1(r int, s int)的設定----------

和原本的SQL相比，你會發現我已經把編號2和編號6，都替換成了英文字母【r,s】，這時候再看看我打算設計的function【ithelp_asso_fun1(r int, s int)】，我打算讓這個function有【r,s】這兩個變數，而且，是以integer整數的形式。

這時候你更會發現先把t3寫好的優點，就是你可以不斷在t3那個階段，去看你要怎麼做出第一筆資料，測試確認一切OK後，再整個複製進t4要寫query的位置，最後一個步驟是，將原本被你固定寫死的【2,6】，改成變動的【r,s】。

讓我們來看看執行起來的效果：

哇~ 原本很複雜的內容，現在只要一條簡單的select function，丟入指定變數，就能跑出前面想要的結果了呢！到這邊，【迴圈】的雛型已經浮現了，只要針對這一條function，讓兩個數字都從1跑到17，就完成迴圈了，目標就在眼前，我們今天一口氣把它解決吧！

ithelp_asso_fun2

----------以下開始ithelp_asso_fun2(u int, v int)的設定----------
DROP FUNCTION IF EXISTS ithelp_asso_fun2(u int, v int);
CREATE OR REPLACE FUNCTION ithelp_asso_fun2(u int, v int)
RETURNS SETOF ithelp_asso_t3_setof AS
$BODY$
BEGIN
 FOR r IN 1..u
 LOOP
 FOR s IN 1..v
 LOOP
            RETURN query select * from ithelp_asso_fun1(r,s);
 END LOOP;
 END LOOP;
END
$BODY$
LANGUAGE 'plpgsql';
----------以上結束ithelp_asso_fun2(u int, v int)的設定----------

不要躁進，務必理解架構，再繼續往下。
這串fun2與前面fun1的差別是什麼？

在fun1中，BEGIN~END的中間，我們直接寫上了RETURN query (SQL);；
但fun2中，我們看到了二個組新團體：FOR...IN、LOOP...END LOOP，
其中LOOP...END LOOP中，放進了RETURN query (SQL);。

因此，雖然fun1的語法較長，但那是因為中間塞的SQL query很長，但以結構來說，是fun2更為複雜，只是中間塞的SQL query極短，所以看起來較簡單。

變數說明

 FOR s IN 1..v
 LOOP
            RETURN query select * from ithelp_asso_fun1(r,s);
 END LOOP;

FOR迴圈：FOR【某個變數】IN【多少..多少】，某個變數要從多少跑到多少？
例如上述例子就是，s這個變數要從1跑到v。
不正是我們想要達到的效果嗎？

另外一點很重要的是，每次某組變數跑完，會UNION在某組變數之前後，這是非常重要的資訊！意思是，它的概念如下：

迴圈執行出來，有著UNION的效果。

兩組LOOP

兩組的LOOP...END LOOP

 FOR r IN 1..u
 LOOP
 FOR s IN 1..v
 LOOP
            RETURN query select * from ithelp_asso_fun1(r,s);
 END LOOP;
 END LOOP;

意思是，當r=1的時候，s從1跑到v，跑完了，接著r=2的時候，s從1跑到v...以此類推。換句話說，如果我們讓u=17、v=17的時候，照理講，它總共會跑17*17=289次。

實際執行fun2

結果如上，會發現，「嗯？怎麼只有272筆？」
原來，仔細想了一下，發現SQL裡面我們曾經下了cnt = 2的條件，因此在r和s相同的時候，會沒有資料，而289次裡面正好會有17次，(r,s)會跑到相同的數字(1,1),(2,2)...(17,17)。因此實際只跑出了272筆。

再往下看，會發現(1,6)和(6,1)是同一個組合，於是我們想起來，實際我們只需要136個組合，重複的組合不要看，因此，我們會在後面加上這個條件：

select * from ithelp_asso_fun2(17,17) where "商品1編號" < "商品2編號"

終於，我們把136筆資料，透過迴圈的方式得到如同此份Excel檔案(.xlsx)的資料。

而上面這個段落長長一串，包含建立二個function，都屬於第四步驟的【建立迴圈】。
下一步，我們要來好好用一下這136筆資料。

5.ithelp_asso_t5_goal

DROP TABLE IF EXISTS ithelp_asso_t5_goal; 
CREATE TABLE ithelp_asso_t5_goal as
select a.*,
b.cnt 單選項次數_商品1,
c.cnt 單選項次數_商品2,
b.total 總人數,
ROUND (共選次數::numeric/b.total::numeric    ,4) "Support",
ROUND (共選次數::numeric/b.cnt  ::numeric    ,4) "Confidence_商品1先選",
ROUND (共選次數::numeric/c.cnt  ::numeric    ,4) "Confidence_商品2先選",
ROUND((共選次數::numeric/c.cnt  ::numeric) /
      (b.cnt  ::numeric/b.total::numeric)  ,4) "Lift"
from ithelp_asso_fun2(17,17) a
LEFT JOIN ithelp_asso_t1_category b
on a."商品1編號" = b.category_no
LEFT JOIN ithelp_asso_t1_category c
on a."商品2編號" = c.category_no
where a."商品1編號" < a."商品2編號"
;
select * from ithelp_asso_t5_goal

看到最中間的 from ithelp_asso_fun2(17,17)了嗎？這就是t4的心血結晶；只要再把t1做出來的【被幾個人消費過(cnt)】和【資料中總共有幾個人(total)】給join回來，所有統計值就能順利得出了。

本篇重點在於把迴圈講完，加上day12的資料分析(Data Analysis) -4.關聯(購物籃)把三個統計值的運算方式和概念講得很清楚，所以這邊就不再花篇幅說明統計值怎麼寫及後續如何運用囉。而最終結果(目標產出)，Excel檔案(.xlsx)也已經在兩天前的SQL迴圈實作 -3.關聯分析的處理工廠1裡率先提供過了，因此所有【關聯分析的SQL處理】就到此告一段落。

明天，我們來運用這篇提到的內迴圈，來試著處理【中文的文字雲】。

上一篇：
SQL迴圈實作 -4.關聯分析的處理工廠2
下一篇：
SQL迴圈實作 -6.英文文字雲的處理工廠1