CONNECT BY¶

Sintaxe¶

A forma geral de uma instrução com CONNECT BY é semelhante à seguinte (algumas variações na ordem são permitidas, mas não são mostradas):

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...

column_list

Isso geralmente segue as regras da cláusula de projeção de uma instrução SELECT.

level_expression

Consultas CONNECT BY permitem algumas pseudocolunas. Uma dessas pseudocolunas é LEVEL, que indica o nível atual da hierarquia (onde o nível 1 representa o topo da hierarquia). A cláusula de projeção da consulta pode usar LEVEL como uma coluna.

data_source

A fonte de dados é geralmente uma tabela, mas pode ser outra fonte de dados semelhante a uma tabela, como uma exibição, UDTF etc.

predicate

O predicado é uma expressão que seleciona o primeiro “nível” da hierarquia (por exemplo, o presidente da empresa ou o componente de nível superior em um detalhamento de peças). O predicado deve ser semelhante a uma cláusula WHERE, mas sem a palavra-chave WHERE.

Consulte a seção Exemplos (neste tópico) para obter exemplos.

colN_identifier

A cláusula CONNECT BY deve conter uma ou mais expressões similares àquelas utilizadas nas junções. Especificamente, uma coluna no nível “atual” da tabela deve se referir a uma coluna no nível “anterior” (superior) da tabela.

Por exemplo, em uma hierarquia de gerente/empregado, a cláusula pode ser semelhante a:

... CONNECT BY manager_ID = PRIOR employee_ID ...

Copy

A palavra-chave PRIOR indica que o valor deve ser retirado do nível anterior (superior/pai).

Neste exemplo, a manager_ID do gerente do funcionário atual deve corresponder à employee_ID do funcionário do nível anterior.

A cláusula CONNECT BY pode conter mais de uma expressão desse tipo, por exemplo:

... CONNECT BY y = PRIOR x AND b = PRIOR a ...

Copy

Cada expressão semelhante às seguintes deve ter exatamente uma ocorrência da palavra-chave PRIOR:

CONNECT BY <col_1_identifier> = <col_2_identifier>

Copy

A palavra-chave PRIOR pode estar no lado esquerdo ou direito do sinal =. Por exemplo:

CONNECT BY <col_1_identifier> = PRIOR <col_2_identifier>

Copy

ou

CONNECT BY PRIOR <col_1_identifier> = <col_2_identifier>

Copy

Notas de uso¶

• Uma cláusula CONNECT BY sempre une uma tabela a si mesma, não a outra tabela.

• Algumas variações dentro da cláusula de projeção são válidas. Embora a sintaxe mostre level_expression ocorrendo após o column_list, a(s) expressão(ões) de nível pode(m) ocorrer em qualquer ordem.

• A palavra-chave PRIOR deve ocorrer exatamente uma vez em cada expressão CONNECT BY. PRIOR pode ocorrer tanto no lado esquerdo como no lado direito da expressão, mas não em ambos.

• Uma consulta com CONNECT BY também pode conter um ou ambos os itens a seguir:

  • Filtros em uma cláusula WHERE.

  • JOINs (que pode estar em uma cláusula FROM ou em uma cláusula WHERE).

  A ordem de avaliação é:

  1. JOINs (independentemente de estar especificado na cláusula WHERE ou na cláusula FROM).

  2. CONNECT BY

  3. Filtros (exceto os filtros JOIN).

  Por exemplo, filtros em uma cláusula WHERE são processados após o CONNECT BY.

• A implementação do Snowflake do CONNECT BY é em sua maioria compatível com a implementação do Oracle; entretanto, o Snowflake não oferece suporte a:

  • NOCYCLE

  • CONNECT_BY_ISCYCLE

  • CONNECT_BY_ISLEAF

• O Snowflake oferece suporte à função SYS_CONNECT_BY_PATH quando usada com a cláusula CONNECT BY. SYS_CONNECT_BY_PATH retorna uma cadeia de caracteres que contém o caminho da raiz até o elemento atual. Um exemplo está incluído na seção Exemplos abaixo.

• O Snowflake oferece suporte ao operador CONNECT_BY_ROOT quando usado com a cláusula CONNECT BY. O operador CONNECT_BY_ROOT permite que o nível atual utilize informações do nível raiz da hierarquia, mesmo que o nível raiz não seja o pai imediato do nível atual. Um exemplo está incluído na seção Exemplos abaixo.

• A cláusula CONNECT BY pode iterar tantas vezes quantas forem necessárias para processar os dados. A criação inadequada de uma consulta pode causar um loop infinito. Nesses casos, a consulta continua a ser executada até que tenha sucesso, até que o tempo limite ocorra (por exemplo, ultrapassa o número de segundos especificado pelo parâmetro STATEMENT_TIMEOUT_IN_SECONDS) ou até que você cancele a consulta.

  Para obter mais informações sobre como podem ocorrer loops infinitos e para diretrizes sobre como evitar este problema, consulte Solução de problemas de uma CTE recursiva.

Exemplos¶

Este exemplo usa um CONNECT BY para mostrar a hierarquia de gestão em uma tabela de informações dos funcionários. A tabela e os dados são mostrados abaixo:

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

Copy

INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

Copy

A consulta e a saída são mostradas abaixo:

SELECT employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|-------------+------------+----------------------------|
|           1 |       NULL | President                  |
|          10 |          1 | Vice President Engineering |
|          20 |          1 | Vice President HR          |
|         100 |         10 | Programmer                 |
|         101 |         10 | QA Engineer                |
|         200 |         20 | Health Insurance Analyst   |
+-------------+------------+----------------------------+

Copy

Este exemplo usa a função SYS_CONNECT_BY_PATH para mostrar a hierarquia do presidente até o funcionário atual:

SELECT SYS_CONNECT_BY_PATH(title, ' -> '), employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+----------------------------------------------------------------+-------------+------------+----------------------------+
| SYS_CONNECT_BY_PATH(TITLE, ' -> ')                             | EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|----------------------------------------------------------------+-------------+------------+----------------------------|
|  -> President                                                  |           1 |       NULL | President                  |
|  -> President -> Vice President Engineering                    |          10 |          1 | Vice President Engineering |
|  -> President -> Vice President HR                             |          20 |          1 | Vice President HR          |
|  -> President -> Vice President Engineering -> Programmer      |         100 |         10 | Programmer                 |
|  -> President -> Vice President Engineering -> QA Engineer     |         101 |         10 | QA Engineer                |
|  -> President -> Vice President HR -> Health Insurance Analyst |         200 |         20 | Health Insurance Analyst   |
+----------------------------------------------------------------+-------------+------------+----------------------------+

Copy

Este exemplo usa a palavra-chave CONNECT_BY_ROOT para exibir informações do topo da hierarquia em cada linha de saída:

SELECT 
employee_ID, manager_ID, title,
CONNECT_BY_ROOT title AS root_title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      | ROOT_TITLE |
|-------------+------------+----------------------------+------------|
|           1 |       NULL | President                  | President  |
|          10 |          1 | Vice President Engineering | President  |
|          20 |          1 | Vice President HR          | President  |
|         100 |         10 | Programmer                 | President  |
|         101 |         10 | QA Engineer                | President  |
|         200 |         20 | Health Insurance Analyst   | President  |
+-------------+------------+----------------------------+------------+

Copy

Este exemplo usa um CONNECT BY para mostrar um “detalhamento de peças”:

Aqui estão os dados:

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    quantity INTEGER,
    component_ID INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Copy

Aqui estão a consulta e a saída:

SELECT
  description,
  quantity,
  component_id, 
  parent_component_ID,
  SYS_CONNECT_BY_PATH(component_ID, ' -> ') AS path
  FROM components
    START WITH component_ID = 1
    CONNECT BY 
      parent_component_ID = PRIOR component_ID
  ORDER BY path
  ;
+----------------+----------+--------------+---------------------+----------------------------+
| DESCRIPTION    | QUANTITY | COMPONENT_ID | PARENT_COMPONENT_ID | PATH                       |
|----------------+----------+--------------+---------------------+----------------------------|
| car            |        1 |            1 |                   0 |  -> 1                      |
| wheel          |        4 |           11 |                   1 |  -> 1 -> 11                |
| tire           |        1 |          111 |                  11 |  -> 1 -> 11 -> 111         |
| #112 bolt      |        5 |          112 |                  11 |  -> 1 -> 11 -> 112         |
| brake          |        1 |          113 |                  11 |  -> 1 -> 11 -> 113         |
| brake pad      |        1 |         1131 |                 113 |  -> 1 -> 11 -> 113 -> 1131 |
| engine         |        1 |           12 |                   1 |  -> 1 -> 12                |
| #112 bolt      |       16 |          112 |                  12 |  -> 1 -> 12 -> 112         |
| piston         |        4 |          121 |                  12 |  -> 1 -> 12 -> 121         |
| cylinder block |        1 |          122 |                  12 |  -> 1 -> 12 -> 122         |
+----------------+----------+--------------+---------------------+----------------------------+

Copy