[SQL] grouping gedoe met select max

[MrHighStone]

Zit met een grouping probleempje, waar ik niet uit kom.

Stel ik heb de volgende (fictieve) tabel Orders:
WeekNr, KlantNaam, OrderNr
1, Jansen, 123
1, Jansen, 124
1, Jansen, 125
1, Klaasen, 126
2, Jansen, 127
2, Jansen, 128
2, Pietersen, 129
3, Klaasen, 130

Met een query wil ik per week zien wie de meeste orders geplaatst heeft. Het resultaat zou moeten zijn:
WeekNr, KlantNaam, MaxOrders
1, Jansen, 3
2, Jansen, 2
3, Klaasen, 1

Ik kwam tot de volgende query (voor de duidelijkheid even uitgeschreven):
SELECT CO.WeekNr, CO.KlantNaam, MAX(CO.AantalOrders) AS MaxOrders FROM (
SELECT WeekNr, KlantNaam, COUNT(OrderNr) AS AantalOrders FROM Orders
GROUP BY WeekNr, KlantNaam ) CO
GROUP BY CO.WeekNr, CO.KlantNaam

Het result is dan echter:
WeekNr, KlantNaam, MaxOrders
1, Jansen, 3
1, Klaasen, 1
2, Jansen, 2
2, Pietersen, 1
3, Klaasen, 1

Het punt is dat ik hiermee gewoon het aantal orders per klant per week krijg, de MAX functie wordt ahw niet gebruikt.
Volgens mij zit het hem in het groupen. Als ik een van de twee velden (WeekNr, KlantNaam) weg laat in de query, krijg ik nl wel de goede results.
Komt iemand dit bekend voor?

[Tux]

Misschien is het handiger om je query wat overzichtelijker neer te zetten

code:

SELECT
 CO.WeekNr,
 CO.KlantNaam,
 MAX(CO.AantalOrders) AS MaxOrders
FROM (
 SELECT
  WeekNr,
  KlantNaam,
  COUNT(OrderNr) AS AantalOrders 
 FROM
  Orders 
 GROUP BY WeekNr, KlantNaam ) CO
GROUP BY
 CO.WeekNr,
 CO.KlantNaam

Misschien is dit iets?

code:

SELECT
 CO.WeekNr,
 CO.KlantNaam,
 count(CO.AantalOrders) as Orders
FROM
 Orders
GROUP BY
 Orders.KlantNaam
WHERE
 WeekNr = [vul_week_nr_in]

[ Voor 4% gewijzigd door Tux op 23-04-2003 20:25 ]

[Varienaja]

Je wil niet het hoogste ordernummer selecteren (MAX) maar het aantal orders tellen (COUNT).

Kijk maar fijn even in de help.

[Verwijderd]

laat ook maar volgens mij had ik onzin neer gezet...

[ Voor 103% gewijzigd door Verwijderd op 23-04-2003 20:31 ]

[MrHighStone]

TUX: ik wil niet per week de query uitvoeren, maar een overzicht per week, per klant
Varienaja: de help heb ik van voor tot achteren bekeken, biedt helaas geen oplossing.

Zoals ik zei, volgens mij zit het hem in de group by, daar moet een andere oplossing voor zijn volgens mij.

Voor alle duidelijkheid, ik wil dus van alle geplaatste orders het maximaal aantal voorkomens per week per klant zien.
Dus wel degelijk een combi van tellen en max volgens mij!

[ Voor 13% gewijzigd door MrHighStone op 23-04-2003 22:03 . Reden: vergat nog wat ]

[whoami]

Je wilt dus enkel diegene zien die het meeste orders heeft geplaatst?

Check eens in de manual hoe je de output kunt limieteren;
Als je MySQL gebruikt: zoek eens op LIMIT
als je MS SQL Server/Access/MSDE gebruikt: zoek eens op TOP.

Ik vind het echter zowiezo al een vage constructie:

code:

select blaat 
from ( select ...

Volgens mij is dat geen ANSI SQL en is dit waarschijnlijk enkel mogelijk in dat ene DBMS dat pretendeerd een RDBMS te zijn (MySQL).
Daarnaast is het denk ik ook niet zo performant.

[MrHighStone]

whoami: het is t-sql, database sqlserver 2000.
Overigens, lees mijn openingspost nog even goed. Ik wil niet de output limiteren, maar per week de naam van de klant die in die bewuste week de meeste orders heeft geplaatst.
Het lijkt zo eenvoudig, maar helaas...

[Varienaja]

MrHighStone schreef op 23 April 2003 @ 22:00:
Voor alle duidelijkheid, ik wil dus van alle geplaatste orders het maximaal aantal voorkomens per week per klant zien.

Eerst per week bepalen hoeveel orders iedereen gedaan heeft:

code:

select week, klant, count(orders) as aantal
group by week, klant

Hieruit selecteren we het item met het hoogste aantal:

code:

select week, klant, max(aantal) from (die vorige query)
group by week, klant

Je krijgt dus:

code:

select week, klant, max(aantal) from
   (
      select week, klant, count(orders) as aantal
      group by week, klant
   )
group by week, klant

Dit werkt niet op iedere DB, misschien moet je een tussentabel ofzoiets gebruiken.

Goh, da's precies wat je had.
Sorry ik dacht niet goed na. Ik kom er wel uit denk ik, maar ik heb geen zin om ermee verder te klooien. Ik ga eerst slapen.

[ Voor 13% gewijzigd door Varienaja op 23-04-2003 22:50 ]

[whoami]

Het probleem zit hem er idd in dat je grouped op weeknr en klantnaam. Het DBMS gaat nl. het record gaan kiezen per groep weeknr en klantnaam.
Als je zou groupen op weeknr alleen, dan zou het wel mogelijk zijn. Alleen, dan kan je de klantnaam er niet zomaar bij zetten.

Je kunt natuurlijk wel de gegevens als volgt gaan ophalen:

code:

SELECT weeknr, klantnaam, COUNT(ordernr) AS aantal
FROM tabel
GROUP BY weeknr, klantnaam
ORDER BY weeknr, aantal

Je hebt dan bv volgende output:

code:

1     Jan        30
1     Piet       17
2     Piet       25
2     Jan        11

En dan mbhv een script iedere keer enkel het eerste record van de week gaan outputten.
Het is wel zo geen mooie oplossing, maar een workaround...

[Verwijderd]

select week, klant, max(aantal) from (die vorige query)group by week, klant

Ik heb het niet getest maar volgens mij geeft dit hetzelfde resultaat als wat die vorige query al gaf. Herinner je dat je gegroupeerd had op week en klant dus als je dit nu weer neemt groupeerd ie telkens 1 veld en kan hij ook maar van 1 veld het maximum nemen.
Als je nu in plaats van grouperen in de WHERE een voorwaarde zou stellen dat aantal gelijk moet zijn aan het maximum gegroupeerd per week van het resultaat van die eerdere subquery dan zou het lukken. Zoiets dus.

code:

select week, klant, aantal
from (
   select week, klant, count(orders) as aantal
   from orders
   group by week, klant
)
where aantal = (
   select max(aantal)
   from (
      select week, klant, count(orders) as aantal
      from orders
      group by week, klant
   )
   group by week
)

Ik kan het dus echt niet controleren maar ik denk dat het zou moeten werken. Wel een boel subqueries zo.

[MrHighStone]

Ha, we komen ergens.
Het bevestigt inderdaad mijn vermoeden dat dit lastiger is dan dat je op het eerste gezicht zou denken.

Somey: Ik zal eens aan de gang gaan met je tip!

[MrHighStone]

Tijd voor een schopje...

[whoami]

Vertel eens hoe het gegaan is met de tip van Somey, en of je al wat anders geprobeerd hebt enzo ipv gewoon te schoppen...

[Verwijderd]

Waarom gebruik je geen subselect? dus

code:

select
  varnaam = test.resultaat,
  nogiets
from tabel a
JOIN (select 
         resultaat = max(mijninteger)
          
) AS test ON b.column = a.column

[Verwijderd]

Ok ik heb even zitten proberen in postgresql en heb de volgende oplossing gevonden

code:

SELECT weeknr, klantnaam, count(klantnaam) as orderno
FROM orders as o
GROUP BY weeknr, klantnaam
HAVING count(klantnaam) >= (
   SELECT max(orderno2)
   FROM (
      SELECT weeknr, klantnaam, count(klantnaam) as orderno2
      FROM orders
      WHERE weeknr = o.weeknr
      GROUP BY weeknr, klantnaam
   ) as o2
   GROUP BY weeknr
);

Nu ter verduidelijking.
De hoofdquery gaat aanvankelijk alle namen per week groeperen en het aantal orders per week tellen. Nu hebben we enkel de grootste nodig dus ga ik in de having kijken of het getelde aantal overeenkomt met de grootste.

Eerste subquery:
Moet dus het grootst aantal orders voor die specifieke week van de huidige rij geven.De wteede subquery zorgt er al voor dat je enkel die van deze week hebt.

Tweede subquery: We moeten uiteraard per klant het aantal orders kennen, deze is dus vrij gelijkaardig aan de hoofdquery maar ik beperk me hier tot de rijen met hetzelfde weeknr.

Aan jou om dit een beetje te optimaliseren

[MrHighStone]

Al met al ben ik gekomen tot het volgende:

code:

SELECT
    CO.WeekNr, CO.KlantNaam, MO.MaxOrders 
FROM 
(   SELECT
        WeekNr, KlantNaam, COUNT(OrderNr) AS AantOrders
    FROM Orders
    GROUP BY WeekNr, KlantNaam ) CO,

(   SELECT WeekNr, MAX(AantOrders) AS MaxOrders 
    FROM
    (  SELECT WeekNr, KlantNaam, COUNT(OrderNr) AS AantOrders
       FROM Orders
       GROUP BY WeekNr, KlantNaam ) CM
       GROUP BY WeekNr ) MO
WHERE
  MO.WeekNr = CO.WeekNr
AND
  MO.MaxOrders = CO.AantOrders

Niet echt optimaal volgens mij, omdat er een twee keer een zelfde count actie wordt uitgevoerd. Maar goed, werken doet het wel.

Ik ga nog eens aan de slag met de tip van Somey.

[MrHighStone]

Somey, uiteraard werkt jouw query ook.
Ik ben niet zo'n enorme sql goeroe, dus wat de meest optimale query is zou ik niet durven zeggen. Ze werken allebei, en dat is het belangrijkste.

[Verwijderd]

Daarmee doelde ik erop dat je indexes gebruikt op de gepaste velden en deze query niet op de gehele tabel uitvoert maar enkel op de laatste maand(en).