SQL查詢實例

本文通過幾個實際查詢題目分享SQL查詢語句的編寫心得

Q1：交易表結構為user_id(用戶ID),order_id(訂單ID),pay_time(付款時間),order_amount(金額)

1.寫sql查詢過去一個月付款用戶量（提示：用戶量需去重）最高的三天分別是哪幾天？

2.寫sql查詢昨天每個用戶最後付款的訂單ID及金額

思路：求最高的三天，肯定是先排序，後limit. 先求出每天的付款用戶量，既然每天，那肯定要按天分組了；按照題目要求過濾條件有：1.過去一個月 2.付款用戶（即要排除未付款的用戶），另外求用戶量需要去重，題目中也有提示，因為存在同一個用戶每天有多筆消費記錄的情況；返回排在前三的付款用戶量及對應的時間（天）

思路：每個用戶=要按用戶分組；過濾條件：1.昨天 2.最後付款

Q2：有PV表a（表結構為user_id(用戶ID），goods_id(商品ID）)，點擊表b（表結構為user_id(用戶ID），goods_id(商品ID）兩個表，各存放40億條user_id的goods_id訪問記錄，在防止數據傾斜的情況下，寫一句sql找出a、b兩個表共同的user_id和與相應的goods_id？

思路：這裡的數據傾斜指兩個大表在關聯的時候卡死的情況，圖片中解決方案是空值導致的數據傾斜的解決方案

Q3：用戶登錄日誌表為user_id,log_time,session_id,plat

寫sql查詢近30天，每天平均登錄用戶數量？

思路：1.求出近30天每天的登錄用戶數量；2.求第一步生成表的ct列的均值

Q4：對於通過不同渠道拉新進來的用戶，經過一段時間許多用戶可能會流失，而留下來的用戶我們稱之為留存用戶。分析用戶留存是拉新和用戶運營的重要指標。假如我們有一張用戶訪問表：person_visit,記錄了所有用戶的訪問信息，包含欄位：用戶ID-user_id,訪問時間-visit_date,訪問頁面page_name,訪問渠道（android,ios)-plat等

1，請統計近7天每天到訪的新用戶數

2，請統計每個訪問渠道7天前（D-7）的新用戶的3日留存和7日留存率

思路：1.求出每個用戶及其第一次登陸的日期，也就是成為新用戶的那天，過濾出近7天；2.在第1步所得表基礎上提取每個用戶成為新用戶的日期，按這個時間分組，求得每天的用戶數

思路：1.求得每個渠道7前的總的新用戶數；2.求3日留存用戶數；3.求7日留存用戶數；4.手動計算留存率

Q5:求復購率和回購率

思路：1.求3月份支付用戶的消費次數；2.count函數和if函數結合將購買兩次以上的用戶與只夠買一次的進行區分，進而計算出復購率

思路：巧妙利用left join： join條件為本月消費，次月依然消費，之後用count計數就可以將本月消費，次月依然消費和只有本月消費的用戶區分開