월별 매장별 고객 수 및 전표건수를 집계하기 위해 매장별 전표 집계의 레코드 수가 매장별 매출 집계 레코드 수보다 많은 경우가 발생했다.
예를들어 아래 전표 테이블을 집계한 SALNO_CNT 의 레코드 수는 100 인데,
SAL_SUM 의 레코드 수가 97 인 경우다.
전표는 있지만, 매출이 없는 예외상황인지 또는 쿼리의 오류인지 확인하기 위해 차집합을 구하여 자세한 정보로 매출 원본데이터에서 검색하기로 했다.
...
, SALNO_CNT AS (
SELECT NATION, YM, STOREID
, COUNT(DISTINCT SALNO) AS CNT_SALNO
FROM SALNO -- 1) 전표 테이블
GROUP BY 1,2,3
)
, SAL_SUM AS (
SELECT A.NATION, A.YM, A.STOREID, B.STORENAME
, COUNT(DISTINCT A.CUSTID) AS CNT_CUST, SUM(A.SALES) SUM_SALES
FROM SEG_SAL AS A -- 2) 매출 서브쿼리 테이블
LEFT JOIN STORE_INFO AS B -- 3) 매장 정보 테이블
ON A.STOREID=B.STOREID AND A.NAION=B.NATION
GROUP BY 1,2,3
)
, RESULT AS (
SELECT A.NATION, A.STOREID, A.STORENAME, A.YM, A.CNT_CUST, A.SUM_SALES, B.CNT_SALNO
FROM SAL_SUM AS A
LEFT JOIN SALNO_CNT AS B ON A.NATION=B.NATION AND A.YM=B.YM AND A.STORENAME=B.STORENAME
ORDER BY 1,2,3,4
)
SELECT * FROM RESULT위와 같은 쿼리로 구하는 결과인 RESULT 테이블의 데이터 검수를 위해 아래와 같이 차집합을 구하였다.
SELECT A.*
FROM SALNO_CNT AS A
LEFT JOIN SAL_SUM AS B ON A.NATION=B.NATION AND A.YM=B.YM AND A.STOREID=B.STOREID
WHERE A.SUM_SALES IS NULL매출에는 없고 전표에만 존재하는 데이터를 확인하기 위해 국가, 연월, 매장ID 를 기준으로 OUTER JOIN 을 하고
매출액이 없는 레코드만 검색하여 해당하는 국가, 연월, 매장ID 를 매출 RAW 데이터에서 검색했다.
그 결과 매출 기록은 있지만, 매출액이 0인 경우로, SEG_SAL 을 구하는 서브쿼리에서 매출액이 0인 경우는 제외한 로직 때문에 2개 테이블의 레코드 수 가 차이나는 경우 였다.
문제가 된 건들을 전표 테이블에서 검색하였을 때 전표 매출액은 0이 아니었다.. 전표에는 반품 처리가 반영되지 않았거나, POS 입력에 오류가 있었으리라.. 하는 추측을 해보았다
Spark SQL accessing outer query column is not allowed in spark sql
OUTER JOIN 이 사용하기 편하다는 이유로 더 좋은 쿼리를 고민하지 않고, 주로 사용했는데
아래 포스팅을 보고 위와 같이 차집합을 구하는 방식에 문제가 있음을 발견했다.
내부 입력의 조인 행 수에 문제가 있는데, 조인 조건을 만족하는 모든 레코드를 OUTER 로 결합 도출한 뒤 마지막 Filter 연산자에서 단 3건을 만족함으로, 반드시 필요한 데이터만 액세스하는 좋은 쿼리 의 기본 원칙에 위배된다고 한다.
그래서 상관 서브 쿼리와 TOP 1 을 사용한 방법을 시도해보았는데,
AnalysisException: Accessing outer query column is not allowed in:요런 에러가 떴다..
찾아보니 SparkSQL 에서 몇몇 서브쿼리를 지원하지 않는다고 한다.
이번 같은 경우에는 집계 데이터 검수 용이었기 때문에 OUTER JOIN 을 사용하는 것이 큰 문제가 되지 않지만, 고객 번호를 검색하거나 하는 경우에는 상당히 비효율적임을 알게 되었다.
Spark 환경에 맞는 쿼리 작성법에 대해 더 연구해 볼 작정이다
(더 좋은 DE 가 되기 위해 💫)
