Hierarchical Query Examples

本文介绍了Oracle数据库中使用CONNECT BY和START WITH子句进行层次查询的方法。通过实例展示了如何获取员工和经理之间的层级关系,包括层级编号、路径以及循环检测等功能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Hierarchical Query Examples

CONNECT BY Example  

The following hierarchical query uses the CONNECTBY clause to define the relationship between employees and managers:

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .

LEVEL Example  

The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3
        200 Whalen                           101          2
        203 Mavris                           101          2
        204 Baer                             101          2
        205 Higgins                          101          2
        206 Gietz                            205          3
        102 De Haan                          100          1
...

START WITH Examples  

The next example adds a STARTWITH clause to specify a root row for the hierarchy and an ORDERBY clause using the SIBLINGS keyword to preserve ordering within the hierarchy:        // connect by层内排序 定义返回时同一父亲下各个兄弟之间的顺序。

SELECT last_name, employee_id, manager_id, LEVELHierarchical Queries
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;
LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3
...
In the hr.employees table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of
department 80. If you update the employees table to set Russell as King's manager, you create a loop in the data:
UPDATE employees SET manager_id = 145
   WHERE employee_id = 100;
SELECT last_name "Employee",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
ERROR:

ORA-01436: CONNECT BY loop in user data

The NOCYCLE parameter in the CONNECTBY condition causes Oracle to return the rows in spite of the loop. The CONNECT_BY_ISCYCLE pseudocolumn shows you

which rows contain the cycle:
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4
   ORDER BY "Employee", "Cycle", LEVEL, "Path";
Employee                       Cycle      LEVEL Path
------------------------- ---------- ---------- -------------------------
Abel                               0          3 /King/Zlotkey/Abel
Ande                               0          3 /King/Errazuriz/Ande
Banda                              0          3 /King/Errazuriz/BandaHierarchical Queries
Bates                              0          3 /King/Cambrault/Bates
Bernstein                          0          3 /King/Russell/Bernstein
Bloom                              0          3 /King/Cambrault/Bloom
Cambrault                          0          2 /King/Cambrault
Cambrault                          0          3 /King/Russell/Cambrault
Doran                              0          3 /King/Partners/Doran
Errazuriz                          0          2 /King/Errazuriz
Fox                                0          3 /King/Cambrault/Fox
...

CONNECT_BY_ISLEAF Example  

The following statement shows how you can use a hierarchical query to turn the values in a column into a comma-delimited list:

SELECT LTRIM(SYS_CONNECT_BY_PATH (warehouse_id,','),',') FROM
   (SELECT ROWNUM r, warehouse_id FROM warehouses)
   WHERE CONNECT_BY_ISLEAF = 1
   START WITH r = 1
   CONNECT BY r = PRIOR r + 1
   ORDER BY warehouse_id;
LTRIM(SYS_CONNECT_BY_PATH(WAREHOUSE_ID,','),',')
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9
CONNECT_BY_ROOT Examples   The following example returns the last name of  each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id
   ORDER BY "Employee", "Manager", "Pathlen", "Path";
Employee        Manager            Pathlen Path
--------------- --------------- ---------- ------------------------------
Gietz           Higgins                  1 /Higgins/Gietz
Gietz           King                     3 /King/Kochhar/Higgins/Gietz
Gietz           Kochhar                  2 /Kochhar/Higgins/Gietz
Higgins         King                     2 /King/Kochhar/Higgins
Higgins         Kochhar                  1 /Kochhar/Higgins
The following example uses a GROUPBY clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy:
SELECT name, SUM(salary) "Total_Salary" FROM (
   SELECT CONNECT_BY_ROOT last_name as name, Salary
      FROM employees
      WHERE department_id = 110
      CONNECT BY PRIOR employee_id = manager_id)
      GROUP BY name
   ORDER BY name, "Total_Salary";
NAME                      Total_Salary
------------------------- ------------
Gietz                             8300
Higgins                          20300
King                             20300

Kochhar                          20300



### 层次聚类概述 层次聚类是一种无监督学习技术,用于发现数据中的自然分组。它通过构建一棵树状图(dendrogram),表示不同簇之间的关系以及它们如何逐步合并或分裂。这种方法可以分为两种主要形式:凝聚型层次聚类(Agglomerative Hierarchical Clustering)和分裂型层次聚类(Divisive Hierarchical Clustering)。前者从单个样本开始并将它们逐渐组合成更大的簇;后者则相反,从整个数据集作为一个单一簇开始,并将其分解为较小的子簇。 在实际应用中,最常用的方法是凝聚型层次聚类。该算法的核心在于定义一种距离度量来衡量两个簇之间的相似性,并采用不同的链接策略(Linkage Criteria)决定如何计算簇间距离。常见的链接方式有三种:最小连接法(Single Linkage)、最大连接法(Complete Linkage)和平均连接法(Average Linkage)。每种方法都有其优缺点,在具体场景下需谨慎选择[^1]。 以下是Python实现层次聚类的一个基本例子: ```python import numpy as np from scipy.cluster.hierarchy import dendrogram, linkage, fcluster from matplotlib import pyplot as plt # 创建随机数据点作为示例 np.random.seed(42) data = np.random.rand(10, 2) # 使用SciPy库执行层次聚类 linked = linkage(data, method='average') # 这里选择了平均连接法 # 绘制树状图 plt.figure(figsize=(8, 5)) dendrogram(linked, orientation='top', labels=range(len(data)), distance_sort='descending') plt.title('Dendrogram of Agglomerative Clustering') plt.xlabel('Data Points') plt.ylabel('Distance') # 提取特定数量的簇标签 num_clusters = 3 clusters = fcluster(linked, num_clusters, criterion='maxclust') print(f'Cluster Labels: {clusters}') ``` 上述代码展示了如何利用`scipy.cluster.hierarchy.linkage()`函数生成层次结构,并借助`matplotlib.pyplot.dendrogram()`绘制对应的树形图。最后还演示了怎样提取固定数目下的最终分类结果。 关于机器学习模型透明性和可信度方面提到,“黑盒”特性确实给深度神经网络带来了挑战。然而对于像层次聚类这样的传统算法来说,则相对更容易理解内部运作机制及其参数调整影响[^2]。 ####
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值