SQL Server: różnica między partycją by a grupą BY

We can take a simple example

Mamy tabelę o nazwie TableA z następującymi wartościami .

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

Klauzula SQL GROUP BY może być używana w instrukcji SELECT do zbierania dane z wielu rekordów i grupowanie wyników według jednego lub więcej kolumny.

W prostszych słowach Grupa według wyrażenia jest używana w połączeniu z funkcje agregujące grupujące wynik-ustawione przez jedną lub więcej kolumny.

Składnia:

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

Możemy zastosować GroupBy w naszej tabeli

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

Wyniki:

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

W naszej rzeczywistej tabeli mamy 7 wierszy i gdy zastosujemy group by id, serwer grupuje wyniki na podstawie id

W prostych słowach

Tutaj grupowanie przez zwykle zmniejsza liczbę wierszy zwracanych przez toczenie je i obliczanie sumy dla każdego wiersza.

Partition by

Przed przejściem do partycji przez

Spójrzmy na klauzulę OVER

Zgodnie z definicją MSDN

Klauzula OVER definiuje okno lub określony przez użytkownika zbiór wierszy wewnątrz zestaw wyników zapytania. Funkcja okna następnie oblicza wartość dla każdego wiersza w oknie. Możesz użyć klauzuli OVER Z FUNKCJAMI do obliczenia wartości zagregowane, takie jak średnie kroczące, Agregaty skumulowane, wyniki biegowe lub najwyższe N na wyniki grupowe.

partycja przez nie zmniejszy Liczba zwróconych wierszy

Możemy zastosować partycję w naszej przykładowej tabeli

select SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname from TableA

Wynik:

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

Spójrz na wyniki To podzieli wiersze i wyniki Wszystkie wiersze nie jak grupy przez.

partition by nie zwija danych. Pozwala na zresetowanie czegoś na podstawie grupy. Na przykład, możesz uzyskać kolumnę porządkową w grupie, dzieląc ją na pole grupowania i używając rownum() nad wierszami w tej grupie. Daje to coś, co zachowuje się trochę jak Kolumna tożsamości, która resetuje się na początku każdej grupy.

PARTYCJA PRZEZ Dzieli zestaw wyników na partycje. Funkcja okna jest stosowana do każdej partycji oddzielnie i obliczenia są restartowane dla każdej partycji.

Znaleziono pod tym linkiem: nad klauzulą

Dostarcza zwijane dane bez zwijania

Tzn. Załóżmy, że chcę zwrócić względną pozycję regionu sprzedaży

Używając partycji by, Mogę zwrócić kwotę sprzedaży dla danego regionu i maksymalną kwotę we wszystkich regionach sprzedaży w tym samym wierszu.

Oznacza to, że będziesz mieć powtarzające się dane, ale może to odpowiadać konsumentowi końcowemu w tym sensie, że dane zostały zagregowane, ale żadne dane nie zostały utracone - jak to ma miejsce w przypadku grupy przez.

PARTITION BY jest analityczny, natomiast {[1] } jest zbiorczy. Aby użyć PARTITION BY, musisz go zawrzeć klauzulą OVER.

Według mnie partycja By jest prawie identyczna jak Group By, ale z następującymi różnicami:

Ta grupa by faktycznie grupuje zestaw wyników zwracający jeden wiersz na grupę, co powoduje, że w SQL Server zezwala tylko na liście SELECT agregować funkcje lub kolumny, które są częścią klauzuli group by(w takim przypadku SQL Server może zagwarantować, że dla każdej grupy są unikalne wyniki).

Rozważ na przykład MySQL, który pozwala mieć w SELECT lista kolumn, które nie są zdefiniowane w klauzuli Group By, w którym to przypadku jeden wiersz jest nadal zwracany na Grupę, jednak jeśli kolumna nie ma unikalnych wyników, to nie ma gwarancji, co będzie wynikiem!

Ale z partycją by, chociaż wyniki funkcji są identyczne z wynikami funkcji zbiorczej z grupą By, nadal otrzymujesz normalny zestaw wyników, co oznacza, że jeden z nich otrzymuje jeden wiersz na wiersz bazowy, a nie jeden wiersz na grupę, i z tego powodu można mieć kolumny, które nie są unikalne dla każdej grupy na liście SELECT.

Więc jako podsumowanie, Grupa By byłaby najlepsza, gdy potrzebuje wyjścia z jednego wiersza na grupę, a partycja By byłaby najlepsza, gdy potrzeba wszystkich wierszy, ale nadal chce zagregowanej funkcji opartej na grupie.

Oczywiście mogą być też problemy z wydajnością, zobacz http://social.msdn.microsoft.com/Forums/ms-MY/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba.

Załóżmy, że mamy 14 rekordów name kolumny w tabeli

W group by

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

Da liczbę w jednym rzędzie tj. 14

Ale w partition by

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

Będzie 14 rzędów wzrostu liczby

Mała obserwacja. Mechanizm automatyzacji dynamicznego generowania SQL za pomocą 'partition by' jest znacznie prostszy do wdrożenia w stosunku do'group by'. W przypadku 'group by' musimy zadbać o zawartość kolumny 'select'.

Przepraszam za mój angielski.

-- 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