🍅 簡介：CSDN博客專家🏆、資訊技術智庫公號作者? 簡歷模板、PPT模板、學習資料、面試題庫、技識訓助【關注我，都給你】
🍅 歡迎點贊 👍 收藏 ?留言 📝
一、每門課程問題
用一條 SQL 陳述句查詢 學生表每門課都大于 80 分的學生姓名，
解決辦法一： having
思路：如果最小的課程都大于80分，那他所有的課程分數，肯定都大于80分！
代碼實作
SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)> 80
解決辦法二：not in
可以用反向思維，先查詢出表里面有小于 80 分的 name,然后用 not in 去除掉
代碼實作
SELECT DISTINCT name
FROM xuesheng
WHERE name NOT IN
(SELECT DISTINCT name
FROM xuesheng
WHERE score <=80);
二、topN 問題
案例：查詢各科成績前兩名的記錄
代碼實作
row_number() over( partition by 課程 order by 成績 desc) as rank
...
where rank <= 2 -- 前兩名
三、連續問題（7 天連續登陸）
實作思路:
-
因為每天用戶登錄次數可能不止一次，所以需要先將用戶每天的登錄日期去重，
-
再用row_number() over(partition by _ order by _)函式將用戶id分組，按照登陸時間進行排序，
-
計算登錄日期減去第二步驟得到的結果值，用戶連續登陸情況下，每次相減的結果都相同，
-
按照id和日期分組并求和，篩選大于等于7的即為連續7天登陸的用戶，
代碼實作
SELECT user_id, MAX(count_val) AS max_count -- 查出了最大連續登陸，where>=7,即7天連續
FROM (
-- group by相同日期
SELECT user_id, symbol_date, COUNT(*) AS count_val
FROM (
-- 日期減rank，連續登陸的話，會得到相同日期
SELECT user_id, log_date, date_sub(log_date, CAST(rn AS INT)) AS symbol_date
FROM (
-- 打上rank標識
SELECT user_id, log_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn
FROM user_logging_format
) c
) d
GROUP BY user_id, symbol_date
) e
GROUP BY user_id;
四、行轉列問題
行轉列可謂是經典中的經典了，必須掌握了！
6行變2行，2列變3列
代碼實作
SELECT SID,
MAX(case CID when '01' then score else 0 end) '01',
MAX(case CID when '02' then score else 0 end)'02',
MAX(case CID when '03' then score else 0 end)'03'
FROM SC
GROUP BY SID
五、留存問題
留存率是衡量用戶質量的最重要指標之一，因此計算用戶留存率是用戶資料分析中必須掌握的技能之一，同樣也成為了面試經典sql之一，
留存率指標中，通常需要關注次日留存、3日留存、7日留存和月留存，對新增用戶而言，需要關注更細顆粒度的資料，也就是7日內每天的留存率，
代碼實作
select
dd
, count( if(id=lead_id and datediff(dd,lead_dd)=1 ,id, null ) ) as '1 日留存'
, count( if(id=lead_id7 and datediff(dd,lead_dd7)=7 ,id, null ) ) as '7 日留存'
from
(
select
id, dd
, lead(dd,1) over(partition by id order by dd asc ) as lead_dd
, lead(id,1) over(partition by id order by dd asc ) as lead_id
, lead(dd,7) over(partition by id order by dd asc ) as lead_dd7
, lead(id,7) over(partition by id order by dd asc ) as lead_id7
from
(
select 'slm' as id, '2018-12-26' as dd
union all select 'slm' as id, '2018-12-27' as dd
union all select 'slm' as id, '2018-12-28' as dd
union all select 'hh ' as id, '2018-12-26' as dd
union all select 'hh ' as id, '2018-12-28' as dd ) aa
) bb
添加文末公眾號「資訊技術智庫」：
🍅 硬核資料：關注即可領取PPT模板、簡歷模板、行業經典書籍PDF，
🍅 技識訓助：技術群大佬指點迷津，你的問題可能不是問題，求資源在群里喊一聲，
🍅 面試題庫：由技術群里的小伙伴們共同投稿，熱乎的大廠面試真題，持續更新中，
🍅 知識體系：含編程語言、演算法、大資料生態圈組件（Mysql、Hive、Spark、Flink）、資料倉庫、前端等，
👇👇技術交流、非誠勿擾👇👇
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/348455.html
標籤：其他
上一篇：K210識別數字（0~9）并與單片機通信通過數字來控制小車移動
下一篇：【歷史上的今天】11 月 4 日：“光纖之父”出生；StumbleUpon 誕生；谷歌推出 Google Home