(my)sql group by

[Verwijderd]

Hallo,

Op dit moment heb ik een resultset die ik verkrijg met behulp van een query die lijkt op deze:

SELECT year,column1,column2,column3 ..... column 110 FROM table WHERE companyid=? ORDER BY year DESC

Resultaat:

year	column1	column2	column3
2003	NULL	10	NULL
2002	30	NULL	NULL
2001	40	NULL	200

Dit is een voorbeeld met 3 jaren, maar in werkelijkheid zijn dit er veel meer. Nu wil ik deze resultset groeperen en wel zo dat van elke kolom het laatst beschikbare jaar resulteert. In mijn voorbeeld komt dat neer op het volgende resultaat:

column1	column2	column3
30	10	200

Is voor een dergelijke groepering een functie in MySQL? Ik vraag me ook af of die er eventueel wel zou zijn in PostgreSQL.

Zo niet, wat is dan de beste oplossing, met de beschikbare programmeertaal de resultset bewerken om het resultaat te krijgen? Het kan gaan om veel data die moet worden opgehaald. Het resultaat wordt gepresenteerd op het web, dus performance speelt een rol.

Alvast bedankt.

[cariolive23]

Het klinkt als een heel fout datamodel, een model dat niet is genormaliseerd. Maar goed, dat kan.

---edit--- plankje misgeslagen, niet goed gelezen

Performance is van heel veel dingen afhankelijk, o.a. van configuratie, indexen, schijven, RAM, etc. etc. Performance is een keuze en daarbij moet je ook kiezen tussen piekperformance of constante performance.

Met EXPLAIN kun je opvragen welk query-plan wordt gebruikt voor de query, met andere instellingen of andere data, kun je ineens een totaal ander plan krijgen. Ga dus testen met bruikbare data en een bruikbare configuratie, anders weet je nog steeds niets.

[ Voor 23% gewijzigd door cariolive23 op 03-03-2010 12:13 . Reden: Missertje... :? ]

[Verwijderd]

In dat geval telt hij toch de waardes op? Dat is niet het resultaat dat ik voor ogen heb. Van elke kolom het laatst beschikbare jaar, daarom heb ik de resultset ook omgekeerd gesorteerd op jaar. Enig idee?

Model probleem is dat van de kolommen een historische weergave moet kunnen worden gemaakt, maar ook de laatst beschikbare gegevens moet kunnen worden gepresenteerd. Zoals je in het voorbeeld kunt, is lang niet elk jaar compleet.

[cariolive23]

Sorry, verkeerd gelezen.

Volgens mij ontstaat jouw probleem door een fout datamodel, dezelfde soort data in meerdere kolommen. Ik zou dat eerst eens oplossen, voordat je met een berg subqueries deze fout probeert recht te breien.

[pedorus]

Dan krijg je 101 subqueries denk ik:

SQL:

SELECT (select column1 from table WHERE companyid=:companyid and column1 is not null 
           ORDER BY year DESC limit 1) as column1,
       (select column2 from table WHERE companyid=:companyid and column2 is not null 
           ORDER BY year DESC limit 1) as column2,
...

Dit lijkt me een hele gekke situatie, die ik me niet direct kan voorstellen. Wat zegt het ook om getallen van verschillende jaren op 1 regel te gaan zetten? Lijkt me dat je meer hebt aan een overzicht van de laatste X jaar, incl. NULL-waardes.

[Ssander]

Deze tabel wil je volgens mij echt normaliseren. Zoveel NULL waarden kan geen goed teken zijn.

Niet getest, maar volgens mij moet iets als dit wel werken;

SQL:

SELECT * FROM table 
WHERE year >= ALL 
              (SELECT year FROM table)

Geen idee hoe deze query qua performance is, though.

EDIT: zie nu dat je iets ingewikkelders wilde doen dan ik dacht, maar misschien is deze query een stap in de goede richting

[ Voor 16% gewijzigd door Ssander op 03-03-2010 12:37 ]

[Verwijderd]

De NULL waardes ontstaan door de start van de database (merge van ongeordende data: excel etc). Het is evenwel de bedoeling ervoor te zorgen dat in toekomstige in te vullen jaren geen tot weinig NULL waardes zullen ontstaan. Het datamodel houd ik dus.

De oplossing voor het (tijdelijke) probleem los ik op door alle data uit de database te halen en met de programmeertaal te bewerken. De resultset cache ik voor lange tijd om performance issues te voorkomen.

Bedankt voor jullie suggesties.

[KabouterSuper]

Mits je sql-dialect het ondersteunt, zou je analytische functies kunnen gebruiken. Met wat basisfuncties is het mogelijk om de eerste non-null waarde te querien...

[RobIII]

Waar hoort mijn topic?
SEA >> PRG

[Janoz]

Verwijderd schreef op woensdag 03 maart 2010 @ 13:28:
De NULL waardes ontstaan door de start van de database (merge van ongeordende data: excel etc). Het is evenwel de bedoeling ervoor te zorgen dat in toekomstige in te vullen jaren geen tot weinig NULL waardes zullen ontstaan. Het datamodel houd ik dus.

Over het algemeen verkrijg je je datamodel door je probleemdomein te analyseren en daar een ontwerp bij te maken, niet omdat het toevallig zo in een excell sheetje stond en dat dat op deze manier zo lekker makkelijk te implementeren is.

[trinite_t]

Ik weet niet helemaal wat de TS er mee bedoelt, maar dit komt op mij over als een tabel met:

code:

companyid, year, option1, option2, option3, ...

wat volgens mij gewoon goed is. En wat je als TS volgens mij als query wilt is de volgende:

SQL:

SELECT year,column1,column2,column3 FROM table WHERE companyid=? ORDER BY year ASC LIMIT 1;

edit:

mss is 110 wel een beetje veel kolommen

[ Voor 7% gewijzigd door trinite_t op 03-03-2010 14:07 ]

[Verwijderd]

Janoz.
Zijn argument om 110 ongenormaliseerde kolommen te houden is namelijk echt 'jammer maar helaas' (imho).

[Janoz]

@trinite_t

Of zijn model nu wel of niet klopt laat ik even in het midden. De manier waarop tot dit model gekomen is is niet correct. Je query levert trouwens niet het door de TS verwachte resultaat. Wanneer een waarde NULL is wil de TS terug in de tijd wandelen totdat er wel een waarde bekend is en deze vervolgens als resultaat terug krijgen.

[Asator]

Dit is het enigste waar ik zo snel op kan komen. Ik heb het ook maar ff uitgetest en het zou het gewenste resultaat op moeten leveren, alleen word dit nogal veel werk als je het voor 110 kolommen moet gaan doen.

Het zou een stuk eenvoudiger worden als die null waardes er niet tussen zaten...

SQL:

SELECT (SELECT year as column1 FROM table WHERE year = (SELECT max(year) FROM table WHERE column1 is not null)) as column1,
(SELECT year as column2 FROM table WHERE year = (SELECT max(year) FROM table WHERE column2 is not null)) as column2,
(SELECT year as column3 FROM table WHERE year = (SELECT max(year) FROM table WHERE column3 is not null)) as column3

Edit:
Ik wist trouwens niet zeker of je nou het jaar terug wou waar de laatste waarde bij staat of die waarde zelf. Ander moet je SELECT year as ... gewoon veranderen in SELECT columnX as ...

[ Voor 12% gewijzigd door Asator op 03-03-2010 14:54 ]

[Guillome]

PHP:

$sql = "";
for ($x = 1; $x <= 110; $x++)
{
  $sql .= "(SELECT year as column$x FROM table WHERE year = (SELECT max(year) FROM table WHERE column$x is not null)) as column$x, ";
}

?

[cariolive23]

Die is vies !

Performance zal lastig worden, maar zoals reeds gezegd, dat is een keuze. Beroerd datamodel, beroerde query en dus is een beroerde performance een logisch gevolg.