[SQL] Samengestelde query met meerder SORTs

[Tieske[82]]

Ik heb even moeite met een SQL query die ik wil draaien. In dit voorbeeld ga ik uit van een bedrijf, wat graag bij wil houden wie van elke functies de beste posts (meeste 'likes') heeft geplaatst. En bij een gelijk aantal likes, telt degene die hem als eerste heeft toegevoegd

In eerste instantie was de oplossing om dmv PHP te loopen over alle functies, daarop een ORDER BY toepassen, en het eerste resultaat gebruiken. Dat komt neer op het uitvoeren van de volgende query voor elke functie afzonderlijk:

SQL:

SELECT 
   `id`, `functie`, `likes`, `posts`, `datum` 
FROM 
   `bedrijf` 
WHERE 
   `functie` = 'Marketing' 
ORDER BY `posts` DESC, `datum` DESC LIMIT 1

Dat moet vast beter en met SQL kunnen dacht ik, dus ik ben begonnen met het maken van een samengestelde query. Het probleem is dat het me tot nu toe alleen maar gelukt is om op één kolom te rangschikken. Onderstaand voorbeeld geeft mijn huidige probeersel weer:

SQL:

SELECT a.`id`, a.`functie`, a.`likes`, a.`posts`, a.`datum` FROM `bedrijf` AS a, (
   SELECT `functie`, MAX(`likes`) `likes`
   FROM `bedrijf`
   GROUP BY `functie`
) m
WHERE a.`likes` = m.`likes`
AND a.`functie` = m.`functie`;

Dit geeft wel de info weer, maar sorteert nog niet op datum. Dus als er twee personen zijn met dezelfde functei die hetzelfde aantal likes hebben gekregen, dan wil ik dat degene die als eerste gepost heeft weergegeven wordt. Simpelweg MAX(datum) 'maxdatum' aan de tweede select toevoegen en opnemen in de WHERE clausule werkt in ieder geval niet.

Heeft iemand een idee hoe ik dit het beste kan aanpakken? Of zijn de afzonderlijke queries hier toch the way to go?

[Verwijderd]

Een tweede GROUP BY select om je eerste heen, die de kleinste datum bepaalt per functie en max. likes?

Aannemende dat je mySQL gebruikt. Als je een database gebruikt die analytic functions ondersteunt (zoals Oracle of SQL Server), dan kan je gebruik maken van ranking en andere leukigheden.

[ Voor 48% gewijzigd door Verwijderd op 09-09-2011 09:30 ]

[Tieske[82]]

Na een beetje puzzelen lijkt het volgende te werken. Ik heb er echter nog niet helemaal 100% vertrouwen in dat dit de juiste resultaten geeft.

1. Zitten hier nog vreemde dingen in?
2. Volgens mij zijn dit 3 SELECT queries in één. Is dit uiteindelijk dan 'sneller' of 'beter' dan 3 losse SELECT queries?

SQL:

SELECT `a`.`functie`, `a`.`likes`, `a`.`datum` FROM `bedrijf` AS a, (
  SELECT `b`.`functie`, `m`.`likes`, MAX(`datum`) AS `maxDatum`
  FROM `bedrijf` AS b, (
    SELECT `functie`, MAX(`likes`) `likes`
    from `bedrijf`
    GROUP BY `functie`
  ) m
  WHERE `b`.`likes` = `m`.`likes` AND `b`.`functie` = `m`.`functie`
  GROUP BY `b`.`functie`
) n
WHERE `a`.`functie` = `n`.`functie` AND `a`.`likes` = `n`.`likes` AND `a`.`datum` = `n`.`maxDatum`
ORDER BY `a`.`functie` ASC;

[RobIII]

Als je betrouwbare resultaten wil hebben zul je sowieso je GROUP BY's moeten fixen: Hoe werkt dat GROUP BY nu eigenlijk?

[ibmos2warp]

offtopic:
Afgezien dat ik dit een beetje vage db vind en niet precies snap wat je wilt...

Zoek jij niet gewoon naar having?

[P_de_B]

Voor de duidelijkheid lijkt het me goed dat je een kort voorbeeld van de data in de tabel geeft en de gewenste output.

[Tieske[82]]

Hieronder staat de tabel weergegeven waar de informatie in zit. Elke regel stelt een geplaatst bericht voor. De kolom 'inhoud_bericht' heb ik voor het gemak even weggelaten.
Wat ik hieruit wil filteren, is een overzicht van de beste berichten per functie. Dus degene met de meeste 'likes'. Als er rijen binnen dezelfde functie met hetzelfde aantal 'likes' zijn, moet de oudste voorrang krijgen. De sorteervolgorde zou dus functie -> likes -> datum moeten zijn. Het uiteindelijke resultaat zouden opeenvolgend de regels met id5, id1, id3 moeten zijn:

• id5 aangezien zowel id4 als id5 de functie 'Eng' hebben, maar id5 meer 'likes' heeft gekregen. De functie 'Eng' komt het eerste voor in het alfabet (voor 'Mkt' en 'Sls').
• id1 aangezien ze beide in Mkt zitten, gelijk aantal likes hebben, maar id1 ('Bob') eerder het beste bericht had geplaatst. De functie 'Mkt' komt na 'Eng' en voor 'Sls'
• id3 aangezien dit het beste bericht in Sls is. De functei 'Sls' is de laatst in het alfabet.

De tabel ziet er als volgt uit:

id	functie	likes	datum	medewerker
1	Mkt	10	2011-08-15 15:53:38	Bob
2	Mkt	10	2011-08-16 15:53:38	Martin
3	Sls	12	2011-08-15 15:53:38	Jeroen
4	Eng	4	2011-08-18 15:53:38	Jan
5	Eng	6	2011-08-18 15:53:38	Roel

[Tieske[82]]

Hij lijkt goed te werken, ook al heb ik er nog een vierde subquery aan toegevoegd welke sorteert op id, zodat regels met dezelfde functie, likes, als datum niet 2 keer voor komen.
Een dergelijke functie wil ik vaak gaan aanroepen. Heeft het zin om hier een Stored Procedure voor te maken? Zijn er guidelines wanneer dat wel en wanneer dat niet verstandig is?

Ik heb het zelf al proberen te benchmarken, maar een loop'je dat x-keer een Stored Procedure uitvoert, komt telkens terug met een sync error na de eerste query. Nu kan dat wel met mysqli_multi_query opgelost worden, maar dan ben ik in feite een andere oplossing aan het benchmarken.

Any ideas?

[lapismontes]

Nog even een paar vragen ter verduidelijking,

zo te lezen wil je een soort scorebord met het "beste" resultaat, wat als er meerdere medewerkers hetzelfde aantal likes hebben en dezelfde datum?

Voorbeeldje, Klaas en Jeroen zouden "gelijk" eindigen.

SQL:

SELECT * FROM likes;
 id | functie | likes |        datum        | medewerker 
----+---------+-------+---------------------+------------
  1 | Mkt     |    10 | 2011-08-15 15:53:38 | Bob
  2 | Mkt     |    10 | 2011-08-16 15:53:38 | Martin
  3 | Sls     |    12 | 2011-08-15 15:53:38 | Jeroen
  4 | Eng     |     4 | 2011-08-18 15:53:38 | Jan
  5 | Eng     |     6 | 2011-08-18 15:53:38 | Roel
  6 | Sls     |    12 | 2011-08-15 15:53:38 | Klaas
(6 rows)

Per afdeling wil je het resultaat?


Als je functies als rank() kunt gebruiken zou dit een oplossing zijn, je creëert een query die de rank binnen de functie aangeeft en filtert daarna op rank=1:

SQL:

SELECT  *
FROM(
    SELECT  *, rank() over (partition by functie order by likes desc, datum)
    FROM    likes
    ) as ranking
WHERE   rank=1
ORDER BY functie, medewerker;
 id | functie | likes |        datum        | medewerker | rank 
----+---------+-------+---------------------+------------+------
  5 | Eng     |     6 | 2011-08-18 15:53:38 | Roel       |    1
  1 | Mkt     |    10 | 2011-08-15 15:53:38 | Bob        |    1
  3 | Sls     |    12 | 2011-08-15 15:53:38 | Jeroen     |    1
  6 | Sls     |    12 | 2011-08-15 15:53:38 | Klaas      |    1
(4 rows)

Wat die rank doet:

• partition by functie Bepaal de rank per functie, niet over het totaal
• order by likes desc, datum Het "beste" resultaat is degene met de meeste likes en de vroegste datum.

Als je MySql gebruikt zul je even op zoek moeten naar een rank functie, of met enkele subqueries die gegroepeerd zijn moeten gaan werken.

N.B. Als je deze query vaak gebruikt zijn er een paar argumenten om het in SQL te doen en niet in een procedure:

• Pure SQL zou op veel databases goed moeten werken
• De databasesoftware kan je query goed optimaliseren
• De databasesoftware kan je query cachen voor betere performance

[Tieske[82]]

Als ze hetzelfde aantal likes en dezelfde datum hebben, dan zou ik ze willen sorteren op Id. Het lijkt me dat die een (fractie van een milli-)seconde eerder was, ook al staat ze niet in de DATETIME als verschillend gemeld. Daarnaast is daarmee ook de kous af. Een Id is uniek, dus na het geven van voorrang aan het laagste Id is de volgorde definitief en zonder dubbelen bepaald zou ik zeggen.
Helaas geen toegang tot RANK() statements. Het toevoegen van nog een subselect is ook een oplossing, maar dan wordt nóg een keer de hele tabel doorzocht. Naast MAX(likes) en MIN(datum), dan ook nog een keer op MIN(id)..

Mbt een procedure: deze query wordt (zonder variabelen) vaak naar de database gestuurd. Ik begreep dat een procedure niets anders was dan een SQL statement(s) welke al in de database opgeslagen worden, en daardoor niet verzonden/geïnterpreteerd hoeven te worden, en dus sneller. Maar ik lees nogal wat tegenstrijdige berichten her en der op het internet. Ik ging er vanuit dat een procedure juist gecached werd, maar dat is dus niet zeker?

[RobIII]

Tieske\[82] schreef op donderdag 15 september 2011 @ 13:47:
Ik ging er vanuit dat een procedure juist gecached werd, maar dat is dus niet zeker?

Bij SP's wordt (vaak) o.a. een execution-plan gecached maar je hebt ook te maken met andere caches zoals de resultset caches etc. Het is allemaal niet zwart/wit en er spelen een aantal factoren mee. Bij wat je leest op het web zul je dus je eigen grijze massa moeten gebruiken en je eigen conclusies moeten trekken. Iedereen roept wel wat maar er zijn er maar een paar die geen onzin uitkramen Wie dat zijn kom je vanzelf achter als je (bijv.) de documentatie er langs legt, tests uitvoert om beweringen te staven etc. en je dus (algemeen) in de materie verdiept.

[lapismontes]

Tieske\[82] schreef op donderdag 15 september 2011 @ 13:47: Naast MAX(likes) en MIN(datum), dan ook nog een keer op MIN(id)..

Als id een counter is waarbij je kunt garanderen dat een lagere id gelijkstaat aan een eerder tijdstip, dan hoef je ook niet op datum te sorteren, dan kun je meteen op min(id) overstappen die is uniek voor deze tabel.

Kun je wel zoiets als dit doen:

SQL:

WITH 
highscores AS (
    SELECT  functie
        ,   max(likes) as likes
    FROM    bedrijf
    GROUP BY functie
) , 
earliest AS (
    SELECT  min(id) as id
    FROM    highscores
    LEFT JOIN bedrijf using(functie,likes)
    GROUP BY functie
)
SELECT  *
FROM    earliest
LEFT JOIN bedrijf using(id)
ORDER BY functie;

Of, als er geen WITH clause is:

SQL:

SELECT  *
FROM    (
        SELECT  min(id) as id
        FROM    (
                SELECT  functie
                    ,   max(likes) as likes
                FROM    bedrijf
                GROUP BY functie
            ) as earliest
        LEFT JOIN bedrijf using(functie,likes)
        GROUP BY functie
    ) as higscores
LEFT JOIN bedrijf using(id)
ORDER BY functie;

Het resultaat van deze queries is:

code:

 id | functie | likes |        datum        | medewerker 
----+---------+-------+---------------------+------------
  5 | Eng     |     6 | 2011-08-18 15:53:38 | Roel
  1 | Mkt     |    10 | 2011-08-15 15:53:38 | Bob
  3 | Sls     |    12 | 2011-08-15 15:53:38 | Jeroen
(3 rows)

Wat betreft performance: de binnenste subquery (highscores) zal veel records bijlangs gaan, de tweede (earliest) al een stuk minder, de laatste zal evenveel records ophalen als er functies zijn, als op id een index rust (omdat deze uniek is) dan zal dat niet heel "zwaar" zijn.

Of dit een probleem is of niet zul je moeten gaan meten en analyseren.

RobIII schreef op donderdag 15 september 2011 @ 14:33:
Het is allemaal niet zwart/wit en er spelen een aantal factoren mee. Bij wat je leest op het web zul je dus je eigen grijze massa moeten gebruiken en je eigen conclusies moeten trekken.

Helemaal mee eens, er is geen "gouden" regel voor heel veel performance gerelateerde vraagstukken op databases. Zolang meningen goed onderbouwd worden heb je wel veel aan adviezen e.d.