การแบ่งข้อมูลในคอลัมน์

การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

การแบ่งคอลัมน์

 camis    | inspection_date |                    violation                    | ... 
 ---------+-----------------+-------------------------------------------------+-----
 ...      | ...             | ...                                             | ...
 50038736 | 03/29/2018      | 09B Thawing procedures                          | ...
 50033304 | 12/18/2019      | 02B Hot food item not held at or above 140º ... | ...
 50081658 | 12/13/2018      | 06F Wiping cloths soiled or not stored in sa... | ...
 50033733 | 02/12/2019      | 10B Plumbing not properly installed or maint... | ...
 40559634 | 08/22/2017      | 04N Filth flies or food/refuse/sewage-associ... | ...
 ...      | ...             | ...                                             | ...
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

ค้นหาตำแหน่งเริ่มต้นของ substring ด้วย STRPOS()

STRPOS(source_string, search_string)

ไดอะแกรมของสตริง violation "09B Thawing procedures" แสดงตัวเลขดัชนีด้านล่าง โดย 1 อยู่ที่ตัว 0, 4 อยู่ที่ช่องว่างแรก และ 22 อยู่ที่ตัว s ท้ายสตริง

SELECT
  STRPOS('09B Thawing procedures', ' ');

4
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

ค้นหาตำแหน่งเริ่มต้นของ substring ด้วย STRPOS()

ไดอะแกรมของสตริง violation "09B Thawing procedures" แสดงตัวเลขดัชนีด้านล่าง โดย 1 อยู่ที่ตัว 0, 4 อยู่ที่ช่องว่างแรก และ 22 อยู่ที่ตัว s ท้ายสตริง

SELECT
  STRPOS('09B Thawing procedures', '?');

0
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

ค้นหาตำแหน่งเริ่มต้นของ substring ด้วย STRPOS()

ไดอะแกรมของสตริง violation "09B Thawing procedures" แสดงตัวเลขดัชนีด้านล่าง โดย 1 อยู่ที่ตัว 0, 4 อยู่ที่ช่องว่างแรก และ 22 อยู่ที่ตัว s ท้ายสตริง

SELECT
  STRPOS('09B Thawing procedures', ' ');

4
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

ดึง substring ด้วย SUBSTRING()

SUBSTRING(source_string FROM start_pos FOR num_chars)

การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

ดึง substring ด้วย SUBSTRING()

SUBSTRING('Homerun' FROM 1 FOR 4) → Home

ไดอะแกรมของสตริง violation "09B Thawing procedures" แสดงตัวเลขดัชนีด้านล่าง โดย 1 อยู่ที่ตัว 0, 4 อยู่ที่ช่องว่างแรก และ 22 อยู่ที่ตัว s ท้ายสตริง

SELECT
  SUBSTRING(
      '09B Thawing procedures'
      FROM 1
      FOR STRPOS('09B Thawing procedures', ' ') - 1
  );

09B
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

ดึง substring ด้วย SUBSTRING()

ไดอะแกรมของสตริง violation "09B Thawing procedures" แสดงตัวเลขดัชนีด้านล่าง โดย 1 อยู่ที่ตัว 0, 4 อยู่ที่ช่องว่างแรก และ 22 อยู่ที่ตัว s ท้ายสตริง ลูกศรสีน้ำเงินคู่หนึ่งชี้ไปยังส่วนที่เป็นคำอธิบาย violation

สิ่งที่ต้องการ:

  • ตำแหน่งเริ่มต้นของคำอธิบาย violation
  • จำนวนอักขระในคำอธิบาย
SELECT
  STRPOS('09B Thawing procedures', ' ') + 1;

5
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

คำนวณความยาวของสตริงด้วย LENGTH()

ไดอะแกรมของสตริง violation "09B Thawing procedures" แสดงตัวเลขดัชนีด้านล่าง โดย 1 อยู่ที่ตัว 0, 4 อยู่ที่ช่องว่างแรก และ 22 อยู่ที่ตัว s ท้ายสตริง ข้อความ "จำนวนอักขระ?" อยู่เหนือส่วนคำอธิบาย violation

LENGTH(string) → INTEGER

SELECT LENGTH('hello')

5
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

คำนวณความยาวของสตริงด้วย LENGTH()

LENGTH('09B Thawing procedures') → 22

STRPOS('09B Thawing procedures', ' ') → 4

LENGTH('09B Thawing procedures') - STRPOS('09B Thawing procedures', ' ') → 18

LENGTH('Thawing procedures') → 18

การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

คำนวณความยาวของสตริงด้วย LENGTH()

SELECT 
    LENGTH('09B Thawing procedures') - 
    STRPOS('09B Thawing procedures', ' ');

18

ไดอะแกรมของสตริง violation "09B Thawing procedures" แสดงตัวเลขดัชนีด้านล่าง โดย 1 อยู่ที่ตัว 0, 4 อยู่ที่ช่องว่างแรก และ 22 อยู่ที่ตัว s ท้ายสตริง ลูกศรสีน้ำเงินคู่หนึ่งชี้ไปยังส่วนที่เป็นคำอธิบาย violation

การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

นำทุกอย่างมารวมกัน

SELECT 
    SUBSTRING(
      '09B Thawing procedures'
      FROM
        STRPOS('09B Thawing procedures', ' ')
        + 1
      FOR
        LENGTH('09B Thawing procedures')
        - STRPOS('09B Thawing procedures', ' ')
    );

Thawing procedures
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

การแบ่งคอลัมน์ violation

SELECT 
    camis, 
    inspection_date, 

    SUBSTRING(
      violation 
      FROM 1 
      FOR STRPOS(violation, ' ') - 1
    ) AS violation_code, 

    SUBSTRING(
      violation 
      FROM STRPOS(violation, ' ') + 1 
      FOR LENGTH(violation) - STRPOS(violation, ' ')
    ) AS violation_description 
FROM 
    restaurant_inspection;
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

การแบ่งคอลัมน์ violation

 camis    | inspection_date |                    violation                    | ... 
 ---------+-----------------+-------------------------------------------------+-----
 ...      | ...             | ...                                             | ...
 50038736 | 03/29/2018      | 09B Thawing procedures                          | ...
 50033304 | 12/18/2019      | 02B Hot food item not held at or above 140º ... | ...
 50081658 | 12/13/2018      | 06F Wiping cloths soiled or not stored in sa... | ...
 50033733 | 02/12/2019      | 10B Plumbing not properly installed or maint... | ...
 40559634 | 08/22/2017      | 04N Filth flies or food/refuse/sewage-associ... | ...
 ...      | ...             | ...                                             | ...
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

การแบ่งคอลัมน์ violation

 camis    | inspection_date | violation_code |            violation_description            | ... 
 ---------+-----------------+----------------+---------------------------------------------+-----
 ...      | ...             | ...            | ...                                         | ...
 50038736 | 03/29/2018      | 09B            | Thawing procedures                          | ...
 50033304 | 12/18/2019      | 02B            | Hot food item not held at or above 140º ... | ...
 50081658 | 12/13/2018      | 06F            | Wiping cloths soiled or not stored in sa... | ...
 50033733 | 02/12/2019      | 10B            | Plumbing not properly installed or maint... | ...
 40559634 | 08/22/2017      | 04N            | Filth flies or food/refuse/sewage-associ... | ...
 ...      | ...             | ...            | ...                                         | ...
การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

มาฝึกกันเถอะ!

การทำความสะอาดข้อมูลในฐานข้อมูล PostgreSQL

Preparing Video For Download...