중첩 데이터 사용 사례

이 주제에서는 중첩된 데이터의 사용 사례를 설명합니다. 중첩 데이터는 중첩된 필드를 포함하는 데이터입니다. 중첩 필드는 배열, 구조체 또는 객체와 같은 단일 엔터티로 함께 조인되는 필드입니다.

앞에서 설명한 확장을 일반적인 SQL 기능과 결합할 수 있습니다. 다음 사용 사례는 일반적인 몇 가지 결합에 대한 설명입니다. 각 사례는 중첩 데이터의 사용 방법을 이해하는 데 도움이 될 것입니다. 자습서에는 포함되어 있지 않습니다.

중첩 데이터 수집

CREATE TABLE AS 문을 사용하여 복합 데이터 형식이 포함된 외부 테이블에서 데이터를 수집할 수 있습니다. 다음은 LEFT JOIN을 사용해 외부 테이블의 고객과 고객 전화 번호를 모두 추출한 후 Amazon Redshift 테이블인 CustomerPhones에 저장하는 쿼리입니다.

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;     

하위 쿼리를 사용한 중첩 데이터 집계

하위 쿼리를 사용해 중첩 데이터를 집계할 수 있습니다. 다음은 이러한 방법을 설명하는 예입니다.

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

다음 데이터가 반환됩니다.

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

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;

또는 FROM 절에서 최상위 쿼리의 별칭(c)을 참조하여 배열 데이터를 추출하는 하위 쿼리를 사용하여 쿼리를 작성할 수도 있습니다. 다음 예에서는 이 방법을 보여 줍니다.

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

Amazon Redshift 및 중첩 데이터 조인

Amazon Redshift 데이터를 외부 테이블의 중첩 데이터와 조인시킬 수 있습니다. 예를 들어 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>>
);

또한 Amazon Redshift에 다음과 같은 테이블이 있다고 가정하겠습니다.

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

다음은 앞의 예를 근거로 각 고객이 구매한 총 상품 수와 양을 요청하는 쿼리입니다. 다음은 이해를 돕기 위한 예입니다. 앞에서 설명한 테이블을 만든 경우에만 데이터를 반환합니다.

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;