SQL資料清洗

SQL語法進行資料清洗

• 前言：ML模型建模前必需先獲得乾淨得資料，如此一來才能根據乾淨的資料建立可靠的模型，以滿足業務部門的需求。
• 必要性：使用Pandas進行資料清洗，必需先由SQL資料庫讀取資料，對於大量的資料處理相當不便，學習SQL語法使用SQL Client對資料進行處理是更有效的做法。
• 參考資料來源：5大SQL資料清洗方法

刪除與重命名列

刪除列

Python:

                #  刪除單一列
                df.drop(col_names, axis=1, inplace=True)
                #  刪除多個列
                df.drop(columns=['col1', 'col2'], axis=1, inplace=True)
            

SQL:

                -- 通常不具備刪除的權限，固將該表應刪的col的其他所有col篩選出來至暫存表中
                CREATE TEMPORARY TABLE temp_table_name AS
                SELECT column1, column2, ...
                FROM original_table_name
                WHERE condition;

                -- 篩出特定的col，並將其刪除(具有原表權限，危險！)
                SELECT col_names FROM Table_Name;
                ALTER TABLE Table_Name DROP COLUMN col_names;
            

重命名列

Python:

                df.rename(index={'row1':'A'}, columns ={'col1':'B'})
            

SQL:

                SELECT col_names AS col_name_B FROM Table_Name;
            

重複值與缺失值處理

重複值

Python:

                #  刪除重複值
                df.drop_duplicates(inplace=True)
            

SQL:

                -- 選出columns的唯一值
                SELECT DISTINCT col_name, col_name_2, ... FROM Table_Name
                -- 聚合
                SELECT col_name, COUNT(*) AS count FROM Table_Name GROUP BY col_name
            

缺失值

Python:

                df.fillna(value)
                df.combine_first(df2)
            

SQL:

                -- IFNULL() 是一個 MySQL 特有的函數，它的作用是檢查第一個參數（col_name）是否為 NULL，如果是，則返回第二個參數（0），否則返回第一個參數。
                SELECT IFNULL(col_name, 0) FROM Table_Name;

                -- 是一個 ANSI SQL 標準函數，因此具有更好的跨資料庫相容性。它接受多個參數。依序評估參數，返回第一個不是 NULL 的表達式。如果所有參數都是 NULL，則返回 NULL。
                SELECT COALESCE(col_name, col_name_A, 0) AS value FROM Table_Name;

                -- CASE 表達式是 SQL 中最通用且功能強大的條件邏輯結構，它可以處理各種複雜的條件判斷。當 col_name 的值是 NULL 時，返回 0；否則，返回 col_name 本身的值。
                SELECT CASE WHEN col_name IS NULL THEN 0 ELSE col_name END FROM Table_Name;
            

替換字串空格、清洗垃圾字元、字串拼接、分隔等字串處理

Python:

                # 空格處理
                df[col_name] = df[col_name].str.lstrip()
                # 垃圾處理
                df[col_name].replace(' &#.*', '', regex=True, inplace=True)
                # 字串分割
                df[col_name].str.split('分割符')
                # 字串拼接
                df[col_name].str.cat()
            

SQL:

                -- 空格处理：ltrim 函數用於移除字串開頭（左側）的空格或指定字元。如果沒有指定要移除的字元，它會預設移除開頭的空格
                SELECT LTRIM(col_name) FROM Table_name 
                -- *%d等垃圾符處理：使用正規表達式 (Regular Expression) 來搜尋並替換字串中符合模式的部分
                SELECT regexp_replace(col_name, 正規式) FROM Table_name 
                -- 字串分割： SPLIT 函數用於將一個字串根據指定的分隔符號分割成一個陣列 (或多個欄位，視資料庫系統而定)。
                SELECT SPLIT(col_name, '分割符') FROM Table_name 
                -- 字串拼接：concat_ws (concatenate with separator) 函數用於將多個字串拼接起來，並在每個字串之間插入指定的分隔符號。它會忽略任何為 NULL 的字串。
                SELECT CONCAT_WS(col_name, '拼接符') FROM Table_name
            

合併處理

左右合併

Python:

                pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
                        left_index=False, right_index=False, sort=True,
                        suffixes=('_x', '_y'), copy=True, indicator=False,
                        validate=None)
                pd.concat([df1,df2])
            

SQL:

                SELECT A.*,B.* FROM Table_a A JOIN Table_b B ON A.id = B.id
                SELECT A.* FROM Table_a A LEFT JOIN Table_b B ON A.id = B.id                
            

上下合併

Python:

                # df2合併入df1，不保留index、不進行排序
                df1.append(df2, ignore_index=True, sort=False)
            

SQL:

                -- Union：對兩個結果集進行並集操作，不包括重複行，同時進行預設規則的排序；
                -- Union All：對兩個結果集進行並集操作，包括重複行，不進行排序；

                SELECT A.* FROM Table_a A 
                UNION
                SELECT B.* FROM Table_b B 

                -- Union 因為會將各查詢子集的記錄做比較，故比起Union All ，通常速度都會慢上許多。一般來說，如果使用Union All能滿足要求的話，務必使用Union All。
            

分組排序

行銷排行榜

應用場景：比較各個門店之類銷售額最佳的產品 創造模擬資料：

                df = pd.DataFrame({
                    'Sale_store':   [['A','B','C'][i] for i in np.random.randint(0,3,100)],
                    'Product': [['apple','orange','banana','grape','peach','melon','kiwi'][i] for i in np.random.randint(0,7,100)],
                    'Sale_Num':   [np.random.randint(100,1000) for i in range(100)]
                })
            

處理範例：
Python:

                # 以Sale_store聚合並選擇Sale_Num，使用transform函數使其在不改變df.shape情況，將前面的分組結果選擇Sale_Num欄位進行組別Ranking，將結果套用回每一個row
                df['Rank'] = df.groupby(by=['Sale_store'])['Sale_Num'].transform(lambda x: x.rank(ascending=False))
            

SQL:

                -- row_number(): 這是一個視窗函數，它會為結果集中的每一列分配一個連續的、唯一的整數。
                -- over(...): over 子句定義了視窗函數的應用範圍（即「視窗」）。
                -- partition by Sale_store：這會將資料集根據 Sale_store 欄位進行分組。換句話說，row_number() 會為每個不同的 Sale_store 重新從 1 開始編號。這就像 Pandas 中的 groupby('Sale_store')。
                -- order by Sale_Num desc: 在每個 Sale_store 的分組內，row_number() 會根據 Sale_Num 欄位進行排序，並且是 降冪 (descending) 排序。這意味著銷售數量最高的記錄會得到排名 1，次高的得到排名 2，以此類推。這類似於 Pandas 中 rank(ascending=False) 的邏輯。
                -- rk: 這是為 row_number() 函數計算出的排名結果所取的別名，您可以透過 rk 來引用它。

                SELECT 
                * 
                FROM
                (
                SELECT 
                    *，
                    row_number() OVER(PARTITION BY Sale_store ORDER BY Sale_Num DESC) rk
                FROM 
                    table_name
                ) b WHERE b.rk = 1

                -- 這個內部查詢會產生一個新的臨時結果集（通常稱為「派生表」或「子查詢」），其中包含了 table_name 的所有原始欄位，以及一個新的 rk 欄位，該欄位表示在每個商店內，根據銷售數字降冪排列的行號。