경영진에게 설명할 때는 골프 비유를 사용하세요.
다른 모든 것에는 자동차 비유를 사용하세요. - 공자.
창 함수의 목적은 비즈니스 보고 요구 사항을 선언적으로 효과적으로 SQL로 변환하여 쿼리 성능과 개발자/비즈니스 분석가의 효율성을 획기적으로 개선하는 것입니다. 실제 보고서와 대시보드가 창 함수를 사용한 후 몇 시간에서 몇 분으로, 몇 분에서 몇 초로 단축되는 것을 보았습니다. 쿼리 크기가 40페이지에서 몇 페이지로 줄어듭니다. 90년대에 Redbrick 데이터베이스는 비즈니스 사용 사례를 잘 이해하고 순위, 실행 합계, 하위 그룹, 직위 등을 기준으로 커미션 및 재고 계산 등 비즈니스 보고를 위한 새로운 기능 계층을 만들었습니다. 이러한 기능은 2003년부터 SQL 표준에 포함되었습니다. 모든 BI 계층(예: Tableau, Looker, Cognos)은 이 기능을 활용합니다.
창 기능 소개
두 라운드를 통해 6명의 골퍼의 스코어가 있다고 가정해 보겠습니다. 이제 리더보드를 만들어 순위를 매겨야 합니다. SQL을 사용하여 순위를 매깁니다.
| 플레이어 | Round1 | Round2 |
| Marco | 75 | 73 |
| Johan | 72 | 68 |
| Chang | 67 | 76 |
| Isha | 74 | 71 |
| Sitaram | 68 | 72 |
| 빙지에 | 71 | 67 |
카우치베이스에 데이터를 삽입합니다.
|
1 2 3 4 5 6 7 8 9 10 11 |
삽입 INTO 골프 가치("KP1", {"player": "Marco", "round1":75, "round2":73}), 가치("KP2", {"player": "Johan", "round1":72, "round2":68}), 가치("KP3", {"player": "Chang", "round1":67, "round2":76}), 가치("KP4", {"player": "Isha", "round1":74, "round2":71}), 가치("KP5", {"player": "Sitaram", "round1":68, "round2":72}), 가치("KP6", {"player": "Bingjie", "round1":71, "round2":67}); |
창 기능 없음(현재 상태 - Couchbase 6.0)
창 함수를 사용하지 않고 쿼리를 작성하려면 각 플레이어의 순위를 계산하는 하위 쿼리가 필요합니다. 이 하위 쿼리는 모든 데이터를 스캔해야 합니다. 의 최악의 알고리즘 복잡성을 초래합니다. O(N^2), 실행 시간과 처리량이 크게 증가합니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
WITH g1 as (선택 플레이어, round1, round2 에서 골프) 선택 g3.player AS 플레이어, (g3.round1+g3.round2) AS T, ((g3.round1+g3.round2) - 144) AS ToPar, (선택 raw 1+COUNT(*) 에서 g1 as g2 어디 (g2.round1 + g2.round2) < (g3.round1 + g3.round2))[0] AS sqlrankR2 FROM g1 as g3 주문 BY sqlrankR2 결과: T ToPar 플레이어 sqlrankR2 138 -6 "Bingjie" 1 140 -4 "Johan" 2 140 -4 "Sitaram" 2 143 -1 "Chang" 4 145 1 "Isha" 5 148 4 "Marco" 6 |
Mad-Hatter의 창 기능 사용(출시 예정)
이 쿼리는 플레이어, 두 라운드(T) 후 합계, 스코어의 오버/언더파(ToPar) 비율을 반환합니다. 순위 첫 두 라운드의 점수를 기준으로 점수를 매깁니다. 이것이 Mad-Hatter의 새로운 기능입니다. 이 기능의 시간 복잡도는 O(N)이므로 실행 시간이 선형적으로만 증가합니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
선택 플레이어 AS 플레이어, (라운드1+라운드2) AS T, ((round1+라운드2) - 144) AS ToPar, RANK() OVER(주문 BY (라운드1+round2)) AS rankR2 FROM 골프; T ToPar 플레이어 rankR2 138 -6 "Bingjie" 1 140 -4 "Johan" 2 140 -4 "Sitaram" 2 143 -1 "Chang" 4 145 1 "Isha" 5 148 4 "Marco" 6 |
관찰:
- 이 쿼리는 요구 사항을 간단하고 명확하게 표현합니다.
- 실제 시나리오에서 이 쿼리의 성능은 훨씬 더 좋습니다. 측정할 계획입니다.
- 순위 요구 사항이 여러 문서에 따라 달라지는 경우 쿼리 작성, 최적화 및 실행이 매우 복잡해집니다.
- 이 모든 것이 전체 TCO에 영향을 미칩니다.
이제 확장된 대시보드를 만들어 보겠습니다.
밀도 추가 순위, 행 번호, 선두자, 선두자 뒤에 있는 스트로크 수를 표시합니다. 모두 보고 리포지셔닝에서 매우 일반적인 것들입니다. OVER() 절이 보일 때마다 새 창 함수가 표시됩니다. 아래 쿼리에는 6개의 창 함수가 있습니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
선택 플레이어 AS 플레이어, (라운드1+라운드2) AS T, ((round1+라운드2) - 144) AS ToPar, RANK() OVER(주문 BY (라운드1+round2)) AS rankR2, DENSE_RANK() OVER (주문 BY (라운드1+round2)) AS rankR2Dense, ROW_NUMBER() OVER() 로넘, ((round1+라운드2) - FIRST_VALUE(라운드1+라운드2) OVER(주문 BY (라운드1+round2))) AS 스트로크 뒤에, RANK() OVER(주문 BY (라운드1)) AS rankR1, LAG(플레이어, 1, "없음") OVER(주문 BY round1+라운드2) AS inFront FROM 골프 주문 BY rankR2 T ToPar inFront 플레이어 rankR1 rankR2 rankR2Dense 로넘 스트로크 뒤 138 -6 "없음" "Bingjie" 3 1 1 3 0 140 -4 "Johan" "Sitaram" 2 2 2 2 2 140 -4 "Bingjie" "Johan" 4 2 2 4 2 143 -1 "Sitaram" "Chang" 1 4 3 1 5 145 1 "Chang" "Isha" 5 5 4 5 7 148 4 "Isha" "Marco" 6 6 5 6 10 |
앞서 보았듯이 이 쿼리를 수행하려면 다음을 사용하는 6가지 창 기능 하위 쿼리 방법은 더 많은 노력이 필요하고 비용이 많이 들며 오류가 발생하기 쉬운 쿼리입니다.
기본 제공 집계(COUNT, SUM, AVG 등)를 창 함수로 만드는 것 외에도 Sitaram은 다음과 같은 창 함수를 추가했습니다. 각각의 구문과 의미는 표준에 잘 정의되어 있으며 아래 참조 섹션의 문서에 잘 설명되어 있습니다.
RANK()
DENSE_RANK()
PERCENT_RANK()
CUME_DIST()
NTILE()
ratio_to_report()
ROW_NUMBER()
LAG()
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()
LEAD()
참조:
- 아마도 가장 멋진 SQL 기능일 것입니다: 창 함수. https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/
- 분석 함수의 세계를 들여다보는 창입니다. https://blogs.oracle.com/oraclemagazine/a-window-into-the-world-of-analytic-functions
- 오라클 참조: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
