การตรวจหาค่าที่ไม่ถูกต้อง

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

Darryl Reeves, Ph.D.

Assistant Professor, Long Island University - Brooklyn

ค่าข้อมูลที่ไม่ถูกต้อง

  camis   |              name               | inspection_date | score |            inspection_type            | ... 
 ---------+---------------------------------+-----------------+-------+---------------------------------------+-----
 ...      | ...                             | ...             | ...   | ...                                   | ...
 41659848 | LA BRISA DEL CIBAO              | 01/30/2018      | 20    | Cycle Inspection / Initial Inspection | ...
 40961447 | MESON SEVILLA RESTAURANT        | 03/19/2019      | 50    | Cycle Inspection / Initial Inspection | ...
 50063071 | WA BAR                          | 05/23/2018      | 15    | Cycle Inspection / Initial Inspection | ...
 50034992 | EMPANADAS MONUMENTAL            | 06/21/2019      | 17    | Cycle Inspection / Re-inspection      | ...
 50095871 | ALPHONSO'S PIZZERIA & TRATTORIA | 01/16/2020      | 10    | Cycle Inspection / Initial Inspection | ...
 ...      | ...                             | ...             | ...   | ...                                   | ...

  camis   |             name             | inspection_date | score |                 inspection_type                 | ... 
 ---------+------------------------------+-----------------+-------+-------------------------------------------------+-----
 ...      | ...                          | ...             | ...   | ...                                             | ...
 41104041 | THE SPARROW TAVERN           | 09/17/2019      | 13    | Cycle Inspection / Initial Inspection           | ...
 50016937 | BURGER KING                  | 09/14/2018      | 12    | Cycle Inspection / Re-inspection                | ...
 50066469 | DARBAR'S CHICKEN & RIBS      | 08/07/2017      | 11    | Pre-permit (Operational) / Reopening Inspection | ...
 41195691 | F & J PINE RESTAURANT        | 05/02/2019      | 26    | Cycle Inspection / Initial Inspection           | ...
 50015706 | EL RINCONCITO DE LOS SABORES | 12/18/2019      | A     | Cycle Inspection / Initial Inspection           | ...
 ...      | ...                          | ...             | ...   | ...                                             | ...

การจัดการข้อมูลที่ไม่ถูกต้องด้วย Pattern Matching

SELECT
  camis,
  name,
  inspection_date,
  score
FROM
  restaurant_inspection
WHERE
  score NOT SIMILAR TO '\d+';

การจัดการข้อมูลที่ไม่ถูกต้องด้วย Pattern Matching

คิวรีกรองเฉพาะอักขระที่ไม่ใช่ตัวเลขเท่านั้น
ไม่ได้จำกัดความยาวของค่า

SELECT
  camis,
  name,
  inspection_date,
  score
FROM
  restaurant_inspection
WHERE
  score NOT SIMILAR TO '\d{1}' AND
  score NOT SIMILAR TO '\d{2}' AND
  score NOT SIMILAR TO '\d{3}';

การใช้ Type Constraint

คอลัมน์เก็บค่าจำนวนเต็ม
คอลัมน์ควรไม่อนุญาตให้มีค่าที่ไม่ใช่จำนวนเต็ม

ALTER TABLE restaurant_inspection
ALTER COLUMN score TYPE SMALLINT USING score::smallint;

SMALLINT: ค่าตั้งแต่ -32,768 ถึง 32,767
คำสั่ง USING ระบุวิธีแปลงค่าเดิม

ทบทวน: พื้นฐาน Regular Expressions

Metacharacter	การใช้งาน	ตัวอย่าง RE	ตัวอย่างที่ตรงกัน
`\d`	ตรงกับตัวเลข (0-9)	`\d\d\d`	'345'
`?`	ตรงกับ 0 หรือ 1 ของอักขระก่อนหน้า	`x\d?`	'x5'
`+`	ตรงกับ 1 ครั้งขึ้นไปของอักขระก่อนหน้า	`\d+`	'10'
`*`	ตรงกับอักขระใดก็ได้ 0 ครั้งขึ้นไป	`\d*`	'3081'
`[]`	ตรงกับอักขระใดก็ได้ภายในวงเล็บ	`[a-z]`	'f'

Type Constraint ช่วยให้ตรวจสอบช่วงค่าได้

ALTER TABLE restaurant_inspection
ALTER COLUMN score TYPE SMALLINT USING score::smallint;

SELECT
  camis,
  name,
  inspection_date,
  score
FROM
  restaurant_inspection
WHERE
  score < 0;

Type Constraint ช่วยให้ตรวจสอบช่วงค่าได้

ALTER TABLE restaurant_inspection
ALTER COLUMN score TYPE SMALLINT USING score::smallint;

SELECT
  camis,
  name,
  inspection_date,
  score
FROM
  restaurant_inspection
WHERE
  score <= -1;

Type Constraint ช่วยให้ตรวจสอบช่วงค่าได้

ALTER TABLE restaurant_inspection
ALTER COLUMN score TYPE SMALLINT USING score::smallint;

SELECT
  camis,
  name,
  inspection_date,
  score
FROM
  restaurant_inspection
WHERE
  score < 0 OR
  score > 100;

Type Constraint ช่วยให้ตรวจสอบช่วงค่าได้

ALTER TABLE restaurant_inspection
ALTER COLUMN score TYPE SMALLINT USING score::smallint;

SELECT
  camis,
  name,
  inspection_date,
  score
FROM
  restaurant_inspection
WHERE
  score < 0 OR
  score >= 101;

ตัวดำเนินการ BETWEEN

SELECT
  camis, name, inspection_date, score
FROM
  restaurant_inspection
WHERE
  score NOT BETWEEN 0 AND 100;

  camis   |          name           | inspection_date | score 
 ---------+-------------------------+-----------------+-------
 ...      | ...                     | ...             |   ...
 41702543 | TROPICAL GRILL          | 05/14/2018      |   109
 50074058 | PAD THAI                | 08/01/2018      |   101
 50085349 | DON CHILE MEXICAN GRILL | 12/04/2018      |   124
 50092932 | ENERGY JUICE BAR        | 06/24/2019      |   102
 41702543 | TROPICAL GRILL          | 05/14/2018      |   109
 50034653 | KAI FAN ASIAN CUISINE   | 12/06/2019      |    -1
 ...      | ...                     | ...             |   ...

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

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