계산은 최소 제곱법에 의존합니다. 기울기(β)는 독립 변수 X와 종속 변수 Y의 공분산을 X의 분산으로 나누어 정의됩니다. 절편(α)은 Y의 평균에서 기울기와 X의 평균의 곱을 뺀 값입니다. ANSI SQL에서 이러한 대수적 정의를 SUM, AVG, COUNT 집계를 사용하여 구현합니다. 일반적으로 GROUP BY 절 또는 OVER 절을 가진 윈도우 함수를 사용합니다. 쿼리는 최종 계수를 해결하기 위해 교차 곱의 합(Σ(X - X̄)(Y - Ȳ))과 X에 대한 제곱 편차의 합(Σ(X - X̄)²)을 명시적으로 계산해야 합니다.
소매 분석 팀은 각 제품 카테고리에 대한 수요의 가격 탄력성을 결정하여 동적 가격 책정 전략을 최적화해야 했습니다. 그들은 unit_price 및 quantity_sold가 포함된 거래 테이블을 보유하고 있었으며, 각 고유한 category_id에 대해 가격 변화에 따라 판매 수량이 어떻게 변하는지를 정량화하는 추세선을 요구했습니다.
제안된 한 가지 솔루션은 매일 집계된 데이터를 외부 Python 스크립트로 내보내 scikit-learn을 사용하여 회귀 모델을 맞추는 것이었습니다. 이 접근 방식은 구현의 단순함과 풍부한 통계 진단에 대한 접근성을 제공했지만, 데이터 지연을 초래하고, 민감한 판매 데이터의 외부 복사본을 생성하여 엄격한 데이터 거버넌스 정책을 위반했으며, 자동화된 가격 알고리즘을 위한 실시간 대시보드 업데이트를 방해했습니다.
또 다른 고려된 옵션은 데이터베이스 엔진 내에서 사용자 정의 집계 함수(UDAF)를 만드는 것으로, REGRESS_SLOPE(price, quantity)와 같은 구문을 허용했습니다. 매력적이고 재사용 가능했지만, 다양한 데이터베이스 시스템 간의 이식성을 희생했으며, 배포를 위해 높은 관리 권한이 필요하여 잠금된 다중 테넌트 클라우드 데이터 웨어하우스 환경에 적합하지 않았습니다.
선택된 솔루션은 ANSI SQL에서 대수적 공식을 표준 집계를 사용하여 직접 구현했습니다. 팀은 category_id로 파티션된 SUM 및 AVG 윈도우 함수를 활용하여 필요한 공분산 및 분산 항목을 데이터에 대한 단일 패스로 계산했습니다. 이 접근 방식은 계산을 데이터와 함께 배치하고, 추출-변환-적재(ETL) 지연을 제거하며, 독점적인 확장 없이 이식 가능한 ANSI SQL 표준을 엄격히 준수했습니다. 그 결과 새로운 거래가 도착할 때마다 자동으로 업데이트되는 서브초 단위 지연의 가격 탄력성 대시보드가 생성되어, 자동화된 가격 알고리즘이 실시간으로 마진을 조정할 수 있도록 직접적으로 지원했습니다.
X 또는 Y의 NULL 값을 어떻게 처리하여 전체 그룹의 계산이 무효화되지 않도록 합니까?
후보자들은 ANSI SQL 집계 함수가 NULL을 무시하는 동안 NULL이 포함된 산술 연산은 NULL을 반환한다는 사실을 종종 잊습니다. 공분산 항을 계산할 때 SUM((x - avg_x) * (y - avg_y))에서 특정 행의 x 또는 y가 NULL인 경우, 곱은 NULL이 되어 해당 행이 합산에서 제외됩니다. 이는 일반적으로 원하는 쌍별 삭제를 수행하지만, 분산 계산에서 자유도의 COUNT가 총 행 수가 아니라 NULL이 아닌 쌍 수를 반영해야 한다는 점을 보장해야 합니다. 해결 방법은 서브쿼리에서 WHERE x IS NOT NULL AND y IS NOT NULL로 필터링하거나, COUNT(x)(필터링 후 COUNT(y)와 같음)를 사용하여 모든 집계 항목에서 일관된 분모를 보장하는 것입니다.
모집단에 대한 회귀 계산과 샘플에 대한 회귀 계산의 차이점은 무엇이며, 이것이 SQL 쿼리에 미치는 영향은 무엇입니까?
많은 후보자들이 공분산 공식과 일관되지 않게 샘플 분산 공식을 적용합니다(n - 1로 나누기). ANSI SQL에서는 VAR_POP 및 VAR_SAMP와 같은 내장 함수가 이 구분을 처리하지만, 분산을 수동으로 SUM(POWER(x - avg_x, 2)) / COUNT(*)로 계산할 때 분모를 의도적으로 선택해야 합니다. 기울기 계산을 위해 분모에서 X의 분산을 수동으로 계산하는 경우, 공분산 계산의 분모와 일치시켜야 합니다. 이를 혼합하면(예: 샘플 공분산을 모집단 분산으로 나누기) 편향된 기울기를 초래합니다. 수정된 접근 방식은 통계적 프레임(모집단 vs. 샘플)을 결정하고, 공분산 분자와 분산 분모에 대해 동일한 분모 논리를 적용하는 것입니다 (n 또는 n-1).
동일한 쿼리 내에서 적합도 측정을 위한 결정 계수(R²)를 어떻게 계산합니까?
후보자들은 종종 검증 메트릭을 간과합니다. R²는 1 - (SS_res / SS_tot)로 계산되며, 여기서 SS_res는 제곱 잔차의 합(Σ(y - ŷ)²)이고 SS_tot은 총 제곱합(Σ(y - ȳ)²)입니다. ŷ(예측된 y)를 계산하려면 앞서 계산한 기울기와 절편이 필요합니다. ANSI SQL에서는 이를 스택된 공통 테이블 표현식(CTE)을 사용하여 계산할 수 있습니다: 먼저 평균을 계산한 후, 두 번째 CTE에서 기울기와 절편을 계산하고, 마지막으로 외부 쿼리에서 실제 값과 예측 값 간의 제곱 차이를 계산합니다. 흔한 오류는 계산된 기울기를 계산된 정도의 집계 내에서 참조하려고 시도하는 것으로, 이는 논리적 처리 순서를 위반합니다. 해결 방법은 논리를 순차적인 CTE로 나누어 계산된 계수를 R²의 최종 집계에서 상수로 재사용할 수 있도록 하는 것입니다.