Teradata RESET WHEN 기능을 Amazon Redshift SQL로 변환

소스: Teradata 데이터 웨어하우스	대상: Amazon Redshift	R 타입: 리아키텍트
환경: 프로덕션	기술: 분석, 데이터베이스, 마이그레이션	워크로드: 기타 모든 워크로드
AWS 서비스: Amazon Redshift

요약

RESET WHEN은 SQL 분석 창 함수에 사용되는 Teradata 기능입니다. ANSI SQL 표준의 확장입니다. RESET WHEN은 특정 조건에 따라 SQL 창 함수가 작동하는 파티션을 결정합니다. 조건이 TRUE로 평가되면 기존 창 파티션 내에 새로운 동적 하위 파티션이 생성됩니다. RESET WHEN에 대한 자세한 내용은 Teradata 설명서를 참조하세요.

Amazon Redshift는 SQL 창 함수에서 RESET WHEN을 지원하지 않습니다. 이 기능을 구현하려면 RESET WHEN을 Amazon Redshift의 네이티브 SQL 구문으로 변환하고 여러 개의 중첩 함수를 사용해야 합니다. 이 패턴은 Teradata RESET WHEN 기능을 사용하는 방법과 이를 Amazon Redshift SQL 구문으로 변환하는 방법을 보여 줍니다.

사전 조건 및 제한 사항

사전 조건

Teradata 데이터 웨어하우스와 해당 SQL 구문에 대한 기본 지식
Amazon Redshift와 해당 SQL 구문에 대한 충분한 이해

아키텍처

소스 기술 스택

Teradata 데이터 웨어하우스

대상 기술 스택

Amazon Redshift

아키텍처

Teradata 데이터베이스를 Amazon Redshift로 마이그레이션하기 위한 전체적인 아키텍처는 AWS SCT 데이터 추출 에이전트를 사용하여 Teradata 데이터베이스를 Amazon Redshift로 마이그레이션 패턴을 참조하세요. 마이그레이션은 Teradata RESET WHEN 구문을 Amazon Redshift SQL로 자동 변환하지 않습니다. 다음 섹션의 지침에 따라 이 Teradata 확장을 변환할 수 있습니다.

도구

코드

RESET WHEN의 개념을 설명하기 위해 Teradata의 다음 테이블 정의를 생각해 보세요.


create table systest.f_account_balance                                
( account_id integer NOT NULL,
  month_id integer,
  balance integer )
unique primary index (account_id, month_id);

다음 SQL 코드를 실행하여 샘플 데이터를 테이블에 삽입합니다.


BEGIN TRANSACTION;
Insert Into systest.f_account_balance values (1,1,60);
Insert Into systest.f_account_balance values (1,2,99);
Insert Into systest.f_account_balance values (1,3,94);
Insert Into systest.f_account_balance values (1,4,90);
Insert Into systest.f_account_balance values (1,5,80);
Insert Into systest.f_account_balance values (1,6,88);
Insert Into systest.f_account_balance values (1,7,90);
Insert Into systest.f_account_balance values (1,8,92);
Insert Into systest.f_account_balance values (1,9,10);
Insert Into systest.f_account_balance values (1,10,60);
Insert Into systest.f_account_balance values (1,11,80);
Insert Into systest.f_account_balance values (1,12,10);
END TRANSACTION;

샘플 테이블에는 다음과 같은 데이터가 있습니다.

account_id	month_id	balance
1	1	60
1	2	99
1	3	94
1	4	90
1	5	80
1	6	88
1	7	90
1	8	92
1	9	10
1	10	60
1	11	80
1	12	10

각 계좌에 대해 연속적인 월간 잔액 증가 순서를 분석한다고 가정합시다. 한 달 잔액이 이전 달의 잔액보다 작거나 같으면 카운터를 0으로 재설정하고 다시 시작해야 합니다.

Teradata RESET WHEN 사용 사례

이 데이터를 분석하기 위해 Teradata SQL은 다음과 같이 중첩 집계와 RESET WHEN 구문이 있는 창 함수를 사용합니다.


SELECT account_id, month_id, balance,
 ( ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY month_id
RESET WHEN balance <= SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ) -1 ) as balance_increase
FROM systest.f_account_balance
ORDER BY 1,2;

출력:

account_id	month_id	balance	balance_increase
1	1	60	0
1	2	99	1
1	3	94	0
1	4	90	0
1	5	80	0
1	6	88	1
1	7	90	2
1	8	92	3
1	9	10	0
1	10	60	1
1	11	80	2
1	12	10	0

쿼리는 Teradata에서 다음과 같이 처리됩니다.

SUM(balance) 집계 함수는 특정 달에 특정 계좌의 모든 잔액 합계를 계산합니다.
특정 달의(특정 계좌의) 잔액이 이전 달의 잔액보다 큰지 확인합니다.
잔액이 증가하면 누적 카운트 값을 추적합니다. RESET WHEN 조건이 거짓으로 평가되면(즉, 잔액이 몇 달 연속 증가함) 카운트를 계속 늘립니다.
ROW_NUMBER() 정렬 분석 함수는 카운트 값을 계산합니다. 잔액이 이전 달의 잔액보다 작거나 같은 달에 도달하면 RESET WHEN 조건이 참으로 평가됩니다. 그렇다면 새 파티션을 시작하고 ROW_NUMBER()는 카운트를 1부터 다시 시작합니다. 이전 행의 값에 액세스하려면 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING을 사용합니다.
카운트 값이 0부터 시작하도록 1을 뺍니다.

Amazon Redshift에 해당하는 SQL

Amazon Redshift는 SQL 분석 창 함수의 RESET WHEN 구문을 지원하지 않습니다. 동일한 결과를 생성하려면 다음과 같이 Amazon Redshift 네이티브 SQL 구문과 중첩된 하위 쿼리를 사용하여 Teradata SQL을 다시 작성해야 합니다.


SELECT account_id, month_id, balance,
   (ROW_NUMBER() OVER(PARTITION BY account_id, new_dynamic_part ORDER BY month_id) -1) as balance_increase
FROM
( SELECT account_id, month_id, balance, prev_balance,
SUM(dynamic_part) OVER (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As new_dynamic_part
FROM ( SELECT account_id, month_id, balance,
SUM(balance) over (PARTITION BY account_id ORDER BY month_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as prev_balance,
(CASE When balance <= prev_balance Then 1 Else 0 END) as dynamic_part
FROM systest.f_account_balance ) A
) B
ORDER BY 1,2;

Amazon Redshift는 단일 SQL 문의 SELECT 절에서 중첩된 창 함수를 지원하지 않으므로 두 개의 중첩된 하위 쿼리를 사용해야 합니다.

내부 하위 쿼리(별칭 A)에서 동적 파티션 표시기(dynamic_part)가 생성되고 채워집니다. 한 달의 잔액이 이전 달의 잔액보다 작거나 같으면 dynamic_part는 1로 설정되고, 그렇지 않으면 0으로 설정됩니다.
다음 레이어(별칭 B)에서는 SUM 창 함수의 결과로 new_dynamic_part 속성이 생성됩니다.
마지막으로 new_dynamic_part를 새 파티션 속성(dynamic partition)으로 기존 파티션 속성(account_id)에 추가하고 Teradata에서와 동일한 ROW_NUMBER() 창 함수를 적용합니다(그리고 1을 뺍니다).

이러한 변경 후 Amazon Redshift SQL은 Teradata와 동일한 출력을 생성합니다.

에픽

작업	설명	필요한 기술
Teradata 창 함수를 생성합니다.	필요에 따라 중첩 집계 및 RESET WHEN 구문을 사용합니다.	SQL Developer
코드를 Amazon Redshift SQL로 변환합니다.	코드를 변환하려면 이 패턴의 ‘도구’ 섹션에 있는 지침을 따르세요.	SQL Developer
Amazon Redshift에서 코드를 실행합니다.	테이블을 생성하고, 테이블에 데이터를 로드하고, Amazon Redshift에서 코드를 실행합니다.	SQL Developer