Aggregating film categories
For the final exercise in this course, let's return to the film_permit
table. It contains a community_board
TEXT
column composed of a comma-separated list of integers. There is interest in doing an analysis of the types of film permits that are being provided for each community board. However, the representation of community boards (INTEGER
s in a TEXT
column) makes this difficult. By using techniques learned in this chapter, the data can be transformed to allow for such an analysis.
In this exercise, you will first create a (temporary) VIEW
that represents the community_board
values individually for two permit categories. A VIEW
is a named query that can be used like a TABLE
once created. You will use this VIEW
in a subquery for aggregating the results in a pivot table.
This exercise is part of the course
Cleaning Data in PostgreSQL Databases
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
CREATE OR REPLACE TEMP VIEW cb_categories AS
SELECT
-- Split community board values
___(___, ___) AS community_board,
category
FROM
film_permit
WHERE
-- Restrict the categories in results
___ ___ (___, ___, ___);
-- View cb_categories
SELECT * FROM cb_categories;