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

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

---

    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 열을 포함 할 수 없습니다.

The PARTITION BY clause let's you break up your aggregate functions. One obvious and useful example would be if you wanted to generate line numbers for order lines on an order:

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

(My syntax might be off slightly)

You would then get back something like:

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

---

Let me explain with an example and you would be able to see how it works.

Assuming you have the following table DIM_EQUIPMENT:

VIN         MAKE    MODEL   YEAR    COLOR
-----------------------------------------
1234ASDF    Ford    Taurus  2008    White
1234JKLM    Chevy   Truck   2005    Green
5678ASDF    Ford    Mustang 2008    Yellow

Run below SQL

SELECT VIN,
  MAKE,
  MODEL,
  YEAR,
  COLOR ,
  COUNT(*) OVER (PARTITION BY YEAR) AS COUNT2
FROM DIM_EQUIPMENT

The result would be as below

VIN         MAKE    MODEL   YEAR    COLOR     COUNT2
 ----------------------------------------------  
1234JKLM    Chevy   Truck   2005    Green     1
5678ASDF    Ford    Mustang 2008    Yellow    2
1234ASDF    Ford    Taurus  2008    White     2

See what happened.

You are able to count without Group By on YEAR and Match with ROW.

Another Interesting WAY to get same result if as below using WITH Clause, WITH works as in-line VIEW and can simplify the query especially complex ones, which is not the case here though since I am just trying to show usage

 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;

---

The OVER clause when combined with PARTITION BY state that the preceding function call must be done analytically by evaluating the returned rows of the query. Think of it as an inline GROUP BY statement.

OVER (PARTITION BY SalesOrderID) is stating that for SUM, AVG, etc... function, return the value OVER a subset of the returned records from the query, and PARTITION that subset BY the foreign key SalesOrderID.

So we will SUM every OrderQty record for EACH UNIQUE SalesOrderID, and that column name will be called 'Total'.

It is a MUCH more efficient means than using multiple inline views to find out the same information. You can put this query within an inline view and filter on Total then.

SELECT ...,
FROM (your query) inlineview
WHERE Total < 200

---

• Also Called Query Petition Clause.

• Similar to the Group By Clause

  • break up data into chunks (or partitions)
  • separate by partition bounds
  • function performs within partitions
  • re-initialised when crossing parting boundary

Syntax:
function (...) OVER (PARTITION BY col1 col3,...)

• Functions

  • Familiar functions such as COUNT(), SUM(), MIN(), MAX(), etc
  • New Functions as well (eg ROW_NUMBER(), RATION_TO_REOIRT(), etc.)

More info with example : 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

That's a result of query. Table used as source is the same exept that it has no last column. This column is a moving sum of third one.

Query:

SELECT prkey,whatsthat,cash,SUM(cash) over (order by whatsthat)
    FROM public.iuk order by whatsthat,prkey
    ;

(table goes as public.iuk)

sql version:  2012

It's a little over dbase(1986) level, I don't know why 25+ years has been needed to finish it up.

참고URL : https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful