윈도 함수
중급 SQL Server
Ginger Grant
Instructor
T-SQL에서 데이터 그룹화
SELECT SalesPerson, SalesYear,
CurrentQuota, ModifiedDate
FROM SaleGoal
WHERE SalesYear = 2011
+------------+----------+---------------+----------------+
|SalesPerson |SalesYear | CurrentQuota | ModifiedDate |
+------------+----------+---------------+----------------+
| Bob | 2011 | 28000.00 | 2011-04-16 |
| Bob | 2011 | 7000.00 | 2011-07-16 |
| Bob | 2011 | 91000.00 | 2011-10-16 |
| Mary | 2011 |367000.00 | 2011-04-16 |
| Mary | 2011 |556000.00 | 2011-07-16 |
| Mary | 2011 |502000.00 | 2011-10-16 |
+------------+----------+---------------+----------------+
T-SQL의 윈도 문법
OVER절로 윈도를 만듭니다PARTITION BY가 프레임을 정의합니다PARTITION BY가 없으면 프레임은 전체 테이블입니다- 결과 정렬은
ORDER BY를 사용합니다 - 윈도와 집계를 동시에 계산할 수 있습니다
. . .
-- Create a Window data grouping
OVER (PARTITION BY SalesYear ORDER BY SalesYear)
윈도 함수 (SUM)
SELECT SalesPerson, SalesYear, CurrentQuota,
SUM(CurrentQuota)
OVER (PARTITION BY SalesYear) AS YearlyTotal,
ModifiedDate AS ModDate
FROM SaleGoal
+------------+----------+-------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota |YearlyTotal | ModDate |
+------------+----------+-------------+------------+----------+
|Bob |2011 |28000.00 |1551000.00 |2011-04-16|
|Bob |2011 |7000.00 |1551000.00 |2011-07-17|
|Mary |2011 |367000.00 |1551000.00 |2011-04-16|
|Mary |2011 |556000.00 |1551000.00 |2011-07-15|
|Bob |2012 |70000.00 |1859000.00 |2012-01-15|
|Bob |2012 |154000.00 |1859000.00 |2012-04-16|
|Bob |2012 |107000.00 |1859000.00 |2012-07-16|
... | | | | |
+------------+----------+-------------+------------+----------+
윈도 함수 (COUNT)
SELECT SalesPerson, SalesYear, CurrentQuota,
COUNT(CurrentQuota)
OVER (PARTITION BY SalesYear) AS QuotaPerYear,
ModifiedDate AS ModDate
FROM SaleGoal
+------------+----------+------------+--------------+----------+
|SalesPerson |SalesYear |CurrentQuota|QuotaPerYear | ModDate |
+------------+----------+------------+--------------+----------+
|Bob |2011 |28000.00 |4 |2011-04-16|
|Bob |2011 |7000.00 |4 |2011-07-17|
|Mary |2011 |367000.00 |4 |2011-04-16|
|Mary |2011 |556000.00 |4 |2011-07-15|
|Bob |2012 |70000.00 |8 |2012-01-15|
|Bob |2012 |154000.00 |8 |2012-04-15|
|Bob |2012 |107000.00 |8 |2012-10-16|
...
+------------+----------+------------+------------+----------+
QuotaPerYear열에서 각 윈도마다 카운트가 다시 시작됩니다
연습해 봅시다!
중급 SQL Server
