{"id":1407,"date":"2023-03-05T04:38:02","date_gmt":"2023-03-05T04:38:02","guid":{"rendered":"https:\/\/kpwalters.com\/?page_id=1407"},"modified":"2023-03-05T05:28:48","modified_gmt":"2023-03-05T05:28:48","slug":"siccodes-recursive-cte","status":"publish","type":"page","link":"https:\/\/kpwalters.com\/index.php\/data-analyst\/recursive-cte-project\/siccodes-recursive-cte\/","title":{"rendered":"siccodes recursive CTE"},"content":{"rendered":"\n<div class=\"wp-block-uagb-container uagb-block-257035b8 alignfull uagb-is-root-container\">\n<div class=\"wp-block-uagb-advanced-heading uagb-block-1d7ac529\"><h5 class=\"uagb-heading-text\">Recursive common table expression (CTE)<br>against siccodes column of gender_pay_gap<\/h5><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-8091461b alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-uagb-container uagb-block-5c0a979f\"><\/div>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-b35c9416\">\n<pre class=\"wp-block-code\"><code>WITH RECURSIVE \"gpg_siccode_recurs\" AS\r\n    (\r\n    SELECT \r\n        1 AS \"counter\"\r\n        , x.gpgkey\r\n        , x.employerid\r\n\r\n        --* The recursive query will extract SIC codes from this field.\r\n        , x.siccode_array\r\n\r\n        --* The siccodes field is delimited by a command and a line-feed. The inner query\r\n        --* below creates an array using the line-feed. Here we remove the comma and pad \r\n        --* each sic code to five characters.\r\n        , RIGHT( '00000' || REPLACE( x.siccode_array&#91;1], ',', '' ), 5 ) AS \"siccode\"\r\n\r\n\r\n        --* In the recursion query, we will limit recursion to the number of elements\r\n        --* in the array. So we need a count of array elements.\r\n        , ARRAY_LENGTH( x.siccode_array, 1 ) AS \"siccode_count\"\r\n\r\n    FROM (\r\n            SELECT\r\n                gpgkey\r\n                , employerid\r\n                , siccodes\r\n                --* The siccodes field is delimited by comma and a line-feed (Ascii 10)\r\n                , STRING_TO_ARRAY( siccodes, CHR(10) ) AS \"siccode_array\"\r\n            FROM gender_pay_gap\r\n\r\n        ) AS x\r\n\r\n    WHERE \r\n        ARRAY_LENGTH( x.siccode_array, 1 ) >= 1\r\n\r\n        --* SIC Codes of '0' and '1' are not legitimate SIC Codes.\r\n        AND REPLACE( x.siccode_array&#91;1], ',', '' ) NOT IN ( '0', '1' )\r\n\r\n    UNION ALL SELECT\r\n        y.counter + 1 AS \"counter\"\r\n        , y.gpgkey\r\n        , y.employerid\r\n        , y.siccode_array\r\n        , RIGHT( '00000' || REPLACE( y.siccode_array&#91;y.counter + 1], ',', '' ), 5 )\r\n                    AS \"siccode\"\r\n        , ARRAY_LENGTH( y.siccode_array, 1 ) AS \"siccode_count\"\r\n\r\n    FROM gpg_siccode_recurs y\r\n\r\n    WHERE \r\n        ARRAY_LENGTH( y.siccode_array, 1 ) >= y.counter + 1\r\n\r\n        --* SIC Codes of '0' and '1' are not legitimate SIC Codes.\r\n        AND REPLACE( y.siccode_array&#91;y.counter + 1], ',', '' ) NOT IN ( '0', '1' )\r\n    )\r\n\r\nSELECT \r\n    gpgkey\r\n    , counter\r\n    , employerid\r\n    , siccode\r\n    , LEFT( siccode, 2 ) AS sicdivision\r\n    , LEFT( siccode, 3 ) AS sicgroup\r\n    , LEFT( siccode, 4 ) AS sicclass\r\n    , siccode_count\r\nFROM gpg_siccode_recurs\r\nORDER BY gpgkey, counter\r\n;<\/code><\/pre>\n<\/div>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-57d9b2ea\"><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Recursive common table expression (CTE)against siccodes column of gender_pay_gap<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":979,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"page-builder","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"disabled","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"enabled","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"kevin.p.walters@live.com","author_link":"https:\/\/kpwalters.com\/index.php\/author\/kevin-p-walterslive-com\/"},"uagb_comment_info":0,"uagb_excerpt":"Recursive common table expression (CTE)against siccodes column of gender_pay_gap","_links":{"self":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1407"}],"collection":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/comments?post=1407"}],"version-history":[{"count":3,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1407\/revisions"}],"predecessor-version":[{"id":1411,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1407\/revisions\/1411"}],"up":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/979"}],"wp:attachment":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/media?parent=1407"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}