Casos de uso de dados aninhados

Este tópico descreve casos de uso para dados aninhados. Dados aninhados são dados que contêm campos aninhados. Campos aninhados são campos unidos como uma única entidade (por exemple, matrizes, structs ou objetos).

Você pode combinar as extensões descritas anteriormente com as características usuais SQL. Os casos de uso a seguir ilustram algumas combinações comuns. Estes exemplos ajudam a demonstrar como você pode usar dados aninhados. Eles não fazem parte do tutorial.

Ingerir dados aninhados

Você pode usar uma declaração CREATE TABLE AS para ingerir dados de uma tabela externa que contenha tipos de dados complexos. A consulta a seguir extrai todos os clientes e seus números de telefone da tabela externa, usando LEFT JOIN e os armazena na tabela CustomerPhones. do Amazon Redshift.

CREATE TABLE CustomerPhones AS
SELECT  c.name.given, c.name.family, p AS phone
FROM    spectrum.customers c LEFT JOIN c.phones p ON true;     

Agregar dados aninhados com subconsultas

Você pode usar uma subconsulta para agregar dados aninhados. O exemplo a seguir ilustra essa abordagem.

SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount 
FROM   spectrum.customers c;

Os dados a seguir são retornados.

given   |  family  |  ordercount
--------|----------|--------------
 Jenny  |  Doe     |       0
 John   |  Smith   |       2
 Andy   |  Jones   |       1
 (3 rows)              

nota

Quando você agrega dados aninhados agrupando os pela linha pai, a forma mais eficiente é a exibida no exemplo anterior. Nesse caso, as linhas aninhadas de c.orders são agrupadas em sua linha pai c. Ou, se você sabe que id é exclusivo para cada customer e o.shipdate e nunca nulo, você pode agregar conforme exibido no seguinte exemplo. Contudo, esta abordagem não é geralmente mais eficiente que o exemplo anterior.

SELECT    c.name.given, c.name.family, COUNT(o.shipdate) AS ordercount 
FROM      spectrum.customers c LEFT JOIN c.orders o ON true 
GROUP BY  c.id, c.name.given, c.name.family;

Você também pode gravar a consulta usando uma subconsulta na cláusula FROM que refere-se a um alias (c) da consulta antecessora e extrai dados da matriz. O exemplo a seguir demonstra essa abordagem.

SELECT c.name.given, c.name.family, s.count AS ordercount
FROM   spectrum.customers c, (SELECT count(*) AS count FROM c.orders o) s;    

Unir o Amazon Redshift e dados aninhados

Você também pode adicionar dados do Amazon Redshift com dados aninhados em uma tabela externa. Por exemplo, digamos que você tenha os dados a seguir aninhados no Amazon S3.

CREATE EXTERNAL TABLE spectrum.customers2 (
  id      int,
  name    struct<given:varchar(20), family:varchar(20)>,
  phones  array<varchar(20)>,
  orders  array<struct<shipdate:timestamp, item:int>>
);

Digamos também que possui a tabela a seguir no Amazon Redshift.

CREATE TABLE prices (
  id int,
  price double precision
);

A consulta a seguir encontra o número total e a quantidade de compras de cada cliente com base em precedência. O exemplo a seguir é apenas uma ilustração. Só retorna dados se você tiver criado as tabelas descritas anteriormente.

SELECT   c.name.given, c.name.family, COUNT(o.date) AS ordercount, SUM(p.price) AS ordersum 
FROM     spectrum.customers2 c, c.orders o, prices p ON o.item = p.id  
GROUP BY c.id, c.name.given, c.name.family;