Forum » Programiranje » [MariaDB] Kako napisat SQL za top N by group?
[MariaDB] Kako napisat SQL za top N by group?
HotBurek ::
Dobro jutro.
Evo, spet sql izziv.
Tabela ima sledeče podatke:
Vprašanje pa se glasi: Kako napisat SQL query, ki bo naredil GROUP BY shop_id, ter za vsako grupo vrnil TOP 3 glede na ranking (DESC).
Problem je, da lahko ima znotraj iste grupe več vrstic isti ranking, ki pade v TOP 3, in izpisat je treba vse te vrstice.
Ker:
- Za shop_id 0 je v TOP 3 skupno 4 rezultatov (id 0, 1, 2, 4). Row id 3 pade ven
- Za shop_id 1 so v TOP 3 vsi rezultati (id 5, 6).
- Za shop_id 2 je v TOP 3 skupno 5 rezultatov (7, 8, 9, 10, 11, 13). Row id 12 pade ven.
Rešitev za to sedaj iščem po internetih...
Evo, spet sql izziv.
Tabela ima sledeče podatke:
id|shop_id|name |ranking| --+-------+-------------+-------+ 0| 0|keksi | 7| 1| 0|coca | 666| 2| 0|mleko | 7| 3| 0|kruh | 6| 4| 0|svinjska mast| 9| 5| 1|ocvirki | 44| 6| 1|krompri | 1| 7| 2|paprika | 100| 8| 2|korenje | 101| 9| 2|kruh | 99| 10| 2|pašteta | 101| 11| 2|sir | 99| 12| 2|salama | 88| 13| 2|skuta | 99|
Vprašanje pa se glasi: Kako napisat SQL query, ki bo naredil GROUP BY shop_id, ter za vsako grupo vrnil TOP 3 glede na ranking (DESC).
Problem je, da lahko ima znotraj iste grupe več vrstic isti ranking, ki pade v TOP 3, in izpisat je treba vse te vrstice.
Ker:
- Za shop_id 0 je v TOP 3 skupno 4 rezultatov (id 0, 1, 2, 4). Row id 3 pade ven
- Za shop_id 1 so v TOP 3 vsi rezultati (id 5, 6).
- Za shop_id 2 je v TOP 3 skupno 5 rezultatov (7, 8, 9, 10, 11, 13). Row id 12 pade ven.
Rešitev za to sedaj iščem po internetih...
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
HotBurek ::
Evo, zgleda sem že half way done.
Trenutni sample:
Vir: https://www.codelabs365.com/sql-cookboo...
Zdej morem še samo ta dense_rank nekako spravit v WHERE pogoj.
Trenutni sample:
SELECT *, DENSE_RANK () OVER ( PARTITION BY `shop_id` ORDER BY `ranking` DESC ) AS 'dense_rank' FROM `database_run`.`test5` ORDER BY `shop_id` ASC, `ranking` DESC;
Vir: https://www.codelabs365.com/sql-cookboo...
Zdej morem še samo ta dense_rank nekako spravit v WHERE pogoj.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
kljuka13 ::
SELECT * FROM ( SELECT *, DENSE_RANK() OVER w AS `rank` FROM `test5` WINDOW w AS (PARTITION BY `shop_id` ORDER BY `ranking` DESC) ) `t` WHERE `t`.`rank` <= 3
HotBurek ::
Tako je oz. zelo podobno. Jaz sem uporabil "manjše od 4". Pa window funkcij še nisem uporabljal in jih ne znam. Zanimivo. Ampak oboje je solid.
Trenuten query:
Naslednji izziv pa je, kako v končnem rezultatu omejit skupno število row-ov per group?
Skratka nekaj takega: LIMIT MAX 100 PER 'shop_id'.
Trenuten query:
SELECT sub.`id`, sub.`shop_id`, sub.`name`, sub.`ranking`, sub.`dense_rank` FROM ( SELECT `id`, `shop_id`, `name`, `ranking`, DENSE_RANK () OVER (PARTITION BY `shop_id` ORDER BY `ranking` DESC) AS 'dense_rank' FROM `database_run`.`test5` ORDER BY `shop_id` ASC, `ranking` DESC ) AS sub WHERE sub.`dense_rank` < 4 ORDER BY sub.`ranking` DESC;
Naslednji izziv pa je, kako v končnem rezultatu omejit skupno število row-ov per group?
Skratka nekaj takega: LIMIT MAX 100 PER 'shop_id'.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
HotBurek ::
Res je. Mislim, da bo to to.
SELECT sub.`id`, sub.`shop_id`, sub.`name`, sub.`ranking`, sub.`dense_rank`, sub.`row_number` FROM ( SELECT `id`, `shop_id`, `name`, `ranking`, DENSE_RANK () OVER (PARTITION BY `shop_id` ORDER BY `ranking` DESC) AS 'dense_rank', ROW_NUMBER () OVER (PARTITION BY `shop_id` ORDER BY `ranking` DESC) AS 'row_number' FROM `database_run`.`test5` ORDER BY `shop_id` ASC, `ranking` DESC ) AS sub WHERE sub.`dense_rank` < 4 AND sub.`row_number` < 3 ORDER BY sub.`ranking` DESC;
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
HotBurek ::
Zanimiva situacija...
Query sem dal v Python, da to požene. In dobim error:
Če iz errorja!!! skopiram SQL ukaz, in ga poženem v grafičnem tool-u (DBeaver) na isti!!! bazi, pa dela.
Krneki. Sem sicer rešil tako, da sem odstranil dense_rank, in uporabil zgolj row_number.
Ampak vseeno. Na internetih nisem našel, zakaj ta funkcija ne bi delala preko Python/SQLAclhemy.
--------
UPDATE 1
Sem se pa sedalje na serverju v terminalu povezal z "mysql -u test -p", ter pognal query, in ne dela.
--------
UPDATE 2
Sem probal v terminal-u še, če je odvisno, katera baza je zbrana.
MariaDB [(none)]> vrača: ERROR 1305 (42000): FUNCTION DENSE_RANK does not exist
MariaDB [database_run]> vrača: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OVER (PARTITION BY `shop_id` ORDER BY...
Krneki.
--------
UPDATE 3
Sedaj sem še v Python-u nastavil default bazo. In v error-ju dobim syntax error. Če SQL query, ki je vključen v error-ju, skopiram v DBeaver tool, in poženem, pa dela.
Query sem dal v Python, da to požene. In dobim error:
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1305, 'FUNCTION DENSE_RANK does not exist')
Če iz errorja!!! skopiram SQL ukaz, in ga poženem v grafičnem tool-u (DBeaver) na isti!!! bazi, pa dela.
Krneki. Sem sicer rešil tako, da sem odstranil dense_rank, in uporabil zgolj row_number.
Ampak vseeno. Na internetih nisem našel, zakaj ta funkcija ne bi delala preko Python/SQLAclhemy.
--------
UPDATE 1
Sem se pa sedalje na serverju v terminalu povezal z "mysql -u test -p", ter pognal query, in ne dela.
--------
UPDATE 2
Sem probal v terminal-u še, če je odvisno, katera baza je zbrana.
MariaDB [(none)]> vrača: ERROR 1305 (42000): FUNCTION DENSE_RANK does not exist
MariaDB [database_run]> vrača: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OVER (PARTITION BY `shop_id` ORDER BY...
Krneki.
--------
UPDATE 3
Sedaj sem še v Python-u nastavil default bazo. In v error-ju dobim syntax error. Če SQL query, ki je vključen v error-ju, skopiram v DBeaver tool, in poženem, pa dela.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
HotBurek ::
--------
UPDATE 4
Sedajle sem poizkusil še z poenostavljenim SQL query-jem, direktno na serverju v terminalu.
Če query poženem z "DENSE_RANK" dobim error: syntax error near "OVER (PARTITION BY..."
Če v tem istem query-u zamenjam "DENSE_RANK" z "ROW_NUMBER" (vse ostalo je isto), query dela.
Kako je mogoče, da DBeaver lahko izvede to funckijo, Python/SQLAlchemy in v terminalu pa to ne gre skos?
Baza je: Server version: 10.10.1-MariaDB-1:10.10.1+maria~deb10 mariadb.org binary distribution
UPDATE 4
Sedajle sem poizkusil še z poenostavljenim SQL query-jem, direktno na serverju v terminalu.
Če query poženem z "DENSE_RANK" dobim error: syntax error near "OVER (PARTITION BY..."
Če v tem istem query-u zamenjam "DENSE_RANK" z "ROW_NUMBER" (vse ostalo je isto), query dela.
Kako je mogoče, da DBeaver lahko izvede to funckijo, Python/SQLAlchemy in v terminalu pa to ne gre skos?
Baza je: Server version: 10.10.1-MariaDB-1:10.10.1+maria~deb10 mariadb.org binary distribution
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
HotBurek ::
--------
UPDATE 5 FINAL
Fantje in dekline. Nea boste verjeli, kaj je bil problem.
Gledam tole dokumentacijo: https://mariadb.com/kb/en/dense_rank/
In si rečem, kaj če bi dal skupi "DENSE_RANK" in "()", brez presledka, tako kot je prikazano v dokumentaciji.
In sem, in je začel delat.
Skratka:
Mogoče gre za pocentialni error oz. bug v Mariji, njej sami. Who knows...
Anyway. Well done. Solid.
UPDATE 5 FINAL
Fantje in dekline. Nea boste verjeli, kaj je bil problem.
Gledam tole dokumentacijo: https://mariadb.com/kb/en/dense_rank/
In si rečem, kaj če bi dal skupi "DENSE_RANK" in "()", brez presledka, tako kot je prikazano v dokumentaciji.
In sem, in je začel delat.
Skratka:
DENSE_RANK () # error DENSE_RANK() # ok ROW_NUMBER () # ok ROW_NUMBER() # ok
Mogoče gre za pocentialni error oz. bug v Mariji, njej sami. Who knows...
Anyway. Well done. Solid.
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
Zgodovina sprememb…
- spremenilo: HotBurek ()
no comment ::
Že to, da v teh časih kričiš v SQL-u je vredno obsojanja. Da pa kar štepaš presledke kjer nimajo kaj iskati...
... je pa čisto prav, da ti kdaj karma ukrade nekaj ur življenja.
;)
... je pa čisto prav, da ti kdaj karma ukrade nekaj ur življenja.
;)
kljuka13 ::
Ne gre za potencialni bug, ampak za sintakso jeziko. Z nastavitvijo IGNORE_SPACE lahko na strežniku nastaviš, da "spregleda" oziroma ignorira presledke. Vendar pa je to mogoče zgolj za nekatere funkcije, preostale pa je treba zmeraj zapisati brez presledka. Podrobnejšo razlago najdeš v dokumentaciji: 9.2.5 Function Name Parsing and Resolution.
Vredno ogleda ...
Tema | Ogledi | Zadnje sporočilo | |
---|---|---|---|
Tema | Ogledi | Zadnje sporočilo | |
» | Mariadb (InnoDB) istočasni insert v tabelo iz različnih procesov (strani: 1 2 )Oddelek: Programiranje | 7279 (3637) | 2g00d4u |
» | Kako napisat SQL query?Oddelek: Programiranje | 1290 (496) | HotBurek |
» | tsql kolona iz vrednosti druge koloneOddelek: Programiranje | 550 (417) | Lonsarg |
» | [SQL] teževa pri iskanju zapisovOddelek: Programiranje | 2102 (1692) | lopow |
» | [sql]-stevilka vrsticOddelek: Programiranje | 1120 (983) | bmandl |