Pencocokan pola

Membersihkan Data di Database PostgreSQL

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

Mengidentifikasi pola: contoh

 camis    |              name               | inspection_date | score | nta  | ... 
 ---------+---------------------------------+-----------------+-------+------+-----
 ...      | ...                             | ...             | ...   | ...  | ...
 41659848 | LA BRISA DEL CIBAO              | 01/30/2018      | 20    | QN26 | ...
 40961447 | MESON SEVILLA RESTAURANT        | 03/19/2019      | 50    | MN15 | ...
 50063071 | WA BAR                          | 05/23/2018      | 15    | MN17 | ...
 50034992 | EMPANADAS MONUMENTAL            | 06/21/2019      | 17    | MN35 | ...
 50095871 | ALPHONSO'S PIZZERIA & TRATTORIA | 01/16/2020      | 10    | MN28 | ...
 41104041 | THE SPARROW TAVERN              | 09/17/2019      | 13    | QN72 | ...
 50016937 | BURGER KING                     | 09/14/2018      | 12    | QN55 | ...
 50066469 | DARBAR'S CHICKEN & RIBS         | 08/07/2017      | 11    | QN55 | ...
 41195691 | F & J PINE RESTAURANT           | 05/02/2019      | 26    | BX49 | ...
 50015706 | EL RINCONCITO DE LOS SABORES    | 12/18/2019      | 20    | QN35 | ...
 ...      | ...                             | ...             | ..    | ...  | ...

¹ https://www1.nyc.gov/site/planning/data-maps/open-data/dwn-nynta.page

Mengidentifikasi pola: contoh

Prefiks valid untuk kode NTA

MN - Manhattan
BK - Brooklyn
BX - Bronx
QN - Queens
SI - Staten Island

Mengidentifikasi pola: contoh

Hasil tabel dengan kotak merah menyorot kode NTA tidak valid

Operator LIKE

SELECT * FROM restaurant_inspection WHERE nta LIKE 'QN544';

SELECT * FROM restaurant_inspection WHERE nta = 'QN544';

Operator LIKE

Karakter pencocokan pola

% - mencocokkan urutan 0+ karakter
_ (garis bawah) - mencocokkan 1 karakter

SELECT 
* 
FROM 
restaurant_inspection 
WHERE nta LIKE 'QN%';

SELECT 
* 
FROM 
restaurant_inspection 
WHERE nta LIKE 'QN%' 
AND nta NOT LIKE 'QN__';

Regular expression (RE)

Pencocokan pola dengan LIKE terbatas
Pola yang lebih spesifik bisa berguna
Regular Expression (RE) memungkinkan pencocokan pola yang lebih ekspresif

Operator SIMILAR TO

Menyediakan fungsi pencocokan pola tambahan

SELECT 
  camis, name, inspection_date, score, nta 
FROM 
  restaurant_inspection 
WHERE nta SIMILAR TO 'QN%' AND nta NOT SIMILAR TO 'QN__';

Dasar-dasar RE

Metakarakter	Penggunaan	Contoh RE	Contoh Kecocokan
`\d`	mencocokkan digit (0–9)	`\d\d\d`	'345'
`?`	mencocokkan 0 atau 1 dari karakter sebelumnya	`x\d?`	'x5'
`+`	mencocokkan satu atau lebih dari karakter sebelumnya	`\d+`	'10'
`*`	mencocokkan karakter apa pun 0+ kali	`\d*`	'3081'
`[]`	mencocokkan karakter apa pun di dalam kurung siku	`[a-z]`	'f'

Menggunakan RE dengan SIMILAR TO

SELECT 
    camis, name, inspection_date, score, nta 
FROM 
    restaurant_inspection 
WHERE nta SIMILAR TO 'QN%' AND nta NOT SIMILAR TO 'QN__';

SELECT 
    camis, name, inspection_date, score, nta 
FROM 
    restaurant_inspection 
WHERE 
    nta NOT SIMILAR TO '[A-Z][A-Z]\d\d';

 camis    |        name        | inspection_date | score | nta  
 ---------+--------------------+-----------------+-------+------
 41659848 | LA BRISA DEL CIBAO | 01/30/2018      | 20    | Q26
 41104041 | THE SPARROW TAVERN | 09/17/2019      | 13    | QN723

Ayo berlatih!

Membersihkan Data di Database PostgreSQL