Correspondance de motifs

Nettoyer des données dans des bases de données PostgreSQL

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

Repérer des motifs : un exemple

 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

Repérer des motifs : un exemple

Préfixes valides du code NTA

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

Repérer des motifs : un exemple

Résultats de table avec un encadré rouge sur un code NTA non valide

L'opérateur LIKE

SELECT * FROM restaurant_inspection WHERE nta LIKE 'QN544';

SELECT * FROM restaurant_inspection WHERE nta = 'QN544';

L'opérateur LIKE

Caractères de correspondance

% : correspond à toute séquence de zéro caractère ou plus
_ (trait de soulignement) : correspond à un seul caractère

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

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

Expressions régulières (RE)

La correspondance avec LIKE est limitée
Des motifs plus précis peuvent aider
Les expressions régulières (RE) offrent une correspondance plus expressive

L'opérateur SIMILAR TO

Offre des fonctions de correspondance supplémentaires

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

Notions de base des ER

Métacaractère	Utilisation	Exemple d'ER	Exemple de correspondance
`\d`	correspond à un chiffre (0-9)	`\d\d\d`	'345'
`?`	correspond à 0 ou 1 du caractère précédent	`x\d?`	'x5'
`+`	correspond à une ou plusieurs occurrences du caractère précédent	`\d+`	'10'
`*`	correspond à tout caractère 0 fois ou plus	`\d*`	'3081'
`[]`	correspond à un caractère parmi ceux entre crochets	`[a-z]`	'f'

Utiliser des ER avec 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

Passons à la pratique !

Nettoyer des données dans des bases de données PostgreSQL