복붙노트

[SQL] 는 SQL OVER () 절 - 언제, 왜 유용한가?

SQL

는 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);

그 절에 대해 읽고 내가 필요한 이유를 이해하지 않습니다. 기능이 이상 무엇을 하는가? 파티션으로는 무엇입니까? 이유는 그룹으로 SalesOrderID에 기입을 쿼리를 만들 수없는 이유는 무엇입니까?

해결법

  1. ==============================

    1.당신은 GROUP BY SalesOrderID에 사용할 수 있습니다. 차이점은 GROUP BY와 함께 만 GROUP BY에 포함되지 않은 열에 대한 집계 값을 가질 수있다.

    당신은 GROUP BY SalesOrderID에 사용할 수 있습니다. 차이점은 GROUP BY와 함께 만 GROUP BY에 포함되지 않은 열에 대한 집계 값을 가질 수있다.

    반면, 대신 GROUP BY의, 윈도우를 집계 함수를 사용하여, 당신은 모두 집계 및 비 집계 값을 검색 할 수 있습니다. 즉, 귀하의 예제 쿼리에서, 개별 OrderQty 값과 같은 SalesOrderIDs 그룹 이상 등 자신의 합계, 개수, 평균 모두를 검색 할 수 있다고하고되지 않더라도이다.

    여기에, 윈도우를 집계 큰 이유의 실제 예입니다. 당신은 모든 값이 전체의 몇 퍼센트 계산해야 가정하자. 윈도우 집계하지 않고 먼저 집계 값 목록을 도출해야 할 것이다 다음 원래 행 집합, 즉이 등을 다시 조인

    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

    훨씬 쉽고 청소기, 그렇지?

  2. ==============================

    2.오버 절은 당신이 GROUP BY 여부 사용 여부, 당신은 ( "윈도") 다른 범위에서 집계를 가질 수 있다는 점에서 강력한

    오버 절은 당신이 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

    다른 수를 얻을, 어떤 그룹 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)
  3. ==============================

    3.당신은 단지 SalesOrderID에 의해 GROUP에 원하는 경우에 당신은 SELECT 절에있는 제품 ID와 OrderQty 열을 포함 할 수 없습니다.

    당신은 단지 SalesOrderID에 의해 GROUP에 원하는 경우에 당신은 SELECT 절에있는 제품 ID와 OrderQty 열을 포함 할 수 없습니다.

    절하자 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
  4. ==============================

    4.내가 예와 함께 설명하게하고 당신이 어떻게 작동하는지 볼 수있을 것입니다.

    내가 예와 함께 설명하게하고 당신이 어떻게 작동하는지 볼 수있을 것입니다.

    다음 테이블 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

    무슨 일이 있었는지를 참조하십시오.

    당신은 행과 년과 일치에 그룹으로하지 않고 계산 할 수 있습니다.

    또 다른 흥미로운 방법은 경우 인라인보기로 작품, 절을 사용하여 다음과 같이 동일한 결과를 얻을 난 그냥 사용을 보여주기 위해 노력하고 있기 때문에 비록 여기이 아니다 쿼리, 특히 복잡한 사람을 간소화 할 수 있습니다

     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;
  5. ==============================

    5.위의 함수 호출은 쿼리의 반환 된 행을 평가하여 분석적으로 수행해야한다는 국가의 파티션과 결합 된 OVER 절. 문 BY 인라인 GROUP이라고 생각.

    위의 함수 호출은 쿼리의 반환 된 행을 평가하여 분석적으로 수행해야한다는 국가의 파티션과 결합 된 OVER 절. 문 BY 인라인 GROUP이라고 생각.

    OVER (SalesOrderID에 BY 파티션) SUM, AVG 등을 위해 ... 기능, 쿼리에서 반환 된 레코드 집합을 통해 값을 반환한다는 및 파티션이 외래 키 SalesOrderID에 BY 부분 집합.입니다

    그래서 우리는 각각의 고유 한 SalesOrderID에 대한 모든 OrderQty 기록을 SUM 것이며, 그 열 이름은 '총'호출됩니다.

    이 같은 정보를 찾기 위해 여러 인라인 뷰를 사용하는 것보다 훨씬 더 효율적인 방법입니다. 그런 다음 총에 인라인보기 및 필터 내에서이 쿼리를 넣을 수 있습니다.

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

    6.통사론: 기능 (...) OVER (COL1 COL3 BY 파티션, ...)

    통사론: 기능 (...) OVER (COL1 COL3 BY 파티션, ...)

    예와 추가 정보 : http://msdn.microsoft.com/en-us/library/ms189461.aspx

  7. ==============================

    7.

    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

    그것은 25 + 년이 그것을 완료하는 데 필요한 된 이유 디베이스 이상 조금 (1986) 수준, 나도 몰라입니다.

    8. from https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL Server Management Studio의 테이블 편집 후 변경 사항을 저장  (0) 2020.04.23
[SQL] 어떻게 이름을 몰라도 SQL 기본 제약 조건을 삭제하려면?  (0) 2020.04.22
[SQL] SQL을 사용하지 않고 젠토 설치 스크립트에서 ALTER 표  (0) 2020.04.22
[SQL] MySQL의 : 잠금 테이블 대 거래  (0) 2020.04.22
[SQL] SQL 키, PRI 대 UNI 대 MUL  (0) 2020.04.22
,

티스토리툴바