文章目錄

• • 1. 題目
  • 2. 解題

1. 題目

表: Visits

+---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | visit_date | date | +---------------+---------+ (user_id, visit_date) 是該表的主鍵 該表的每行表示 user_id 在 visit_date 訪問了銀行

表: Transactions

+------------------+---------+ | Column Name | Type | +------------------+---------+ | user_id | int | | transaction_date | date | | amount | int | +------------------+---------+ 該表沒有主鍵，所以可能有重復行 該表的每一行表示 user_id 在 transaction_date 完成了一筆 amount 數額的交易 可以保證用戶 (user) 在 transaction_date 訪問了銀行 (也就是說 Visits 表包含 (user_id, transaction_date) 行)

銀行想要得到銀行客戶在一次訪問時的交易次數和相應的在一次訪問時該交易次數的客戶數量的圖表

寫一條 SQL 查詢多少客戶訪問了銀行但沒有進行任何交易，多少客戶訪問了銀行進行了一次交易等等

結果包含兩列：

• transactions_count： 客戶在一次訪問中的交易次數
• visits_count： 在 transactions_count 交易次數下相應的一次訪問時的客戶數量
  transactions_count 的值從 0 到所有用戶一次訪問中的 max(transactions_count)

按 transactions_count 排序

下面是查詢結果格式的例子：

Visits 表: +---------+------------+ | user_id | visit_date | +---------+------------+ | 1 | 2020-01-01 | | 2 | 2020-01-02 | | 12 | 2020-01-01 | | 19 | 2020-01-03 | | 1 | 2020-01-02 | | 2 | 2020-01-03 | | 1 | 2020-01-04 | | 7 | 2020-01-11 | | 9 | 2020-01-25 | | 8 | 2020-01-28 | +---------+------------+ Transactions 表: +---------+------------------+--------+ | user_id | transaction_date | amount | +---------+------------------+--------+ | 1 | 2020-01-02 | 120 | | 2 | 2020-01-03 | 22 | | 7 | 2020-01-11 | 232 | | 1 | 2020-01-04 | 7 | | 9 | 2020-01-25 | 33 | | 9 | 2020-01-25 | 66 | | 8 | 2020-01-28 | 1 | | 9 | 2020-01-25 | 99 | +---------+------------------+--------+ 結果表: +--------------------+--------------+ | transactions_count | visits_count | +--------------------+--------------+ | 0 | 4 | | 1 | 5 | | 2 | 0 | | 3 | 1 | +--------------------+--------------+ * 對于 transactions_count = 0, * visits 中 (1, "2020-01-01"), (2, "2020-01-02"), * (12, "2020-01-01") 和 (19, "2020-01-03") * 沒有進行交易，所以 visits_count = 4 。 * 對于 transactions_count = 1, * visits 中 (2, "2020-01-03"), (7, "2020-01-11"), * (8, "2020-01-28"), (1, "2020-01-02") * 和 (1, "2020-01-04") 進行了一次交易， * 所以 visits_count = 5 。 * 對于 transactions_count = 2, * 沒有客戶訪問銀行進行了兩次交易， * 所以 visits_count = 0 。 * 對于 transactions_count = 3, * visits 中 (9, "2020-01-25") 進行了三次交易， * 所以 visits_count = 1 。 * 對于 transactions_count >= 4, * 沒有客戶訪問銀行進行了超過3次交易， * 所以我們停止在 transactions_count = 3 。

如下是這個例子的圖表：

來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/number-of-transactions-per-visit
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。

2. 解題

• 先生成第一列，多了沒關系，一會篩選，注意加個0

select 0 transactions_count union all select row_number() over(order by transaction_date) transactions_count from Transactions

• with as 創建 臨時表 t

select distinct v.user_id, visit_date, transaction_date,count(*) over(partition by user_id, visit_date, visit_date=transaction_date) times from Visits v left join Transactions tr on v.user_id=tr.user_id and v.visit_date=tr.transaction_date {"headers": ["user_id", "visit_date", "transaction_date", "times"], "values": [ [1, "2020-01-01", null, 1], [1, "2020-01-02", "2020-01-02", 1], [1, "2020-01-04", "2020-01-04", 1], [2, "2020-01-02", null, 1], [2, "2020-01-03", "2020-01-03", 1], [7, "2020-01-11", "2020-01-11", 1], [8, "2020-01-28", "2020-01-28", 1], [9, "2020-01-25", "2020-01-25", 3], [12, "2020-01-01", null, 1], [19, "2020-01-03", null, 1]]}

• 統計數量

select 0 transactions_count, count(*) visits_count from t where transaction_date is null union all select times transactions_count, count(*) visits_count from t where transaction_date is not null group by times {"headers": ["transactions_count", "visits_count"], "values": [[0, 4], [1, 5], [3, 1]]}

• 左連接，篩選數據

# Write your MySQL query statement below with t as (select distinct v.user_id, visit_date, transaction_date,count(*) over(partition by user_id, visit_date, visit_date=transaction_date) timesfrom Visits v left join Transactions tron v.user_id=tr.user_id and v.visit_date=tr.transaction_date )select t1.transactions_count, ifnull(t2.visits_count,0) visits_count from (select 0 transactions_countunion allselect row_number() over(order by transaction_date) transactions_countfrom Transactions ) t1 left join (select 0 transactions_count, count(*) visits_countfrom twhere transaction_date is nullunion allselect times transactions_count, count(*) visits_countfrom twhere transaction_date is not nullgroup by times ) t2 on t1.transactions_count = t2.transactions_count where t1.transactions_count <= (select max(transactions_count) from (select 0 transactions_count, count(*) visits_countfrom twhere transaction_date is nullunion allselect times transactions_count, count(*) visits_countfrom twhere transaction_date is not nullgroup by times) t3)

評論區簡潔解答

# Write your MySQL query statement below with temp1 as (select transactions_count, count(user_id) visits_countfrom (select v.user_id, count(t.user_id) transactions_countfrom Visits v left join Transactions ton v.user_id = t.user_id and visit_date = transaction_dategroup by v.user_id, v.visit_date) agroup by transactions_count )select temp2.transactions_count, ifnull(temp1.visits_count,0) visits_count from (select 0 transactions_countunionselect row_number() over (order by transaction_date) transactions_countfrom Transactions ) temp2 left join temp1 on temp2.transactions_count = temp1.transactions_count where temp2.transactions_count <= (select max(transactions_count)from temp1)

---

我的CSDN博客地址 https://michael.blog.csdn.net/

長按或掃碼關注我的公眾號（Michael阿明），一起加油、一起學習進步！

總結

以上是生活随笔為你收集整理的LeetCode MySQL 1336. 每次访问的交易次数的全部內容，希望文章能夠幫你解決所遇到的問題。

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