如何在Snowflake中创建递归连接

huangapple
go评论90阅读模式
英文:

How to create a recursive join in snowflake

问题

我有一个查询，其中包括多个左连接以获取员工的管理层次列表。我知道Snowflake有递归功能，但我在理解上有困难。有人知道如何递归地编写类似这样的查询吗？

  1. select
  2.     d1.employee_id,
  3.     d1.employee_id1,
  4.     d1.employee_id2,
  5.     d1.employee_id3,
  6.     d2.employee_id1 as employee_id4,
  7.     d2.manager_name as employee_id4_name,
  8.     d3.employee_id1 as employee_id5,
  9.     d3.manager_name as employee_id5_name,
  10.     d4.employee_id1 as employee_id6,
  11.     d4.manager_name as employee_id6_name,
  12.     d5.employee_id1 as employee_id7,
  13.     d5.manager_name as employee_id7_name
  14. from employees d1
  15. left join employees d2
  16. on d1.employee_id3 = d2.employee_id
  17. and d1.report_effective_date = d2.report_effective_date
  18. left join employees d3
  19. on d2.employee_id1 = d3.employee_id
  20. and d2.report_effective_date = d3.report_effective_date
  21. left join employees d4
  22. on d3.employee_id1 = d4.employee_id
  23. and d3.report_effective_date = d4.report_effective_date
  24. left join employees d5
  25. on d4.employee_id1 = d5.employee_id
  26. and d4.report_effective_date = d5.report_effective_date

根据我理解的方式，我尝试过以下内容，但似乎无法按我希望的格式排列？我想要获取个人的管理层次结构，但每个经理都在一个新列中。

  1. with cte as (
  2.   select
  3.     employee_id,
  4.     employee_name,
  5.     employee_id1,
  6.     manager_name
  7.   from employees
  8.   where employee_name = <name>
  9.   
  10.   union all
  12.   select
  13.     employee.employee_id,
  14.     employee.employee_name,
  15.     employee.employee_id1,
  16.     employee.manager_name
  17.   from employees
  18.   join cte
  19.   on employee.employee_id = cte.employee_id1
  20. )
  21. select * from cte
英文:

I have a query with a bunch of left joins to get a hierarchal list of managers for an employee. I know snowflake has a recursive functionality but having trouble following. Does anyone know how something like this can be written recursively?

  1. select
  2.     d1.employee_id,
  3.     d1.employee_id1,
  4.     d1.employee_id2,
  5.     d1.employee_id3,
  6.     d2.employee_id1 as employee_id4,
  7.     d2.manager_name as employee_id4_name,
  8.     d3.employee_id1 as employee_id5,
  9.     d3.manager_name as employee_id5_name,
  10.     d4.employee_id1 as employee_id6,
  11.     d4.manager_name as employee_id6_name,
  12.     d5.employee_id1 as employee_id7,
  13.     d5.manager_name as employee_id7_name
  14. from employees d1
  15. left join employees d2
  16. on d1.employee_id3 = d2.employee_id
  17. and d1.report_effective_date = d2.report_effective_date
  18. left join employees d3
  19. on d2.employee_id1 = d3.employee_id
  20. and d2.report_effective_date = d3.report_effective_date
  21. left join employees d4
  22. on d3.employee_id1 = d4.employee_id
  23. and d3.report_effective_date = d4.report_effective_date
  24. left join employees d5
  25. on d4.employee_id1 = d5.employee_id
  26. and d4.report_effective_date = d5.report_effective_date

Edit with what I've tried - and from my understanding the way I want this formatted doesn't seem possible? I'm looking to get an individual's manager hierarchy but with each manager in a new column.

What I tried was like this, but it would give me the hierarchy in rows and would require that I have an anchored query filtered to someone.

  1. with cte as (
  2.   select
  3.     employee_id,
  4.     employee_name,
  5.     employee_id1,
  6.     manager_name
  7.   from employees
  8.   where employee_name = &lt;name&gt;
  9.   
  10.   union all
  12.   select
  13.     employee.employee_id,
  14.     employee.employee_name,
  15.     employee.employee_id1,
  16.     employee.manager_name
  17.   from employees
  18.   join cte
  19.   on employee.employee_id = cte.employee_id1
  20. )
  21. select * from cte

答案1

得分: 1

你说得对，你会得到行而不是列。你还说得对，你需要在查询中锚定某个人或某个群体。考虑将一个没有分配employee_id1的员工作为锚点（如果我理解你的数据的话，这个员工是组织中的最高层）。

至于"Data in rows"，考虑构建一个层次路径，你可以将其拆分成数组或其他形式：

  1. with cte as (
  2.   select
  3.     employee_id as employee,
  4.     employee_name,
  5.     employee_id1 as manager,
  6.     manager_name,
  7.     CAST(employee_id AS VARCHAR(500)) as path,
  8.     1 as depth
  9.   from employees
  10.   where employee_id1 IS NULL
  11.   
  12.   union all
  14.   select
  15.     employee.employee_id,
  16.     employee.employee_name,
  17.     employee.employee_id1,
  18.     employee.manager_name,
  19.     cte.path || '>' || employee.employee_id,
  20.     cte.depth + 1
  21.   from employees
  22.   join cte
  23.   on employee.employee_id = cte.employee_id1
  24. )
  25. select * from cte

现在你有了员工的完整路径以及该员工的深度，这应该会有很大帮助。

英文:

You are correct that you would get back rows instead of columns. You are also correct that you would need to anchor the query on someone, or some group. Consider, as an anchor, an employee that has no employee_id1 assigned (The top most person in the org, if I understand your data).

As for the "Data in rows" consider building a hierarchical path that you can split to an array or what-have-you:

  1. with cte as (
  2.   select
  3.     employee_id as employee,
  4.     employee_name,
  5.     employee_id1 as manager,
  6.     manager_name,
  7.     CAST(employee_id AS VARCHAR(500)) as path,
  8.     1 as depth
  9.   from employees
  10.   where employee_id1 IS NULL
  11.   
  12.   union all
  14.   select
  15.     employee.employee_id,
  16.     employee.employee_name,
  17.     employee.employee_id1,
  18.     employee.manager_name,
  19.     cte.path || &#39;&gt;&#39; || employee.employee_id,
  20.     cte.depth + 1
  21.   from employees
  22.   join cte
  23.   on employee.employee_id = cte.employee_id1
  24. )
  25. select * from cte

Now you have the full path for the employee as well as the depth of that employee, which should help plenty.

huangapple
  • 本文由 发表于 2023年6月15日 03:41:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/76477041.html
  • recursive-query
  • snowflake-cloud-data-platform
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定