☰

» »

[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:

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

HotBurek ::

Evo, zgleda sem že half way done.

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

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. 8-)

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

Zgodovina sprememb…

  • spremenilo: HotBurek ()

no comment ::

Uporabiš še row_number().

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

HotBurek ::

Zanimiva situacija...

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

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
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window

Zgodovina sprememb…

  • spremenilo: HotBurek ()

OracleDev ::

Verjetno uporablja drug driver/connector za dostop do baze

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:

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. 8-)
root@debian:/# iptraf-ng
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.

;)

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 ...

TemaSporočilaOglediZadnje sporočilo
TemaSporočilaOglediZadnje sporočilo
»

Mariadb (InnoDB) istočasni insert v tabelo iz različnih procesov (strani: 1 2 )

Oddelek: Programiranje		877335 (3693) 2g00d4u
»

Kako napisat SQL query?

Oddelek: Programiranje		131305 (511) HotBurek
»

tsql kolona iz vrednosti druge kolone

Oddelek: Programiranje		5555 (422) Lonsarg
»

[SQL] teževa pri iskanju zapisov

Oddelek: Programiranje		102109 (1699) lopow
»

[sql]-stevilka vrstic

Oddelek: Programiranje		121133 (996) bmandl

Več podobnih tem