Birden Çok Dönüşüm için Stratejiler

SQL ile Keşifsel Veri Analizi

Christina Maimone

Data Scientist

Birden çok dönüşüm

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

-- :, -, ve | için ayrı durumlar

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,  -- sonucu takma adla belirt

       sum(businesses)          -- işletme sayısını da seç

  FROM naics

 GROUP BY major_category;       -- Yukarıda oluşturulan kategorilere göre grupla

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

Yeniden kodlama tablosu

Orijinal değerler: fruit tablosu

 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

Standartlaştırılmış değerler: recode tablosu

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

Adım 1: CREATE TEMP TABLE

CREATE TEMP TABLE recode AS

  SELECT DISTINCT fav_fruit AS original,  -- orijinal, dağınık değerler

         fav_fruit AS standardized        -- yeni standartlaştırılmış değerler

    FROM fruit;

İlk tablo

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)

Adım 2: Değerleri UPDATE etme

UPDATE table_name
   SET column_name = new_value
 WHERE condition;

Adım 2: Değerleri UPDATE etme

-- Tüm satırlar: küçük harfe çevir, uç boşlukları kaldır

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

-- Belirli satırlar: yazım hatasını düzelt

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

-- Tüm satırlar: s harfini sonda kaldır

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

Ortaya çıkan recode tablosu

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)

Adım 3: Orijinal ve recode tablolarını JOIN etme

Yalnızca orijinal

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)

Yeniden kodlanmış değerlerle

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

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

Özet

Orijinal değerlerle CREATE TEMP TABLE
Standart değerler için UPDATE
Orijinali standartlaştırılmış veriyle JOIN et

Evanston 311 verilerini temizleyin!

SQL ile Keşifsel Veri Analizi