- 算成績
表名：subject_scores
輸入
Name subject score
王建國 數(shù)學(xué) 95
王建國 語文 89
李雪琴 數(shù)學(xué) 100
李雪琴 語文 100
李雪琴 英語 100
輸出
Name math chinese English
王建國 95 89 0
李雪琴 100 100 100
解答：
所涉知識點：GROUP BY 和 CASE WHEN 實現(xiàn)行變列
注意:(1)空的數(shù)據(jù)這里判斷為0瘫里；(2)CASE WHEN 前要使用聚合函數(shù)，不然報錯）
SELECT
name,
MAX(CASE subject WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as math,
MAX(CASE subject WHEN '語文' THEN score ELSE 0 END) as chinese,
MAX(CASE subject WHEN '英語' THEN score ELSE 0 END) as English
FROM subject_scores
GROUP BY name;
2.算成績
輸入
Name math chinese English
王建國 95 89 0
李雪琴 100 100 100
輸出
Name subject score
王建國 數(shù)學(xué) 95
王建國 語文 89
李雪琴 數(shù)學(xué) 100
李雪琴 語文 100
李雪琴 英語 100
-- 解答
知識點：通過使用UNION ALL 列變行;
注意：0值處理垫竞；最后根據(jù)姓名排序
SELECT name ,'數(shù)學(xué)' as subject,math as 'score' FROM subject_scores_2 WHERE math>0
UNION ALL
SELECT name ,'語文' as subject,chinese as 'score' FROM subject_scores_2 WHERE chinese>0
UNION ALL
SELECT name ,'英語' as subject,english as 'score' FROM subject_scores_2 WHERE English>0
ORDER BY name DESC;
3.算昨天每個城市top 10消費金額的用戶切黔，輸出city_id,city_name,uid, 消費總金額
表名：orders
每次消費記錄一條
city_id,city_name,uid,order_id,amount,pay_order_time, pay_date
解答：(窗口函數(shù))
SELECT a.city_id,
a.city_name,
a.uid,
a.pay_amount as '消費總金額'
FROM
(
SELECT city_id,city_name,uid,SUM(amount) as pay_amount,RANK()over(PARTITION BY city_id ORDER BY SUM(amount) DESC) as rank_no
FROM orders
WHERE pay_date='2020-01-01'
GROUP BY city_id,city_name,uid
) a
WHERE a.rank_no<=10;
4.sql解析出json數(shù)組中的 fruit_id, fruit_name
[{'fruit_id':1, 'fruit_name':'apple'},
{'fruit_id':2, 'fruit_name':'pear'},
{'fruit_id':3, 'fruit_name':'peach'}]
解答：
SELECT * FROM JSON_TABLE('[
{"fruit_id":1,"fruit_name":"apple"},
{"fruit_id":2,"fruit_name":"pear"},
{"fruit_id":3,"fruit_name":"peach"}
]','$[*]' COLUMNS( fruit_id INT PATH '$.fruit_id' ,fruit_name VARCHAR(25) PATH '$.fruit_name')) as jt;
5.計算end_date
輸入：
begin_date, 開始日期
unit_num, --單元 12個單元
week_num, --周序號 1～4谓松，每個單元4周
day_num, --day的序號 1～7焕妙，每周7天
輸出
end_date 結(jié)束日期（12）
解答：(也不知道有沒有讀懂題意？亂寫的)
set @begin_date='2010-01-01',@unit_num=12;
SELECT DATE_ADD(@begin_date,INTERVAL @unit_num*4*7 DAY) AS end_date;
- 計算占比和同比增長
t_user記錄了用戶注冊時間和平臺沃斤，統(tǒng)計2018年1月份
每天各平臺("ios","android","h5")注冊用戶總量占所有平臺總用戶的比例圣蝎，以及各平臺注冊用戶按周同比增長（與一周前相比）的比例
建表語句
create table t_user
(
uid BIGINT COMMENT "用戶id"
, reg_time STRING COMMENT "注冊時間,如2018-07-01 08:11:39"
, platform STRING COMMENT "注冊平臺，包括app ios h5"
);
解答：
知識點：窗口函數(shù)衡瓶。
注意：如果存在某天的缺失數(shù)據(jù)徘公，偏移函數(shù)會有錯誤
SELECT a.reg_date
,a.platform
,ROUND(a.reg_num/sum(a.reg_num)over(PARTITION BY a.reg_date),4) as rate
,ROUND((a.reg_num-a.reg_num_7)/a.reg_num_7,4) as rate_week
FROM(
SELECT
DATE(reg_time) as reg_date
,platform
,COUNT(uid) as reg_num
,lag(COUNT(uid),7)over(PARTITION BY platform ORDER BY DATE(reg_time)) as reg_num_7
FROM t_user
WHERE SUBSTR(reg_time,1,7)='2018-01'
GROUP BY DATE(reg_time),platform
) a ;
二、分析題
1.撲克牌54張哮针，里面有4個2关面，隨機(jī)抽取10張，其中有1個2的概率十厢。
需要寫出分析過程
2.指標(biāo)定義：
參課滲透率--參加課程學(xué)習(xí)人數(shù)/整體報名人數(shù)
對比上個招生期等太，本期大齡兒童組參課滲透率增加，低齡兒童組參課滲透率也增加蛮放，本期是否參課滲透率整體會提升澈驼？為什么
（知識點：辛普森悖論）
