[mysql] SQL OVER () 절-언제 그리고 왜 유용합니까?

Program / 글쓴이 
    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

다른 COUNTs를, 아니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 년 이상이 왜 필요한지 모르겠습니다.