寫不好 SQL？ 送你一個大招

這兩天一直在看《推薦系統(tǒng)實戰(zhàn)》。書中提到基于用戶行為推薦算法的時候，提到一個場景，其實用 SQL 來解，也非常容易。

已知場景是這樣的，某視頻網(wǎng)站收集了用戶觀影數(shù)據(jù)：

{
    "電影":"你好，李煥英",
    "上市日期":"2021-02",
    "觀影者":{
        {"姓名":"小林", "年齡":22,"購買日期":"2021-02-18","票價":98,"地址":"陸家嘴星美影院"},
         {"姓名":"小李", "年齡":21,"購買日期":"2021-02-16","票價":92,"地址":"萬達(dá)星美影院"},
          {"姓名":"小北", "年齡":24,"購買日期":"2021-02-19","票價":96,"地址":"五角場星美影院"},
           {"姓名":"小民", "年齡":22,"購買日期":"2021-02-18","票價":98,"地址":"陸家嘴星美影院"}
        
    }，
    
    "電影":"速度與激情9",
    "上市日期":"2021-05",
    "觀影者":{
        {"姓名":"小林", "年齡":22,"購買日期":"2021-05-20","票價":98,"地址":"陸家嘴星美影院"},
         {"姓名":"小李", "年齡":21,"購買日期":"2021-05-21","票價":92,"地址":"萬達(dá)星美影院"},
          {"姓名":"小北", "年齡":24,"購買日期":"2021-05-22","票價":96,"地址":"五角場星美影院"} 
        
    }
    
    ...
}

網(wǎng)站需要根據(jù)品味和觀影歷史，向用戶推薦其他影片。

兩個人的品味是否相近，依據(jù)年齡是否相仿，并不能最好地做出判斷。但如果兩人觀影記錄重疊，品味相近的概率就大很多?；诖耍涂梢曰ハ嗤扑]對方還未看過的電影了。

這個時候，用什么樣的編程方式，來計算觀影重疊，就值得商榷了。

有人說用Python, 輕便簡易；有人說用 Java,庫多不愁；還有人說c++, 性能賊快。

作為 SQL 博主，當(dāng)然推薦 SQL, 這種集合類計算，SQL 是把快刃。對于舉棋不定的朋友，你一定是缺少數(shù)據(jù)建模思維，正所謂：心中有模型，則SQL自然成！

為什么我一直推薦金融，財會，產(chǎn)品的朋友，都要學(xué)一學(xué)SQL, 學(xué)一學(xué)集合理論，原因就在這。SQL 理念有助于你理解現(xiàn)實中的思維邏輯，成為5分鐘看透世界本質(zhì)的人。

所有工作中遇到的邏輯分析難題，都可以借助SQL來完成。下面這段話，建議你讀三遍：

SQL 在手，人無我有。數(shù)據(jù)再大，我用SQL!

注意：SQL 在這里，一定要讀 "色扣"。

那么怎么培養(yǎng)自己的數(shù)據(jù)建模思維呢？如下詳細(xì)來說。

依據(jù)上面的觀影日志記錄，經(jīng)過 ETL 裁剪，可得到觀影記錄如下:

ETL(Extract Transform Load) , 負(fù)責(zé)把數(shù)據(jù)轉(zhuǎn)換成SQL可操作的格式。

此時，推薦問題就轉(zhuǎn)換成了：哪兩個人的觀影重疊次數(shù)最多。

最終，問題就化解為簡單的SQL題， Group by .... Order By... 模式:

SELECT  觀看者1， 觀看者2，COUNT(DISTINCT 電影) AS 電影數(shù)量
FROM tblUserFilms
GROUP BY 觀看者1， 觀看者2
ORDER BY 觀看者1，COUNT(DISTINCT 電影) DESC 

由此可推導(dǎo)，與他/她同好的人，可能還喜歡對方的其他愛好。

那么，怎么才能生成如下兩兩組合，求觀影重疊次數(shù)的數(shù)據(jù)模型呢？

很顯然，要把原始數(shù)據(jù)打散，打平，破除原先不符合三范式的結(jié)構(gòu):

這個時候，最小粒度的數(shù)據(jù)模型就出來了，影片 + 觀看者，沒有有重復(fù)記錄，也沒有粘連的記錄。

實現(xiàn)這一步，最常規(guī)的思維就是拆字符串， 可以自定義函數(shù)實現(xiàn)，也可以利用系統(tǒng)自帶的函數(shù)。比如SQL Server中就有 string_split函數(shù):

select
    act.Film,
    usr.*
from
    dbo.UserFilms act
    OUTER APPLY (
        SELECT
            *
        FROM
            String_Split(act.Watcher, ',') tmp
    ) usr
    
    

最后一步，是真正揭開本次算法的關(guān)鍵，也是我平常運(yùn)用最多的一個思維，無中生有。

單列觀影者，怎么才能組合成雙列觀影者呢？

很多教材，都有涉及 Join 這個主題。大多數(shù)一直在強(qiáng)調(diào)相等性 join, 對于不等性和半等性 Join, 重視不多。所以很多初學(xué)者自然不知道，Join 其實可以用 <> 來連接:

;

with base_query as (
    select
        act.Film,
        usr.*
    from
        dbo.UserFilms act
        OUTER APPLY (
            SELECT
                *
            FROM
                String_Split(act.Watcher, ',') tmp
        ) usr
),
base_query_com as (
    select
        act.Film,
        act.value as Watcher,
        act1.value as Watcher2
    from
        base_query act
        inner join base_query act1 on act1.Film = act.Film
        and act1.value <> act.value
)
select
    Watcher,
    Watcher2,
    COUNT(DISTINCT Film) as Films
from
    base_query_com
group by
    Watcher,
    Watcher2
order by
    Watcher,
    Films DESC
    

這里著重注意不等性 Join 的表達(dá):

  inner join base_query act1 on act1.Film = act.Film
        and act1.value <> act.value

最終，順利完成兩兩聚合求最多的運(yùn)算：

這個例子在平時工作中，非常具有典型性。用圖再展示下一步步的思考流程：

在這里，有兩步模型的轉(zhuǎn)換值得記錄：打破范式約束（打散粘連的字符串）和 不等性 Join.

每一步模型的轉(zhuǎn)換，都可以沉淀出來一個套路，累積這些套路，你將會有一個強(qiáng)大的兵器庫，來拆解各類邏輯問題。

工作剛開始的那幾年，我一直熱衷于編碼，求多沒求精，想來也是唏噓。刷題固然重要，但刻意積累，才是最快地精通技藝的方法呢！真希望20歲時有人告訴我這些。