창 기능(SQL)

Window function (SQL)

SQL에서 창 함수 또는 분석 함수는[1] 하나 또는 여러 행의 값을 사용하여 각 행에 대한 값을 반환하는 함수를 말한다. (이는 여러 행에 대해 단일 값을 반환하는 집계 함수와 대비된다.)창 함수는 OUP 절이 있다; OUP 절이 없는 함수는 창 함수가 아니라 집계 또는 단일 행(scalar) 함수다.[2]null

예를 들어, 창구 기능을 사용하여 각 직원을 소속 부서의 평균 급여와 비교(Postgre의 예)하는 질의가 여기에 있다.SQL 설명서:[3]

선택 이름을 삭제하다, 엠프노, 봉급, 탐내다(봉급) 오버 (파티션 BY 이름을 삭제하다) From 암말의;

출력:

depname 엠프노 급여 avg ----------------------------------------------------------------------------------------------------------- 11 5200 5020.00000000000000 7 4200 5020.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 3700.0000000000000000000000 2 3900 3700.000000000000 판매 3 48666666666667 판매 3 486666666667 판매 1 5000 4866666667 판매 4 4800 486666666666666667(10열)

그PARTITION BY절은 행을 파티션으로 그룹화하고, 함수는 각 파티션에 개별적으로 적용된다.만약PARTITION BY조항이 생략됨(예: 빈 항목이 있는 경우)OVER()조항), 그런 다음 전체 결과 집합을 단일 파티션으로 처리한다.[4]이 질의의 경우 보고된 평균 급여는 모든 행을 차지하는 평균 급여가 될 것이다.null

윈도우 함수는 집계 후 평가된다(예를 들어 절과 비 윈도우 집계 함수 후).[1]null

구문

포스그리에 따르면SQL 문서, 창 함수의 구문은 다음 중 하나이다.[4]

함수_이름 ([표현 [, 표현 ... ]]) 오버 window_name 함수_이름 ([표현 [, 표현 ... ]]) 오버 ( window_definition ) 함수_이름 ( * ) 오버 window_name 함수_이름 ( * ) 오버 ( window_definition )

어디에window_definition구문 포함:

[ existing_built_name ] [ 파티션 BY 표현 [, ...] ] [ 주문 BY 표현 [ ASC   DECK   사용. 교환원의 ] [ NULS { 퍼스트   지난 } ] [, ...] ] [ frame_frame ]

frame_clause다음 중 하나의 구문을 가지고 있다.

{ 범위   행   무리 } frame_start [ frame_frame ] { 범위   행   무리 } 사이 frame_start AND frame_end [ frame_frame ]

frame_start그리고frame_end수 있습니다.UNBOUNDED PRECEDING,offset PRECEDING,CURRENT ROW,offset FOLLOWING또는UNBOUNDED FOLLOWING.frame_exclusion수 있습니다.EXCLUDE CURRENT ROW,EXCLUDE GROUP,EXCLUDE TIES또는EXCLUDE NO OTHERS.

expression창 함수에 대한 호출을 포함하지 않는 식을 말한다.null

표기법:

  • 괄호 []는 선택적 절을 나타낸다.
  • 컬리브 가새 {}은(는) 가능한 여러 옵션 집합을 나타내며 각 옵션은 세로 막대로 구분되어 있음

예

윈도 기능은 현재 기록 직전과 직후 기록의 데이터에 접근할 수 있게 해준다.[5][6][7][8]창 함수는 현재 행을 중심으로 일정한 길이의 행의 프레임이나 창을 정의하고, 창의 데이터 집합에 걸쳐 계산을 수행한다.[9][10]null

이름 ----------------------------------- 앞 (무경계) 앤드류 아멜리아 제임스 질 조니 <-- 앞줄 마이클 <-- 현재 줄 닉 <-- 오필리아 자흐 <-- 후속 (무경계)

위의 표에서 다음 쿼리는 각 행에 대해 앞 행과 다음 행이 각각 하나씩 있는 창의 값을 추출한다.

 선택   LAG(이름을 붙이다, 1)      오버(주문 BY 이름을 붙이다) "prev",   이름을 붙이다,    이끌다(이름을 붙이다, 1)      오버(주문 BY 이름을 붙이다) "다음"  From 사람  주문 BY 이름을 붙이다

결과 쿼리는 다음 값을 포함한다.

PreV NAME NEXT ----------------------------------------------------------------------------------------------------------------------------------- 애런 앤드류 아멜리아 제임스 질 제임스 질 조니 질 조니 마이클 닉 오필리아닉 오필리아 자흐 오필리아 자흐(null)

역사

윈도 기능은 SQL:2003에 도입되었고 이후 사양에서 기능이 확장되었다.[11]null

참고 항목

참조

  1. ^ a b "Analytic function concepts in Standard SQL BigQuery". Google Cloud. Retrieved 2021-03-23.
  2. ^ "Window Functions". sqlite.org. Retrieved 2021-03-23.
  3. ^ "3.5. Window Functions". PostgreSQL Documentation. 2021-02-11. Retrieved 2021-03-23.
  4. ^ a b "4.2. Value Expressions". PostgreSQL Documentation. 2021-02-11. Retrieved 2021-03-23.
  5. ^ Leis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas (June 2015). "Efficient Processing of Window Functions in Analytical SQL Queries". Proc. VLDB Endow. 8 (10): 1058–1069. doi:10.14778/2794367.2794375. ISSN 2150-8097.
  6. ^ Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee (July 2012). "Optimization of Analytic Window Functions". Proc. VLDB Endow. 5 (11): 1244–1255. arXiv:1208.0086. doi:10.14778/2350229.2350243. ISSN 2150-8097.
  7. ^ "Probably the Coolest SQL Feature: Window Functions". Java, SQL and jOOQ. 2013-11-03. Retrieved 2017-09-26.
  8. ^ "Window Functions in SQL - Simple Talk". Simple Talk. 2013-10-31. Retrieved 2017-09-26.
  9. ^ "SQL Window Functions Introduction". Apache Drill.
  10. ^ "PostgreSQL: Documentation: Window Functions". www.postgresql.org. Retrieved 2020-04-04.
  11. ^ "Window Functions Overview". MariaDB KnowledgeBase. Retrieved 2021-03-23.