使用Mysql工具進行資料清洗

資料資源

https://gitee.com/pingfanrenbiji/resource/blob/master/%E9%87%91%E8%9E%8D%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90/%E7%AC%AC%E4%B8%80%E7%AB%A0/database1/transaction_info.csv

將csv匯入資料庫

一路continue

有一份追加資料

https://gitee.com/pingfanrenbiji/resource/blob/master/%E9%87%91%E8%9E%8D%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90/%E7%AC%AC%E4%B8%80%E7%AB%A0/database1/insert.csv

insert.csv資料欄位和transaction_info.csv資料欄位一致

將insert.csv裡面的資料追加到transaction_info表中

同樣的匯入方式 匯入insert.csv

資料清洗

刪除重複資料

分析：
先分組、再計數、再篩選、再去重

select Transaction_index from transaction_info GROUP BY Transaction_index  HAVING count(Transaction_index)>1;
delete from transaction_info where Transaction_index='2009-01-02_0039';

修改null值為0

# 更新的邏輯是 先查詢 再更新
update transaction_info set Amount=0,Last_Amount=0 where Amount is null;
update transaction_info set Amount=0,Last_Amount=0 where Last_Amount is null;

修正異常值

# 查詢國家的異常值 對國家進行分組 然後統計個數 
select Country,count(Country) from transaction_info GROUP BY Country;
# 從而可以找到異常值
Country in (1,2,3)
select * from transaction_info where Country in (1,2,3);
# 獲取城市為Houston的國家資訊 檢視該城市屬於哪個國家
SELECT DISTINCT Country FROM transaction_info WHERE City = 'Houston';
# 將異常值更新為正常的國家名稱
UPDATE transaction_info SET Country = 'United States' WHERE City = 'Houston' AND Country = '3';

熟練使用mysql常用句式

• 資料

https://gitee.com/pingfanrenbiji/resource/tree/master/%E9%87%91%E8%9E%8D%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90/%E7%AC%AC%E4%B8%80%E7%AB%A0/database2

• 案例

需求:

1、購買了產品1的使用者資料 標是查詢使用者資訊 所以使用者表是基礎表
2、Trans_Account_age(交易日期距離註冊日期的自然月間隔)、days_since_last_login(交易距離上次登入的天數)
3、分組 在agegroup欄位準確分組Trans_Account_age資料，以 “0~1”表示0至1（包含1）， “1~12”表示1至12（包含12）， “12+”表示12以上

SQL:

SELECT U.* ,T.*
, TimeStampDiff (MONTH, U.Account_Created, T.Transaction_date) AS Trans_Account_age #交易日期距離註冊日期的自然月間隔
, TimeStampDiff (DAY, T.Last_login, T.Transaction_date) AS days_since_last_login #交易距離上次登入的天數
, CASE 
 WHEN TimeStampDiff (MONTH, U.Account_Created, T.Transaction_date) IN (0,1) THEN '0~1'
 WHEN TimeStampDiff (MONTH, U.Account_Created, T.Transaction_date) > 1 AND TimeStampDiff (MONTH, U.Account_Created, T.Transaction_date) <= 12 THEN '1~12'
 WHEN TimeStampDiff (MONTH, U.Account_Created, T.Transaction_date) > 12 THEN '12+'
 ELSE ''
 END AS agegroup
FROM user_info AS U
LEFT JOIN transaction_info AS T ON T.ID = U.ID
WHERE T.Product = 'Product1'
;