{"id":1868,"date":"2022-12-02T09:40:21","date_gmt":"2022-12-02T00:40:21","guid":{"rendered":"https:\/\/www.kwonline.org\/memo2\/?p=1868"},"modified":"2023-03-29T13:00:07","modified_gmt":"2023-03-29T04:00:07","slug":"query-org-tree-from-employee-db-snowflake","status":"publish","type":"post","link":"https:\/\/www.kwonline.org\/memo2\/2022\/12\/02\/query-org-tree-from-employee-db-snowflake\/","title":{"rendered":"\u5f93\u696d\u54e1 DB \u304b\u3089\u7d44\u7e54\u30c4\u30ea\u30fc\u3092\u62bd\u51fa\u3059\u308b SQL"},"content":{"rendered":"<p>&nbsp;<br \/>\nSnowflake \u306b\u683c\u7d0d\u3055\u308c\u3066\u308b\u5f93\u696d\u54e1\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u7d44\u7e54\u30c4\u30ea\u30fc\u3092\u62bd\u51fa\u3059\u308b\u30af\u30a8\u30ea\u3092\u66f8\u3044\u305f\u306e\u3067\u30e1\u30e2<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nwith recursive mgmt(delim, user_id, manager_id, job_title, emp_hier_level) as (\r\n    select \r\n        ' ' as delim\r\n        ,user_id\r\n        ,manager_id\r\n        ,job_title\r\n        ,1 as emp_hier_level\r\n    from dim_employee\r\n    where job_title = 'CEO'\r\n    union all\r\n    select \r\n        (delim || '-&gt; ')\r\n        ,e.user_id\r\n        ,e.manager_id\r\n        ,e.job_title\r\n        ,emp_hier_level + 1\r\n    from dim_employee as e\r\n    join mgmt as m\r\n    on e.manager_id = m.user_id\r\n)\r\n, b as (select \r\n    delim || job_title as emp_hier\r\n    ,user_id\r\n    ,manager_id\r\n    ,job_title\r\n    ,emp_hier_level\r\nfrom mgmt)\r\nselect distinct * from b;\r\n<\/pre>\n<p>dim_employee (\u5f93\u696d\u54e1\u30c6\u30fc\u30d6\u30eb) \u306b user_id (\u5f93\u696d\u54e1ID) \u3068 job_title (\u80a9\u66f8) \u3068 manager_id \u304c\u5165\u3063\u3066\u3044\u308b\u3002<br \/>\n\u305d\u3053\u304b\u3089 recursive CTE \u3067\u30eb\u30fc\u30d7\u3057\u306a\u304c\u3089\u793e\u54e1\u3054\u3068\u306bCEO\u304b\u3089\u81ea\u5206\u307e\u3067\u306e\u30ce\u30fc\u30c9(\u8ddd\u96e2)\u3092\u300c -> \u300d\u533a\u5207\u308a\u3067\u4e26\u3079\u3066\u3076\u3089\u4e0b\u3052\u3066\u884c\u304f\u3068\u3044\u3046\u51e6\u7406\u3002<\/p>\n<p>recursive \u3084\u3063\u3071\u4fbf\u5229\u3088\u3002<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Snowflake \u306b\u683c\u7d0d\u3055\u308c\u3066\u308b\u5f93\u696d\u54e1\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u7d44\u7e54\u30c4\u30ea\u30fc\u3092\u62bd\u51fa\u3059\u308b\u30af\u30a8\u30ea\u3092\u66f8\u3044\u305f\u306e\u3067\u30e1\u30e2 with recursive mgmt(delim, user_id, manager_id, job_tit [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,20],"tags":[],"class_list":["post-1868","post","type-post","status-publish","format-standard","hentry","category-snowflakedb","category-sql"],"_links":{"self":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1868","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/comments?post=1868"}],"version-history":[{"count":5,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1868\/revisions"}],"predecessor-version":[{"id":1897,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1868\/revisions\/1897"}],"wp:attachment":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/media?parent=1868"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/categories?post=1868"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/tags?post=1868"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}