질문에 대한 답변

파레토 원칙은 이탈리아의 토지 소유에 대한 빌프레도 파레토의 관찰에서 출발하여, 후에 조셉 주란의 작업을 통해 품질 관리와 재고 관리의 핵심 요소가 되었습니다. 관계형 데이터베이스에서는 ABC 분석의 필요성을 나타내며, 분석가들은 외부 통계 도구를 사용하지 않고 비즈니스 가치의 대부분을 드라이브하는 중요한 소수의 레코드를 식별해야 합니다.

이 문제는 내림차순 정렬된 메트릭에 대한 누적 비율을 절대 총액에 대해 계산한 다음 80% 임계값에서 잘라내는 과정을 요구합니다. ANSI SQL은 반복 커서가 아닌 집합에서 작동하므로, 창 함수는 선언적 메커니즘을 제공합니다. 이 솔루션은 전체 결과 집합에 대해 파티셔닝된 누적 합을 사용하는데, 이는 내림차순으로 정렬되며 같은 행 컨텍스트 내에서 총액으로 나누어 백분위수를 도출합니다.

중요하게도, 프레임 사양 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 행별로 결정적인 누적을 보장합니다. 동점 처리에 대한 엄격한 처리가 필요한 경우, 모든 레코드가 경계 값을 공유할 때 이를 단위로 포함하거나 제외하는 경우에 RANGE가 ROWS를 대체합니다. 최종 필터링은 외부 쿼리에서 발생해야 합니다. 왜냐하면 창 함수는 WHERE 절 이후에 논리적으로 계산되기 때문입니다.

WITH ranked_products AS (
    SELECT 
        product_id,
        product_name,
        annual_revenue,
        SUM(annual_revenue) OVER (
            ORDER BY annual_revenue DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_revenue,
        SUM(annual_revenue) OVER () AS total_revenue
    FROM inventory
),
pareto_subset AS (
    SELECT 
        product_id,
        product_name,
        annual_revenue,
        CAST(cumulative_revenue AS DECIMAL) / total_revenue AS cumulative_pct
    FROM ranked_products
)
SELECT product_id, product_name, annual_revenue, cumulative_pct
FROM pareto_subset
WHERE cumulative_pct <= 0.80;

실제 상황

한 국가의 전자 제품 소매업체는 분기 재고 감사 중 비용이 증가하고 있어 전체 창고 자본($50M에 40,000개 품목)이 차지하는 80%에 해당하는 고가치 SKU를 분리하여 사이클 카운트를 우선시해야 했습니다.

솔루션 1: 스프레드시트 추출은 분석가가 CSV 파일을 Excel로 내보내고 단가에 따라 정렬한 후 임계값에 도달할 때까지 수작업으로 합산하는 것이었습니다. 전문가는 개발 시간이 필요 없었습니다. 단점으로는 대규모 데이터 세트에서 어플리케이션 충돌, 시간당 재계산 필요, 창고 관리 시스템과 실시간 통합이 방해되었습니다.

솔루션 2: 애플리케이션 레이어 계산은 Python 스크립트를 활용하여 행을 스트리밍하고 실행 누산기를 유지하는 방식이었습니다. 장점은 유동적인 논리와 쉬운 디버깅을 제공했습니다. 단점으로는 수백만 개의 행을 전송하면서 상당한 네트워크 지연이 발생했고, 단일 스레드 실행으로 Analytics 대시보드를 차단하며, 클라이언트 머신의 메모리 제약이 있었습니다.

솔루션 3: ANSI SQL 집합 기반 접근법은 PostgreSQL 창고 내에서 창 함수 쿼리를 직접 구현한 것입니다. 장점은 밀리초 수준의 지연, 데이터 이동 제거, 야간 업데이트에 따른 자동 새로 고침이 포함되었습니다. 단점으로는 유지보수를 위한 고급 SQL 지식이 필요했습니다.

선택된 솔루션과 결과: 솔루션 3이 뷰로 배포되었으며, 단지 12%의 SKU가 80%의 가치를 차지한다는 것을 밝혀냈습니다. 감사 범위가 88% 줄어들어 분기별 340 노동 시간을 절약하면서도 자재 가치의 완전한Coverage를 유지했습니다.

후보자들이 종종 놓치는 점

동점이 존재할 때 ROWS와 RANGE 프레임 사양의 선택이 80% 임계값에 어떤 영향을 미칩니까?

RANGE는 동일한 ORDER BY 값을 가진 동급 행을 단일 그룹으로 취급합니다. 만약 80% 경계가 동점 내에 있을 경우, RANGE는 전체 그룹을 포함하여 80%를 초과할 수 있습니다. ROWS는 동점과 관계없이 물리적 오프셋을 처리하므로 논리적 비즈니스 단위를 나눌 수 있습니다. 후보자들은 종종 ANSI SQL이 이 동작을 명시적으로 조정할 수 있도록 허용한다는 사실을 놓칩니다. 재무 보고를 위해 RANGE는 일관된 기간이 나눠지지 않도록 보장하며, ROWS는 별도의 항목에 대해 더 세밀한 세분화를 제공합니다.

왜 누적 비율 계산은 파생 테이블이나 CTE에서 수행해야 하며, 직접 WHERE 절에서 수행할 수 없는가?

창 함수는 SELECT 단계 중에 논리적으로 평가되며, 이는 WHERE 절이 행을 필터링한 후에 발생합니다. cumulative_revenue / total_revenue <= 0.8을 WHERE에서 직접 필터링하면 구문 오류가 발생합니다. 왜냐하면 창 결과가 아직 구체화되지 않았기 때문입니다. 후보자들은 ANSI SQL의 논리적 처리 순서인 FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT → ORDER BY를 자주 혼란스러워합니다. 솔루션은 내 쿼리 내에서 창 함수를 계산한 다음, 외부 쿼리에서 결과 열을 필터링하기 위해 중첩이 필요합니다.

재고 테이블에 수십억 개의 행이 포함되어 있고 80% 하위 집합이 매우 작을 것으로 예상되는 경우 이 쿼리를 어떻게 최적화하시겠습니까?

후보자들은 종종 Top-N 최적화 패턴을 놓칩니다. 전체 테이블에 대해 창 함수를 계산하는 대신, DENSE_RANK() 또는 **NTILE()**를 사용하는 하위 쿼리로 초기 필터링을 수행하여 창 계산을 가장 중요한 후보로 제한해볼 수 있습니다. 대안으로, 분석이 범주별로 세분화된다면 PARTITION BY를 활용하여 전체 테이블 검색을 방지할 수 있습니다. 창 함수가 정렬 작업을 강제하므로, 수익 열의 내림차순 인덱스가 정렬 비용을 제거할 수 있다는 것을 이해하는 것이 매우 중요합니다.

ANSI SQL 창 함수 기술을 사용하여 누적 기여도가 총계의 80%를 차지하는 최소 정렬된 레코드 하위 집합을 분리하는 방법을 설명하시오.

질문에 대한 답변

실제 상황

후보자들이 종종 놓치는 점