Dotaz: Postgres: filtrovani na zaklade vztahu mezi jednotlivymi radky

[Jiri Tomasek]

Ahoj,

predstavme si, ze mame tuto sql tabulku:

identifikator skore
a 13
a 12
a 15
a 14
b 3
b 5
b 7

chci ji transformovat na tabulku, kde budou ponechany max 3 radky pro kazdy identifikator, ktere maji nejvetsi skore. Tedy aby "a 12" bylo vyfiltrovano. Jde to nejak jednoduse?

(BTW Proc to chci: potrebuji totiz prumer 3 nejvetsich skore pro kazdy identifikator. Ano, to by asi slo primo pres nejakou agregacni funkci, ale ta defaultne nejspis neexistuje. Proto zkousim tabulku nejdrive vyfiltrovat a pak je to snadne.)

---

---

Odpovědi

[karl82]

Technik jak tento problém vyřešit je několik, jeden z nejjednodušších je použití window funkce:

postgres=> select * from t;
 x | y  
---+----
 a | 13
 a | 12
 a | 15
 a | 14
 b |  3
 b |  5
 b |  7
(7 rows)

postgres=> select x,y from (select rank() over(partition by x order by y desc), x, y from t) s where rank <= 3;
 x | y  
---+----
 a | 15
 a | 14
 a | 13
 b |  7
 b |  5
 b |  3
(6 rows)

Je to dobrá úloha na naučení se trochu netriviálního SQL (naučit se všechny varianty).

[okbob]

Jinak přes agregační funkce by to vyřešit nešlo - ty agregují nikoliv filtrují.

[Jiri Tomasek]

Uzasny! Diky za rozsireni obzoru.

Agregacni funkce - myslel jsem napsat si vlastni, neco jako

SELECT id, PRUMER_NEJVSSICH_TRI(skore) GROUP BY id;

[okbob]

agregacni funkce vraci skalarni (jednu) hodnotu - takze to z principu nejde. Ale mozna je to o terminologii - mozna jste mel na mysli window funkce :)

[Jiri Tomasek]

Jasne, vsak PRUMER_NEJVYSSICH_TRI() prece je skalarni hodnota

Jen jsme si asi nerozumeli, ta agregacni funkce by vyresila trochu jiny problem nez filtrovani - viz text za BTW v puvodni otazce.

[okbob]

Aha, to jsem si neprecetl pozorne otazku. Pak by to slo.

[rich]

agregaci muzes pouzit primo v dotazu... netreba vytaret dalsi funkce

[Chobotnice]

Pokud se to skore ma opakovat, chces asi spis dense rank nez rank.

[Josef Kufner]

Dá se to naprasit pomocí několika subselectů:

SELECT t1.id, (
  SELECT AVG(t2.skore)
  FROM (
    SELECT t3.skore
    FROM t AS t3
    WHERE t3.id = t1.id
    ORDER BY t3.skore DESC
    LIMIT 3
  ) AS t2
) AS prumer_3_nejvyssich
FROM t as t1
GROUP BY t1.id
ORDER BY t1.id

[Jiri Tomasek]

No vida, to je taky hezky reseni :)