【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点

赵延东的一亩三分地 2023-05-21 原文

系列文章目录

文章目录

前言

本篇文章讲解的主要内容是：第一个案例给大家介绍如何在Oracle中进行树形(分层)查询，主要介绍START WITH、CONNECT BY、PRIOR语法的使用。接着以如何确定根节点、分支节点和叶子节点的需求，给大家介绍level与connect_by_isleaf函数的使用。最后介绍节点路径函数 sys_connect_by_path以及它在Oracle11.2之前可以替代Listagg函数和wm_concat函数的写法。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、简单的树形查询案例

我们经常会用一些表来保存上下级关系,比如总公司表、分公司表、员工表等,为了按上下级关系递归查询这些数据,就需要用到树形查询,下面以emp表为例。
先来看下emp表的数据：

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

14 rows selected


SQL>

我们来以empno=7566 为起点，向下递归查询员工和主管关系：

SELECT empno AS 员工编码,
ename AS 姓名,
mgr AS 主管编码,
(PRIOR ename) AS 主管姓名
FROM emp
START WITH empno=7566
CONNECT BY (PRIOR empno)=mgr;  
 员工编码 姓名        主管编码 主管姓名
----- ---------- ----- ----------
 7566 JONES       7839 
 7788 SCOTT       7566 JONES
 7876 ADAMS       7788 SCOTT
 7902 FORD        7566 JONES
 7369 SMITH       7902 FORD

来分析一下上面语句。

起点：这个语句以"START WITH empno=7566"为起点向下递归查询。

通过操作符"PRIOR"可以取得上一级的信息,如上面查询中的主管姓名(PRIOR ename)。
CONNECT BY子句列出来递归的条件（上一级的编码）等于本级的主管编码。然后再以7566为起点,向下一级就是(7788,7902)。
7788的下一级是7876,7902的下一级是7369。至此,递归查找完成。

二、根节点、分支节点、叶子节点的确定

在树形查询中常用的有两个伪列：level与connect_by_isleaf。level返回当前行所在的等级,根节点为1级,其下为2级……
如果当前节点下没有其他的节点,则connect_by_isleaf 返回1,否则返回0。这样就可以通过level与connect_by_isleaf来判断标识根节点、分支节点、叶子节点。如下案例：

SELECT lpad('->', (level - 1) * 2, '->') || empno AS 员工编码,
       ename AS 姓名,
       mgr AS 主管编码,
       (PRIOR ename) AS 主管姓名,
       level as 级别,
       decode(level, 1, 1) as 根节点,
       decode(connect_by_isleaf, 1, 1) as 叶子节点,
       CASE
         WHEN (connect_by_isleaf = 0 AND LEVEL > 1) THEN
          1
       END AS 分支节点
  FROM emp
 START WITH empno = 7566
CONNECT BY (PRIOR empno) = mgr;
员工编码                                                                         姓名        主管编码 主管姓名           级别        根节点       叶子节点       分支节点
-------------------------------------------------------------------------------- ---------- ----- ---------- ---------- ---------- ---------- ----------
7566                                                                             JONES       7839                     1          1            
->7788                                                                           SCOTT       7566 JONES               2                                1
->->7876                                                                         ADAMS       7788 SCOTT               3                     1 
->7902                                                                           FORD        7566 JONES               2                                1
->->7369                                                                         SMITH       7902 FORD                3                     1

三、查看节点路径函数 sys_connect_by_path

当数据级别比较多时,不容易看清根节点到当前节点的路径,这时就可用sys_connect_by_path函数把这些信息展示出来：

SELECT lpad('->', (level - 1) * 2, '->') || empno AS 员工编码,
       ename AS 姓名,
       mgr AS 主管编码,
       (PRIOR ename) AS 主管姓名,
       level as 级别,
       decode(level, 1, 1) as 根节点,
       decode(connect_by_isleaf, 1, 1) as 叶子节点,
       CASE
         WHEN (connect_by_isleaf = 0 AND LEVEL > 1) THEN
          1
       END AS 分支节点,
       sys_connect_by_path(ename,'->') as enames
  FROM emp
 START WITH empno = 7566
CONNECT BY (PRIOR empno) = mgr;

员工编码                                                                         姓名        主管编码 主管姓名           级别        根节点       叶子节点       分支节点 ENAMES
-------------------------------------------------------------------------------- ---------- ----- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
7566                                                                             JONES       7839                     1          1                       ->JONES
->7788                                                                           SCOTT       7566 JONES               2                                1 ->JONES->SCOTT
->->7876                                                                         ADAMS       7788 SCOTT               3                     1            ->JONES->SCOTT->ADAMS
->7902                                                                           FORD        7566 JONES               2                                1 ->JONES->FORD
->->7369                                                                         SMITH       7902 FORD                3                     1            ->JONES->FORD->SMITH

前面介绍过用分析函数listagg来合并字符串,然而Oracle11.2之前的版本没有listagg怎么办？其实可以借助树形查询中的sys_connect_by_path函数：

with t as/*分组生成序号*/
 (select deptno,
         ename,
         row_number() over(partition by deptno order by ename) as rn
    from emp)
    /*用sys_connect_by_path合并字符串*/
select deptno, sys_connect_by_path(ename, ',') as emps
  from t
 where connect_by_isleaf = 1 --要取出来叶子节点所在的那个分支才是完整的拼接
 start with rn = 1
connect by (prior deptno) = deptno
       and (prior rn) = rn - 1;
DEPTNO EMPS
------ --------------------------------------------------------------------------------
    10 ,CLARK,KING,MILLER
    20 ,ADAMS,FORD,JONES,SCOTT,SMITH
    30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

这种方法的要点是分组生成序号,然后通过序号递归循环。注意：要过滤多余的数据时,只需要加条件"WHEREconnect_by_isleaf=1"来取叶子节点就可以。

总结

树形技巧 span class token 原力计划分层/树形查询 CONNECT BY 根节点、叶子节点数据仓库

有关【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点的更多相关文章

python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2
关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。
ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby - 使用 C 扩展开发 rubygem 时，如何使用 Rspec 在本地进行测试？ - 2
我正在编写一个包含C扩展的gem。通常当我写一个gem时，我会遵循TDD的过程，我会写一个失败的规范，然后处理代码直到它通过，等等......在“ext/mygem/mygem.c”中我的C扩展和在gemspec的“扩展”中配置的有效extconf.rb，如何运行我的规范并仍然加载我的C扩展？当我更改C代码时，我需要采取哪些步骤来重新编译代码？这可能是个愚蠢的问题，但是从我的gem的开发源代码树中输入“bundleinstall”不会构建任何native扩展。当我手动运行rubyext/mygem/extconf.rb时，我确实得到了一个Makefile(在整个项目的根目录中)，然后当
ruby-on-rails - 使用一系列等级计算字母等级 - 2
这里是Ruby新手。完成一些练习后碰壁了。练习:计算一系列成绩的字母等级创建一个方法get_grade来接受测试分数数组。数组中的每个分数应介于0和100之间，其中100是最大分数。计算平均分并将字母等级作为字符串返回，即“A”、“B”、“C”、“D”、“E”或“F”。我一直返回错误:avg.rb:1:syntaxerror,unexpectedtLBRACK,expecting')'defget_grade([100,90,80])^avg.rb:1:syntaxerror,unexpected')',expecting$end这是我目前所拥有的。我想坚持使用下面的方法或.join，
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
Ruby Sinatra 配置用于生产和开发 - 2
我已经在Sinatra上创建了应用程序，它代表了一个简单的API。我想在生产和开发上进行部署。我想在部署时选择，是开发还是生产，一些方法的逻辑应该改变，这取决于部署类型。是否有任何想法，如何完成以及解决此问题的一些示例。例子:我有代码get'/api/test'doreturn"Itisdev"end但是在部署到生产环境之后我想在运行/api/test之后看到ItisPROD如何实现？最佳答案根据SinatraDocumentation:EnvironmentscanbesetthroughtheRACK_ENVenvironm
ruby - 简单获取法拉第超时 - 2
有没有办法在这个简单的get方法中添加超时选项？我正在使用法拉第3.3。Faraday.get(url)四处寻找，我只能先发起连接后应用超时选项，然后应用超时选项。或者有什么简单的方法？这就是我现在正在做的:conn=Faraday.newresponse=conn.getdo|req|req.urlurlreq.options.timeout=2#2secondsend 最佳答案试试这个:conn=Faraday.newdo|conn|conn.options.timeout=20endresponse=conn.get(url
ruby - 是否可以覆盖 gemfile 进行本地开发？ - 2
我们的git存储库中目前有一个Gemfile。但是，有一个gem我只在我的环境中本地使用(我的团队不使用它)。为了使用它，我必须将它添加到我们的Gemfile中，但每次我checkout到我们的master/dev主分支时，由于与跟踪的gemfile冲突，我必须删除它。我想要的是类似Gemfile.local的东西，它将继承从Gemfile导入的gems，但也允许在那里导入新的gems以供使用只有我的机器。此文件将在.gitignore中被忽略。这可能吗？最佳答案设置BUNDLE_GEMFILE环境变量:BUNDLE_GEMFI
ruby - 在 Windows 机器上使用 Ruby 进行开发是否会适得其反？ - 2
这似乎非常适得其反，因为太多的gem会在window上破裂。我一直在处理很多mysql和ruby-mysqlgem问题(gem本身发生段错误，一个名为UnixSocket的类显然在Windows机器上不能正常工作，等等)。我只是在浪费时间吗？我应该转向不同的脚本语言吗？最佳答案我在Windows上使用Ruby的经验很少，但是当我开始使用Ruby时，我是在Windows上，我的总体印象是它不是Windows原生系统。因此，在主要使用Windows多年之后，开始使用Ruby促使我切换回原来的系统Unix，这次是Linux。Rub
ruby - 用 Ruby 编写一个简单的网络服务器 - 2
我想在Ruby中创建一个用于开发目的的极其简单的Web服务器(不，不想使用现成的解决方案)。代码如下:#!/usr/bin/rubyrequire'socket'server=TCPServer.new('127.0.0.1',8080)whileconnection=server.acceptheaders=[]length=0whileline=connection.getsheaders想法是从命令行运行这个脚本，提供另一个脚本，它将在其标准输入上获取请求，并在其标准输出上返回完整的响应。到目前为止一切顺利，但事实证明这真的很脆弱，因为它在第二个请求上中断并出现错误:/usr/b