질문 배경
15세기에 루카 파치올리(Luca Pacioli)에 의해 정립된 복식부기(Double-entry bookkeeping)는 자산 = 부채 + 자본이 성립해야 한다고 요구합니다. 현대 ERP 시스템은 부모 계정이 자식 계정을 집계하는 계층적 계정 목록 구조를 통해 이를 구현합니다. 반대 계정(예: 누적 감가상각 또는 자사주)은 부모의 잔액을 증가시키지 않고 감소시킵니다. 이 방정식을 각 통합 수준—루트 뿐만 아니라—에서 검증하는 것은 자회사 원장이 통합 재무 제표로 집계되기 전에 내부적으로 일관성을 유지하도록 합니다.
문제
표준 SQL 집계(SUM)는 덧셈 관계를 가정합니다. 그러나 반대 계정은 뺄셈을 요구하며, 중첩된 경우(하나의 반대 계정이 또 다른 반대 계정 하위에 있을 때) 부호는 곱해야 합니다(부정 × 부정 = 긍정). 게다가 최상위 루트 노드만 검증하면 중간 비즈니스 유닛의 오류가 가려집니다. 문제는 이러한 부호 곱셈기를 임의의 계층 깊이를 통해 전파하면서 각 노드에서 대수 검증을 수행하는 것입니다.
해결책
루트에서 리프까지 계층을 탐색하는 재귀 CTE를 사용하여 누적 부호 곱셈기를 전파합니다. 각 노드는 부모의 부호 맥락을 상속받고 자신의 반대 계정 논리를 곱셈적으로 적용합니다. 그런 다음 쿼리는 노드별로 결과를 그룹화하여 회계 방정식을 지역적으로 검증합니다.
WITH RECURSIVE AccountHierarchy AS ( -- 앵커: 초기 부호 로직을 가진 루트 계좌 SELECT a.account_id, a.parent_id, a.account_type, a.amount, CASE WHEN a.is_contra = 1 THEN -1 ELSE 1 END AS sign_multiplier, CAST(a.account_id AS VARCHAR(1000)) AS path, 1 AS depth FROM accounts a WHERE a.parent_id IS NULL UNION ALL -- 재귀: 자식이 부모의 누적 부호를 상속받음 SELECT c.account_id, c.parent_id, c.account_type, c.amount, p.sign_multiplier * CASE WHEN c.is_contra = 1 THEN -1 ELSE 1 END, p.path || ',' || CAST(c.account_id AS VARCHAR(1000)), p.depth + 1 FROM accounts c INNER JOIN AccountHierarchy p ON c.parent_id = p.account_id -- 사이클 감지: 잘못된 데이터로 인해 무한 루프 방지 WHERE p.path NOT LIKE '%,' || CAST(c.account_id AS VARCHAR(1000)) || ',%' AND p.path != CAST(c.account_id AS VARCHAR(1000)) ), NodeBalances AS ( SELECT account_id, depth, SUM(CASE WHEN account_type = 'Asset' THEN amount * sign_multiplier ELSE 0 END) AS assets, SUM(CASE WHEN account_type IN ('Liability', 'Equity') THEN amount * sign_multiplier ELSE 0 END) AS liab_equity FROM AccountHierarchy GROUP BY account_id, depth ) SELECT account_id, CASE WHEN ABS(assets - liab_equity) < 0.01 THEN 'Balanced' ELSE 'IMBALANCE DETECTED' END AS validation_status, assets, liab_equity FROM NodeBalances ORDER BY depth, account_id;
한 포춘 500 제조 회사는 분기 SEC 보고서를 준비하고 있었습니다. 그들의 일반 원장에는 12개의 자회사를 포함하여 50,000개 이상의 계좌가 있으며, 계층 깊이는 15단계였습니다. 통합 과정에서 회계 팀은 기업 재무 제표의 잔액이 맞았지만, 개별 비즈니스 유닛은 잘못 분류된 반대 계정(예: 자사주가 자본에서 더하는 것이 아니라 빼는 것으로 처리됨)으로 인해 불가능한 부정 자산 총계를 보였습니다.
문제 설명
CFO는 기업 본사로 집계되기 전에 계정 목록의 모든 노드에서 회계 방정식의 검증을 요구했습니다. 단순한 하향 집계는 실패했습니다. 왜냐하면 자사주(반대 자본)는 총 자본을 줄여야 하지만, 자식 계정(특정 주식 매입 분할)은 양의 값을 유지하며 부정 집계 논리를 상속받아야 했기 때문입니다. 데이터 양과 48시간 제출 마감일로 인해 Excel을 통한 수동 검증은 불가능했습니다.
고려한 대안
절차적 ETL 접근법: 전체 계층을 Python으로 추출하고, networkx를 사용하여 트리 구조를 생성한 다음, 균형을 재귀적으로 계산하고 위반 사항을 데이터베이스에 다시 기록합니다. 장점: 복잡한 비즈니스 논리를 명령형 코드로 쉽게 구현할 수 있습니다. 단점: 2GB의 재무 데이터를 네트워크로 전송해야 하며, 회사의 "데이터 거주지" 보안 정책을 위반하고 6시간이 소요되었습니다.
수준 물질화를 통한 자가 조인: 각 계정의 수준을 비재귀적 방법으로 미리 계산한 후, 각 수준마다 부호 논리를 적용하여 15개의 자가 조인을 수행합니다. 장점: 재귀 없이 순수 SQL입니다. 단점: 쿼리가 15배 자가 조인 악몽이 되었으며, 복잡도가 기하급수적으로 증가하고 성능이 45분으로 저하되었으며, 16번째 수준을 추가하려면 쿼리를 완전히 다시 작성해야 했습니다. 또한 "반대의 반대" 부호 곱셈을 중첩된 CASE 문으로 다루어야 했습니다.
부호 전파를 통한 재귀 CTE: ANSI SQL 재귀 CTE를 사용하여 위에서 설명한 솔루션을 구현합니다. 장점: 동적으로 임의 깊이를 처리할 수 있으며(최대 20단계 테스트 완료), 전체 데이터 세트에서 8초 이내에 실행되며, 데이터 지역성을 유지하고 산술적으로 부호 곱셈을 올바르게 구현합니다(-1 × -1 = 1). 단점: 재귀 CTE 실행 계획 및 잘못된 계층 데이터로 인해 잘못된 쿼리가 발생하지 않도록 사이클 감지에 대한 이해가 필요합니다.
어떤 솔루션을 선택했으며 그 이유는 무엇입니까?
재귀 CTE 접근 방식이 선택되었습니다. 이는 엄격한 보안 요구 사항(데이터 거주지)을 충족하고 15분 SLA 내에 수행되며, 새로운 자회사를 인수했을 때 코드 변경이 전혀 필요하지 않았습니다. 모든 노드에서 검증할 수 있는 기능을 통해 10-K 제출에 발생할 수 있는 물질적 오해를 일으킬 수 있는 23개의 잘못 분류된 계정을 첫 번째 실행에서 식별했습니다.
결과
검증 쿼리는 SOX 준수 프레임워크의 중요한 자동화된 제어가 되었으며, 이제 매 재무 마감 전에 자동으로 실행되어 통합 오류를 방지하고 조정 시간을 6시간에서 10분 이내로 줄였습니다. 두 번째 분기에는 이전의 Excel 기반 프로세스가 간과했던 "대손 충당금"에서 230만 달러의 분류 오류를 감지하여 재제출을 피하는 데 기여했습니다.
상반된 계정이 다른 상반된 계정의 상위에 있을 때 여러 수준에서 부호 곱셈기를 올바르게 전파하려면 어떻게 해야 합니까?
많은 후보자들은 최종 SELECT에서 계정의 고유 is_contra 플래그와 account_type만을 기반으로 CASE 문을 사용하여 부호를 결정하려고 시도합니다. 이는 계층적 맥락을 무시하기 때문에 실패합니다. 올바른 접근법은 부호를 누적 속성으로 취급하여 재귀 중에 부모의 sign_multiplier에 자식의 고유한 부호(1 또는 -1)를 곱하는 것입니다. 이렇게 하면 다른 반대 계정 하위에 있는 반대 계정(부호 -1)이 결과적으로 긍정적인 기여를 하게 되어(-1 × -1 = 1) 할아버지에 더해지는 반대 계정으로 올바르게 표현됩니다. 이러한 곱셈 전파 없이 중간 잔액이 올바르지 않게 됩니다. 루트 잔액이 우연히 일치하더라도 마찬가지입니다.
부모 중 어떤 노드에도 나타나지 않는 자산의 경우 회계 방정식을 검증하는 ANSI SQL 방법은 무엇입니까?
후보자들은 종종 방정식이 모든 임의의 노드에서 성립해야 한다고 가정하지만, "유동 자산(Current Assets)"와 같은 부분 트리는 해당 부채 섹션이 없을 수 있습니다. 해결책은 검증 논리가 집합 노드(순수 집계 부모)와 완전한 회계 방정식을 구별해야 한다는 것을 인식하는 것입니다. 어떠한 노드에 대해서도 부호가 있는 금액의 대수적 합계를 계산하고 자산 측면을 부채+자본 측면과 비교합니다. HAVING 절을 사용하여 양쪽에서 잔액이 0인 노드(순수 헤더)를 필터링하여 잘못된 긍정 결과를 피합니다. 부분 서브트리에 대해서는 포함된 카테고리에 대해 수학적 관계가 성립하는지 확인해야 하며, 모든 세 가지 카테고리가 반드시 존재해야 하는 것은 아닙니다.
ANSI SQL 재귀 CTE가 계정 계층을 탐색할 때 명시적인 사이클 감지를 필요로 하는 이유는 무엇이며, 이것을 독점 데이터베이스 확장 없이 어떻게 구현합니까?
후보자들은 종종 생산 재무 데이터에 순환 참조와 같은 데이터 품질 문제(예: 계정 A가 계정 B의 부모이고, 계정 B가 계정 C의 부모이며, 계정 C가 우연히 계정 A를 가리킴)가 포함되어 있음을 간과합니다. 이러한 safeguard 없이는 재귀 CTE가 데이터베이스 재귀 한도에 도달하거나 모든 임시 저장소를 소비할 때까지 실행되어, 중요한 재무 마감 중 검증 작업이 중단됩니다. ANSI SQL:1999는 CYCLE 절을 도입했지만, 이식 가능한 구현을 위해서는 재귀 CTE에서 방문한 ID의 경로 문자열 또는 배열을 유지해야 합니다. 자식과 조인하기 전에 그 ID가 부모의 경로 문자열에 이미 존재하지 않는지 확인합니다. 탐지되면 해당 행을 제외하여 종료를 보장합니다. 이러한 방어적 프로그래밍은 손상된 계층 데이터로도 쿼리가 완료되도록 합니다.