Znanost Podataka I Baze Podataka

Uvod u funkcije SQL prozora

Vrlo moćna značajka koju volite mrziti (ali morate znati)

SQL funkcije prozora pružaju neke izuzetno moćne i korisne značajke. No mnogima je, budući da su toliko strani standardnom SQL-u, teško naučiti i razumjeti, imaju neobičnu sintaksu - i vrlo često ih se izbjegava.

Funkcije prozora mogu se jednostavno objasniti kao funkcije izračuna slične agregiranju, ali tamo gdje je normalno agregiranje putem GROUP BY klauzula kombinira zatim skriva pojedinačne retke koji se agregiraju, funkcije prozora imaju pristup pojedinačnim redovima i mogu dodati neke atribute iz tih redaka u skup rezultata.

Dijagram koji uspoređuje agregatne funkcije i funkcije prozora

U ovom vodiču za SQL funkcije prozora započet ću s radom s funkcijama prozora, objasniti prednosti i kada biste ih koristili te dati stvarne primjere za pomoć u konceptima.

Prozor u vaše podatke

Jedna od najčešće korištenih i najvažnijih značajki SQL-a je sposobnost agregiranja ili grupiranja redaka podataka na određene načine. Međutim, u nekim slučajevima grupiranje može postati izuzetno složeno, ovisno o tome što je potrebno.

Jeste li ikad poželjeli pregledavati rezultate vašeg upita da biste dobili rang listu, top x listu ili slično? Jeste li imali bilo kakvih analitičkih projekata u kojima ste željeli pripremiti svoje podatke baš za alat za vizualizaciju, ali smatrali ste da je to gotovo nemoguće ili toliko složeno da nije vrijedilo?

internet stvari kućanski aparati

Funkcije prozora mogu olakšati stvari. Nakon što dobijete rezultat svog upita - tj., Nakon WHERE klauzule i bilo koje standardno zbrajanje, funkcije prozora djelovat će na preostale retke ( prozor podataka) i dobiti vam ono što želite.

pametni dom internet stvari

Neke od funkcija prozora koje ćemo pogledati uključuju:

OVER
COUNT()
SUM()
ROW_NUMBER()
RANK()
DENSE_RANK()
LEAD()
LAG()

Prelako

The OVER klauzula je ono što specificira funkciju prozora i mora uvijek biti uključeno u izraz. Zadana vrijednost u OVER klauzula je cijeli niz redaka. Kao primjer, pogledajmo tablicu zaposlenika u bazi podataka tvrtke i pokažimo ukupan broj zaposlenika u svakom retku, zajedno s podacima o svakom zaposleniku, uključujući kada su započeli s tvrtkom.

SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

BrojZaposlenika	ime	prezime	datum_počeo
3	Ivan	Smith	2019-01-01 00: 00: 00.000
3	Sally	Jones	2019-02-15 00: 00: 00.000
3	Sam	Gordon	2019-02-18 00: 00: 00.000

I gore navedeno, kao i mnoge funkcije prozora, također se mogu napisati na poznatiji način bez prozora - što u ovom jednostavnom primjeru nije loše:

SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Ali sada, recimo da želimo prikazati broj zaposlenika koji su započeli u istom mjesecu kao zaposlenik u nizu. Morat ćemo suziti ili ograničiti brojanje na samo taj mjesec za svaki redak. Kako se to radi? Koristimo prozor PARTITION klauzula, ovako:

SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

NumPerMonth	Mjesec	Ime	Prezime
jedan	Siječnja 2019	Ivan	Smith
2	Veljače 2019	Sally	Jones
2	Veljače 2019	Sam	Gordon

Particije vam omogućuju filtriranje prozora u odjeljke prema određenoj vrijednosti ili vrijednostima. Svaki se odjeljak često naziva prozorom okvir .

Da nastavimo dalje, recimo da ne samo da smo željeli saznati koliko je zaposlenika započelo u istom mjesecu, već želimo pokazati kojim su redoslijedom započeli taj mjesec. Za to se možemo poslužiti poznatim ORDER BY klauzula. Međutim, unutar funkcije prozora, ORDER BY djeluje malo drugačije nego na kraju upita.

SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

NumThisMonth	Mjesec	Ime	prezime
jedan	Siječnja 2019	Ivan	Smith
jedan	Veljače 2019	Sally	Jones
2	Veljače 2019	Sam	Gordon

U ovom slučaju, ORDER BY mijenja prozor tako da ide od početka particije (u ovom slučaju mjeseca i godine kada je zaposlenik započeo) do trenutnog retka. Dakle, brojanje se ponovno pokreće na svakoj particiji.

Poredak

Funkcije prozora mogu biti vrlo korisne u svrhe rangiranja. Prije smo vidjeli da se pomoću COUNT funkcija agregacije omogućila nam je da vidimo kojim redoslijedom su se zaposlenici pridružili tvrtki. Mogli smo koristiti i funkcije rangiranja prozora, kao što su ROW_NUMBER(), RANK() i DENSE_RANK().

Razlike se vide nakon što sljedećeg mjeseca dodamo novog zaposlenika i uklonimo particiju:

SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

StartingRank	Rang zaposlenika	DenseRank	Mjesec	ime	prezime	datum_počeo
jedan	jedan	jedan	Siječnja 2019	Ivan	Smith	2019-01-01
2	2	2	Veljače 2019	Sally	Jones	2019-02-15
3	2	2	Veljače 2019	Sam	Gordon	2019-02-18
4	4	3	Ožujka 2019	Julie	Sanchez	2019-03-19

Možete vidjeti razlike. ROW_NUMBER() daje sekvencijalno brojanje unutar zadane particije (ali s odsutnošću particije prolazi kroz sve retke). RANK() daje rang svakog retka na temelju ORDER BY klauzula. Pokazuje veze, a zatim preskače sljedeće rangiranje. DENSE_RANK također pokazuje veze, ali zatim nastavlja sa sljedećom uzastopnom vrijednošću kao da ne postoji veza.

Ostale funkcije rangiranja uključuju:

CUME_DIST - Izračunava relativni rang trenutnog retka unutar particije
NTILE - Dijeli redove za svaku particiju prozora što je moguće ravnomjernije
PERCENT_RANK - Procentualni rang trenutnog retka

Također primijetite u ovom primjeru da u jednom upitu možete imati više funkcija prozora - a i particija i redoslijed mogu se razlikovati u svakom!

sql savjeti za podešavanje izvedbe upita

Redovi i dometi i okviri, Oh My

Da biste dalje definirali ili ograničili svoj okvir prozora unutar OVER() klauzulu, možete koristiti ROWS i RANGE. Uz ROWS klauzulom, možete odrediti retke koji su uključeni u vašu particiju kao one koji su prethodili trenutnom retku ili nakon njega.

SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

U ovom primjeru okvir prozora ide od prvog reda do trenutnog retka minus 1, a veličina prozora nastavlja se povećavati za svaki redak.

Doseg djeluje malo drugačije i možda ćemo dobiti drugačiji rezultat.

privatni investicijski fondovi za nekretnine

SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Raspon će uključivati one retke u okviru prozora koji imaju jednake ORDER BY vrijednosti kao trenutni redak. Stoga je moguće da duplikate možete dobiti s RANGE ako je ORDER BY nije jedinstven.

Neki opisuju ROWS kao fizički operator dok RANGE je logički operator. U svakom slučaju, zadane vrijednosti za ROWS i RANGE su uvijek UNBOUNDED PRECEDING AND CURRENT ROW.

Što drugo?

Većina standardnih agregatnih funkcija radi s Window funkcijama. Vidjeli smo COUNT u primjerima već. Ostalo uključuje SUM, AVG, MIN, MAX, itd.

S funkcijama prozora također možete pristupiti i prethodnim i sljedećim zapisima pomoću LAG i LEAD, i FIRST_VALUE i LAST_VALUE. Na primjer, recimo da na svakom retku želite prikazati broj prodaje za tekući mjesec i razliku između prošlogodišnje prodaje. Možete učiniti nešto poput ovoga:

SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

U osnovi su funkcije SQL prozora vrlo moćne

Iako je ovo brz uvod u funkcije SQL prozora, nadamo se da će vas potaknuti da vidite sve što mogu učiniti. Saznali smo da funkcije prozora vrše izračune slične onima koje rade funkcije agregiranja, ali s dodatnom pogodnošću što imaju pristup podacima unutar pojedinih redaka, što ih čini prilično moćnima. Uvijek sadrže OVER klauzula, a može sadržavati PARTITION BY, ORDER BY, i mnoštvo agregiranja (SUM, COUNT, itd.) i drugih pozicijskih funkcija (LEAD, LAG). Također smo saznali o okvirima prozora i kako oni obuhvaćaju dijelove podataka.

Imajte na umu da različiti okusi SQL-a mogu različito implementirati funkcije prozora, a neki možda nisu implementirali sve funkcije prozora ili klauzule. Obavezno provjerite dokumentaciju za platformu koju koristite.

Ako je kao SQL programer , zainteresirani ste za podešavanje provjere performansi vaše baze podataka SQL Ugađanje izvedbe SQL baze podataka za programere .

Sretan prozor!

Za više informacija o određenim implementacijama pogledajte:

PostgreSQL-a Funkcije prozora dokumentacija za implementaciju PostgreSQL-a.
Klauzula SELECT - OVER (Transact-SQL) dokumente tvrtke Microsoft.
Funkcije prozora u SQL poslužitelju za sjajan pregled implementacija SQL Servera i njegovih 2. dio .

Razumijevanje osnova

Što radi funkcija prozora u SQL-u?

Funkcija prozora vrši izračune u skupu redaka i po potrebi koristi podatke unutar pojedinačnih redaka.

koji su principi dizajna?

Kako se funkcija skupne funkcije prozora razlikuje od agregatne funkcije 'grupa po'?

Pomoću 'grupiraj prema' možete kombinirati samo stupce koji nisu u klauzuli 'grupiraj prema'. Funkcije prozora omogućuju istovremeno prikupljanje i agregiranih i neagregatnih vrijednosti.