SQL vraagje

[remmelt]

Ik zit met de volgende query in m'n maag. Ik heb een tabel (view, eigenlijk) met in het kort de volgende kolommen:

person_id, product_id, date

Het gaat om gekochte producten op een datum. Personen kunnen natuurlijk meerdere producten kopen (graag zelfs) en ook meerdere keren hetzelfde product, dat maakt allemaal niet uit. Voorbeeldrijen zouden kunnen zijn (1, a, '1-1-2009'), (1, b, '2-2-2009'), (1, c, '3-3-2009'), (2, a, '1-1-2009'), (2, b, '2-2-2009'), (3, b, '3-3-2009'), etc.

Wat ik nu in een view wil toveren is van elk persoon het eerste en tweede gekochte product, liefst naast elkaar in verschillende kolommen:

person_id, product_id_1, product_id_2

De rest van de producten maakt me niet uit. Ik wil een overzicht van de eerste twee gekochte producten per persoon, zeg maar.

Het gaat om mysql 5, er kunnen geen subselects in de view definitie voorkomen, dat moeten dus sub-views worden. Ik heb zelf wel per persoon het eerst gekochte product boven water (simpel met een min(date) en group by person_id) maar ik kom niet aan een view/query met het tweede product. Ik probeer dan iets van where date > min(date) en dan groeperen maar dat komt toch niet op het juiste resultaat uit.

Het hoeft alleen in mysql te werken, ANSI zou mooi zijn maar is zeker geen vereiste. Heeft iemand een tip over waar ik zou kunnen zoeken om dit op te lossen?

Dank!

[Janoz]

simpel met een min(date) en group by person_id

Simpel? Ik denk vooral fout. Dat je nu de juiste resultaten gekregen hebt is puur toeval. Product_id niet in je 'group by' op te nemen betekent niet dat het product_id dat bij de kleinste datum hoort terug komt. Welke je terug krijgt is helemaal niet gespecificeerd.

Wat je beter zou kunnen doen is iets met order by en limit, maar dat werkt alleen per klant.

Trouwens, je titel mag wel wat uitgebreider. Je kunt een voorstel doen via een topic report.

[ Voor 10% gewijzigd door Janoz op 18-06-2009 15:57 ]

[remmelt]

Hmm, je hebt gelijk, die volgorde is natuurlijk chronologisch en daardoor wel toevallig goed, maar niet gegarandeerd. Zo had ik het nog niet bekeken.

De order by en limit had ik inderdaad al onder de loep genomen, maar het gaat juist om een klantenoverzicht, niet om gegevens per klant. Ik kan het natuurlijk ook in code op gaan lossen, maar ik heb liever een mooie view. En mijn baas ook.

[cariolive23]

Je weet dat 1-1-2009 geen datum is? Dat je daar helemaal niets meer mee kunt? Sla datums op als datum en behandel ze als datum. Dus een DATE en dus in het ISO-formaat yyyy-mm-dd: 2009-01-01

GROUP BY zul je altijd op de juiste manier moeten gebruiken, ook als MySQL geen foutmeldingen geeft op klinkklare onzin. Of het mag zo zijn dat je onzin retour wilt hebben, maar daar heb je geen query voor nodig, dat kun je zo wel uit je duim zuigen. Configureer MySQL zo dat je foutmeldingen krijgt op onzin-queries, scheelt je een hoop ellende. En dat configureren kan je per sessie doen, je hoeft niet perse de configuratiebestanden van de database aan te passen.

[MueR]

remmelt schreef op donderdag 18 juni 2009 @ 16:14:
De order by en limit had ik inderdaad al onder de loep genomen, maar het gaat juist om een klantenoverzicht, niet om gegevens per klant. Ik kan het natuurlijk ook in code op gaan lossen, maar ik heb liever een mooie view. En mijn baas ook.

Ik ontwerp mijn databases ook altijd ruk om een view mooi weer te geven. Database informatie komt uiteraard altijd in de view, zonder tussenkomst van je business logic. Wat een kul zeg. Een database is bedoeld om je informatie gestructureerd op te slaan, je business logic moet rapen, je view zorgt maar voor de presentatie. De view is echt het minst belangrijk en moet op geen enkele manier invloed hebben op je database.

[Verwijderd]

MueR schreef op donderdag 18 juni 2009 @ 17:31:
[...]

Ik ontwerp mijn databases ook altijd ruk om een view mooi weer te geven. Database informatie komt uiteraard altijd in de view, zonder tussenkomst van je business logic. Wat een kul zeg. Een database is bedoeld om je informatie gestructureerd op te slaan, je business logic moet rapen, je view zorgt maar voor de presentatie. De view is echt het minst belangrijk en moet op geen enkele manier invloed hebben op je database.

Een database kent ook een concept wat view heet...

[Cousin Boneless]

MySql 5.1 ondersteunde toch al subqueries?
Zo niet, dan heb je hier dus niets aan (en vrees ik dat het niet mogelijk is)
En ik zou een auto increment order_id toevoegen, want een datum is niet zo uniek.. Twee bestellingen van dezelfde persoon op dezelfde datum geeft nu een volgend probleem.

SQL:

select o.person_id, o3.product_id, o4.product_id
from (
  select o1.person_id, min(o1.date) as fstDate, min(o2.date) as sndDate
  from tb_order o1
  left join tb_order o2 on o2.person_id = o1.person_id and o2.date > o1.date
  group by o1.person_id
) o
left join tb_order o3 on o3.person_id = o.person_id and o3.date = o.fstDate
left join tb_order o4 on o4.person_id = o.person_id and o4.date = o.sndDate

[ Voor 41% gewijzigd door Cousin Boneless op 19-06-2009 00:58 . Reden: nog wat korter ]

[MueR]

Verwijderd schreef op donderdag 18 juni 2009 @ 20:23:
Een database kent ook een concept wat view heet...

Doet niks af aan het feit dat ie rotzooi in zn database stopt om het er mooi uit te laten zien. In plaats van data waar je iets mee kan staat er nu tekst in, omdat het er leuk uit ziet.

[Remus]

MueR schreef op vrijdag 19 juni 2009 @ 00:15:
[...]

Doet niks af aan het feit dat ie rotzooi in zn database stopt om het er mooi uit te laten zien. In plaats van data waar je iets mee kan staat er nu tekst in, omdat het er leuk uit ziet.

Wie zegt dat er nu tekst in staat. Veel databases zijn prima instaat om '11-01-2009' om te zetten naar een DATE (al moet je dan wel zeker zijn dat de locale dan goed staat: is het nou januari of november ...) en bij het querien in een tool wordt er vaak ook een tekst-resultaat getoond met een vergelijkbaar formaat. Misschien iets meer focus op het probleem en iets minder op stokpaardjes

[winkbrace]

Dit probleem komt opvallend vaak terug in dit forum.

Oracle heeft de functies row_number(), rownum en rank() die reuzehandig zijn hiervoor. Wat mij betreft een prioriteitspuntje voor MySQL developers.

SQL:

select person_id
,      min(case when nr = 1 then datum else null end) datum1
,      min(case when nr = 1 then order else null end) order1
,      min(case when nr = 2 then datum else null end) datum2
,      min(case when nr = 2 then order else null end) order2
from
(
    select row_number() nr
    ,      person_id
    ,      order
    ,      datum
    from   table
    order by datum
)
group by person_id

maar goed, daar heeft TS waarschijnlijk niets aan

[ Voor 19% gewijzigd door winkbrace op 19-06-2009 17:26 ]

[Jehjoa]

Waarom niet gewoon zo?

SQL:

select person_id, product_id, date
from table
where person_id = '1337'  /* voorbeeld */
order by date
limit 2

Of zie ik iets over het hoofd?

edit: Oh, ik zie nu pas dat het gaat om de eerste 2 producten aangeschaft op een bepaalde datum. Dan zul je inderdaad naast de datum ook de tijd op moeten slaan.

[ Voor 30% gewijzigd door Jehjoa op 19-06-2009 17:40 . Reden: TS iets beter gelezen... ]

[curry684]

remmelt schreef op donderdag 18 juni 2009 @ 16:14:
De order by en limit had ik inderdaad al onder de loep genomen, maar het gaat juist om een klantenoverzicht, niet om gegevens per klant. Ik kan het natuurlijk ook in code op gaan lossen, maar ik heb liever een mooie view. En mijn baas ook.

Misschien moet je baas dan even investeren in een enterprise DB-server want dit ga je in MySQL niet voor elkaar krijgen.

Daarnaast is het natuurlijk vanzelfsprekend een volstrekt belachelijke view die je aan het ontwerpen bent. Views zijn bedoeld om tables op een andere, handigere manier te representeren of te aggregeren, zodat deze eenvoudiger en beter cachebaar geschikt zijn om andere queries op uit te voeren. Wat je nu echter aan het doen bent is niet het eenvoudiger ontsluiten van data maar een volstrekt triviale eindtoepassing hard in je database bakken. In other words: je bent presentation logic in je data layer aan het bakken, waarmee je snoeihard 2 tiers fout zit.

[remmelt]

Mensen mensen mensen toch. Die datum is natuurlijk een DATE, ik had 'm alleen even zo neergezet als pseudocode. Bovendien is dit natuurlijk een onderdeel van een veel grotere datastructuur die niet door mij is ontworpen en deels ook gewoon niet lekker in elkaar zit.

Mijn enige vraag is: kan dit in een view in mysql, zo ja, hoe?

Cousin Boneless: ik ga het proberen, die subqueries kunnen in "subviews" komen.

Dat dit niet mooi is in een view, dat heb ik ook wel door. Jammergenoeg is dat niet iets waar ik op dit moment veel aan kan doen.

[curry684]

remmelt schreef op vrijdag 19 juni 2009 @ 18:30:
Dat dit niet mooi is in een view, dat heb ik ook wel door. Jammergenoeg is dat niet iets waar ik op dit moment veel aan kan doen.

Je bent tenslotte een initiatiefloze zak hooi die alle opdrachten van z'n baas blindelings uitvoert, ook als ze volstrekt onzinnig zijn. Kan ik inkomen.

[/sarcasm]

Hoezo kun je er niets aan doen, als je snapt dat het onzinnig is ga je dat toch aan de baas in kwestie uitleggen of is het echt zo'n gewetenloze tiran dat ie je kwalijk neemt als je net doet alsof je een verstandige techneut bent die protesteert tegen technisch inferieure implementaties?

[roy-t]

curry684 schreef op zondag 21 juni 2009 @ 02:23:
[...]

Je bent tenslotte een initiatiefloze zak hooi die alle opdrachten van z'n baas blindelings uitvoert, ook als ze volstrekt onzinnig zijn. Kan ik inkomen.

[/sarcasm]

Hoezo kun je er niets aan doen, als je snapt dat het onzinnig is ga je dat toch aan de baas in kwestie uitleggen of is het echt zo'n gewetenloze tiran dat ie je kwalijk neemt als je net doet alsof je een verstandige techneut bent die protesteert tegen technisch inferieure implementaties?

Sorry hoor maar de TS geeft duidelijk aan dat het in een bestaand systeem moet met een al vastgelegde datastructuur, op school (waar ik gelukkig nog zit) gooi je je projectje dan gewoon lekker om, maar in een draaiende omgeving is het erg moeilijk en erg veel werk om dit aan te gaan passen en vaak is de baas juist wel iemand die dan zegt "NEE". (Gek dat ik dat nu moet vertellen terwijl ik aan neem dat jij hier juist meer ervaring mee hebt).

Iemand een initiatiefloze zak noemen, al is het met een sarcasme tag vind ik dan ook echt niet kunnen, sowieso mag dit topic wel wat aardiger, er worden allemaal aannames gedaan (HUUUU DAT IS EEN STRING ALS DATE) terwijl dat niet af te leiden was en zelfs niet zo blijkt te zijn, ook wil de TS misschien ee beetje misbruik maken van de database om er voor te zorgen dat alles er goed in staat, en ja hij moet zich nu in wat bochten wurmen door het bestaande systeem waardoor dat mogelijk een van de beste oplossingen is. Ook vind ik associaal dat er meteen wordt gezeken op een ietwat brakke sql statement met een group by *(helft v/d discussie is dat MySQL dit gewoon anders doet, en de andere helft dat je in een strict database hier problemen mee krijgt, allemaal heel erg waar maar alleen maar zeuren daar over terwijl het gewoon eerste probeer code was om wat te illustreren, kom op! Zet dan iig nog wat nuttigs in je post.

Ik zou de TS graag helpen maar weet helaas geen antwoord maar ik vond dit nu toch echt teveel gezeur worden (ondanks dat de modjes daar na 2 dagen nog steeds niet uit zijn (zie SM)).

Dus vrolijk en vriendelijk GoT

[/modje]

[curry684]

roy-t schreef op zondag 21 juni 2009 @ 12:12:
[...]


Sorry hoor maar de TS geeft duidelijk aan dat het in een bestaand systeem moet met een al vastgelegde datastructuur, op school (waar ik gelukkig nog zit) gooi je je projectje dan gewoon lekker om, maar in een draaiende omgeving is het erg moeilijk en erg veel werk om dit aan te gaan passen en vaak is de baas juist wel iemand die dan zegt "NEE". (Gek dat ik dat nu moet vertellen terwijl ik aan neem dat jij hier juist meer ervaring mee hebt).

Uh laten we even iets heel belangrijks onderscheiden he - ik zeg nergens dat ie het hele systeem overhoop moet gooien. Hij wil een aantal views ontwikkelen als uitbreiding op het bestaande systeem. Hij is dus nieuwe dingen aan het ontwikkelen en die maakt ie ruk ondanks dat ie blijkbaar weet dat het ruk is, puur omdat de baas zegt dat ie het ruk moet doen.

En ja, ik heb er verdomd veel ervaring mee. Ik heb carriere gemaakt door respect af te dwingen van projectmanagers en lead developers door te doen waarvoor ze mij in dienst hadden of inhuurden: een goede technicus zijn die protesteert tegen de implementatie van technisch inferieure oplossingen.

Iemand een initiatiefloze zak noemen

Ik zei 'zak hooi', geen zak, stukken minder onvriendelijk omdat het om iemand's acties draait en niet om de persoon. En initiatiefloos is ie zeker als ie enkel vraagt hoe hoog als de baas 'm vraagt te springen.