Consulta de dados hierárquicos¶

Este tópico descreve como armazenar e consultar dados hierárquicos usando:

JOINs
CTEs recursivas (expressões de tabela comuns)
CONNECT BY

Consulte também:: CONNECT BY, a parte CTE recursiva do comando WITH, Como trabalhar com CTEs (expressões de tabela comuns), UDFs tabulares de SQL (UDTFs)

Neste tópico:

Armazenamento de dados hierárquicos¶

Muitos tipos de dados são melhor representados como uma hierarquia, como por exemplo uma árvore.

Por exemplo, funcionários são geralmente organizados em uma hierarquia, com um presidente da empresa no topo da hierarquia.

Outro exemplo de uma hierarquia é um detalhamento de peças. Por exemplo, um carro contém um motor; um motor contém uma bomba de combustível; e uma bomba de combustível contém uma mangueira.

Você pode armazenar dados hierárquicos em:

Uma hierarquia de tabelas.
Uma única tabela com uma (ou mais) colunas representando a hierarquia (por exemplo, indicando o gerente direto de cada funcionário).

Ambas as técnicas são descritas abaixo.

Nota

Este tópico enfoca dados hierárquicos armazenados como dados estruturados. Dados hierárquicos também podem ser armazenados como dados semiestruturados (por exemplo, dados JSON podem ser armazenados em tipos de dados ARRAY, OBJECT ou VARIANT). Para obter mais informações sobre dados semiestruturados, consulte:

Dados semiestruturados

Consulta de dados semiestruturados

Dados hierárquicos em múltiplas tabelas¶

Bancos de dados relacionais geralmente armazenam dados hierárquicos utilizando diferentes tabelas. Por exemplo, uma tabela pode conter dados “pais” e outra tabela pode conter dados “filhos”. Quando toda a hierarquia é conhecida antecipadamente, uma tabela pode ser criada para cada camada da hierarquia.

Por exemplo, considere um banco de dados de Recursos Humanos que armazena informações de funcionários e informações de gerente. Se a empresa é pequena, então pode haver apenas dois níveis; por exemplo, um gerente e dois funcionários.

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

Copy

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

Copy

INSERT INTO managers (title, employee_ID) VALUES
    ('President', 1);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('Vice President Engineering', 10, 1),
    ('Vice President HR', 20, 1);

Copy

Dados hierárquicos em uma única tabela¶

Em algumas situações, o número de níveis na hierarquia pode mudar.

Por exemplo, uma empresa que começou com uma hierarquia de dois níveis (presidente e outros funcionários) pode aumentar o número de níveis à medida que a empresa cresce. A empresa pode se expandir para incluir um presidente, vice-presidentes e funcionários regulares.

Se o número de níveis for desconhecido, de modo que não seja possível criar uma hierarquia com um número conhecido de tabelas, então em alguns casos os dados hierárquicos podem ser armazenados em uma única tabela. Por exemplo, uma única tabela pode conter todos os funcionários e pode incluir uma coluna que armazena a ID_gerente de cada funcionário, que aponta para outro funcionário nessa mesma tabela. Por exemplo:

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

O armazenamento de toda uma hierarquia de dados em uma tabela funciona melhor se todos os níveis da hierarquia armazenarem os mesmos dados – em nosso exemplo, ID de funcionário, título, etc. Se os dados em níveis diferentes não se encaixam na mesma estrutura de registros, então o armazenamento de todos os dados em uma única tabela pode não ser prático.

Uso de junções para consultar dados hierárquicos¶

Em uma hierarquia de dois níveis (por exemplo, gerentes e funcionários), os dados podem ser consultados com uma junção bidirecional:

SELECT 
        employees.title, 
        employees.employee_ID, 
        managers.employee_ID AS MANAGER_ID, 
        managers.title AS "MANAGER TITLE"
    FROM employees, managers
    WHERE employees.manager_ID = managers.employee_ID
    ORDER BY employees.title;
+----------------------------+-------------+------------+---------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE |
|----------------------------+-------------+------------+---------------|
| Vice President Engineering |          10 |          1 | President     |
| Vice President HR          |          20 |          1 | President     |
+----------------------------+-------------+------------+---------------+

Copy

Em uma hierarquia de três níveis, você pode usar uma união tridirecional:

SELECT
     emps.title,
     emps.employee_ID,
     mgrs.employee_ID AS MANAGER_ID, 
     mgrs.title AS "MANAGER TITLE"
  FROM employees AS emps LEFT OUTER JOIN employees AS mgrs
    ON emps.manager_ID = mgrs.employee_ID
  ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE              |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+

Copy

Este conceito pode ser estendido a tantos níveis quantos forem necessários, desde que você saiba quantos níveis são necessários. Mas se o número de níveis mudar, as consultas precisam mudar.

Uso de CONNECT BY ou CTEs recursivas para consultar dados hierárquicos¶

O Snowflake oferece duas maneiras de consultar dados hierárquicos nas quais o número de níveis não é conhecido antecipadamente:

CTEs recursivas (expressões de tabela comuns)
Cláusulas CONNECT BY.

Uma CTE recursiva permite criar uma cláusula WITH que pode se referir a si mesma. Isto lhe permite iterar através de cada nível de sua hierarquia e acumular resultados.

Uma cláusula CONNECT BY permite criar um tipo de operação JOIN que processa a hierarquia um nível de cada vez e permite que cada nível se refira aos dados no nível anterior.

Para obter mais detalhes, consulte:

Diferenças entre autojunções, CTE recursiva e CONNECT BY¶

CONNECT BY permite apenas autojunções. CTEs recursivas são mais flexíveis e permitem que uma tabela seja unida a uma ou mais tabelas.

Uma cláusula CONNECT BY tem a maior parte do poder de uma CTE recursiva. No entanto, uma CTE recursiva pode fazer algumas coisas que um CONNECT BY não pode fazer.

Por exemplo, se você examinar os exemplos de CTE recursiva, vê que uma das consultas recua a saída e também ordena a saída de modo que cada “filho” apareça debaixo do “pai” correspondente. A classificação é feita criando uma chave de classificação que contém a cadeia de IDs do topo até o nível atual. No exemplo do gerente/funcionário, a cadeia contém a ID do presidente, seguida pela ID do vice-presidente, etc. Este tipo de chave agrupa linhas de forma semelhante a uma árvore lateral. A sintaxe CONNECT BY não suporta isto porque a cláusula “START WITH“ não permite que o código especifique colunas adicionais (além daquelas na própria tabela), como sort_key. Contraste os dois trechos de código abaixo:

SELECT indent(LEVEL) || employee_ID, manager_ID, title
  FROM employees
    -- This sub-clause specifies the record at the top of the hierarchy,
    -- but does not allow additional derived fields, such as the sort key.
    START WITH TITLE = 'President'
    CONNECT BY ...

WITH RECURSIVE current_layer
   (employee_ID, manager_ID, sort_key) AS (
     -- This allows us to add columns, such as sort_key, that are not part
     -- of the employees table.
     SELECT employee_ID, manager_ID, employee_ID AS sort_key
     ...
     )

Copy

Você pode, entretanto, usar a função SYS_CONNECT_BY_PATH para alcançar um efeito semelhante com a cláusula CONNECT BY.

Embora a versão da cláusula CONNECT BY seja limitada porque a cláusula START WITH não pode adicionar colunas às que já estão na linha (mesmo colunas derivadas baseadas em valores já na linha), ela também tem algumas vantagens:

Você tem acesso a todas as colunas de cada linha sem especificar essas colunas em uma lista de colunas. Em uma CTE recursiva, a cláusula recursiva não tem acesso às colunas que não estão explicitamente especificadas na CTE.
Em uma CTE recursiva, deve-se especificar as colunas naCTE, e as listas de projeção das seleções na cláusula de âncora e a cláusula recursiva, devem ambas corresponder às colunas na CTE. Se a ordem das colunas nas várias cláusulas de projeção não corresponderem, você pode causar problemas, tais como loops infinitos.
A sintaxe CONNECT BY suporta pseudocolunas convenientes, como LEVEL, CONNECT_BY_ROOT e CONNECT_BY_PATH

Uma pequena diferença entre CONNECT BY e a CTE recursiva é que em CONNECT BY você usa a palavra-chave PRIOR para indicar quais valores de coluna devem ser obtidos da iteração anterior, enquanto que em uma CTE recursiva você usa o nome da tabela e o nome da CTE para indicar quais valores são obtidos da iteração atual e quais são obtidos da iteração anterior. (Em uma CTE recursiva, você também pode distinguir entre iterações atuais e anteriores usando nomes de colunas diferentes na lista de colunas CTE em vez de na tabela ou expressão de tabela de origem).

Hierarquias não contíguas¶

Este tópico descreveu hierarquias e como as relações pai-filho podem ser usadas por CTEs recursivas (expressões de tabela comuns) e cláusulas CONNECT BY. Em todos os exemplos deste tópico, assim como em todos os exemplos da documentação de CONNECT BY e na documentação de CTE recursiva, as hierarquias são contíguas. Nenhum dos exemplos tem um pai e um neto sem ter um filho correspondente entre eles.

Por exemplo, se você fizer um detalhamento das peças de um carro, você não terá um componente para o carro e um componente para o pneu sem ter um componente para a roda que contém o pneu (e que é contida pelo carro).

Entretanto, pode haver casos em que os dados estejam incompletos. Por exemplo, em uma hierarquia de funcionários/gerentes, suponha que o vice-presidente de engenharia se aposente e que a empresa não contrate um substituto imediatamente. Se o registro de funcionários VP for excluído, os funcionários abaixo desse VP serão “cortados” do resto da hierarquia, de modo que a tabela de funcionários não contém mais uma única hierarquia contígua.

Se você usar CTEs recursivas ou CONNECT BY para processar dados, precisa pensar se os dados em sua tabela representam uma árvore única e contígua. Você pode usar CTEs recursivas e CONNECT BY em uma única tabela que contém várias árvores, mas você só pode consultar uma árvore de cada vez, e essa árvore deve ser contígua.