Tách dữ liệu bằng dấu phân tách
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL
Darryl Reeves, Ph.D.
Industry Assistant Professor, New York University
Tách dữ liệu thành nhiều cột
camis | name | inspection_type | ...
---------+-----------------------+----------------------------------------------+-----
... | ... | ... | ...
50084922 | JUICE POINT | Cycle Inspection / Re-inspection | ...
50075375 | ATOMIC WINGS | Administrative Miscellaneous / Re-inspection | ...
50048685 | KENNEDY FRIED CHICKEN | Cycle Inspection / Re-inspection | ...
50058910 | HUNGER PANG | Pre-permit (Operational) / Re-inspection | ...
50047834 | SUBWAY | Smoke-Free Air Act / Re-inspection | ...
... | ... | ... | ...
Dấu phân tách giá trị: ' / '
sub_inspection_type | count
-----------------------------+-------
Reopening Inspection | 56
Re-inspection | 1333
Initial Inspection | 3488
Second Compliance Inspection | 2
Compliance Inspection | 27
Tách dữ liệu thành nhiều cột
camis | name | inspection_type | ...
---------+-----------------------+----------------------------------------------+-----
... | ... | ... | ...
50084922 | JUICE POINT | Cycle Inspection / Re-inspection | ...
50075375 | ATOMIC WINGS | Administrative Miscellaneous / Re-inspection | ...
50048685 | KENNEDY FRIED CHICKEN | Cycle Inspection / Re-inspection | ...
50058910 | HUNGER PANG | Pre-permit (Operational) / Re-inspection | ...
50047834 | SUBWAY | Smoke-Free Air Act / Re-inspection | ...
... | ... | ... | ...
camis | name | main_inspection_type | sub_inspection_type | ...
---------+-----------------------+------------------------------+---------------------+-----
... | ... | ... | ... | ...
50084922 | JUICE POINT | Cycle Inspection | Re-inspection | ...
50075375 | ATOMIC WINGS | Administrative Miscellaneous | Re-inspection | ...
50048685 | KENNEDY FRIED CHICKEN | Cycle Inspection | Re-inspection | ...
50058910 | HUNGER PANG | Pre-permit (Operational) | Re-inspection | ...
50047834 | SUBWAY | Smoke-Free Air Act | Re-inspection | ...
... | ... | ... | ... | ...
Tách chuỗi bằng SPLIT_PART()
SPLIT_PART(source_string, delimiter_string, part_number)
SELECT
SPLIT_PART('Cycle Inspection / Re-inspection', ' / ', 1);
Cycle Inspection
SELECT
SPLIT_PART('Cycle Inspection / Re-inspection', ' / ', 2);
Re-inspection
Tách chuỗi bằng SPLIT_PART()
SELECT
camis,
name,
SPLIT_PART(inspection_type, ' / ', 1) AS main_inspection_type,
SPLIT_PART(inspection_type, ' / ', 2) AS sub_inspection_type
FROM
restaurant_inspection;
camis | name | main_inspection_type | sub_inspection_type | ...
---------+-----------------------+------------------------------+---------------------+-----
... | ... | ... | ... | ...
50084922 | JUICE POINT | Cycle Inspection | Re-inspection | ...
50075375 | ATOMIC WINGS | Administrative Miscellaneous | Re-inspection | ...
50048685 | KENNEDY FRIED CHICKEN | Cycle Inspection | Re-inspection | ...
50058910 | HUNGER PANG | Pre-permit (Operational) | Re-inspection | ...
50047834 | SUBWAY | Smoke-Free Air Act | Re-inspection | ...
... | ... | ... | ... | ...
Tách dữ liệu thành nhiều hàng
camis | name | cuisine_description | ...
---------+-----------------------+---------------------+-----
... | ... | ... | ...
50066768 | FIRST LAMB SHABU | Chinese | ...
41450971 | GIOVANNI'S RESTAURANT | Pizza/Italian | ...
41628459 | KFC | Chicken | ...
50043003 | BANGIA | Korean | ...
41418978 | BAGEL EXPRESS III | Bagels/Pretzels | ...
... | ... | ... | ...
camis | name | cuisine_description | ...
-------+-----------------------+---------------------+-----
... | ... | ... | ...
50066768 | FIRST LAMB SHABU | Chinese | ...
41450971 | GIOVANNI'S RESTAURANT | Pizza | ...
41450971 | GIOVANNI'S RESTAURANT | Italian | ...
41628459 | KFC | Chicken | ...
50043003 | BANGIA | Korean | ...
41418978 | BAGEL EXPRESS III | Bagels | ...
41418978 | BAGEL EXPRESS III | Pretzels | ...
... | ... | ... | ...
Tách dữ liệu với REGEXP_SPLIT_TO_TABLE()
REGEXP_SPLIT_TO_TABLE(source, pattern)
SELECT REGEXP_SPLIT_TO_TABLE('Pizza/Italian', '/');
Pizza
Italian
Tách dữ liệu với REGEXP_SPLIT_TO_TABLE()
SELECT
camis,
name,
REGEXP_SPLIT_TO_TABLE(cuisine_description, '/') AS cuisine_description,
...
FROM
restaurant_inspection;
camis | name | cuisine_description | ...
---------+-----------------------+---------------------+-----
... | ... | ... | ...
50066768 | FIRST LAMB SHABU | Chinese | ...
41450971 | GIOVANNI'S RESTAURANT | Pizza | ...
41450971 | GIOVANNI'S RESTAURANT | Italian | ...
41628459 | KFC | Chicken | ...
50043003 | BANGIA | Korean | ...
41418978 | BAGEL EXPRESS III | Bagels | ...
41418978 | BAGEL EXPRESS III | Pretzels | ...
... | ... | ... | ...
Đánh số các hàng kết quả
cuisine_num | camis | name | cuisine_description | ...
------------+----------+-----------------------+---------------------+-----
... | ... | ... | ... | ...
1 | 41418978 | BAGEL EXPRESS III | Bagels | ...
2 | 41418978 | BAGEL EXPRESS III | Pretzels | ...
1 | 41450971 | GIOVANNI'S RESTAURANT | Pizza | ...
2 | 41450971 | GIOVANNI'S RESTAURANT | Italian | ...
1 | 41628459 | KFC | Chicken | ...
1 | 50043003 | BANGIA | Korean | ...
1 | 50066768 | FIRST LAMB SHABU | Chinese | ...
... | ... | ... | ... | ...
ROW_NUMBER() OVER()
PARTITION BY col1, col2, ...
ORDER BY colA, colB, ...
Đánh số các hàng kết quả
SELECT
ROW_NUMBER() OVER (
PARTITION BY
-- group columns for numbering
camis,
name
ORDER BY
-- set ordering of results
camis,
name
) AS cuisine_num,
*
FROM (
SELECT
camis,
name,
REGEXP_SPLIT_TO_TABLE(cuisine_description, '/')
AS cuisine_description
FROM
restaurant_inspection;
cuisine_num | camis | name | cuisine_description | ...
------------+----------+-----------------------+---------------------+-----
... | ... | ... | ... | ...
1 | 41418978 | BAGEL EXPRESS III | Bagels | ...
2 | 41418978 | BAGEL EXPRESS III | Pretzels | ...
1 | 41450971 | GIOVANNI'S RESTAURANT | Pizza | ...
2 | 41450971 | GIOVANNI'S RESTAURANT | Italian | ...
1 | 41628459 | KFC | Chicken | ...
1 | 50043003 | BANGIA | Korean | ...
1 | 50066768 | FIRST LAMB SHABU | Chinese | ...
... | ... | ... | ... | ...
Ayo berlatih!
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL
