【面試題】
下表是某金融App的資料，請結合Excel資料集中的資料完成下列問題（某金融公司面試題，文末有資料下載下傳 ）
原始資料如下所示：
為友善後期分析，我們對資料進行分類。資料通常分為3類：使用者資料、行為資料、産品資料。
使用者資料指使用者基本情況，行為資料指使用者做過什麼，産品資料是指平台賣的東西。
原始資料中年齡、性别、手機省份、手機城市、注冊時間、使用者注冊終端、使用者注冊管道、會員級别等資訊為使用者資料，其餘資料為行為資料，無産品資料。
因為行為資料過多不利于分析，我們把行為資料中與最近一次登陸相關的資訊提取出來，列為最近登入資料。
把是否...7天内...類的問題提取出來，作為活躍資料。
行為資料如下：
問題：
1.了解資料
如果将該資料建立一個資料庫，有哪些表，各表之間的關系是什麼？
2.資料整理
假設該Excel資料是一個資料表（Datatable），請用SQL寫出：
（1）最近一次登入城市各有多少使用者在表中？
（2）“使用者ID”之間的可能關系？（比如家庭成員，情侶等）
（3）請找出資料表中的異常值，并以此闡述你如何控制資料品質并以本資料為例設計資料品質報表。
【解題思路】
如果将該資料建立一個資料，有哪些表，各表之間的關系是什麼？
和面試官溝通該金融App後，了解了該産品的業務流程，畫出下圖：
拆分為4個表（也就是Excel對應的各個sheet表）：
（1）“使用者資訊”表中包含使用者id，年齡，性别等基本資訊。
其中使用者id已進行加密，手機省份和手機城市是注冊所在地，使用者注冊終端是注冊時的終端（電腦端web、蘋果手機、安卓手機），使用者注冊管道展示的是管道ID。
（2）“最近登入”表中是最近一次登陸ip，展示的是已加密的ip。
（3）“行為資料”表中，投資金額經過等比例放大。
表中字段含義，首投距今時間（天）是固收(含債轉)_首次投資時間距今天的時間。
最近一次投資距今時間（天）是固收(含債轉)_最近一次投資時間 距今天的時間。
首投距注冊時長（天）是首次投資距離注冊時間，以天為機關。
使用者浏覽産品期限傾向是指固收含轉讓區（産品期限：1-3,4-6,7-12,12以上（PC+H5+APP））。
使用者浏覽産品利率傾向是指固收含轉讓區（利率：(0,4%],(4%,5.5%],(5.5%,6%],(6%,7.5%],7.5%以上（PC+H5+APP））。
（4）“活躍資料”表中，本月是否有大額回款指本月回款額度1萬及以上。
表中字段含義，
是否通路7天内未注冊：首次通路到注冊時間>7天，或有通路已超過7天但未注冊。
是否注冊7天内未充值：注冊到首次充值時間>7天，或有注冊已超過7天但未充值。
是否注冊7天内未投資：注冊到首次投資時間>7天，或有注冊已超過7天但未投資。
是否托管7天内未充值：托管到首次充值時間>7天，或有托管已超過7天但未充值。
是否托管7天内未投資：托管到首次投資時間>7天，或有托管已超過7天但未投資。
是否充值7天内未投資：最近一次充值到最近一次投資時間>7天，或最近一次充值已超過7天但未投資。
根據上面的業務分析，建立資料庫，建立4個表有使用者資料、行為資料、最近登入資料、活躍資料表，各表之間以使用者id為主鍵聯結，關系如下圖。
“最近一次登入”在“最近登陸資料“表中。
“各有”，翻譯過來就是“每個城市”。涉及到“每個”到業務問題，要想到《猴子 從零學會SQL》裡講過的用“分組彙總”來實作。
這裡分組按使用者id，彙總使用count函數進行計數。
select 最近一次登入城市,count(使用者id) as 人數 from 最近登陸資料 group by 最近一次登入城市; 輸出結果（資料過多僅展示前8行資料）：
使用多元度拆解分析方法來拆解問題：當最近登入ip相同時，使用者之間可能會存在關系。是以，分兩步解決該業務問題。
（1）找出ip重複的資料；
（2）找出重複ip對應的使用者資訊。也就是輸出使用者id、性别、年齡，最近一次登陸ip等資訊，并對最近一次登陸ip進行升序排列。
第1步：找出ip重複的資料
“最近一次登入ip”在“最近登陸資料表“中。
該問題是“找重複資料”類問題，按“最近一次登陸ip”分組（group by），然後使用having來篩選出每組裡面次數>1次的就是重複資料。
select 最近一次登陸ip from 最近登陸資料 group by 最近一次登陸ip having count(最近一次登陸ip)>1; 第2步：找出重複ip對應的使用者資訊
輸出使用者id、性别，年齡，最近一次登陸ip等資訊。
select 使用者id,最近一次登陸ip FROM 最近登陸資料 where 最近一次登陸ip in( select 最近一次登陸ip from 最近登陸資料 group by 最近一次登陸ip having count(最近一次登陸ip)>1 ); 第3步：聯結使用者資料表，輸出資訊
把使用者資料作為臨時表a，第二步得出的結果作為臨時表b，并對所有資料進行升序排列。
select a.使用者id,a.性别,a.年齡,b.最近一次登陸ip from 使用者資料 as a right join( select 使用者id,最近一次登陸ip from 最近登陸資料 where 最近一次登陸ip in( select 最近一次登陸ip from 最近登陸資料 group by 最近一次登陸ip having count(最近一次登陸ip)>1)) as b on a.使用者id=b.使用者id order by b.最近一次登陸ip asc; 查詢結果：
可以看出使用者id（1、2）具有相同ip位址，都為女性，年齡相差20歲，可以推測是母女關系。
使用者id（3、4）具有相同ip位址，性别一男一女，年齡相差6歲，可以推測是夫妻關系。
使用者id（5、6）具有相同ip位址，性别一男一女，年齡相差8歲，可以推測是夫妻關系。
使用者id（7、8）具有相同ip位址，性别一男一女，年齡相差3歲，可以推測是夫妻關系。
根據《描述統計分析》裡講過的異常值方法，我們可以使用Tukey's test方法找出宜昌至的範圍。
我們以該案例中的“年齡”為例，展示如何找出異常值。
第一步：計算出上四分位數
首先我們增加一列行号，使用“@”自定義“行号”這一變量，并用指派運算符：：=對“@行号”指派為0。
select @行号 := 0; 結果如下：
然後，我們使用内聯結把原表格和新增的一列聯結在一起，形成新的表格，記為臨時表r。
select 年齡,@行号 := 0 from 使用者資料 inner join ( select @行号 := 0 ) as c; 然後，用指派運算符“：=”指派行号為“@行号：=@行号+1”，并該列名為行号，進行升序排列，把該表結果記為臨時表t。
select 年齡,@行号 := @行号 + 1 as 行号 from 使用者資料 inner join ( select @行号 := 0 ) as c order by 年齡 asc; 最後，使用sql的floor函數來計算下四分位數，floor函數：向下舍入為指定小數位數。
select avg(年齡) as 下四分位數 from( select 年齡,@行号 := @行号 + 1 as 行号 from 使用者資料 inner join ( select @行号 := 0 ) as c order by 年齡 asc ) as d where 行号 = floor(( @行号+1) /4 ); 同理，使用3*（n+1）/4可以用來計算上四分位數。查詢結果如下：
第二步，用Tukey's test方法中k=1.5來找出中度異常值。
可以使用where函數找出異常值——在最大估算值和最小估算值之外的資料。
select 年齡,count(*) as 人數,count(*)/5000 as 人數占比 from 使用者資料 where 年齡>(48+1.5*(48-32)) or 年齡<(32-1.5*(48-32)) group by 年齡; 查詢結果如下：
用同樣的方法我們可以計算出投資金額的異常值。結果如下（因資料較多隻選取部分資料呈現）：
第三步：制作資料品質報表，以年齡資料為例。
把第二步中得到的結果作為臨時表e，用sum函數計算出年齡異常值的總人數與總人數占比。
select 年齡,sum(人數) as 總人數,sum(人數)/5000 as 總人數占比 from 臨時表e ; 按照上述步驟可得投資金額異常值的總人數與總人數占比，結果如下：
資料品質報表如下：
異常值的發生原因可以分為兩類：人為異常值和自然異常值。
自然異常值不是由于錯誤産生的，無需避免。
人為異常值可能由于資料輸入錯誤，故意填寫錯誤，資料處理錯誤等原因産生，這些情況是可以盡量避免的，這就要求我們做到以下幾點：
- 認真細心。避免資料輸入錯誤。
- 做好使用者宣傳，盡量填寫真實資訊。
- 提高提取資料的正确率，確定資料來源準确。
【本題考點】
1.考察對業務的了解能力，拿到資料以後，不是立馬開始分析，而是要先了解資料，對資料分類。
2.“每個問題”要想到用分組彙總來實作。
3.查找出重複資料。對資料項進行分組，找出數量大于2的資料即為重複值。
4.利用sql計算四分位數，找出異常值。增加一列行号并升序排列，利用公式取出上四分位數和下四分位數，找出最小和最大估計值，在此範圍外的即為異常值。
5.excel觀察資料特征：平均數、中位數、衆數。
6.掌握常用的分析方法解決問題的能力。