Fórum Root.cz

Hlavní témata => Vývoj => Téma založeno: to_je_jedno 13. 10. 2015, 20:24:40

Název: MySQL: největší záznamy uživatele
Přispěvatel: to_je_jedno 13. 10. 2015, 20:24:40

Mam dve tabulky:
users (user_id int PK, name varchar)
items (item_id bigint PK, user_id int, value int, timestamp int)

Potreboval bych vypsat takovou tabulku:
uid x, suma tri nejlepsich zaznamu uzivatele, suma vsech zaznamu uzivatele
 - nejvyssi items.value tohoto uzivatele
 - druha items.value item tohoto uzivatele
 - treti items.value item tohoto uzivatele
uid y, suma tri nejlepsich zaznamu uzivatele, suma vsech zaznamu uzivatele
 - nejvyssi items.value tohoto uzivatele
 - druha nejvyssi items.value tohoto uzivatele
 - treti nejvyssi items.value tohoto uzivatele
ORDER BY (suma tri nejlepsich zaznamu uzivatele) DESC a to cele pouze z polozek za poslednich 365 dnu

Jak nejefektivneji na to?

Napadlo me treba pri vlozeni zaznamu prepocitat uzivatele, ale narazi to na problem kazdodenniho prepoctu vsech uzivatelu protoze nejlepsi polozka se stane starsi 365 dnu.

Název: Re:MySQL: největší záznamy uživatele
Přispěvatel: Pavel... 14. 10. 2015, 09:23:41

Citace: to_je_jedno  13. 10. 2015, 20:24:40

Jak nejefektivneji na to?

Ked si odmyslime fakt, ze zadanie je scestne a toto sa jednoducho nema robit na DB-vrstve, tak osobne by som ako prve skusal, ze ci to nepojde indexom nad uzivatelom a hrubou silou napisat monstrozne SQL co tie data vrati.

Je tych zaznamov per uzivatel tolko, ze by to bolo prilis pomale?

Název: Re:MySQL: největší záznamy uživatele
Přispěvatel: k 14. 10. 2015, 10:06:46

Takhle nějak se dělá první řádek:

Kód: [Vybrat]

select users.user_id,
(
  select sum(x.value) as sum_value
  from
  (
    select items.value
    from items
    where items.user_id=users.user_id and items.timestamp<(now()-365)
    order by items.value desc
    limit 3
  )
 X
)
as Three_Largest,
(
  select sum(items.value) as sum_value
  from items
  where items.user_id=users.user_id and items.timestamp<(now()-365)
)
as Sum_All
from users

Název: Re:MySQL: největší záznamy uživatele
Přispěvatel: k 14. 10. 2015, 10:07:58

podmínka s timestamp měla být obráceně

Název: Re:MySQL: největší záznamy uživatele
Přispěvatel: petr 14. 10. 2015, 13:11:01

Jaká je to db?

pokud Oracle, nebo MS SQL, tak doporučuju použít windowing funkce - dost to urychlí práci.

Název: Re:MySQL: největší záznamy uživatele
Přispěvatel: to_je_jedno 14. 10. 2015, 13:33:53

petr - je to v titulku  ;D
k - diky, to vypada jako presne ono, vyzkousim, order apod uz si samozrejme doresim.

Název: Re:MySQL: největší záznamy uživatele
Přispěvatel: eL 14. 10. 2015, 21:39:03

taky jsem si s tim hral, tak se podelim:

Citace

SELECT * ,
   (SELECT value      FROM items WHERE items.user_id=users.id ORDER BY value DESC LIMIT 0,1) AS v1,
   (SELECT value      FROM items WHERE items.user_id=users.id ORDER BY value DESC LIMIT 1,1) AS v2,
   (SELECT value      FROM items WHERE items.user_id=users.id ORDER BY value DESC LIMIT 2,1) AS v3,
   (SELECT SUM(value) FROM items WHERE items.user_id=users.id                              ) AS vTotal
   FROM users
ORDER BY v1+v2+v3 DESC

Casove omezeni si uz urcite vlozis sam.
Sum top 3 je lepsi udelat az aplikacne v1+v2+v3.
Varianta pro total se SUM+join+groupby se mi chovala hur nez tato, ale mohlo to byt ovlivneno konkretnimi daty(a indexy), nad kterymi jsem testoval. I tak je to kupodivu docela svizne (Percona 5.6), ubira tomu jen ten globalni order by podle dat, ktery do indexu nedas.
Kdybych hledal jeste vykonejsi variantu, tak pokud zapisu do items relativne malo, tak bych asi zkusil variantu s tabulkou itemsTop3 a plneni triggrem po insertu/updatu v items. Ale dost slozite by se tam resila ta casova platnost s ohledem na index souctu top3.