Tạo bảng pivot

Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Darryl Reeves, Ph.D.

Assistant Professor, Long Island University - Brooklyn

Bản ghi đa danh mục

              name               |            inspection_type            | grade | ... 
 --------------------------------+---------------------------------------+-------+-----
 ...                             | ...                                   | ...   | ...
 EMPANADAS MONUMENTAL            | Kiểm tra chu kỳ / Tái kiểm tra        | B     | ...
 ALPHONSO'S PIZZERIA & TRATTORIA | Kiểm tra chu kỳ / Kiểm tra ban đầu    | A     | ...
 THE SPARROW TAVERN              | Kiểm tra chu kỳ / Kiểm tra ban đầu    | A     | ...
 BURGER KING                     | Kiểm tra chu kỳ / Tái kiểm tra        | A     | ...
 ASTORIA PIZZA                   | Kiểm tra chu kỳ / Tái kiểm tra        | B     | ...
 ...                             | ...                                   | ...   | ...
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Truy cập điểm kiểm tra theo loại

SELECT
    inspection_type,
    grade,
    COUNT(*)
FROM
    restaurant_inspection 
WHERE
    grade IS NOT NULL
GROUP BY
    inspection_type,
    grade
ORDER BY 
    inspection_type,
    grade;
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Kết quả tổng hợp theo loại

                  inspection_type                  | grade | count 
 --------------------------------------------------+-------+-------
 Kiểm tra chu kỳ / Kiểm tra ban đầu               | A     |  1063
 Kiểm tra chu kỳ / Tái kiểm tra                   | A     |   723
 Kiểm tra chu kỳ / Tái kiểm tra                   | B     |   270
 Kiểm tra chu kỳ / Tái kiểm tra                   | C     |    93
 Kiểm tra chu kỳ / Tái kiểm tra                   | Z     |    29
 Kiểm tra chu kỳ / Kiểm tra mở lại                | C     |     8
 Kiểm tra chu kỳ / Kiểm tra mở lại                | P     |    26
 Kiểm tra chu kỳ / Kiểm tra mở lại                | Z     |     3
 Trước cấp phép (Không hoạt động) / Kiểm tra đầu | N     |     4
 Trước cấp phép (Hoạt động) / Kiểm tra đầu       | A     |   119
 Trước cấp phép (Hoạt động) / Kiểm tra đầu       | N     |    17
 Trước cấp phép (Hoạt động) / Tái kiểm tra       | A     |    79
 Trước cấp phép (Hoạt động) / Tái kiểm tra       | B     |    49
 Trước cấp phép (Hoạt động) / Tái kiểm tra       | C     |    13
 Trước cấp phép (Hoạt động) / Tái kiểm tra       | Z     |     9
 Trước cấp phép (Hoạt động) / Kiểm tra mở lại    | C     |     3
 Trước cấp phép (Hoạt động) / Kiểm tra mở lại    | P     |     3
 Trước cấp phép (Hoạt động) / Kiểm tra mở lại    | Z     |     1
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Đổi hướng dữ liệu (pivot)

                  inspection_type                  |  A   |  B  | C  | N  | P  | Z  
 --------------------------------------------------+------+-----+----+----+----+----
 Kiểm tra chu kỳ / Tái kiểm tra                   |  723 | 270 | 93 |  0 |  0 | 29
 Kiểm tra chu kỳ / Kiểm tra ban đầu               | 1063 |   0 |  0 |  0 |  0 |  0
 Trước cấp phép (Hoạt động) / Kiểm tra mở lại     |    0 |   0 |  3 |  0 |  3 |  1
 Kiểm tra chu kỳ / Kiểm tra mở lại                |    0 |   0 |  8 |  0 | 26 |  3
 Trước cấp phép (Không hoạt động) / Kiểm tra đầu |    0 |   0 |  0 |  4 |  0 |  0
 Trước cấp phép (Hoạt động) / Kiểm tra đầu       |  119 |   0 |  0 | 17 |  0 |  0
 Trước cấp phép (Hoạt động) / Tái kiểm tra       |   79 |  49 | 13 |  0 |  0 |  9
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Mệnh đề FILTER

  • Áp dụng tổng hợp trên một tập con bản ghi
  • Tập con xác định bởi WHERE đi kèm
  • Dùng trong danh sách SELECT của truy vấn
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Mệnh đề FILTER

  • Ví dụ: AVG(qty_sold) FILTER (WHERE qty_sold > 1)
  • Cú pháp: AGG_FUNC(expression) FILTER (WHERE condition)
    • AGG_FUNC() - hàm tổng hợp
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Truy vấn tạo bảng pivot

SELECT
    summary_column,
    AGG(agg_column) FILTER (WHERE agg_column = PIVOT_VALUE_1) AS "pivot_column_1",
    AGG(agg_column) FILTER (WHERE agg_column = PIVOT_VALUE_2) AS "pivot_column_2",
    ...
    AGG(agg_column) FILTER (WHERE agg_column = PIVOT_VALUE_N) AS "pivot_column_N"
FROM
    source_table
GROUP BY
    summary_column;
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Kết quả bảng pivot

Sơ đồ bố cục sau khi pivot với các cột summary_column, pivot_column_1, pivot_column, ..., pivot_column_N. summary_val_1 đến summary_val_M điền vào summary_column. Kết quả tổng hợp cho PV1, PV2, đến PVN điền vào các cột pivot_column tương ứng.

Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Pivot dữ liệu kiểm tra nhà hàng

SELECT
    inspection_type,
    COUNT(grade) FILTER (WHERE grade = 'A') AS "A",
    COUNT(grade) FILTER (WHERE grade = 'B') AS "B",
    COUNT(grade) FILTER (WHERE grade = 'C') AS "C",
    COUNT(grade) FILTER (WHERE grade = 'N') AS "N",
    COUNT(grade) FILTER (WHERE grade = 'P') AS "P",
    COUNT(grade) FILTER (WHERE grade = 'Z') AS "Z"
FROM
    restaurant_inspections 
WHERE
    grade IS NOT NULL
GROUP BY
    inspection_type;
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Kết quả pivot cho dữ liệu kiểm tra

                  inspection_type                  |  A   |  B  | C  | N  | P  | Z  
 --------------------------------------------------+------+-----+----+----+----+----
 Kiểm tra chu kỳ / Tái kiểm tra                   |  723 | 270 | 93 |  0 |  0 | 29
 Kiểm tra chu kỳ / Kiểm tra ban đầu               | 1063 |   0 |  0 |  0 |  0 |  0
 Trước cấp phép (Hoạt động) / Kiểm tra mở lại     |    0 |   0 |  3 |  0 |  3 |  1
 Kiểm tra chu kỳ / Kiểm tra mở lại                |    0 |   0 |  8 |  0 | 26 |  3
 Trước cấp phép (Không hoạt động) / Kiểm tra đầu |    0 |   0 |  0 |  4 |  0 |  0
 Trước cấp phép (Hoạt động) / Kiểm tra đầu       |  119 |   0 |  0 | 17 |  0 |  0
 Trước cấp phép (Hoạt động) / Tái kiểm tra       |   79 |  49 | 13 |  0 |  0 |  9
Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Hãy thực hành!

Làm sạch dữ liệu trong cơ sở dữ liệu PostgreSQL

Preparing Video For Download...