【面試題】

小紅書月活躍使用者數已經過億，使用者在小紅書上透過文字、圖片、影片筆記分享生活，並建立相關商品連結，吸引相同愛好的使用者進行收藏購買，使用者的消費有什麼行為特徵呢？（小紅書面試題）

現有使用者訂單表、使用者收藏表。

使用者訂單表：記錄使用者id、購買的商品id、使用者下單的時間及商品的種類。

使用者收藏商品表：記錄使用者id、使用者收藏的商品id及收藏時間。

問題：請用一句sql語句得出以下查詢結果，得到所有使用者的商品行為特徵，其中使用者行為分類為4種：已購買、購買未收藏、收藏未購買、收藏且購買。

【解題步驟】

題目要求得出查詢表，需要增加兩個表中都沒有的4個欄位，分別為4種使用者行為特徵：已購買、購買未收藏、收藏未購買、收藏且購買。

1.如何得到使用者行為特徵？

因為要透過使用者id和商品id來判斷使用者在訂單表和收藏表的情況，所以用使用者id、商品id聯結兩表。

兩表聯結後，會出現下面4種使用者行為特徵。

1）如果商品在使用者訂單表中出現，表示使用者購買了該商品，屬於“已購買”。

2）如果商品在使用者訂單表中出現，但是使用者收藏商品表中沒有出現該商品，屬於“購買未收藏”。

3）如果商品在使用者收藏商品表中出現，但是使用者訂單表中沒有出現該商品，屬於“收藏未購買“。

4）如果商品在使用者收藏商品表中出現，同時使用者訂單表中也出現該商品，屬於“收藏且購買”。

上訴使用者行為特徵可以從表的角度又分為2大類：

1）從使用者訂單表角度來看

如果商品在使用者訂單表中出現，表示使用者購買了該商品，屬於“已購買”。

如果商品在使用者訂單表中出現，但是使用者收藏商品表中沒有出現該商品，屬於“購買未收藏”。

上面從A表角度來看B表，就是保留A表裡的全部資料，所以要用到左連線（left join）保留左表裡到資料。我們把使用者訂單表記錄為a表，把使用者收藏商品表記錄為b表。

這種情況，我們使用使用者訂單表（表a）左聯結使用者收藏商品表（表b）可以判斷出，也就是保留左表使用者訂單表（表a）裡的全部資料。

2）從使用者收藏商品表角度來看

如果商品在使用者收藏商品表中出現，但是使用者訂單表中沒有出現該商品，屬於“收藏未購買“。

如果商品在使用者收藏商品表中出現，同時使用者訂單表中也出現該商品，屬於“收藏且購買”。

這種情況，我們使用使用者收藏商品表（表b）左聯結使用者訂單表（表a）可以判斷出，也就是保留左表使用者收藏商品表（表b）裡的全部資料。

2.如何判斷使用者行為特徵？

不同使用者行為特徵，需要用到多條件判斷，這就要用到《猴子 從零學會SQL》裡講過的多條件判斷（case表示式）。

3.從使用者訂單表角度來看

這種情況，我們使用使用者訂單表（表a）左聯結使用者收藏商品表（表b）可以判斷出，也就是保留左表使用者訂單表（表a）裡的全部資料。

1 select a.使用者id,a.商品id2 from 使用者訂單表 as a3 left join 使用者收藏商品表 as b4 on a.使用者id=b.使用者id and a.商品id=b.商品id;

下面使用條件判斷（case）新增4列分別表示4種使用者行為特徵。新增的列裡用1表示有該使用者行為特徵，用0表示沒有該使用者特徵。

1）如果商品在使用者訂單表中出現，表示使用者購買了該商品，屬於“已購買”。因為是左聯結，所以是保留了左表使用者訂單（表a）裡到全部資料。

因此這樣的聯結結果裡的商品都來自左表使用者訂單表，所以都是“已購買”，在sql裡用“1 as '已購買'”表示這一列都有該使用者行為特徵。

2）如果商品在使用者訂單表中出現，但是使用者收藏商品表中沒有出現該商品，屬於“購買未收藏”，對應條件判斷是：

1 (case when b.商品id is null 2            then 1 3            else 0 4            end) as '購買未收藏'

3）如果商品在使用者收藏商品表中出現，但是使用者訂單表中沒有出現該商品，屬於“收藏未購買“。

前面說了，現在是使用者訂單表（表a）左聯結使用者收藏商品表（表b）可以判斷出，也就是保留左表使用者訂單表（表a）裡的全部資料。

因此這樣的聯結結果裡的商品都來自左表使用者訂單表，所以這一列對應的沒有該使用者行為特徵（用0表示）。在sql裡用“0 as'收藏未購買'”表示。

4）如果商品在使用者收藏商品表中出現，同時使用者訂單表中也出現該商品，屬於“收藏且購買”，對應條件判斷是：

1 (case when a.商品id=b.商品id 2            then 1 3            else 0 4           end) as '購買且收藏'

把上面4種使用者行為特徵內容寫成完整的sql：

1 select a.使用者id,a.商品id,2        1 as  '已購買',3 (case when b.商品id is null then 1 else 0 end) as '購買未收藏', 3 0 as'收藏未購買',4 (case when a.商品id=b.商品id then 1 else 0 end) as '購買且收藏'5 from 使用者訂單表 as a6 left join 使用者收藏商品表 as b7 on a.使用者id=b.使用者id and a.商品id=b.商品id;

查詢結果

4.從使用者收藏商品表角度來看

這種情況，我們使用使用者收藏商品表（表b）左聯結使用者訂單表（表a）可以判斷出，也就是保留左表使用者收藏商品表（表b）裡的全部資料。

1 select b.使用者id,b.商品id2 from 使用者收藏商品表 as b3 left join 使用者訂單表 as a4 on b.使用者id=a.使用者id and b.商品id=a.商品id;

下面使用條件判斷（case）新增4列分別表示4種使用者行為特徵。新增的列裡用1表示有該使用者行為特徵，用0表示沒有該使用者特徵。

1）如果商品在使用者訂單表（表a）中出現，表示使用者購買了該商品，屬於“已購買”，對應判斷條件是：

1 (case when a.商品id is not null 2            then 1 3            else 0 4            end) as '已購買'

2）如果商品在使用者訂單表（表a）中出現，但是使用者收藏商品表（表b）中沒有出現該商品，屬於“購買未收藏”。

因為是左聯結，所以是保留了使用者收藏商品表（表b）裡到全部資料。

因此這樣的聯結結果裡的商品都來自左表使用者收藏商品表（表b），所以沒有“購買未收藏”這樣的使用者特徵，在sql裡用“0 as'購買未收藏'”表示這一列都沒有該使用者行為特徵。

3）如果商品在使用者收藏商品表（表b）中出現，但是使用者訂單表（表a）中沒有出現該商品，屬於“收藏未購買“，對應條件判斷是：

1 (case when a.商品id is null 2           then 1 3            else 0 4            end) as '收藏未購買'

4）如果商品在使用者收藏商品表中出現，同時使用者訂單表中也出現該商品，屬於“收藏且購買”，對應條件判斷是：

1 (case when b.商品id=a.商品id 2            then 1 3           else  0 4            end) as '購買且收藏'

把上面4種使用者行為特徵內容寫成完整的sql：

1 select b.使用者id,b.商品id,2 (case when a.商品id is not null then 1 else 0 end) as '已購買',3 0 as'購買未收藏',4 (case when a.商品id is null then 1 else 0 end) as '收藏未購買', 5 (case when b.商品id=a.商品id then 1 else 0 end) as '購買且收藏'7 from 使用者收藏商品表 as b8 left join 使用者訂單表 as a9 on b.使用者id=a.使用者id and b.商品id=a.商品id;

查詢結果：

5.全部商品

因為上面兩個查詢結果分別只保留了左表的全部資料，並不是全部的商品，如果要得出全部使用者的行為特徵的話，需要將兩個表合併起來（表的加法）（注意：用union語句連線兩表字段格式必須一致）。

對應sql如下：

1 (select a.使用者id,a.商品id,2        1 as  '已購買',3 (case when b.商品id is null then 1 else 0 end) as '購買未收藏', 4 0 as'收藏未購買',5 (case when a.商品id=b.商品id then 1 else 0 end) as '購買且收藏'6 from 使用者訂單表 as a7 left join 使用者收藏商品表 as b8 on a.使用者id=b.使用者id and a.商品id=b.商品id)9 union10 (select b.使用者id,b.商品id,11 (case when a.商品id is not null then 1 else 0 end) as '已購買',12 0 as'購買未收藏',13 (case when a.商品id is null then 1 else 0 end) as '收藏未購買', 14 (case when b.商品id=a.商品id then 1 else 0 end) as '購買且收藏'15 from 使用者收藏商品表 as b16 left join 使用者訂單表 as a17 on b.使用者id=a.使用者id and b.商品id=a.商品id);

查詢結果：

【本題考點】

1.用多維度拆解分析方法，將複雜的業務問題拆解為可以解決的簡單問題。

2.遇到多條件判斷的問題，要想到用case語句來實現。

3.遇到只有一個表且只能用一條SQL語句完成，可以聯想到用多表聯結，來實現複雜的業務。

4.使用外連線union注意連線表格必須欄位格式一致方可連線成功。

推薦：如何從零學會SQL？