20200701效率優(yōu)化
【between and】查看一段時間范圍內(nèi)的數(shù)據(jù)缠诅，between and效率高于關(guān)系運算符蒋川。
【exists>in>關(guān)系運算符】
【索引】減少對磁盤的讀寫操作，提高查詢速度尖滚。而視圖的缺點是不能使用索引档冬，這也是使用物化視圖（進一步提高查詢速度）的原因⌒较Γ【物化視圖 VS 視圖】同：反映某個查詢的結(jié)果脚草；異：視圖僅保存SQL定義，而物化視圖本身會存儲數(shù)據(jù)寥殖，故名物化了的視圖（需要占用存儲空間存儲數(shù)據(jù)玩讳；當(dāng)?shù)讓颖淼臄?shù)據(jù)發(fā)生變化時涩蜘，物化視圖也應(yīng)相應(yīng)更新到最新數(shù)據(jù)）嚼贡。
20200603
【1】4種風(fēng)格的嵌套：關(guān)鍵詞in、exists（exists比in查詢更快同诫，但hive沒有exists實現(xiàn)嵌套查詢的功能）粤策、比較運算符!=或=、any或all误窖。
【復(fù)雜的多表查詢】
- 縱向的表合并（焊接）：union all（速度快：僅合并叮盘，無其他附加動作）秩贰、union（合并+排重+排序）
- 橫向的表連接（多個表中的字段合并到一張寬表中）
【通過索引提高數(shù)據(jù)的查詢速度】類似書的目錄；盡管有提速功能柔吼，但濫用的話會降低數(shù)據(jù)表的寫操作（如降低表記錄的增加insert毒费、更新update或刪除delete速度），也會占用一定的磁盤空間愈魏。
根據(jù)索引類型可分為普通索引（使用最頻繁觅玻、無任何約束，不管變量的值是否重復(fù)或缺失）培漏、唯一索引（唯一即不存在重復(fù)值）溪厘、主鍵索引（最嚴(yán)格：既不重復(fù)也不缺失）、聯(lián)合索引和全文索引牌柄。
【索引的查詢和刪除】查詢show inex from table畸悬；刪除drop index indexname on table。
七周成為數(shù)據(jù)分析師課后題
1珊佣、統(tǒng)計3月份的回購率
select count(ct),count(if(ct>1,1,null)) from
(select userid,count() as ct from orderinfo
where ispaid = '已支付' and month(paidtime)=3
group by userid)t
2蹋宦、統(tǒng)計3月份的復(fù)購率【date_sub(t2.m,interval 1 month)】
select t1.m,count(t1.m) ,count(t2.m) from
(select userid,date_format(paidtime,'%Y-%m-%01') as m
from orderinfo where ispaid='已支付'
group by userid,date_format(paidtime,'%Y-%m-%01') )t1
left join
(select userid,date_format(paidtime,'%Y-%m-%01') as m
from orderinfo where ispaid='已支付'
group by userid,date_format(paidtime,'%Y-%m-%01') )t2
on t1.userid=t2.userid and t1.m=date_sub(t2.m,interval 1 month)
group by t1.m
【滴滴面試題】快車訂單order表
order_id,
user_id
start_address
end_address
call_time如20200101 08：35：26 （起始時間為2020-01-01到2020-03-31）
問題1：5分鐘內(nèi)的重復(fù)訂單數(shù)量（起止地點相同）
問題2：20200101的用戶在以后每天的留存率
【留存率 / 回購率】構(gòu)造用戶id和日期的中間表！咒锻！
select t1.d,count(t1.d),count(t2.d) from
(select userid,date_format(calltime,'%Y-%m-%d') as d from order
group by userid,date_format(calltime,'%Y-%m-%d')
having date_format(calltime,'%Y-%m-%d')='2020-01-01' )t1
left join
(select userid,date_format(calltime,'%Y-%m-%d') as d from order
group by userid,date_format(calltime,'%Y-%m-%d'))t2
on t1.userid=t2.userid and t1.d=date_sub(t2.d,interval 1 day)
group by t1.d
3妆档、統(tǒng)計男女用戶的消費頻次差異
4、多次消費的用戶虫碉，首末次消費間隔是多少
【計算天數(shù)datediff(max,min)贾惦；而非秒數(shù)】
select userid,datediff( max(paidtime),min(paidtime))
from order where ispaid='已支付'
group by userid having count(1)>1
大小廠面試題匯總
sql 面試題(難題匯總)
1、騰訊面試題：table_A （ 用戶userid和登錄時間time）求連續(xù)登錄3天的用戶數(shù)
【解析：用窗口函數(shù)row_number 進行排序敦捧，日期函數(shù)DATESUB须板，將（日期-排序數(shù)）得到一個相等的日期flag_date，以其進行分組兢卵，將連續(xù)的日期分為一組】
select userid,date_sub(time,interval t.rn day) as flag_date,count(*)
from (
select userid,time,row_number() over(partition by userid order by time) as rn
from table_A
)t
group by userid,flag_date
having count(*)>=3
2习瑰、原始座次表 ‘seat’如下，現(xiàn)需要更換相鄰位置學(xué)生的座次秽荤。
select
(case
when mod(id, 2) != 0 and c!= id then id + 1
when mod(id, 2) != 0 and c = id then id
else id - 1
end) as id2,student
from seat ,(select count(*) as c from seat)as b
order by id2
3甜奄、現(xiàn)在需要找出語文課中成績第二高的學(xué)生成績。如果不存在第二高成績的學(xué)生窃款，那么查詢應(yīng)返回 null课兄。
題目要求，如果沒有第二高的成績晨继，返回空值烟阐，所以這里用判斷空值的函數(shù)（ifnull）函數(shù)來處理特殊情況。select ifnull(第2步的sql,null) as '語文課第二名成績';
select
ifnull( (
select max(distinct 成績)
from 成績表
where 課程='語文' and
成績 < (select max(distinct 成績)
from 成績表
where 課程='語文') ),null)
as '語文課第二名成績';
4、如何提高SQL查詢的效率蜒茄？
1. select子句中盡量避免使用*
2唉擂、為了提高效率，where子句中遇到函數(shù)或加減乘除的運算檀葛，應(yīng)當(dāng)將其移到比較符號的右側(cè)玩祟。
where 成績 > 90 – 5（表達式在比較符號的右側(cè)）
3、 盡量避免使用in和not in【會導(dǎo)致數(shù)據(jù)庫進行全表搜索屿聋，增加運行時間】
4. 盡量避免使用or
select 學(xué)號
from 成績表
where 成績 = 88 or 成績 = 89
優(yōu)化后：
select 學(xué)號 from 成績表 where 成績 = 88
union
select 學(xué)號 from 成績表 where 成績 = 89
5卵凑、用戶訪問次數(shù)表，列名包括用戶編號胜臊、用戶類型勺卢、訪問量。要求在剔除訪問次數(shù)前20%的用戶后象对，每類用戶的平均訪問次數(shù)黑忱。（拼多多、網(wǎng)易面試題）
select 用戶類型,avg(訪問量)
from
(select *
from
(select *,
row_number() over(order by 訪問量 desc) as 排名
from 用戶訪問次數(shù)表) as a
where 排名 > (select max(排名) from a) * 0.2) as b
group by 用戶類型;
爬漳В客網(wǎng)刷題
【202005】
0甫煞、【重點回顧】
給出每個員工每年薪水漲幅超過5000的員工編號emp_no、薪水變更開始日期from_date以及薪水漲幅值salary_growth冠绢，并按照salary_growth逆序排列抚吠。
0、【重點回顧】
查找所有員工自入職以來的薪水漲幅情況弟胀，給出員工編號emp_no以及其對應(yīng)的薪水漲幅growth楷力，并按照growth進行升序
select a.emp_no,c.salary-b.salary as growth from employees a
inner join salaries b
on a.emp_no=b.emp_no and a.hire_date=b.from_date
inner join salaries c
on a.emp_no=c.emp_no and c.to_date='9999-01-01'
order by c.salary-b.salary asc
0、【重點回顧：有思路（這種場景孵户，最重要的是學(xué)會拆分）萧朝，完整寫出來不易】
獲取員工其當(dāng)前的薪水比其manager當(dāng)前薪水還高的相關(guān)信息
0、【重點回顧】dense_rank的排序（有序號） VS 最后輸出顯示的排序order by（無序號）
dense_rank() over()的用法 以及
我錯在哪order by salary desc,emp_no asc夏哭？【正確寫法——按照一個標(biāo)準(zhǔn)來排序order by salary desc 】
對所有員工的當(dāng)前(to_date='9999-01-01')薪水按照salary進行按照1-N的排名检柬，相同salary并列且按照emp_no升序排列
【正確寫法——按照一個標(biāo)準(zhǔn)來排序order by salary desc 】
select emp_no,salary, dense_rank() over (order by salary desc ) as rank
from salaries
where to_date='9999-01-01'
order by salary desc,emp_no asc
【錯誤寫法：2處錯誤】
select emp_no,salary,dense_rank(order by salary desc,emp_no asc) as rank
from salaries where to_date='9999-01-01'
order by salary desc,emp_no asc
1、【如何去重 distinct（distinct效率不行竖配，且大數(shù)據(jù)量的時候都禁止用distinct何址，建議用group by解決重復(fù)問題） 和 group by】查找入職員工時間排名倒數(shù)第三的員工所有信息
【法1：groupby來去重】
select * from employees
where hire_date = (
select hire_date from employees
group by hire_date
order by hire_date desc
limit 2,1)
【法2：distinct來去重】
select * from employees
where hire_date = (
select distinct hire_date from employees order by hire_date desc limit 2,1)
2、【SQL是支持集合運算：EXPECT 集合差運算进胯、 UNION 集合并運算用爪、 INTERSECT 集合交運算】獲取所有非manager的員工emp_no
SELECT employees.emp_no
FROM salaries
EXCEPT
SELECT dept_manager.emp_no
FROM dept_manager;
3、【MAX(SALARY) 和 emp_no 不一定對應(yīng)哦Ａ浼酢Ｏ钆ァ班眯！因為GROUP BY 默認(rèn)取非聚合的第一條記錄】最大最小問題要善用row_number（）
【知識點】使用group by子句時希停，select子句中只能有聚合鍵烁巫、聚合函數(shù)、常數(shù)宠能。emp_no并不符合這個要求亚隙。
獲取所有部門中當(dāng)前員工薪水最高的相關(guān)信息，給出dept_no, emp_no以及其對應(yīng)的salary
4违崇、查找員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth
select max(salary)-min(salary) as growth from salaries
group by emp_no
having emp_no='10001'
【嚴(yán)謹(jǐn)?shù)乃悸贰?SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth
5阿弃、【累計求和】所有聚合函數(shù)都能用做窗口函數(shù)，其語法和專用窗口函數(shù)完全相同——sum(<匯總列>) over(<排序列>) as 別名羞延；
select emp_no,salary,sum(salary) over(order by emp_no) as running_total
from salaries
where to_date = '9999-01-01';
【把所有小于等于當(dāng)前編號的表s1和當(dāng)前編號表s2聯(lián)立起來渣淳，然后按照當(dāng)前編號分組，計算出所有小于等于當(dāng)前標(biāo)號的工資總數(shù)】
select b.emp_no,b.salary,sum(a.salary) as running_total from salaries a
inner join salaries b
on a.emp_no<=b.emp_no and a.to_date = "9999-01-01" and b.to_date = "9999-01-01"
group by b.emp_no,b.salary
6伴箩、對于employees表中入愧，給出奇數(shù)行的first_name
【未通過】
select first_name from(
select *,row_number() over(order by first_name) as rank from employees)a
where a.rank%2=1
select e1.first_name from employees as e1
where (select count(e2.first_name) from employees as e2
where e1.first_name >= e2.first_name)%2 = 1;
【202004】
1、case when 的用法（離散數(shù)值映射為對應(yīng)的實際含義嗤谚、連續(xù)數(shù)值映射為離散區(qū)間棺蛛、構(gòu)建長形統(tǒng)計表、構(gòu)建寬形統(tǒng)計表）
- 簡單case函數(shù)法
-
case搜索函數(shù)法（推薦使用巩步，因為其既可完成等式表達、也可實現(xiàn)不等式表達）
case when 的用法：映射處理、篩選計算
case
when expr1 then expr2
when expr3 then expr4
else expr5
`case` `eb.btype`
`when ``1` `then s.salary*``0.1`
`when ``2` `then s.salary*``0.2`
`else` `s.salary*``0.3`
-- 收入?yún)^(qū)間分組
select id,
(case
when CAST(salary as float)<50000 Then '0-5萬'
when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10萬'
when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20萬'
when CAST(salary as float)>200000 then '20萬以上'
else NULL end ) as qujian
from table_1;
2拳恋、 EXISTS用于檢查子查詢是否至少會返回一行數(shù)據(jù)榜旦，該子查詢實際上并不返回任何數(shù)據(jù)，而是返回值True或False
SQL中EXISTS的用法
select * from employees
where not exists (select * from dept_emp where emp_no=employees.emp_no)
3竟闪、LIMIT 1 OFFSET 2 -- 去掉OFFSET排名倒數(shù)第一第二的時間声离，取倒數(shù)第三
【limit y,x和limit x offset y等價】
【LIMIT 后的數(shù)字代表返回幾條記錄，OFFSET 后的數(shù)字代表從第幾條記錄開始返回（第一條記錄序號為0）瘫怜，也可理解為跳過多少條記錄后開始返回】
【在 LIMIT X,Y 中术徊，Y代表返回幾條記錄，X代表從第幾條記錄開始返回（第一條記錄序號為0）鲸湃，切勿記反】
`以下的兩種方式均表示取``2``,``3``,``4``三條條數(shù)據(jù)赠涮。`
`1``.select* from test LIMIT` `1``,``3``;`
`當(dāng)limit后面跟兩個參數(shù)的時候，第一個數(shù)表示要跳過的數(shù)量暗挑，后一位表示要取的數(shù)量笋除。`
`2``.select * from test LIMIT` `3` `OFFSET` `1``;(在mysql` `5``以后支持這種寫法)`
`當(dāng) limit和offset組合使用的時候，limit后面只能有一個參數(shù)炸裆，表示要取的的數(shù)量,offset表示要跳過的數(shù)量 垃它。`
4、查找排除當(dāng)前最大、最小salary之后的員工的平均工資avg_salary国拇。
select avg(salary) as avg_salary
from salaries
where to_date = '9999-01-01'
and salary<(select max(salary) from salaries)
and salary>(select min(salary) from salaries)
5洛史、【group_concat(X,Y)】
SQLite的聚合函數(shù)group_concat(X,Y)，其中X是要連接的字段酱吝，Y是連接時用的符號也殖，可省略，默認(rèn)為逗號务热。此函數(shù)必須與 GROUP BY 配合使用忆嗜。
select dept_no,
group_concat(emp_no,',') as employees
from dept_emp
group by dept_no
6、【substr而非substring】substr(字符串崎岂，起始位置【第一個字符的位置為1捆毫，而不為0】，長度【省略冲甘，則從一直截取到字符串末尾】）
SQL中有l(wèi)ength()函數(shù)
select first_name from employees
order by substr(first_name,length(first_name)-1,2)
7冻璃、【length()函數(shù)與replace()函數(shù)的結(jié)合靈活地解決了統(tǒng)計子串出現(xiàn)次數(shù)的問題】查找字符串'10,A,B' 中逗號','出現(xiàn)的次數(shù)cnt。
SELECT (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt
8损合、【SQLite 中用 “||” 符號連接字符串】
SELECT last_name || "'" || first_name FROM employees
9省艳、【update的語法】將所有獲取獎金的員工當(dāng)前的薪水增加10%。
屬于范圍：exists嫁审、where in跋炕、inner join
update salaries set salary = salary*1.1 #此處不能用salary*1.1 as salary
where emp_no in (
select a.emp_no from emp_bonus a
inner join salaries b
on a.emp_no=b.emp_no and b.to_date='9999-01-01')
10、將id=5以及emp_no=10001的行數(shù)據(jù)替換成id=5以及emp_no=10005,其他數(shù)據(jù)保持不變律适，使用replace實現(xiàn)辐烂。
REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')
11、【刪除語句DELETE FROM [不用加table字段] ... WHERE ... NOT IN ... 】刪除emp_no重復(fù)的記錄捂贿，只保留最小的id對應(yīng)的記錄纠修。
delete from titles_test
where id not in(
select min(id) from titles_test
group by emp_no)
12、針對actor表創(chuàng)建視圖actor_name_view厂僧，只包含first_name以及l(fā)ast_name兩列扣草，并對這兩列重新命名，first_name為first_name_v颜屠，last_name修改為last_name_v.
create view actor_name_view
as
select first_name as first_name_v,last_name as last_name_v
from actor
13辰妙、創(chuàng)建索引：對first_name創(chuàng)建唯一索引uniq_idx_firstname，對last_name創(chuàng)建普通索引idx_lastname甫窟。
create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);
14【用 WHERE 來篩選 category_id IS NULL 】使用join查詢方式找出沒有分類的電影id以及名稱
select a.film_id ,a.title from film a
left join film_category b
on a.film_id =b.film_id
where b.category_id is null #不能用and b.category_id is null !!!!!!!!!
注意：最后一句若寫成 ON f.film_id = fc.film_id AND fc.category_id IS NULL密浑，則意義變成左連接兩表 film_id 相同的記錄，且 film_category 原表中的 fc.category 的值為 null粗井。顯然尔破，原表中的 fc.category 的值恒不為 null街图，因此（f.film_id = fc.film_id AND fc.category_id IS NULL）恒為 FALSE，左連接后則只會顯示 film 表的數(shù)據(jù)懒构，而 film_category 表的數(shù)據(jù)全顯示為 null
15餐济、給出每個員工每年薪水漲幅超過5000的員工編號emp_no、薪水變更開始日期from_date以及薪水漲幅值salary_growth痴脾，并按照salary_growth逆序排列颤介。
SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1
OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC
經(jīng)典題目
【 In/exist的聯(lián)系與區(qū)別】子查詢過程中梳星，In和exist函數(shù)效率比較：
- 當(dāng)進行連接的兩個表大小相似赞赖，效率差不多；
- 如果子查詢的內(nèi)表更大冤灾，則exist的效率更高（exist先查詢外表前域，然后根據(jù)外表中的每一個記錄，分別執(zhí)行exist語句判斷子查詢的內(nèi)表是否滿足條件韵吨，滿足條件就返回ture）匿垄。
- 如果子查詢的內(nèi)表小，則in的效率高（in在查詢的時候归粉，首先查詢子查詢的表椿疗，然后將內(nèi)表和外表做一個笛卡爾積 (表中的每一行數(shù)據(jù)都能夠任意組合A表有a行，B表有b行糠悼，最后會輸出a*b行)届榄，然后按照條件進行篩選。所以相對內(nèi)表比較小的時候倔喂，in的速度較快）铝条。
【Exist的原理】使用exist時，若子查詢能夠找到匹配的記錄席噩，則返回true班缰，外表能夠提取查詢數(shù)據(jù)；使用 not exist 時悼枢，若子查詢找不到匹配記錄埠忘，則返回true，外表能夠提取查詢數(shù)據(jù)馒索。
【字符串常見操作函數(shù)】
concat给梅、concat_ws、group_concat函數(shù)用法
concat(): 將多個字符串連接成一個字符串双揪，連接符用“”包起來动羽；
concat_ws()：是CONCAT()的特殊形式， 將多個字符串連接成一個字符串渔期，在最開始的位置指定連接符（指定一次即可运吓；第一個參數(shù)是其它參數(shù)的分隔符）渴邦。——select concat_ws(',','11','22','33'); 11,22,33
group concat()：【分組拼接函數(shù)】將group by產(chǎn)生的同一個分組中的值連接起來拘哨，返回一個字符串谋梭；
【語法】group_concat([DISTINCT] 要連接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
select id,group_concat(name order by name desc) from aa group by id;
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
select id,group_concat(distinct name) from aa group by id;
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
like(): 需要與通配符一起使用（'%'代表任意字符出現(xiàn)任意次數(shù)；'_'僅能匹配單個字符）倦青；
substr(): 用于從字段中提取相應(yīng)位置的字符瓮床；
regexp() : 正則表達式匹配函數(shù)；
【列拆分為多行】lateral view explode()：按照一定的格式（比如split(food,'产镐、')先按照頓號分割隘庄，然后鋪開為多行）分裂數(shù)據(jù)
select name,sum(calorie)
from
(select t1.name,fd,t2.calorie
from p_food t1 lateral view explode(split(food,'、')) as fd
left join f_calorie t2 on t1.food = t2.food)a
group by name
spark sql如何把一列拆分為多行：Lateral view explode()
【行轉(zhuǎn)列：在行列互換結(jié)果表中癣亚，其他列里的值分別使用case和max來獲取】
【不加聚合max（group by）的效果】有幸去華為面試數(shù)據(jù)分析崗丑掺，看到SQL后我拒絕了
行列互換問題，怎么辦述雾？送你一個萬能模版
select stu_name,
max(case when course_name='Chinese' then grades else 0 end) as Chinese,
max(case when course_name = 'English' then grades else 0 end) as English,
max(case when course_name = 'Physics' then grades else 0 end) as Physics,
max(case when course_name = 'Chinese' then grades else 0 end) as Mathematics
from students_grades
group by stu_name;
語法學(xué)習(xí)
WHERE語句在GROUP BY語句之前街州；SQL會在分組之前計算WHERE語句。
HAVING語句在GROUP BY語句之后玻孟；SQL會在分組之后計算HAVING語句唆缴。
“Where” 是一個約束聲明，使用Where來約束來之?dāng)?shù)據(jù)庫的數(shù)據(jù)黍翎，Where是在結(jié)果返回之前起作用的面徽，且Where中不能使用聚合函數(shù)。
“Having”是一個過濾聲明玩敏，是在查詢返回結(jié)果集以后對查詢結(jié)果進行的過濾操作斗忌，在Having中可以使用聚合函數(shù)。
通配符
函數(shù)
-
文本處理
文本處理 -
數(shù)值計算
disticnt只能放在前面旺聚？织阳？？ -
時間處理
時間處理.PNG
【count()與count(列)】count(列)忽略null值的個數(shù)砰粹；count()返回樣本數(shù)量（含null值的個數(shù)）唧躲；
【union與union all（列數(shù)據(jù)類型必須相同）】union去重；union all 不去重碱璃。
【表的創(chuàng)建與刪除】
drop table if exists a;
create table if not exist a as select * from b;
面試題練習(xí)
第二題
數(shù)據(jù)下載
編碼格式轉(zhuǎn)換
將其由 xlsx 轉(zhuǎn)化為 utf-8 格式的csv保存弄痹，文件保存路徑'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/xiangji.csv'。
數(shù)據(jù)加載過程
詳見下圖
create table user
(uid varchar(10),
app_name varchar(20),
duration int(10), -- 在hive中建該表嵌器，可能需要改為 duration int肛真，
times int(10), -- 在hive中建該表，可能需要改為 times int爽航，
dayno varchar(30)
);
SHOW VARIABLES LIKE "secure_file_priv";
load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/xiangji.csv' into table user
fields terminated by ',' ignore 1 lines;
查尋代碼
select
a.day1,
count(distinct a.uid) as 活躍用戶數(shù),
count(distinct case when day2-day1=1 then a.uid end) as 次留,
-- #體會差別：我的寫法是sum(case when day2-day1=1 then 1 else 0 end)as 次留,
count(distinct case when day2-day1=3 then a.uid end) as 3留,
count(distinct case when day2-day1=7 then a.uid end) as 7留,
concat(count(distinct case when day2-day1=1 then a.uid end)/count(distinct a.uid)*100,'%') as 次留率,
concat(count(distinct case when day2-day1=3 then a.uid end)/count(distinct a.uid)*100,'%') 三日留存率,
concat(count(distinct case when day2-day1=7 then a.uid end)/count(distinct a.uid)*100,'%') 七日留存率
from
(SELECT uid,date_format(dayno,'%Y%m%d') as day1 FROM data.user
where app_name='相機') a
-- #用date_format把dayno的文本格式改為可計算的形式
left join
(SELECT uid,date_format(dayno,'%Y%m%d') as day2 FROM data.user
where app_name='相機') b
on a.uid=b.uid
group by a.day1
第三題
行轉(zhuǎn)列(圖中左變右)
建表蚓让、查尋代碼
create table course (
id varchar(20),
teacher_id varchar(20),
week_day varchar(20),
has_course varchar(20));
insert into course value
(1,1,2,"Yes"),
(2,1,3,"Yes"),
(3,2,1,"Yes"),
(4,3,2,"Yes"),
(5,1,2,"Yes");
select
teacher_id,
(case when week_day= 1 then 'yes' else '' end) as mon,
(case when week_day = 2 then "Yes" else " " end) "tue",
(case when week_day = 3 then "Yes" else " " end) "thi",
(case when week_day = 4 then "Yes" else " " end) "thu",
(case when week_day = 5 then "Yes" else " " end) "fri"
from course
第四題
建表乾忱、查尋代碼
create table a1 (
name varchar(20),
english int,
maths int,
music int);
insert into a1 values
("Jim",90,88,99);
select name,'english' as subject,english as score
from a1
-- #把表格內(nèi)容 english 加上字段名 subject
union
select name,'maths' as subject,maths as score
from a1
union
select name,'music' as subject,music as score
from a1;
第五題
建表、查尋代碼
第六題
建表历极、查尋代碼
解答第一問
select name,
max(datetime) as 最近登錄時間,
count(distinct date) as 登錄總次數(shù)
-- 錯誤寫法(需要去重后計數(shù))：count(date) ,
-- 另一種正確寫法:count(distinct date_format(lastlogon,'%Y-%m-%d')),
from(
select name,
date(date_format(lastlogon,"%Y-%m-%d %H:%i:%s")) as date,
date_format(lastlogon,"%Y-%m-%d %H:%i:%s") as datetime
-- 分鐘的表示是：%i;小時是：%H
from userlog)t
group by name
-- 啟示distinct/group by:若去重后計數(shù)窄瘟，用distinct(計數(shù))；若去重后求和等操作趟卸，用group by(求和).
【啟示】去重的兩種方法：distinct / group by
（1） 若去重后計數(shù)蹄葱，用distinct(計數(shù))；
（2）若去重后求和等操作锄列，用group by(求和)
解答第二問
-- #法1：子查詢图云，好理解
drop table if exists tmp_table;
create temporary table tmp_table
select name,lastlogon,
@rank:=if(@test=name,@rank+1,1) as num_logontime,
@rank2:=if(@test=name,if(@date=date,@rank2,@rank2+1),1) as num_logonday,
@test:=name,
@date:=date
from(
SELECT name,lastlogon,
date(date_format(lastlogon,"%Y-%m-%d %H:%i:%s")) as date,
date_format(lastlogon,"%Y-%m-%d %H:%i:%s") as datetime
from userlog
order by name,datetime)t,(select @rank:=0, @test:=null,@rank2:=0, @date:=null)tmp;
select name,lastlogon,num_logontime,num_logonday from tmp_table;
-- #法2：無查詢，比法1稍難理解
drop table if exists tmp_table;
create temporary table tmp_table
SELECT name,lastlogon,
date(date_format(lastlogon,"%Y-%m-%d %H:%i:%s")) as date,
date_format(lastlogon,"%Y-%m-%d %H:%i:%s") as datetime,
@rank:=if(@test=name,@rank+1,1) as num_logontime,
@rank2:=if(@test=name,if(@date=date(date_format(lastlogon,"%Y-%m-%d %H:%i:%s")),@rank2,@rank2+1),1) as num_logonday,
-- if嵌套語句右蕊，第二重判斷也要有if琼稻。
@test:=name,
@date:=date(date_format(lastlogon,"%Y-%m-%d %H:%i:%s"))
FROM data.userlog
order by name,datetime;
select name,lastlogon,num_logontime,num_logonday from tmp_table;
-- #法3：hive的窗口函數(shù)方法
row_number() over(partition by name order by datetime) as num_logontime,
dense_rank() over(partition by name order by date) as num_logonday,
第七題
建表吮螺、查尋代碼
create table tableA (
qq int(20),
game varchar(20));
insert into tableA values
(10000,"a"),
(10000,"b"),
(10000,"c"),
(20000,"c"),
(20000,"d");
第一問解答
drop table if exists tableB;
create TEMPORARY table tableB(
select qq,group_concat(game separator"-") as game
from tableA group by qq);
select * from tableB;
第二問解答
mysql函數(shù)substring_index的用法
略復(fù)雜饶囚，先存著。鸠补。萝风。
第八題
建表、查尋代碼
解答第一問
解答第二問
select imp_date,is_new_state
,count(distinct qimei) as 領(lǐng)紅包人數(shù)
,sum(add_money)/count(distinct qimei) as 平均領(lǐng)取金額
,count(report_time)/count(distinct qimei) as 平均領(lǐng)取次數(shù)
from(
select a.imp_date,a.qimei,a.add_money,a.report_time,b.is_new
,(Case when b.is_new = 1 then '新用戶' when b.is_new = 0 then '老用戶' else '領(lǐng)取紅包但未登陸'end) as is_new_state
from tmp_liujg_packed_based a
Left join tmp_liujg_dau_based b
on a.imp_date = b.imp_date and a.qimei = b.qimei
where a.imp_date > '20190601')t
group by imp_date,is_new_state;
解答第三問
計算2019年3月以來的每個月紫岩，每個月按領(lǐng)紅包取天數(shù)為1规惰、2、3……30泉蝌、31天區(qū)分歇万，計算取每個月領(lǐng)取紅包的用戶數(shù)，人均領(lǐng)取金額勋陪，人均領(lǐng)取次數(shù)
select
left(imp_date,6) as 月份,
count(distinct imp_date) as 每月有紅包領(lǐng)取的天數(shù),
count(distinct qimei) as 每月領(lǐng)取紅包的用戶數(shù),
sum(add_money)/ count(distinct qimei) as 每月人均領(lǐng)取金額,
count(report_time)/count(distinct qimei) as 每月人均領(lǐng)取次數(shù)
from tmp_liujg_packed_based
where imp_date>='20190301'
group by left(imp_date,6);
解答第四問
計算2019年3月以來贪磺，每個月領(lǐng)過紅包用戶和未領(lǐng)紅包用戶的數(shù)量，平均月活躍天數(shù)（即本月平均活躍多少天）
Select
left(cc.imp_date,6) 月份,
cc.is_packet_user 紅包用戶,
Count(distinct cc.qimei) 用戶數(shù)量,
-- Count(is_packet_user) #不理解啥意思诅愚？每月活躍天數(shù),
Count(is_packet_user)/Count(distinct cc.qimei) 月活躍天
from(
Select a.imp_date, a.qimei,b.qimei hb_qimei,
Case when b.qimei is not null then '紅包用戶' else '非紅包用戶' end is_packet_user,
Case when b.qimei is not null then b.qimei else a.qimei end is_qimei
from tmp_liujg_dau_based a
Left join
(select distinct
left(imp_date,6) imp_date ,
qimei
from tmp_liujg_packed_based
where imp_date >= '20190301')b
On left(a.imp_date,6) = b.imp_date and a.qimei = b.qimei)cc
Group by left(cc.imp_date,6),cc.is_packet_user;
解答第五問
select distinct
left(a.imp_date,6) 月份,
a.qimei,
b.用戶注冊日期
from tmp_liujg_dau_based a
left join(
select qimei,min(imp_date) as 用戶注冊日期
from tmp_liujg_dau_based
where is_new=1 and imp_date >= '20190301'
group by qimei)b
on a.qimei=b.qimei
Where a.imp_date >='20190301'
order by 月份,qimei;
解答第六問
解答第七問
解答第八問
五級標(biāo)題
- 列表第一項
- 列表第二項
- 有序列表第一項
- 有序列表第二項
標(biāo)題
[圖片上傳失敗...(image-5bb63b-1582723711145)]
斜體
粗體
引用段落
