SQL: PARTITION BY와 GROUP BY의 차이

SQL: PARTITION BY와 GROUP BY의 차이

계속 사용하고 있습니다.GROUP BY여러 해 동안 모든 유형의 집계 쿼리에 사용할 수 있습니다.최근에, 저는 리버스 엔지니어링을 하고 있습니다.PARTITION BY집계를 수행합니다.

모든 문서를 읽어보면서 찾을 수 있는 것은 다음과 같습니다.PARTITION BY그것은 매우 비슷하게 들립니다.GROUP BY아마도 약간의 추가 기능이 추가되었을 것입니다.

일반적인 기능이 동일한 두 버전입니까? 아니면 완전히 다른 버전입니까?

그것들은 다른 장소에서 사용됩니다. GROUP BY다음과 같이 전체 쿼리를 수정합니다.

select customerId, count(*) as orderCount
from Orders
group by customerId

그렇지만PARTITION BY다음과 같은 창 기능에서만 작동합니다.

select row_number() over (partition by customerId order by orderId)
    as OrderNumberForThisCustomer
from Orders

• GROUP BY일반적으로 행을 롤업하고 각 행의 평균 또는 합계를 계산하여 반환되는 행 수를 줄입니다.
• PARTITION BY반환되는 행 수에는 영향을 주지 않지만 창 함수의 결과 계산 방법은 변경됩니다.

간단한 예를 들 수 있습니다.

이름이 지정된 테이블을 고려합니다.TableA다음 값을 사용합니다.

id  firstname                   lastname                    Mark
-------------------------------------------------------------------
1   arun                        prasanth                    40
2   ann                         antony                      45
3   sruthy                      abc                         41
6   new                         abc                         47
1   arun                        prasanth                    45
1   arun                        prasanth                    49
2   ann                         antony                      49

GROUP BY

SQL GROUP BY 절은 SELECT 문에서 여러 레코드에 걸쳐 데이터를 수집하고 결과를 하나 이상의 열로 그룹화하는 데 사용할 수 있습니다.

더 간단한 말로 GROUP BY 문은 결과 집합을 하나 이상의 열로 그룹화하기 위해 집계 함수와 함께 사용됩니다.

구문:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

신청할 수 있습니다.GROUP BY다음 표에서:

select SUM(Mark)marksum,firstname from TableA
group by id,firstName

결과:

marksum  firstname
----------------
94      ann                      
134     arun                     
47      new                      
41      sruthy   

의 실제 의 행이 가 우의개실테 7의행이 7있있우다습니를 할 때 사용합니다.GROUP BY id는 서버그결기합로니다반으과를에 .id:

간단히 말하면,

여기서GROUP BY되는 행 수를 하면 반환되는 행.Sum()각 행에 대해

PARTITION BY

BY로 에 PARTITION BY를 .OVER절:

MSDN 정의에 따라:

OVER 절은 쿼리 결과 집합 내에서 창 또는 사용자가 지정한 행 집합을 정의합니다.그런 다음 창 함수는 창의 각 행에 대한 값을 계산합니다.OVER 절을 이동 평균, 누적 집계, 실행 합계 또는 그룹별 결과 상위 N과 같은 집계 값을 계산하는 함수와 함께 사용할 수 있습니다.

PARTITION BY는 반환되는 행 수를 줄이지 않습니다.

예제 표에서 PARTITION BY를 적용할 수 있습니다.

SELECT SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname FROM TableA

결과:

marksum firstname 
-------------------
134     arun                     
134     arun                     
134     arun                     
94      ann                      
94      ann                      
41      sruthy                   
47      new  

결과를 확인합니다. GROUP BY와 달리 행을 분할하고 모든 행을 반환합니다.

partition by실제로 데이터를 롤업하지 않습니다.그룹별로 무언가를 재설정할 수 있습니다.예를 들어, 그룹화 필드에서 분할하고 다음을 사용하여 그룹 내에서 순서 열을 가져올 수 있습니다.rownum()해당 그룹 내의 행에 대해 설명합니다.이렇게 하면 각 그룹의 시작 부분에서 재설정되는 ID 열과 약간 유사한 동작을 하게 됩니다.

PARTITION BY 결과 집합을 파티션으로 나눕니다.창 기능이 각 파티션에 개별적으로 적용되고 각 파티션에 대해 계산이 다시 시작됩니다.

다음 링크에서 발견됨: OVER 절

PARTITION BY반에분입니다적석면▁is,.GROUP BY집합체입니다.사용하기 위해PARTITION BYOVER 절과 함께 포함해야 합니다.

롤업하지 않고 롤업된 데이터를 제공합니다.

예: 판매 지역의 상대적 위치를 반환하고 싶다고 가정합니다.

PARTITION BY를 사용하면 지정된 지역의 판매 금액과 동일한 행에 있는 모든 판매 지역의 MAX 금액을 반환할 수 있습니다.

이는 반복 데이터가 있음을 의미하지만, GROUP BY의 경우와 마찬가지로 데이터는 집계되었지만 데이터 손실은 없었다는 점에서 최종 소비자에게 적합할 수 있습니다.

파티션 기준은 그룹 기준과 거의 동일하지만 다음과 같은 차이점이 있습니다.

이 그룹은 실제로 그룹당 하나의 행을 반환하는 결과 집합을 그룹화합니다. 따라서 SQL Server는 SELECT 목록의 집계 함수 또는 그룹에 속한 열만 허용합니다(이 경우 SQL Server는 각 그룹에 대해 고유한 결과가 있음을 보장할 수 있습니다).

예를 들어 Group By 절에 정의되지 않은 SELECT 목록 열에 포함할 수 있는 MySQL을 생각해 보십시오. 이 경우에도 그룹당 하나의 행이 반환되지만 열에 고유한 결과가 없으면 출력이 무엇인지 보장할 수 없습니다!

그러나 Partition By를 사용하면 함수의 결과가 Group By를 사용한 집계 함수의 결과와 동일하더라도 기본 행당 하나의 행을 얻는 것이지 그룹당 하나의 행을 얻는 것이 아닙니다.따라서 SELECT 목록에서 그룹별로 고유하지 않은 열이 있을 수 있습니다.

따라서 요약하자면 Group By는 그룹당 하나의 행 출력이 필요할 때가 가장 좋으며 Partition By는 모든 행이 필요하지만 그룹을 기반으로 집계 기능이 필요할 때가 가장 좋습니다.

물론 성능 문제도 있을 수 있습니다. http://social.msdn.microsoft.com/Forums/ms-MY/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba 을 참조하십시오.

PARTITION BY의미론

당신의 질문은 특히 현재 지원하는 SQL Server에 관한 것이었습니다.PARTITION BY창 기능에만 있는 절이지만, 이 블로그 게시물에서 SQL의 다양한 의미에 대해 설명했듯이 다음과 같은 다른 절도 있습니다.

• 창 파티션(창 기능은 SQL 표준)
• 테이블 파티션(예: Oracle 또는 Postgre에서 스토리지를 구성하기 위한 공급업체별 확장)SQL)
• MATCH_REGOGNIZE파티션(SQL 표준이기도 함)
• MODEL또는SPREADSHEET파티션(SQL에 대한 Oracle 확장)
• OUTER JOIN파티션(SQL 표준)

마지막으로, 그것을 재사용하는 것을 제외하고.PARTITION BY일종의 구현을 위한 구문CROSS JOIN논리, 이 모든 것.PARTITION BY절의 의미는 동일합니다.

파티션은 데이터 집합을 중복되지 않는 하위 집합으로 분리합니다.

이 파티션을 기반으로 파티션당 추가 계산 또는 스토리지 작업을 구현할 수 있습니다.예: 다음과 같은 창 기능이 있는 경우COUNT(*) OVER (PARTITION BY criteria),COUNT(*)파티션당 값이 계산됩니다.

GROUP BY

GROUP BY에서는 유사한 파티셔닝 동작을 허용하지만 전체 쿼리의 의미를 다양한 이상한 방식으로 변환합니다.대부분의 쿼리 사용GROUP BY기능을 할 수 기을사다작수있성다습니할대신종시종능용창여, 하,종▁can,GROUP BY구문이 더 간결하고 더 잘 최적화될 수 있습니다.

예를 들어, 이것들은 논리적으로 동일하지만, 나는 예상합니다.GROUP BY나은 절: 은나성을위절한:

-- Classic
SELECT a, COUNT(*)
FROM t
GROUP BY a

-- Using window functions
SELECT DISTINCT a, COUNT(*) OVER (PARTITION BY a)
FROM t

주요 차이점은 다음과 같습니다.

• 되지 않은 함수일 를 들어, 창 는 수 있 습 도 니 다 일 수 함 함 비 수 집 계 니ROW_NUMBER()
• 창 한 각윈우기고기유가능질수있다습니을한능은도를 가질 수 .PARTITION BY 반면에 반에면, 항은조.GROUP BY쿼리당 하나의 식을 기준으로만 그룹화할 수 있습니다.

를 할 때GROUP BY결과 행은 일반적으로 들어오는 행보다 작습니다.

하지만, 당신이 사용할 때.PARTITION BY결과 행 수는 들어오는 행 수와 같아야 합니다.

작은 관찰.'partition by'를 사용하여 동적으로 SQL을 생성하는 자동화 메커니즘은 'group by'와 관련하여 구현하기가 훨씬 간단합니다.'group by'의 경우 'select'란의 내용을 관리해야 합니다.

제 영어실력에 대해 죄송합니다.

우리가 14개의 기록을 가지고 있다고 가정해 봅시다.name의 열

group by

select name,count(*) as totalcount from person where name='Please fill out' group BY name;

그것은 단일 행, 즉 14로 카운트할 것입니다.

그나러로partition by

select row_number() over (partition by name) as total from person where name = 'Please fill out';

그것은 14줄의 숫자가 증가할 것입니다.

사용 시나리오가 정말 다릅니다.GROUP BY를 사용하면 동일한 열의 일부 레코드를 병합하고 결과 집합을 집계할 수 있습니다.

그러나 결과 집합별 파티션을 사용하는 경우 창 기능에 집계만 있고 레코드를 병합하지 않는 경우에도 동일한 레코드 수가 유지됩니다.

차이를 설명하는 집회에 도움이 되는 기사가 있습니다. http://alevryustemov.com/sql/sql-partition-by/

-- BELOW IS A SAMPLE WHICH OUTLINES THE SIMPLE DIFFERENCES
-- READ IT AND THEN EXECUTE IT
-- THERE ARE THREE ROWS OF EACH COLOR INSERTED INTO THE TABLE
-- CREATE A database called testDB


-- use testDB
USE [TestDB]
GO


-- create Paints table
CREATE TABLE [dbo].[Paints](
    [Color] [varchar](50) NULL,
    [glossLevel] [varchar](50) NULL
) ON [PRIMARY]

GO


-- Populate Table
insert into paints (color, glossLevel)
select 'red', 'eggshell'
union
select 'red', 'glossy'
union
select 'red', 'flat'
union
select 'blue', 'eggshell'
union
select 'blue', 'glossy'
union
select 'blue', 'flat'
union
select 'orange', 'glossy'
union
select 'orange', 'flat'
union
select 'orange', 'eggshell'
union
select 'green', 'eggshell'
union
select 'green', 'glossy'
union
select 'green', 'flat'
union
select 'black', 'eggshell'
union
select 'black', 'glossy'
union
select 'black', 'flat'
union
select 'purple', 'eggshell'
union
select 'purple', 'glossy'
union
select 'purple', 'flat'
union
select 'salmon', 'eggshell'
union
select 'salmon', 'glossy'
union
select 'salmon', 'flat'


/*   COMPARE 'GROUP BY' color to 'OVER (PARTITION BY Color)'  */

-- GROUP BY Color 
-- row quantity defined by group by
-- aggregate (count(*)) defined by group by
select count(*) from paints
group by color

-- OVER (PARTITION BY... Color 
-- row quantity defined by main query
-- aggregate defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color)
from paints

/* COMPARE 'GROUP BY' color, glossLevel to 'OVER (PARTITION BY Color, GlossLevel)'  */

-- GROUP BY Color, GlossLevel
-- row quantity defined by GROUP BY
-- aggregate (count(*)) defined by GROUP BY
select count(*) from paints
group by color, glossLevel



-- Partition by Color, GlossLevel
-- row quantity defined by main query
-- aggregate (count(*)) defined by OVER-PARTITION BY
select color
, glossLevel
, count(*) OVER (Partition by color, glossLevel)
from paints

언급URL : https://stackoverflow.com/questions/2404565/sql-difference-between-partition-by-and-group-by