o funcție analitică calculează valori pe un grup de rânduri și returnează un singur rezultat pentru fiecare rând. Aceasta este diferită de o funcție agregată,care returnează un singur rezultat pentru un grup de rânduri.

o funcție analitică include o clauzăOVER, care definește o fereastră de rânduri în jurul rândului evaluat. Pentru fiecare rând, rezultatul funcției analiticeeste calculat folosind fereastra selectată de rânduri ca intrare, posibil făcând agregare.

cu funcții analitice puteți calcula medii mobile, elemente de rang, sume calculatecumulative, și de a efectua alte analize.

următoarele funcții pot fi utilizate ca funcții analitice:funcții de navigație,funcții de numerotare și funcții analitice agregate

sintaxa funcției analitice

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }window_specification: ] ] window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }

reguli de notare

• paranteze pătrate „” indicați clauze opționale.
• paranteze „() ” indicați paranteze literale.
• bara verticală ” / ” indică o logică sau.
• bretele Curly „{ } ” anexați un set de opțiuni.
• o virgulă urmată de o elipsă între paranteze pătrate „” indică faptul căelementul precedent se poate repeta într-o listă separată prin virgulă.

descriere

o funcție analitică calculează rezultatele pe un grup de rânduri. Puteți utiliza sintaxa următoare pentru a construi o funcție analitică:

• analytic_function_name: funcția care efectuează o operație analitică.De exemplu, funcția de numerotare RANK() ar putea fi utilizată aici.
• argument_list: argumente specifice funcției analitice.Unele funcții le au, altele nu.
• OVER: cuvânt cheie necesar în sintaxa funcției analitice care precedeOVER clauza.
• over_clause: face referire la o fereastră care definește un grup de rânduri într-un tabel pe care să se utilizeze o funcție analitică.
• window_specification: definește specificațiile pentru fereastră.
• window_frame_clause: definește cadrul ferestrei pentru fereastră.
• rows_range: definește rândurile fizice sau intervalul alogic pentru un cadru de fereastră.

Note

o funcție analitică poate apărea ca un operand de Expresie scalară în două locuri din interogare:

• listaSELECT. Dacă funcția analitică apare în listaSELECT, lista argumentelor sale și clauzaOVER nu se pot referi la pseudonimele introduse în aceeași listă de selectare.
• clauzaORDER BY. Dacă funcția analitică apare înORDER BY clauza interogării, lista argumentelor sale se poate referi laSELECT alias-uri listă.

o funcție analitică nu se poate referi la o altă funcție analitică din lista sa de argumente sau la clauzaOVER, chiar indirect printr-un alias.

o funcție analitică este evaluată după agregare. De exemplu, clauzaGROUP BY și funcțiile agregate non-analitice sunt evaluate mai întâi.Deoarece funcțiile agregate sunt evaluate înainte de funcțiile analitice, funcțiile agregate pot fi utilizate ca operanzi de intrare la funcțiile analitice.

returnează

un singur rezultat pentru fiecare rând din intrare.

definirea clauzei OVER

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }

descriere

clauzaOVER face referire la o fereastră care definește un grup de rânduri dintr-un tabel pe care se utilizează o funcție analitică. Puteți furniza un named_window care este definit în interogarea dvs. sau puteți defini specificațiile pentru o fereastră nouă.

Note

dacă nu este furnizată nici o fereastră numită, nici o specificație a ferestrei, toate rândurile de intrare sunt incluse în fereastră pentru fiecare rând.

Exemple folosindOVER clauza

aceste interogări folosesc fereastra specificații:

• calculați un total Mare
• calculați un subtotal
• calculați o sumă cumulativă
• calculați o medie mobilă
• calculați numărul de articole dintr-un interval
• Obțineți cel mai popular element în fiecare categorie
• obțineți ultima valoare dintr-un interval
• compute rank

aceste interogări utilizează o fereastră numită:

• obțineți ultima valoare dintr-un interval
• utilizați o fereastră numită într-o clauză cadru fereastră

definirea specificației ferestrei

window_specification: ] ] 

descriere

definește specificațiile pentru fereastră.

• named_window: numele unei ferestre existente care a fost definită cu o clauzăWINDOW.

• PARTITION BY: împarte rândurile de intrare în partiții separate, pestecare funcția analitică este evaluată independent.
  • Mai multe expresii de partiție sunt permise înPARTITION BY clauza.
  • o expresie nu poate conține tipuri de virgulă mobilă, tipuri non-grupabile,constante sau funcții analitice.
  • dacă această clauză opțională nu este utilizată, toate rândurile din tabelul de intrarecomprinde o singură partiție.
• ORDER BY: definește modul în care rândurile sunt ordonate într-o partiție.Această clauză este opțională în majoritatea situațiilor, dar este necesară în unele cazuri pentru funcțiile de navigare.
• window_frame_clause: Pentru analiza agregatăfuncții, definește cadrul ferestrei în partiția curentă.Cadrul ferestrei determină ce să includă în fereastră.Dacă această clauză este utilizată, ORDER BY este necesară, cu excepția ferestrelor complet nelimitate.

Note

dacă nici clauzaORDER BY și nici clauza cadru fereastră nu sunt prezente,cadrul ferestrei include toate rândurile din acea partiție.

pentru funcțiile analitice agregate, dacă clauzaORDER BY este prezentă, dar clauza cadrului ferestrei nu este, următoarea clauză a cadrului ferestrei este utilizată implicit:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

de exemplu, următoarele interogări sunt echivalente:

SELECT book, LAST_VALUE(item) OVER (ORDER BY year)FROM Library

SELECT book, LAST_VALUE(item) OVER ( ORDER BY year RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)FROM Library

reguli pentru utilizarea unei ferestre denumite în specificațiile ferestrei

dacă utilizați o fereastră numită în specificațiile ferestrei, se aplică aceste reguli:

• specificațiile din fereastra numită pot fi extendedwith specificații noi pe care le definiți în clauza specificație fereastră.
• nu puteți avea definiții redundante. Dacă aveți un ORDER BY clausîn fereastra numită și clauza de specificație a ferestrei, anerror este aruncat.

• ordinea clauzelor contează. PARTITION BY trebuie să fie primul,urmat de ORDER BY și window_frame_clause. Dacă adăugați o fereastră numită, specificațiile ferestrei sale sunt procesate mai întâi.

  --this works:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW item_window AS (ORDER BY purchases)--this does not work:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popularFROM ProduceWINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

• o fereastră numită șiPARTITION BY nu pot apărea împreună în specificația ferestrei. Dacă aveți nevoie de PARTITION BY, adăugați-l la fereastra numită.

• nu puteți face referire la o fereastră numită într-o clauză ORDER BY, o interogare exterioară sau orice subinterogare.

Exemple folosind specificația ferestrei

aceste interogări definesc partiții într-o funcție analitică:

• calculați un subtotal
• calculați o sumă cumulativă
• Obțineți cel mai popular element din fiecare categorie
• obțineți ultima valoare dintr-un interval
• calculați rangul
• utilizați o fereastră numită într-o clauză cadru fereastră

aceste interogări includ o fereastră numită într-o specificație fereastră:

• obțineți ultima valoare dintr-un interval
• utilizați o fereastră numită într-o clauză cadru fereastră

aceste interogări definesc modul în care rândurile sunt ordonate într-o partiție:

• calculați un subtotal
• calculați o sumă cumulativă
• calculați o medie mobilă
• calculați numărul de articole dintr-un interval
• Obțineți cel mai popular articol din fiecare categorie
• obțineți ultima valoare dintr-un interval
• calculați rangul
• utilizați o fereastră numită într-o clauză cadru fereastră

clauza

window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }frame_between: { BETWEEN unbounded_preceding AND frame_end_a | BETWEEN numeric_preceding AND frame_end_a | BETWEEN current_row AND frame_end_b | BETWEEN numeric_following AND frame_end_c }frame_start: { unbounded_preceding | numeric_preceding | }frame_end_a: { numeric_preceding | current_row | numeric_following | unbounded_following }frame_end_b: { current_row | numeric_following | unbounded_following }frame_end_c: { numeric_following | unbounded_following }unbounded_preceding: UNBOUNDED PRECEDINGnumeric_preceding: numeric_expression PRECEDINGunbounded_following: UNBOUNDED FOLLOWINGnumeric_following: numeric_expression FOLLOWINGcurrent_row: CURRENT ROW

Clauza cadrului ferestrei definește cadrul ferestrei în jurul rândului curent cuo partiție, peste care este evaluată funcția analitică.Numai funcțiile analitice agregate pot utiliza o clauză cadru fereastră.

• rows_range: o clauză care definește un cadru de fereastră cu rânduri fizice sau un interval logic.

  • ROWS: calculează cadrul ferestrei pe baza compensărilor fizice din rândul curent. De exemplu, puteți include două rânduri înainte și dupărândul curent.
  • RANGE: calculează cadrul ferestrei pe baza unui interval logic de rânduriîn jurul rândului curent, pe baza valorii cheiiORDER BY a rândului curent.Valoarea intervalului furnizat este adăugată sau scăzută la valoarea cheii rândului curent pentru a defini o limită a intervalului de început sau de sfârșit pentru cadrul ferestrei. Într-un cadru de fereastră bazat pe interval, trebuie să existe exact unulexpresie în clauza ORDER BY, iar expresia trebuie să aibă un tip numeric.

  Sfat: Dacă doriți să utilizați un interval cu o dată, utilizațiORDER BY cu funcțiaUNIX_DATE(). Dacă doriți să utilizați un interval cu o marcă de timp, utilizați UNIX_SECONDS()UNIX_MILLIS() sau UNIX_MICROS() funcție.

• frame_between: creează un cadru de fereastră cu o limită inferioară și superioară.Prima limită reprezintă limita inferioară. Al doilea limitarereprezintă limita superioară. Numai anumite combinații de limite pot fifolosit, așa cum se arată în sintaxa precedentă.

  • definiți începutul cadrului ferestrei cuunbounded_precedingnumeric_precedingnumeric_following saucurrent_row.
    • unbounded_preceding: cadrul ferestrei începe la începutul partiției.
    • numeric_preceding saunumeric_following: începutul cadrului ferestrei este relativ la rândul curent.
    • current_row: cadrul ferestrei începe de la rândul curent.
  • definiți capătul cadrului ferestrei cunumeric_precedingnumeric_followingcurrent_row sauunbounded_following.
    • numeric_preceding saunumeric_following: sfârșitul cadrului ferestrei este relativ la rândul curent.
    • current_row: Cadrul ferestrei se termină la rândul curent.
    • unbounded_following: cadrul ferestrei se termină la sfârșitul partiției.

• frame_start: creează un cadru de fereastră cu o limită inferioară.Cadrul ferestrei se termină la rândul curent.

  • unbounded_preceding: cadrul ferestrei începe la începutul partiției.
  • numeric_preceding: începutul cadrului ferestrei este relativ la rândul curent.
  • current_row: cadrul ferestrei începe de la rândul curent.

• numeric_expression: o expresie care reprezintă un tip numeric.Expresia numerică trebuie să fie un parametru constant, non-negativ.

Note

dacă o limită se extinde dincolo de începutul sau sfârșitul unei partiții,cadrul ferestrei va include numai rânduri din interiorul partiției respective.

nu puteți utiliza o clauză cadru fereastră cu funcții de navigație și funcții de numerotare,cum ar fiRANK().

Exemple folosind clauza cadru fereastră

aceste interogări calculează valorile cu ROWS:

• calculați o sumă cumulativă
• calculați o medie mobilă
• Obțineți cel mai popular articol din fiecare categorie
• obțineți ultima valoare dintr-un interval
• utilizați o fereastră numită într-o clauză cadru fereastră

aceste interogări calculează valori cu RANGE:

• numărul de elemente dintr-un interval

aceste interogări calculează valori cu o fereastră parțial sau complet nelegată:

• calculați un total Mare
• calculați un subtotal
• calculați o sumă cumulativă
• Obțineți cel mai popular articol din fiecare categorie
• calculați rangul

aceste interogări calculează valori cu limite numerice:

• calculați o sumă cumulativă
• calculați o medie mobilă
• calculați numărul de articole dintr-un interval
• obțineți ultima valoare dintr-un interval
• utilizați o fereastră numită într-o clauză cadru fereastră

aceste interogări calculează valorile cu rândul curent ca limită:

• calculați un total Mare
• calculați un subtotal
• calculați o sumă cumulativă

referindu-se la o fereastră numită

SELECT query_expr, analytic_function_name ( ) OVER over_clauseFROM from_itemWINDOW named_window_expression over_clause: { named_window | ( ) }window_specification: ] ] named_window_expression: named_window AS { named_window | ( ) }

o fereastră numită reprezintă un grup de rânduri dintr-un tabel pe care să se utilizeze funcția ananalitică. O fereastră numită este definită înWINDOW clauza și se face referire laOVER clause.In oOVER clauză, o fereastră numită poate apărea fie de la sine sau embeddedwithin o specificație fereastră.

Exemple

• obțineți ultima valoare dintr-un interval
• utilizați o fereastră numită într-o clauză cadru fereastră

concepte de funcții de navigare

funcțiile de navigare calculează în general unelevalue_expression pe un rând diferit din cadrul ferestrei de rândul curent. Sintaxa clauzeiOVER variază între funcțiile de navigare.

Cerințe pentruOVER clauza:

• PARTITION BY: opțional.
• ORDER BY:
  1. Disallowed for PERCENTILE_CONT and PERCENTILE_DISC.
  2. Required for FIRST_VALUELAST_VALUENTH_VALUELEADand LAG.
• window_frame_clause:
  1. Disallowed for PERCENTILE_CONTPERCENTILE_DISCLEAD and LAG.
  2. Optional for FIRST_VALUELAST_VALUE, and NTH_VALUE.

pentru toate funcțiile de navigare, tipul de date rezultat este același tip cavalue_expression.

concepte de funcții de numerotare

funcțiile de numerotare atribuie valori întregi lafiecare rând pe baza poziției lor în fereastra specificată.

exemplu de RANK()DENSE_RANK() și ROW_NUMBER():

WITH Numbers AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 10 UNION ALL SELECT 10)SELECT x, RANK() OVER (ORDER BY x ASC) AS rank, DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY x) AS row_numFROM Numbers+---------------------------------------------------+| x | rank | dense_rank | row_num |+---------------------------------------------------+| 1 | 1 | 1 | 1 || 2 | 2 | 2 | 2 || 2 | 2 | 2 | 3 || 5 | 4 | 3 | 4 || 8 | 5 | 4 | 5 || 10 | 6 | 5 | 6 || 10 | 6 | 5 | 7 |+---------------------------------------------------+

• RANK(): Pentru x = 5,rank este 4, deoareceRANK() incrementează cu numărul de colegi din grupul anterior de comandă a ferestrelor.
• DENSE_RANK(): pentru x=5,dense_rank este 3, deoareceDENSE_RANK() întotdeauna crește cu 1, fără a sări peste o valoare.
• ROW_NUMBER(): pentru x=5, row_num este 4.

concepte de funcții analitice agregate

o funcție agregată este o funcție care efectuează un calcul pe aset de valori. Majoritatea funcțiilor agregate pot fi utilizate în funcție ananalitică. Aceste funcții agregate sunt numitefuncții analitice agregate.

cu funcții analitice agregate, clauzaOVER este anexată la apelul funcției agregate; sintaxa apelului funcției rămâne neschimbată.Ca și omologii lor de funcții agregate, aceste funcții analitice efectueazăagregări, dar în mod specific peste cadrul ferestrei relevante pentru fiecare rând.Tipurile de date rezultate ale acestor funcții analitice sunt aceleași ca și omologii lor de funcții agregate.

Exemple de funcții analitice

în aceste exemple, elementul evidențiat este rândul curent. Boldeditems sunt rândurile care sunt incluse în analiză.

tabele comune utilizate în exemple

următoarele tabele sunt utilizate în exemplele de analiză agregată ulterioare:ProduceEmployeesșiFarm.

Produce tabelul

câteva exemple de referință un tabel numitProduce:

WITH Produce AS (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category UNION ALL SELECT 'orange', 2, 'fruit' UNION ALL SELECT 'cabbage', 9, 'vegetable' UNION ALL SELECT 'apple', 8, 'fruit' UNION ALL SELECT 'leek', 2, 'vegetable' UNION ALL SELECT 'lettuce', 10, 'vegetable')SELECT * FROM Produce+-------------------------------------+| item | category | purchases |+-------------------------------------+| kale | vegetable | 23 || orange | fruit | 2 || cabbage | vegetable | 9 || apple | fruit | 8 || leek | vegetable | 2 || lettuce | vegetable | 10 |+-------------------------------------+

tabelul angajaților

câteva exemple fac referire la un tabel numit Employees:

WITH Employees AS (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29) UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24) UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23) UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11) UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))SELECT * FROM Employees+-------------------------------------+| name | department | start_date |+-------------------------------------+| Isabella | 2 | 1997-09-28 || Anthony | 1 | 1995-11-29 || Daniel | 2 | 2004-06-24 || Andrew | 1 | 1999-01-23 || Jacob | 1 | 1990-07-11 || Jose | 2 | 2013-03-17 |+-------------------------------------+

tabelul fermei

câteva exemple fac referire la un tabel numit Farm:

WITH Farm AS (SELECT 'cat' as animal, 23 as population, 'mammal' as category UNION ALL SELECT 'duck', 3, 'bird' UNION ALL SELECT 'dog', 2, 'mammal' UNION ALL SELECT 'goose', 1, 'bird' UNION ALL SELECT 'ox', 2, 'mammal' UNION ALL SELECT 'goat', 2, 'mammal')SELECT * FROM Farm+-------------------------------------+| animal | category | population |+-------------------------------------+| cat | mammal | 23 || duck | bird | 3 || dog | mammal | 2 || goose | bird | 1 || ox | mammal | 2 || goat | mammal | 2 |+-------------------------------------+

calculați un total general

aceasta calculează un total general pentru toate elementele din tabelulProduce.

• (portocale, mere, praz, varză, salată, varză) = 54 total achiziții
• (portocale, mere, praz, varză, salată, varză) = 54 total achiziții
• (portocale, mere, praz, varză, salată, varză) = 54 total achiziții
• (portocale, mere, praz, varză, salată, varză) = 54 total achiziții
• (portocale, mere, praz, varză, salată, varză) = 54 total achiziții
• (portocale, mere, praz, varză, kale) = 54 total achiziții
• (portocale, mere, praz, varză, salată, kale)=54 total achiziții

SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 54 || leek | 2 | vegetable | 54 || apple | 8 | fruit | 54 || cabbage | 9 | vegetable | 54 || lettuce | 10 | vegetable | 54 || kale | 23 | vegetable | 54 |+-------------------------------------------------------+

calculați un subtotal

aceasta calculează un subtotal pentru fiecare categorie din Produce tabel.

• fructe
  • (portocale, mere) = 10 Total achiziții
  • (portocale, mere) = 10 Total achiziții

legume

• (praz, varză, salată, varză) = 44 total achiziții
• (praz, varză, salată, varză) = 44 total achiziții
• (praz, varză, salată, varză) = 44 total achiziții
• (praz, varză, salată, varză) = 44 total achiziții

/li>

• (praz, varză, salată, varză)=44 total achiziții

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 10 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 44 || cabbage | 9 | vegetable | 44 || lettuce | 10 | vegetable | 44 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+

calculați o sumă cumulativă

aceasta calculează o sumă cumulativă pentru fiecare categorie din tabelul Produce. Suma este calculată în raport cu theorder definit folosindORDER BY clauza.

• fructe
  • (portocale, mere) = 2 Total achiziții
  • (portocale, mere) = 10 Total achiziții

legume

• (praz, varză, salată, varză) = 2 Total achiziții
• (praz, varză, salată, varză) = 11 total achiziții
• (praz, varză, salată, varză) = 21 total achiziții
• (praz, varza, salata verde, varza) = 44 total achiziții

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 2 || cabbage | 9 | vegetable | 11 || lettuce | 10 | vegetable | 21 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+

Acest lucru face același lucru ca și exemplul precedent. Nu trebuie să adăugați CURRENT ROW ca limită decât dacă doriți pentru lizibilitate.

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS UNBOUNDED PRECEDING ) AS total_purchasesFROM Produce

în acest exemplu, toate elementele din tabelulProduce Sunt incluse în partiție. Sunt analizate doar rândurile precedente. Analiza începe cu douărows înainte de rândul curent din partiție.

• (portocale, praz, mere, varza, salata verde, varza) = NULL
• (portocale, praz, mere, varza, salata verde, varza) = NULL
• (portocale, praz, mere, varza, salata verde, varza) = 2
• (portocale, praz, mere, varza, salata verde, varza) = 4
• (portocale, praz, mere, varza, salata verde, varza) = 12
• (portocale, praz, mere, varză, salată, varză) = 21

SELECT item, purchases, category, SUM(purchases) OVER ( ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING ) AS total_purchasesFROM Produce;+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | NULL || leek | 2 | vegetable | NULL || apple | 8 | fruit | 2 || cabbage | 9 | vegetable | 4 || lettuce | 10 | vegetable | 12 || kale | 23 | vegetable | 21 |+-------------------------------------------------------+

calculați o medie mobilă

aceasta calculează o medie mobilă în tabelulProduce.Limita inferioară este de 1 rând înainterândul curent. Limita superioară este de 1 rând după rândul curent.

• (portocale, praz, mere, varză, salată, varză) = 2 achiziții medii
• (portocale, praz, mere, varză, salată, varză) = 4 achiziții medii
• (portocale, praz, mere, varză, salată, varză) = 6.3333 achiziții medii
• (portocale, praz, mere, varză, salată, varză) = 9 achiziții medii
• (portocale, praz, mere, varză, salată, varză) = 9 achiziții medii
• (portocale, praz, mere, varză, salată, kale) = 14 achiziții medii
• (portocaliu, praz, măr, varză, salată, Kale) = 16.5 achiziții medii

SELECT item, purchases, category, AVG(purchases) OVER ( ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | avg_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || leek | 2 | vegetable | 4 || apple | 8 | fruit | 6.33333 || cabbage | 9 | vegetable | 9 || lettuce | 10 | vegetable | 14 || kale | 23 | vegetable | 16.5 |+-------------------------------------------------------+

calculați numărul de articole dintr-un interval

acest exemplu obține numărul de animale care au o populație similară în tabelulFarm.

• (gâscă, câine, bou, capră, rață, pisică) = 4 animale între populația 0-2.
• (gâscă, câine, bou, capră, rață, pisică) = 5 animale între populația 1-3.
• (gâscă, câine, bou, capră, rață, pisică) = 5 animale între populația 1-3.
• (gâscă, câine, bou, capră, rață, pisică) = 5 animale între populația 1-3.
• (gâscă, câine, bou, capră, rață, pisică) = 4 animale între populația 2-4.
• (gâscă, câine, bou, capră, rață, pisică) = 1 animal între populația 22-24.

SELECT animal, population, category, COUNT(*) OVER ( ORDER BY population RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS similar_populationFROM Farm;+----------------------------------------------------------+| animal | population | category | similar_population |+----------------------------------------------------------+| goose | 1 | bird | 4 || dog | 2 | mammal | 5 || ox | 2 | mammal | 5 || goat | 2 | mammal | 5 || duck | 3 | bird | 4 || cat | 23 | mammal | 1 |+----------------------------------------------------------+

Obțineți cel mai popular articol din fiecare categorie

acest exemplu devine cel mai popular articol din fiecare categorie. Definește modul în care rândurile dintr-o fereastră sunt partiționate și ordonate în fiecare partiție. Se face referire la tabelulProduce.

• fructe
  • (portocale, mere) = mărul este cel mai popular
  • (portocale, mere) = mărul este cel mai popular
• legume
  • (praz, varza, salata verde, varza) = varza este cel mai popular
  • (praz, varza, salata verde, varza) = varza este cel mai popular
  • (praz, varza, salata verde, varza) = varza este cel mai popular

  /li>

  • (praz, varza, salata verde, varza) = varza este cel mai popular

SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | kale || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+

obțineți ultima valoare într-un interval

acest exemplu devine cel mai popular element într-un cadru de fereastră specific, utilizândProduce masă. Cadrul ferestrei analizează până la treirânduri la un moment dat. Aruncați o privire atentă la coloana most_popular pentru legume.În loc să obțină cel mai popular articol dintr-o anumită categorie, acesta devine cel mai popular articol dintr-un anumit interval din acea categorie.

• fructe
  • (portocale, mere) = mărul este cel mai popular
  • (portocale, mere) = mărul este cel mai popular
• legume
  • (praz, varză, salată, varză) = varza este cea mai populară
  • (praz, varză, salată, varză) = salata este cea mai populară
  • (praz, varză, salată, varză) = varza este cea mai populară

  /li>

  • (praz, varza, salata verde, varza) = varza este cel mai popular

SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | cabbage || cabbage | 9 | vegetable | lettuce || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+

acest exemplu returnează aceleași rezultate ca exemplul precedent, dar includeo fereastră numităitem_window. Unele dintre specificațiile ferestrei sunt definite direct înOVER clauza și unele sunt definite în fereastra numită.

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases)

Compute rank

Acest exemplu calculează rangul fiecărui angajat din departamentul său,pe baza datei de începere. Specificația ferestrei este definită directîn clauzaOVER. Se face referire la tabelulEmployees.

• Departamentul 1
  • (Jacob, Anthony, Andrew) = atribuie rangul 1 lui Jacob
  • (Jacob, Anthony, Andrew) = atribuie rangul 2 lui Anthony
  • (Jacob, Anthony, Andrew) = atribuie rangul 3 lui Andrew
• Departamentul 2
  • (Isabella, Daniel, Jose) = atribuie rangul 1 Isabellei
  • (Isabella, Daniel, Jose) = atribuie rangul 2 Daniel
  • (Isabella, Daniel, Jose) = atribuie rangul 3 lui Jose

SELECT name, department, start_date, RANK() OVER (PARTITION BY department ORDER BY start_date) AS rankFROM Employees;+--------------------------------------------+| name | department | start_date | rank |+--------------------------------------------+| Jacob | 1 | 1990-07-11 | 1 || Anthony | 1 | 1995-11-29 | 2 || Andrew | 1 | 1999-01-23 | 3 || Isabella | 2 | 1997-09-28 | 1 || Daniel | 2 | 2004-06-24 | 2 || Jose | 2 | 2013-03-17 | 3 |+--------------------------------------------+

utilizați o fereastră numită într-o clauză cadru fereastră

puteți defini o parte din logica într-o fereastră numită și o parte din ea în o clauză cadru fereastră. Această logică este combinată. Iată un exemplu, folosind tabelulProduce.

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)+-------------------------------------------------------+| item | purchases | category | most_popular |+-------------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | lettuce || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+-------------------------------------------------------+

puteți obține și rezultatele anterioare cu aceste exemple:

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), item_window AS (c)

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), item_window AS (b)

următorul exemplu produce o eroare deoarece o clauză a cadrului ferestrei a fost definită de două ori:

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)