USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
나는 그 조항에 대해 읽고 왜 그것이 필요한지 이해하지 못한다. 기능 Over
은 무엇을합니까? 무엇을 Partitioning By
합니까? 글쓰기로 질의를 할 수없는 이유는 무엇 Group By SalesOrderID
입니까?
답변
당신은 할 수 있습니다 사용 GROUP BY SalesOrderID
. 차이점은 GROUP BY를 사용하면 GROUP BY에 포함되지 않은 열에 대해 집계 된 값만 가질 수 있다는 것입니다.
반대로 GROUP BY 대신 창 집계 함수를 사용하면 집계 된 값과 집계되지 않은 값을 모두 검색 할 수 있습니다. 즉, 예제 쿼리에서 수행하지 않지만 OrderQty
동일한 값 SalesOrderID
의 그룹에서 개별 값과 해당 합계, 수, 평균 등을 모두 검색 할 수 있습니다.
다음은 윈도우 집계가 큰 이유에 대한 실제 예입니다. 모든 값의 총 백분율을 계산해야한다고 가정하십시오. 창 집계가 없으면 먼저 집계 된 값 목록을 가져온 다음 원래 행 집합에 다시 결합해야합니다. 예를 들면 다음과 같습니다.
SELECT
orig.[Partition],
orig.Value,
orig.Value * 100.0 / agg.TotalValue AS ValuePercent
FROM OriginalRowset orig
INNER JOIN (
SELECT
[Partition],
SUM(Value) AS TotalValue
FROM OriginalRowset
GROUP BY [Partition]
) agg ON orig.[Partition] = agg.[Partition]
이제 창 집계로 어떻게 똑같이 할 수 있는지 살펴보십시오.
SELECT
[Partition],
Value,
Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent
FROM OriginalRowset orig
훨씬 쉽고 깨끗하지 않습니까?
답변
이 OVER
절은 사용자가 사용하는 여부에 관계없이 다른 범위 ( “창”)에 대한 집계를 가질 수 있다는 점에서 강력합니다.GROUP BY
여부
예 : 당 SalesOrderID
개수 및 전체 개수 가져 오기
SELECT
SalesOrderID, ProductID, OrderQty
,COUNT(OrderQty) AS 'Count'
,COUNT(*) OVER () AS 'CountAll'
FROM Sales.SalesOrderDetail
WHERE
SalesOrderID IN(43659,43664)
GROUP BY
SalesOrderID, ProductID, OrderQty
다른 COUNT
s를, 아니GROUP BY
SELECT
SalesOrderID, ProductID, OrderQty
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'CountQtyPerOrder'
,COUNT(OrderQty) OVER(PARTITION BY ProductID) AS 'CountQtyPerProduct',
,COUNT(*) OVER () AS 'CountAllAgain'
FROM Sales.SalesOrderDetail
WHERE
SalesOrderID IN(43659,43664)
답변
SalesOrderID 만 그룹화하려는 경우 SELECT 절에 ProductID 및 OrderQty 열을 포함 할 수 없습니다.
PARTITION BY 절을 사용하면 집계 함수를 분리 할 수 있습니다. 한 가지 분명하고 유용한 예는 주문에서 주문 라인의 라인 번호를 생성하려는 경우입니다.
SELECT
O.order_id,
O.order_date,
ROW_NUMBER() OVER(PARTITION BY O.order_id) AS line_item_no,
OL.product_id
FROM
Orders O
INNER JOIN Order_Lines OL ON OL.order_id = O.order_id
(내 문법이 약간 꺼져있을 수 있습니다)
그런 다음 다음과 같은 것을 다시 얻을 수 있습니다.
order_id order_date line_item_no product_id
-------- ---------- ------------ ----------
1 2011-05-02 1 5
1 2011-05-02 2 4
1 2011-05-02 3 7
2 2011-05-12 1 8
2 2011-05-12 2 1
답변
예를 들어 설명하면 어떻게 작동하는지 볼 수 있습니다.
다음 테이블 DIM_EQUIPMENT가 있다고 가정합니다.
VIN MAKE MODEL YEAR COLOR
-----------------------------------------
1234ASDF Ford Taurus 2008 White
1234JKLM Chevy Truck 2005 Green
5678ASDF Ford Mustang 2008 Yellow
SQL에서 실행
SELECT VIN,
MAKE,
MODEL,
YEAR,
COLOR ,
COUNT(*) OVER (PARTITION BY YEAR) AS COUNT2
FROM DIM_EQUIPMENT
결과는 다음과 같습니다
VIN MAKE MODEL YEAR COLOR COUNT2
----------------------------------------------
1234JKLM Chevy Truck 2005 Green 1
5678ASDF Ford Mustang 2008 Yellow 2
1234ASDF Ford Taurus 2008 White 2
무슨 일이 있었는지보십시오.
YEAR에 Group By없이 계산하고 ROW와 일치시킬 수 있습니다.
WITH WITHe 절을 사용하여 아래에서와 같이 동일한 결과를 얻는 또 다른 재미있는 방법, WITH는 인라인 VIEW로 작동하고 특히 복잡한 쿼리를 쿼리를 단순화 할 수 있습니다.
WITH EQ AS
( SELECT YEAR AS YEAR2, COUNT(*) AS COUNT2 FROM DIM_EQUIPMENT GROUP BY YEAR
)
SELECT VIN,
MAKE,
MODEL,
YEAR,
COLOR,
COUNT2
FROM DIM_EQUIPMENT,
EQ
WHERE EQ.YEAR2=DIM_EQUIPMENT.YEAR;
답변
OVER 절은 PARTITION BY와 결합 될 때 쿼리의 리턴 된 행을 평가하여 선행 함수 호출을 분석적으로 수행해야 함을 나타냅니다. 인라인 GROUP BY 문으로 생각하십시오.
OVER (PARTITION BY SalesOrderID)
SUM, AVG 등 … 함수에 대해 쿼리에서 반환 된 레코드의 하위 집합에 대한 값을 반환하고 외래 키 SalesOrderID에 의해 해당 하위 집합에 PARTITION을 반환합니다.
따라서 각 UNIQUE SalesOrderID에 대한 모든 OrderQty 레코드를 합산하고 해당 열 이름을 ‘Total’이라고합니다.
동일한 정보를 찾기 위해 여러 인라인보기를 사용하는 것보다 훨씬 효율적인 방법입니다. 이 쿼리를 인라인보기 내에 넣고 Total을 필터링 할 수 있습니다.
SELECT ...,
FROM (your query) inlineview
WHERE Total < 200
답변
Query Petition
절 이라고도합니다 .-
Group By
조항과 유사- 데이터를 청크 (또는 파티션)로 분할
- 파티션 경계로 분리
- 기능은 파티션 내에서 수행
- 절단 경계를 넘을 때 다시 초기화 됨
구문 :
function (…) OVER (PARTITION BY col1 col3, …)
-
기능
- 같은 친숙한 기능
COUNT()
,SUM()
,MIN()
,MAX()
, 등 - 뿐만 아니라 새로운 기능 (예를 들어
ROW_NUMBER()
,RATION_TO_REOIRT()
등)
- 같은 친숙한 기능
예를 들어 추가 정보 : http://msdn.microsoft.com/en-us/library/ms189461.aspx
답변
prkey whatsthat cash
890 "abb " 32 32
43 "abbz " 2 34
4 "bttu " 1 35
45 "gasstuff " 2 37
545 "gasz " 5 42
80009 "hoo " 9 51
2321 "ibm " 1 52
998 "krk " 2 54
42 "kx-5010 " 2 56
32 "lto " 4 60
543 "mp " 5 65
465 "multipower " 2 67
455 "O.N. " 1 68
7887 "prem " 7 75
434 "puma " 3 78
23 "retractble " 3 81
242 "Trujillo's stuff " 4 85
그것은 쿼리의 결과입니다. 소스로 사용 된 테이블은 마지막 열이없는 것과 같은 예외입니다. 이 열은 세 번째 열의 합입니다.
질문:
SELECT prkey,whatsthat,cash,SUM(cash) over (order by whatsthat)
FROM public.iuk order by whatsthat,prkey
;
(표는 public.iuk로갑니다)
sql version: 2012
dbase (1986) 수준을 약간 넘어서서 마무리하는 데 25 년 이상이 왜 필요한지 모르겠습니다.