【題目】

某商場為了分析用戶購買渠道。表1是用戶交易記錄表，記錄了用戶id、交易日期、交易類型和交易金額。

表2是用戶類型表，記錄了用戶支付類型（微信、支付寶、信用卡等），分別有type1、type2。

要求：

1.請在 type1的用戶類型中，找出總交易金額最大的用戶。

2.篩選每個用戶的第2筆交易記錄。

3.如下表：如何實現表3的數據格式？

4.兩表做關聯時，其中一張表的關聯鍵有大量的null值會造成什么影響？如何規避？

【解題思路】

1.type1的用戶類型中，找出單用戶的總交易金額最大的一位用戶？

我們先來把這個業務需求翻譯成大白話：

1）表中的字段要有用戶類型、交易金額、用戶id

2）“單用戶的總交易金額”是指每種類型用戶的總交易金額

3）找出用戶類型=type1，總交易金額最大的用戶

1）表中的字段要有用戶類型、交易金額、用戶id

交易金額、用戶id在用戶交易記錄表中，用戶類型在用戶類型表中，涉及到兩個表的字段，所以需要進行多表聯結。

觀察兩個表，得知兩表的相同字段為用戶id ，所以通過用戶id聯結兩張表。

使用哪種聯結呢？拿出《猴子 從零學會SQL》里面的多表聯結圖。

因為后面要分析“每種類型用戶的總交易金額”，所以保留左表（用戶交易記錄表）中的全部用戶數據。

select 用戶交易記錄表.* ,用戶類型表.用戶類型 from 用戶交易記錄表 left join 用戶類型表 on 用戶交易記錄表.用戶id=用戶類型表.用戶id;

查詢結果：

2）每種類型用戶的總交易金額

當有“每個”出現的時候，要想到《猴子 從零學會SQL》中講過的用分組匯總來實現該業務問題。

根據各用戶類型、用戶id分組 （group by），統計每個用戶數總費用（求和函數sum）

select 用戶交易記錄表.*,用戶類型表.用戶類型,sum(用戶交易記錄表.交易金額) as 總金額 from 用戶交易記錄表 left join 用戶類型表 on 用戶交易記錄表.用戶id=用戶類型表.用戶id group by 用戶類型表.用戶類型,用戶交易記錄表.用戶id;

查詢結果：

3）找出用戶類型=type1，總交易金額最大的用戶

用where 篩選用戶類型=type1的用戶，按照總金額降序排序（order by decs)，找出的第一行(limit 1)記錄即為交易金額最大的用戶。

select?用戶類型表.用戶類型,??用戶交易記錄表.用戶id,sum(用戶交易記錄表.交易金額)?as?總金額 from 用戶交易記錄表 left join 用戶類型表 on 用戶交易記錄表.用戶id = 用戶類型表.用戶id where 用戶類型表.用戶類型 = 'type1' group by 用戶類型表.用戶類型, 用戶交易記錄表.用戶id order by 總金額 desc limit 1;

查詢結果：

2.篩選每個用戶的第2筆交易記錄？

1）題目要求查詢“每個用戶”，當每個出現的時候，就要想到分組匯總（group by或者窗口函數的partiotion ?by）。

2）第2筆交易記錄，是指按照交易時間對每個用戶的交易記錄進行排名，然后取出排名第2的數據。

又涉及到分組，又涉及到排名的問題，要想到用《猴子 從零學會SQL》里講過的窗口函數來實現。

所以使用分組（窗口函數partiotion by 用戶id），并按最后交易時間升序排列(order by交易時間 asc)，套入窗口函數的語法，得出下面的sql語句：

select 用戶交易記錄表.*,row_number() over(partition?by?用戶交易記錄表.用戶id?order?by?用戶交易記錄表.交易日期?asc)?as?交易筆數 from?用戶交易記錄表;

查詢結果：

2）用where 篩選出每個用戶的第2條記錄，就是每個用戶的第2筆交易記錄

?

select * from (select?用戶交易記錄表.*, row_number() over(partition by 用戶交易記錄表.用戶id order by 用戶交易記錄表.交易日期 asc ) as 交易筆數 from 用戶交易記錄表) as a where 交易筆數=2；

查詢結果：

3.如下表：如何實現表3的數據格式？

題目要求將同一用戶、不用的交易時間和交易類型多行合并為一行，用group_concat函數可解決。

sql入下：

select 用戶id, group_concat(交易日期), group_concat(交易類型) from 用戶交易記錄表 group?by?用戶id;

4.兩表做關聯時，其中一張表的關聯鍵有大量的null值會造成什么影響？如何規避？

因為在關聯表中的全部數據不一定在另一張表都匹配，這樣沒有匹配到的話就會出現null，避免出現表連接出現null值，只要在表關聯時加一個where條件進行判斷（not null ），具體見下圖

【本題考點】

1.如何將復雜的業務問題，使用多維度拆解分析方法，翻譯成大白話的能力。

2.遇到排名問題，要想到使用窗口函數來實現。

3.多表聯結各個情況如何去實現？把上圖看懂就可以解決99%的多表聯結問題了。

推薦：如何從零學會sql？

總結

以上是生活随笔為你收集整理的如何分析交易记录？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。