{"id":979,"date":"2023-03-01T22:50:28","date_gmt":"2023-03-01T22:50:28","guid":{"rendered":"https:\/\/kpwalters.com\/?page_id=979"},"modified":"2023-08-14T14:35:02","modified_gmt":"2023-08-14T14:35:02","slug":"recursive-cte-project","status":"publish","type":"page","link":"https:\/\/kpwalters.com\/index.php\/data-analyst\/recursive-cte-project\/","title":{"rendered":"Recursive CTE project"},"content":{"rendered":"\n
\n
Recursive CTE project<\/h5><\/div>\n<\/div>\n\n\n\n
\n
\n

October, 2022<\/em>
 <\/p>\n\n\n\n

Summary
Outcome<\/a>
Project Steps<\/a><\/p>\n<\/div>\n\n\n\n

\n
Summary<\/h5><\/div>\n\n\n\n

The data from the UK Gender Pay Gap service contains a column called siccodes<\/code> which is based on the UK SIC code classification system. In another project in this portfolio<\/a>, I created a local database of that data.

Unfortunately, the siccodes<\/code> column contains a variable length, comma-delimited list of SIC codes, making it impossible to join directly to any SIC code information.<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

As you can see, above, some companies have multiple SIC codes\u2014and many have an invalid code \u201c1.\u201d

The solution to the problem is to create a recursive common table expression (CTE) to return one row for every SIC code (and omitting the bogus \u201c1s\u201d).

This project does that.<\/p>\n<\/div>\n\n\n\n

\n

PostgreSQL
pgAdmin<\/em><\/p>\n<\/div>\n<\/div><\/div>\n\n\n\n

\n
<\/div>\n\n\n\n
\n
Outcome<\/h5><\/div>\n\n\n\n

The final outcome of the recursive CTE is a view, vw_gpg_sic_codes<\/code>. The view joins gender_pay_gap<\/code> to siccodes<\/code> on the SIC code data.<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

With the view created, I use Python code to do some basic analysis and visualizations of the Gender Pay Gap data based on SIC code.<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n<\/div>\n\n\n\n
<\/div>\n<\/div><\/div>\n\n\n\n
\n
<\/div>\n\n\n\n
\n
Project Steps<\/h5><\/div>\n\n\n\n

STEP ONE<\/strong>: Understand the basics of a recursive CTE.<\/p>\n\n\n\n

A recursive CTE has two parts, an initial query, often called the \u201canchor\u201d query, and the secondary query, often called the \u201crecursive\u201d query, which begins execution using the results of the anchor query and adds to it through recursion.

The execution can be graphically represented in a flowchart:<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

The two parts of the CTE form a UNION query. The recursive query\u2019s FROM clause calls back to the CTE.

The following simple query demonstrates:<\/p>\n\n\n\n

\"\"<\/a>
(Click image to open a new tab and access the SQL code.)<\/em> <\/figcaption><\/figure><\/div>\n\n\n\n

STEP TWO<\/strong>: Apply the concepts to the gender_pay_gap<\/code> table.<\/p>\n\n\n\n

With a basic understanding of recursive CTEs, the following is the process for applying this to the siccodes<\/code> field:<\/p>\n\n\n\n