Adatbázis letöltése <3

mo_telepulesek.sql

Ablakok és partíciók kezelése

• Lekérdezések írása közben lehetőségünk van arra, hogy egyes műveleteket adat window-okban (ablakokban) hajtsunk végre.

  • Ilyenkor a művelet vagy az egész adathalmazt egy ablakként dolgozza fel, vagy az adathalmazban szereplő rekordokat csoportosítva, partíciókba rendezve, partíciónként dolgozza fel.

• Egy window megadásához egy összesítő vagy window függvény meghívása után az OVER() kulcsszót használjuk.

  window_function()
  OVER ([order_clause])
  

• A particionálás alatt azt a folyamatot értjük, amely során nagy adattáblákat több, kisebb részre darabolunk.

• Az adatok több részre bontása növelheti a teljesítményt, mivel a lekérdezéseknek csak kisebb adathalmazokon kell lefutniuk.

• A particionálás célja a nagy adattáblák karbantartásának segítése és az adatok lekérdezéséhez szükséges idő csökkentése.

• Partíciók segítségével a rekordokat csoportokba sorolhatjuk. Ehhez a PARTITION BY kulcsszavakat kell használnunk.

  window_function()
  OVER (PARTITION BY expr [order_clause] [frame_clause])
  

  • Mindig az OVER() kifejezésben használjuk (ezzel definiáljuk a window-t).
  • A window és a partíció csak összesítő vagy úgynevezett window függvényekkel használható (pl.: ROW_NUMBER()).
  • Ha kihagyjuk az OVER() kifejezésből a PARTITION BY kulcsszavakat, akkor az egész tábla egy partícióként lesz kezelve.

• Példa:

  • Adott egy hacker nevű tábla:

  h_id	h_name	challenge_id	score
  3	shubh	111	20
  2	aayush	111	80
  5	krithik	112	40
  5	krithik	114	90
  4	tushar	112	30
  1	parth	112	40

  • Meg kell találnunk a helyezését a hackereknek minden kihívásban. Tehát ki kell listáznunk minden résztvevőt a kihívásokban a helyezésükkel együtt, amit az adott kihívásban elértek.

  • A lekérdezés:

    SELECT challenge_id, h_id, h_name, score, 
    dense_rank() OVER (PARTITION BY challenge_id ORDER BY score DESC) 
    AS rank, FROM hacker;
    

    • Ebben a lekérdezésben a PARTITION BY kifejezés particionálja a táblát csoportokba, amelyek rendelkeznek valamilyen challenge_id értékkel.
    • Az ORDER BY kulcsszavak sorba rendezik a hackereket minden egyes partíción belül az elért pontszámok (score) alapján.
    • Az OVER() kifejezés definiálja az ablakot, tehát, hogy hogyan kell particionálni és rendezni a rekordokat a táblában, amiket aztán partíciónként feldolgoz a rank() window függvény.
    • A dense_rank() egy olyan window függvény, amely sorszámot rendel minden rendezett partíciójához a kihívásoknak. Ha két hacker ugyanazzal a pontszámmal rendelkezik, akkor ugyanazt a sorszámot (helyezést) kapják.

  • Az output:

  challenge_id	h_id	h_name	score	rank
  111	2	aayush	80	1
  111	3	shubh	20	2
  112	5	krithik	40	1
  112	1	parth	40	1
  112	4	tushar	30	2
  114	5	krithik	90	1

Window függvények

• A Window függvények összesítő és sorszámozó műveleteket hajtanak végre rekordok egy adott csoportján (ablakán), amelyek valahogy kapcsolódnak az éppen aktuális rekordhoz.

  • Ezt az ablakot az OVER() kifejezéssel adjuk meg.

• Az összesítő függvényekkel szemben a window függvények nem csoportosítják egy output rekordba az összes rekordot, hanem minden rekord megőrzi a saját “egyéniségét”.

• A szokásos összesítő függvények (pl.: SUM(), COUNT(), AVG(), MAX(), MIN()) is használhatók rekordok egy ablakára. Ilyenkor összesítő window függvényeknek nevezzük őket.

• Fontos, hogy nem használhatunk hagyományosan összesítő függvényeket és összesítő window függvényeket egy lekérdezésben. Más szavakkal, ha GROUP BY-t használunk, akkor window függvényeket nem használhatunk.

• Példa 1: Számoljuk ki a teljes másodpercben mért időtartamot running_total néven az egész adathalmazt véve, start_time szerint rendezve.

  SELECT duration_seconds,
  SUM(duration_seconds) OVER (ORDER BY start_time) AS running_total
  FROM tutorial.dc_bikeshare_q1_2012;
  

  • Ezt tovább bővíthetjük úgy, hogy ne az egész adathalmazt egyben kezelje a függvény, hanem partíciókra bontva, külön csoportokként kezelje.

  SELECT start_terminal, duration_seconds,
  SUM(duration_seconds) OVER (PARTITION BY start_terminal ORDER BY start_time) AS running_total
  FROM tutorial.dc_bikeshare_q1_2012
  WHERE start_time < '2012-01-08';
  

  • Ez a lekérdezés már csoportosítja az adatokat a start_terminal mező alapján. A mező minden értékénél start_time szerint rendezve kiszámításra kerül a teljes időtartam.

• Példa 2: Szeretnénk részlegenként kiszámítani az alkalmazottak átlagos fizetését.

  name	age	department	salary
  Ramesh	20	Finance	50, 000
  Deep	25	Sales	30, 000
  Suresh	22	Finance	50000
  Ram	28	Finance	20, 000
  Pradeep	22	Sales	20, 000

  SELECT name, age, department, salary, 
  AVG(salary) OVER(PARTITION BY department) AS avg_salary,
  FROM employee;
  

  • Az output:

  name	age	department	salary	avg_salary
  Ramesh	20	Finance	50,000	40,000
  Suresh	22	Finance	50000	40,000
  Ram	28	Finance	20,000	40,000
  Pradeep	22	Sales	20,000	25,000
  Deep	25	Sales	30,000	25,000

  • Most rendezzük kor szerint az alkalmazottakat a partíciókon belül:

  SELECT name, age, department, salary, 
  AVG(salary) OVER(PARTITION BY department ORDER BY age) AS avg_salary
  FROM employee;
  

  • Az output:

  name	age	department	salary	avg_salary
  Ramesh	20	Finance	50, 000	50, 000
  Suresh	22	Finance	50000	50, 000
  Ram	28	Finance	20, 000	40, 000
  Pradeep	22	Sales	20, 000	20, 000
  Deep	25	Sales	30, 000	25, 000

  • Láthatjuk, hogy az átlagos fizetés értékei megváltoztak az adatok sorba rendezését követően. Ezért mindig legyünk óvatosak a partíciók rendezésével ha window függvényként használunk összesítő függvényeket.

• Sorszámozó window függvények:

  • RANK(): Minden partícióban számozza a rekordokat. Az első sor 1-es sorszámot kap, majd minden azonos tartalmú rekord azonos számot fog kapni. Azonos számozás után a következő rekord egy (vagy több, az azonos számok darabszámától függően) szám kihagyásával kapja a következő számot.

  • DENSE_RANK(): Hasonlóan működik, mint a RANK() függvény, csak ha azonos tartalmú rekordokat talál, akkor a következő különböző rekordnál nem hagy ki számokat.

  • ROW_NUMBER(): Szekvenciális sorszámot rendel minden új rekordhoz egy partíción belül. Két azonos érték esetén a partíción belül két különböző sorszámot fog a rekordokhoz rendelni.

  • A sorszámozó window függvények használatakor a partíciók rendezése kötelező az ORDER BY kulcsszavakkal.

  • Példa:

    SELECT 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) 
    AS emp_row_no, name, department, salary,
    RANK() OVER(PARTITION BY department 
    ORDER BY salary DESC) AS emp_rank,
    DENSE_RANK() OVER(PARTITION BY department 
    ORDER BY salary DESC) 
    AS emp_dense_rank,
    FROM employee;
    

    emp_row_no	name	department	salary	emp_rank	emp_dense_rank
    1	Suresh	Finance	50, 000	1	1
    2	Ramesh	Finance	50, 000	1	1
    3	Ram	Finance	20, 000	3	2
    1	Deep	Sales	30, 000	1	1
    2	Pradeep	Sales	20, 000	2	2

• További window függvények:

• Window aliasok definiálása: