recursion - 雪花中的递归
问题描述
长期聆听者,第一次来电者。
我正在尝试使用经典的组织结构图学习 Snowflake SQL 中的递归。我尝试的输出将是每位员工经理的总工资,包括该经理的直接和间接员工。
create table Employee(
employeeid int primary key,
managerid int,
title string
);
INSERT INTO EMPLOYEE VALUES(1,NULL,'CEO');
INSERT INTO EMPLOYEE VALUES(2,1,'SVP 1');
INSERT INTO EMPLOYEE VALUES(3,1,'SVP 2');
INSERT INTO EMPLOYEE VALUES(4,2,'DIR 1');
INSERT INTO EMPLOYEE VALUES(5,2,'DIR 2');
INSERT INTO EMPLOYEE VALUES(6,2,'DIR 3');
INSERT INTO EMPLOYEE VALUES(7,3,'MGR 1');
INSERT INTO EMPLOYEE VALUES(8,3,'MGR 2');
INSERT INTO EMPLOYEE VALUES(9,3,'MGR 3');
INSERT INTO EMPLOYEE VALUES(10,9,'ASST');
create TABLE EMPLOYEE_SALARY(
EMPLOYEE_ID INT PRIMARY KEY,
SALARY INT
);
INSERT INTO EMPLOYEE_SALARY VALUES(1,100);
INSERT INTO EMPLOYEE_SALARY VALUES(2,90);
INSERT INTO EMPLOYEE_SALARY VALUES(3,90);
INSERT INTO EMPLOYEE_SALARY VALUES(4,80);
INSERT INTO EMPLOYEE_SALARY VALUES(5,80);
INSERT INTO EMPLOYEE_SALARY VALUES(6,80);
INSERT INTO EMPLOYEE_SALARY VALUES(7,70);
INSERT INTO EMPLOYEE_SALARY VALUES(8,70);
INSERT INTO EMPLOYEE_SALARY VALUES(9,90);
INSERT INTO EMPLOYEE_SALARY VALUES(10,60);
select
managerid,
sum(salary) as salary
from
(
select emp.managerid,
emp.employeeid,
sal.salary
from employee emp
inner join employee_salary sal on emp.employeeid = sal.employee_id
)
group by 1
start with managerid is Null
connect by managerid = prior employeeid
我得到的错误是“SQL 编译错误:位置 12 处的错误行 3 无效标识符 'SALARY'”。
提前感谢您提供的任何方向。
解决方案
我会使用递归公用表表达式来解决这个问题。Snowflake 支持该标准语法,我发现遵循该connect by
子句更容易:
with cte as (
select managerid, employeeid from employee
union all
select c.managerid, e.employeeid
from cte c
inner join employee e on e.managerid = c.employeeid
)
select c.managerid, sum(s.salary) total_salaries
from cte c
inner join employee_salary s on s.employeeid = c.employeeid
group by c.managerid
推荐阅读
- windows-10 - 使用 Xming 显示 VMS 显示和接收有关字体/颜色的错误
- swift - 音频缓冲区和视频缓冲区的呈现时间不相等
- mysql - CreateProcess Error=3 与我的待办事项列表和日记条目代码,将代码复制并粘贴到新项目中,现在新错误说没有名为 mysql 的模块
- python - 数据框到字典 Python
- linux - sl - 条目数
- mysql - 加入 2 个表并将重复的行值显示到新列中
- android - expo-location watchHeadingAsync 在 Android 上不起作用
- swift - 将一行从 Swift 转换为 Objective-C
- http - 从需要登录的私人站点提取信息(Golang)
- excel - 是否可以在 Excel 中显示计算步骤?