【面試題】
小紅書月活躍用戶數已經過億，用戶在小紅書上通過文字、圖片、視頻筆記分享生活，并創建相關商品鏈接，吸引相同愛好的用戶進行收藏購買，用戶的消費有什么行為特征呢？（小紅書面試題）
現有用戶訂單表、用戶收藏表，
用戶訂單表：記錄用戶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）里的全部資料，
select a.用戶id,a.商品id
from 用戶訂單表 as a
left join 用戶收藏商品表 as b
on a.用戶id=b.用戶id and a.商品id=b.商品id;
下面使用條件判斷（case）新增4列分別表示4種用戶行為特征，新增的列里用1表示有該用戶行為特征，用0表示沒有該用戶特征，
1）如果商品在用戶訂單表中出現，表示用戶購買了該商品，屬于“已購買”，因為是左聯結，所以是保留了左表用戶訂單（表a）里到全部資料，
因此這樣的聯結結果里的商品都來自左表用戶訂單表，所以都是“已購買”，在sql里用“1 as '已購買'”表示這一列都有該用戶行為特征，
2）如果商品在用戶訂單表中出現，但是用戶收藏商品表中沒有出現該商品，屬于“購買未收藏”，對應條件判斷是：
(case when b.商品id is null
then 1
else 0
end) as '購買未收藏'
3）如果商品在用戶收藏商品表中出現，但是用戶訂單表中沒有出現該商品，屬于“收藏未購買“，
前面說了，現在是用戶訂單表（表a）左聯結用戶收藏商品表（表b）可以判斷出，也就是保留左表用戶訂單表（表a）里的全部資料，
因此這樣的聯結結果里的商品都來自左表用戶訂單表，所以這一列對應的沒有該用戶行為特征（用0表示），在sql里用“0 as'收藏未購買'”表示，
4）如果商品在用戶收藏商品表中出現，同時用戶訂單表中也出現該商品，屬于“收藏且購買”，對應條件判斷是：
(case when a.商品id=b.商品id
then 1
else 0
end) as '購買且收藏'
把上面4種用戶行為特征內容寫成完整的sql：
select a.用戶id,a.商品id,
1 as '已購買',
(case when b.商品id is null then 1 else 0 end) as '購買未收藏',
0 as'收藏未購買',
(case when a.商品id=b.商品id then 1 else 0 end) as '購買且收藏'
from 用戶訂單表 as a
left join 用戶收藏商品表 as b
on a.用戶id=b.用戶id and a.商品id=b.商品id;
查詢結果
4.從用戶收藏商品表角度來看
這種情況，我們使用用戶收藏商品表（表b）左聯結用戶訂單表（表a）可以判斷出，也就是保留左表用戶收藏商品表（表b）里的全部資料，
select b.用戶id,b.商品id
from 用戶收藏商品表 as b
left join 用戶訂單表 as a
on b.用戶id=a.用戶id and b.商品id=a.商品id;
下面使用條件判斷（case）新增4列分別表示4種用戶行為特征，新增的列里用1表示有該用戶行為特征，用0表示沒有該用戶特征，
1）如果商品在用戶訂單表（表a）中出現，表示用戶購買了該商品，屬于“已購買”，對應判斷條件是：
(case when a.商品id is not null
then 1
else 0
end) as '已購買'
2）如果商品在用戶訂單表（表a）中出現，但是用戶收藏商品表（表b）中沒有出現該商品，屬于“購買未收藏”，
因為是左聯結，所以是保留了用戶收藏商品表（表b）里到全部資料，
因此這樣的聯結結果里的商品都來自左表用戶收藏商品表（表b），所以沒有“購買未收藏”這樣的用戶特征，在sql里用“0 as'購買未收藏'”表示這一列都沒有該用戶行為特征，
3）如果商品在用戶收藏商品表（表b）中出現，但是用戶訂單表（表a）中沒有出現該商品，屬于“收藏未購買“，對應條件判斷是：
(case when a.商品id is null
then 1
else 0
end) as '收藏未購買'
4）如果商品在用戶收藏商品表中出現，同時用戶訂單表中也出現該商品，屬于“收藏且購買”，對應條件判斷是：
(case when b.商品id=a.商品id
then 1
else 0
end) as '購買且收藏'
把上面4種用戶行為特征內容寫成完整的sql：
select b.用戶id,b.商品id,
(case when a.商品id is not null then 1 else 0 end) as '已購買',
0 as'購買未收藏',
(case when a.商品id is null then 1 else 0 end) as '收藏未購買',
(case when b.商品id=a.商品id then 1 else 0 end) as '購買且收藏'
from 用戶收藏商品表 as b
left join 用戶訂單表 as a
on b.用戶id=a.用戶id and b.商品id=a.商品id;
查詢結果：
5.全部商品
因為上面兩個查詢結果分別只保留了左表的全部資料，并不是全部的商品，如果要得出全部用戶的行為特征的話，需要將兩個表合并起來（表的加法）（注意：用union陳述句連接兩表欄位格式必須一致），
對應sql如下：
(select a.用戶id,a.商品id,
1 as '已購買',
(case when b.商品id is null then 1 else 0 end) as '購買未收藏',
0 as'收藏未購買',
(case when a.商品id=b.商品id then 1 else 0 end) as '購買且收藏'
from 用戶訂單表 as a
left join 用戶收藏商品表 as b
on a.用戶id=b.用戶id and a.商品id=b.商品id)
union
(select b.用戶id,b.商品id,
(case when a.商品id is not null then 1 else 0 end) as '已購買',
0 as'購買未收藏',
(case when a.商品id is null then 1 else 0 end) as '收藏未購買',
(case when b.商品id=a.商品id then 1 else 0 end) as '購買且收藏'
from 用戶收藏商品表 as b
left join 用戶訂單表 as a
on b.用戶id=a.用戶id and b.商品id=a.商品id);
查詢結果：
【本題考點】
1.用多維度拆解分析方法，將復雜的業務問題拆解為可以解決的簡單問題，
2.遇到多條件判斷的問題，要想到用case陳述句來實作，
3.遇到只有一個表且只能用一條SQL陳述句完成，可以聯想到用多表聯結，來實作復雜的業務，
4.使用外連接union注意連接表格必須欄位格式一致方可連接成功，
推薦：如何從零學會SQL？
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/251752.html
標籤：java