05 實踐：SQL！！！

行為數據

1. 好評率是會員對平台評價的重要指標，現在需統計2018年1月1日到2018年1月31日，用戶小明提交的母嬰「花王」品牌的好評率（好評率=「好評」評價量/總評價量）

用戶評價詳情表：a

欄位：id（評價id，主鍵），create_time（評價創建時間，格式「2017-01-01」），user_name（用戶名稱），good_id（商品id，外鍵），sub_time（評價提交時間，格式「2017-01-01 23:10:32」），sat_name（好評率類型，包含：「好評」，「中評」，「差評」）

用戶詳情表：b

欄位：good_id（商品id，主鍵），bu_name（商品類目），brand_name（商品名稱）

2. 我們把用戶對商品的評分用稀疏向量表示，保存在資料庫表t裡面：

t的欄位有：uid，goods_id，star

uid是用戶id；goodsid是商品id；star是用戶對該商品的評分，值為1-5。

現在我們想要計算向量兩兩之間的內積，內積在這裡的語義為：對於兩個不同的用戶，如果他們都對同樣的一批商品打了分，那麼對於這裡面的每個人的分數乘起來，並對這些乘積求和。

例子，資料庫表裡有以下的數據：

U0 g0 2

U0 g1 4

U1 g0 3

U1 g1 1

……

計算後的結果為：

U0 U1 2*3+4*1=10

……

編寫SQL做上述計算。

業務數據

1. 考拉運營「小明」負責多個品牌的銷售業績，請完成：

（1）請統計小明負責的各個品牌，在2017年銷售最高的3天，及對應的銷售額。

銷售表a：

欄位：logday（日期，主鍵組），SKUID（商品SKU，主鍵組），sale_amt（銷售額）

商品基礎信息表b：

欄位：SKUID（商品SKU，主鍵），buname（商品類目），brandname（商品類目），brandname（品牌名稱），user_name（運營負責人名稱）

（2）請統計小明負責的各個品牌，在2017年連續3天增長超過50%的日期，及對應的銷售額。

2. 交易表結構為user_id,order_id,pay_time,order_amount

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

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

流量數據

1. 現有一個資料庫表Tourists，記錄了某個景點7月份每天來訪遊客的數量如下：

id date visits

1 2017-07-01 100

……

非常巧，id欄位剛好等於日期裡面的幾號。現在請篩選出連續3天都有大於100天的日期。

上面例子的輸出為：

date

2017-07-01

……

2. 用戶登錄日誌表為user_id，log_id，session_id，plat

（1）用sql查詢近30天每天平均登錄用戶數量

（2）用sql查詢出近30天連續訪問7天以上的用戶數量

4. person表：

欄位：useri_d，visiti_d，page_name，plat

（1）統計近7天到訪的新用戶總數/每天到訪的新用戶數

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

微信取消關注分析

抽樣

1. 表結構為user_id，reg_time，age

（1）寫一句sql按user_id尾數隨機抽樣2000個用戶

（2）寫一句sql取出按各年齡段（每10歲一個分段，如（0,10））分別抽樣1%的用戶

匹配

1. 現有A表，有21個列，第一列id，剩餘列為特徵欄位，列名從d1-d20，共10W條數據！

另外一個表B稱為模式表，和A表結構一樣，共5W條數據

請找到A表中的特徵符合B表中模式的數據，並記錄下相對應的id

有兩種情況滿足要求：

（1）每個特徵列都完全匹配的情況下

（2）最多有一個特徵列不匹配，其他19個特徵列都完全匹配，但哪個列不匹配未知

2. PV表a(表結構為user_id,goods_id),點擊表b(user_id,goods_id),數據量各為50萬？條，在防止數據傾斜的情況下，寫一句sql找出兩個表共同的user_id和相應的goods_id

修復

2. 在一張工資表salary裡面，發現2017-07這個月的性別欄位男m和女f寫反了，請用一個Updae語句修復數據

例如表格數據是：

id name gender salary month

1 A m 1000 2017-06

2 B f 1010 2017-06

業務

1. 考拉海購始終以用戶為中心，為用戶提供高品質的商品，幫助用戶「用更少的錢，過更好的生活」，為了滿足不同用戶的需求，（比如新用戶的需求往往跟老用戶不同，流失客戶需要特殊的關懷），請你設計一套具體

推薦閱讀：