Chiến lược cho nhiều phép biến đổi

Phân tích Khám phá Dữ liệu bằng SQL

Christina Maimone

Data Scientist

Nhiều phép biến đổi

SELECT * FROM naics;

   id   |                  category                   | businesses 
--------+---------------------------------------------+------------
 111110 | Agriculture: Soybean Farming                |       4788
 111130 | Agriculture | Dry Pea and Bean Farming      |       3606
 111140 | Agriculture: Wheat Farming                  |       6393
 111150 | Agriculture - Corn Farming                  |      26469
 111160 | Agriculture: Rice Farming                   |        949
 111199 | Agriculture - All Other Grain Farming       |      15035
 111211 | Agriculture | Potato Farming                |        617
 611110 | Education - Elementary and Secondary        |     187859
 611210 | Education | Junior Colleges                 |       3961
 611310 | Education: Colleges and Universities        |      29148

CASE WHEN

-- Trường hợp cho từng ký tự :, -, và |

SELECT CASE WHEN category LIKE '%: %' THEN split_part(category, ': ', 1)

            WHEN category LIKE '% - %' THEN split_part(category, ' - ', 1)

            ELSE split_part(category, ' | ', 1)

        END AS major_category,  -- đặt bí danh cho kết quả

       sum(businesses)          -- cũng chọn số lượng doanh nghiệp

  FROM naics

 GROUP BY major_category;       -- Gom nhóm theo danh mục tạo ở trên

 major_category |  sum   
----------------+--------
 Education      | 220968
 Agriculture    |  57857

Bảng recode

Giá trị gốc: bảng fruit

 customer | fav_fruit 
----------+-----------
      349 | apple
      874 | Apple
      703 | apple
      667 | bannana
      622 | banana
      387 | BANANA
      300 | APPLES
      313 |  apple
      499 |  banana
      418 | apple
      841 | BANANA
      300 | APPLE
      754 | apple

Giá trị chuẩn hóa: bảng recode

 original | standardized  
----------+--------------
 APPLES   | apple
 apple    | apple  
 Apple    | apple
 bannana  | banana
 apple    | apple
  banana  | banana
 banana   | banana
 APPLE    | apple
  apple   | apple
 BANANA   | banana

Bước 1: CREATE TEMP TABLE

CREATE TEMP TABLE recode AS

  SELECT DISTINCT fav_fruit AS original,  -- giá trị gốc, lộn xộn

         fav_fruit AS standardized        -- giá trị chuẩn hóa mới

    FROM fruit;

Bảng ban đầu

SELECT * 
  FROM recode;

 original | standardized 
----------+--------------
 APPLES   | APPLES
 apple    | apple  
 Apple    | Apple
 bannana  | bannana
 apple    | apple
  banana  |  banana
 banana   | banana
 APPLE    | APPLE
  apple   |  apple
 BANANA   | BANANA
(10 rows)

Bước 2: UPDATE giá trị

UPDATE table_name
   SET column_name = new_value
 WHERE condition;

Bước 2: UPDATE giá trị

-- Mọi hàng: chuyển thường, bỏ khoảng trắng đầu/cuối

UPDATE recode 
   SET standardized=trim(lower(original));

-- Hàng cụ thể: sửa lỗi chính tả

UPDATE recode
   SET standardized='banana'
 WHERE standardized LIKE '%nn%';

-- Mọi hàng: bỏ ký tự s ở cuối

UPDATE recode
   SET standardized=rtrim(standardized, 's');

Bảng recode sau khi xử lý

SELECT * 
  FROM recode;

 original | standardized 
----------+--------------
 APPLES   | apple
 apple    | apple
 Apple    | apple
 apple    | apple
  banana  | banana
 banana   | banana
 APPLE    | apple
  apple   | apple
 BANANA   | banana
 bannana  | banana
(10 rows)

Bước 3: JOIN bảng gốc và recode

Chỉ dữ liệu gốc

SELECT fav_fruit, count(*) 
  FROM fruit 
 GROUP BY fav_fruit;

 fav_fruit | count 
-----------+-------
 APPLES    |     1
 apple     |     1
 apple     |     3
  banana   |     1
 BANANA    |     2
  apple    |     1
 APPLE     |     1
 bannana   |     1
 banana    |     1
 Apple     |     1
(10 rows)

Với giá trị đã chuẩn hóa

SELECT standardized, 
       count(*) 
  FROM fruit
       LEFT JOIN recode
       ON fav_fruit=original
 GROUP BY standardized;

 standardized | count 
--------------+-------
 apple        |     8
 banana       |     5
(2 rows)

Tóm tắt

CREATE TEMP TABLE với giá trị gốc
UPDATE để tạo giá trị chuẩn hóa
JOIN dữ liệu gốc với dữ liệu chuẩn hóa

Làm sạch dữ liệu Evanston 311!

Phân tích Khám phá Dữ liệu bằng SQL