중첩 데이터 사용 사례

앞에서 설명한 확장을 일반적인 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)              

참고

상위 행을 기준으로 그룹화하여 중첩 데이터를 집계할 때는 이전 예와 같은 방법이 가장 효율적입니다. 위의 예에서 중첩 행인 c.orders는 상위 행인 c를 기준으로 그룹화됩니다. 또는 id가 각 customer마다 고유하고, o.shipdate는 절대 NULL 값이 아니라는 사실을 알고 있다면 다음 예와 같이 집계하는 방법도 있습니다. 하지만 이 방법은 일반적으로 이전 예만큼 효율적이지 않습니다.

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;