[SQL/MySQL] Hoe selecteer ik deze tabel?

[Hatsieflatsie]

Ik heb een aardig probleem. Ik heb 2 dagen mee geworsteld, en toen kwam ik er nog niet uit. Ervaren programmeurs hier wisten het ook niet. .

Ik heb even uitgetypt, met een MySQL tabel als voorbeeld met daarin fictieve waarden:

  +-----------+-----------------+---------------------+             
   |   Naam    |   Gespreksduur  |   TypeGesprek       |             
   +---------------------------------------------------+             
   |           |                 |                     |             
   |   Jaap    |   00:17:45      |   Vaste lijn        |             
   |   Anita   |   00:06:33      |   Vaste lijn        |             
   |   Kees    |   00:06:44      |   Mobiele lijn      |             
   |   Jaap    |   00:02:36      |   Vaste lijn        |             
   |   Jaap    |   00:02:34      |   Mobiele lijn      |             
   |   Anita   |   00:04:50      |   Mobiele lijn      |             
   |   Jaap    |   00:02:41      |   Vaste lijn        |             
   |   Maartje |   00:03:43      |   Mobiele lijn      |             
   |   Kees    |   01:02:46      |   Vaste lijn        |             
   |   Anita   |   00:05:34      |   Mobiele lijn      |             
   |   Jaap    |   00:16:34      |   Vaste lijn        |             
   +-----------+-----------------+---------------------+         

Je hebt dus in de tabel een naam die gebeld heeft, tijdsduur van gesprek, en soort gesprek.

Ik heb de volgende SQL-statement:

code:

SELECT                                          naam
SEC_TO_TIME(SUM(TIME_TO_SEC(Gespreksduur))) as totaalduur 
FROM                                             telefoongesprekken
GROUP BY                                    naam

Dan verschijnen er 2 columns, namelijk de totale tijd en per persoon. Je ziet dus wie totaal uren gebeld heeft. Dan kun je daarop selecteren.

Nu moet er een 3e column bijkomen.

Nu moet ik dat uitscheiden naar type gesprek per persoon, binnen dezelfde query, zodat er een column verschijnt met totale uren per type gesprek, gegroepeerd naar naam.
Dus er moeten nog columns naast de huidige 2 columns komen, namelijk hoeveel uren daarvan aan Mobiele Gesprekken besteed zijn, en hoeveel daarvan aan Vaste gesprekken.

Ik heb verschillende varianten geprobeerd zoals:

code:

SELECT   
(
SELECT naam
SEC_TO_TIME(SUM(TIME_TO_SEC(Gespreksduur))) as vastduur
FROM     telefoongesprekken
WHERE TypeGesprek = 'Vaste lijn'
GROUP BY naam
),
(
SELECT naam
SEC_TO_TIME(SUM(TIME_TO_SEC(Gespreksduur))) as mobielduur
FROM     telefoongesprekken
WHERE TypeGesprek = 'Mobiele lijn'
GROUP BY naam
)

Werkte niet, naast talloze varianten zoals:

code:

SELECT   naam
SEC_TO_TIME(SUM(TIME_TO_SEC(Gespreksduur))) as vastduur
FROM     telefoongesprekken
WHERE TypeGesprek = 'Vaste lijn'
GROUP BY naam

UNION 

SELECT
SEC_TO_TIME(SUM(TIME_TO_SEC(Gespreksduur))) as mobielduur
FROM     telefoongesprekken
WHERE TypeGesprek = 'Mobiele lijn'
GROUP BY naam

Dat gaf niet het gewenste resultaat. De waarden worden dan in 1 column gesorteerd, terwijl er eigenlijk 2 columns bij moeten komen. .

Ik ben niet iemand die het gaat vragen, maar alles uitzoekt. Maar na 2 hele dagen zoeken op internet, is het nog steeds niet gelukt. Wel trof ik suggesties voor LEFT JOIN etc aan, maar die hebben betrekking op externe tabels. Anders zouden de waarden van naam, en totale gespreksduur ook weer weg vallen. .

Hebben de Twiekers onder ons nog tips?

[ Voor 3% gewijzigd door Hatsieflatsie op 04-08-2014 23:40 ]

[NMe]

Waarom moeten het kolommen worden en breid je niet gewoon je select én je group by uit met de typekolom? Dan moet je alleen in je programmacode de data weer netjes naast elkaar zetten maar dat nog geen probleem zijn...

Wat je wil kán wel (redelijk makkelijk zelfs) maar een database is daar helemaal niet op berekend. Presentatie hoort in je presentatielaag.

[ Voor 3% gewijzigd door NMe op 04-08-2014 23:39 ]

[Speedener]

Eensch met NMe dat je dit eigenlijk in de presentatie laag wilt regelen. Als je jezelf toch niet kan bedwingen, iets in deze richting (iig in MySQL kan dit):

code:

SELECT naam,
  SEC_TO_TIME(SUM(TIME_TO_SEC(tTgsGespreksduur))) as totaalduur,
  SEC_TO_TIME(SUM(IF(TypeGesprek = 'Mobiele lijn', TIME_TO_SEC(tTgsGespreksduur), 0))) AS totaal_mobiel,
  SEC_TO_TIME(SUM(IF(TypeGesprek = 'Vaste lijn', TIME_TO_SEC(tTgsGespreksduur), 0))) AS totaal_vast
FROM telefoongesprekken
GROUP BY naam

[ Voor 0% gewijzigd door Speedener op 04-08-2014 23:45 . Reden: Te weinig haakjes ]

[NMe]

Of desnoods aparte subqueries in je select; mogelijk performt dat beter omdat het beter cachebaar is. Los daarvan is een platte query met een group by op elk type handiger. Niet in de laatste plaats omdat je dan nooit je query hoeft aan te passen, ook niet als er ooit een "encrypted gesprek" of "hi-def gesprek" of ... bij komt.

[Ramon]

Hatsieflatsie schreef op maandag 04 augustus 2014 @ 23:34:
Ik heb een aardig probleem..... Ik heb 2 dagen mee geworsteld, ..... binnen dezelfde query,

Jij en je collega's komen niet op het idee om zoiets met meerdere queries op te lossen? Er is geen reden om het per se in 1 query te willen dunkt mij? Haal eerst dmv group by alle unieke users, op, dan doe je per user twee simpele selects, één voor de vaste lijn en één voor de mobiele lijn. Bouw er een mooie array van en klaar, lijkt me?

[ Voor 21% gewijzigd door Ramon op 04-08-2014 23:52 ]

[NMe]

Ramon schreef op maandag 04 augustus 2014 @ 23:49:
[...]
Jij en je collega's komen niet op het idee om zoiets met meerdere queries op te lossen? Er is geen reden om het per se in 1 query te willen dunkt mij? Haal eerst dmv group by alle unieke users, op, dan doe je per user twee simpele selects, één voor de vaste lijn en één voor de mobiele lijn. Bouw er een mooie array van en klaar, lijkt me?

Zelfs die twee heb je niet nodig met die group by op naam én type? Als je dan toch aan de applicatiekant twee query's aan elkaar moet gaan knopen kun je veel beter één query doen om in één keer al die data op te halen en in een array of iets dergelijks zetten. Dan is hooguit paging een probleem.

[Hatsieflatsie]

-edit. Dubbel.

[ Voor 100% gewijzigd door Hatsieflatsie op 05-08-2014 00:15 ]

[Hatsieflatsie]

Speedener schreef op maandag 04 augustus 2014 @ 23:43:
Eensch met NMe dat je dit eigenlijk in de presentatie laag wilt regelen. Als je jezelf toch niet kan bedwingen, iets in deze richting (iig in MySQL kan dit):

code:

SELECT naam,
  SEC_TO_TIME(SUM(TIME_TO_SEC(tTgsGespreksduur))) as totaalduur,
  SEC_TO_TIME(SUM(IF(TypeGesprek = 'Mobiele lijn', TIME_TO_SEC(tTgsGespreksduur), 0))) AS totaal_mobiel,
  SEC_TO_TIME(SUM(IF(TypeGesprek = 'Vaste lijn', TIME_TO_SEC(tTgsGespreksduur), 0))) AS totaal_vast
FROM telefoongesprekken
GROUP BY naam

Ik kreeg hem eerst niet werkend, totdat ik de haakjes bij plaatste. Maar ik zie dat je je post weer aangepast hebt. Dit was het juiste antwoord, waarvoor zeer hartelijk dank!

NMe schreef op maandag 04 augustus 2014 @ 23:39:
Waarom moeten het kolommen worden en breid je niet gewoon je select én je group by uit met de typekolom? Dan moet je alleen in je programmacode de data weer netjes naast elkaar zetten maar dat nog geen probleem zijn...

Wat je wil kán wel (redelijk makkelijk zelfs) maar een database is daar helemaal niet op berekend. Presentatie hoort in je presentatielaag.

Ramon schreef op maandag 04 augustus 2014 @ 23:49:
[...]
Jij en je collega's komen niet op het idee om zoiets met meerdere queries op te lossen? Er is geen reden om het per se in 1 query te willen dunkt mij?

Ramon schreef op maandag 04 augustus 2014 @ 23:49:
[...]
Jij en je collega's komen niet op het idee om zoiets met meerdere queries op te lossen? Er is geen reden om het per se in 1 query te willen dunkt mij? Haal eerst dmv group by alle unieke users, op, dan doe je per user twee simpele selects, één voor de vaste lijn en één voor de mobiele lijn. Bouw er een mooie array van en klaar, lijkt me?

Het zijn meer dan 2 miljoen gesprekken, 9 verschillende typen gesprekken, en daarbij komen de kostencalculaties ook bij. Dus niet bepaald een huis-tuin-keuken query. .


Ik heb het via PHP een array_merge toegepast, voor in een HTML-tabel, maar dat gaf onbegrijpelijke foutmeldingen. Ik zal even een simpele opzet laten zien hoe ik het aangepakt heb:

PHP:

      while($aBeltabel = mysql_fetch_array($aTabel)){
                                    $naam =    $aBeltabel ['naam'];
                                    $tTijdTotaal           = $aBeltabel ["totaalduur"];
$telefoontabel =
'<table>
   <tbody>
      <thead>
         <tr>
         <th> Naam
         <th> Totale duur
         <th> Mobiel duur
         <th> Vast duur 
         </tr>
      </thead>

   <tbody>
      <tr>
         <td>Van '. $naam .'          </td>
         <td>&nbsp;'. $tTijdTotaal .' </td>
         <td>                         </td>
         <td>                         </td>
      </tr>
   </tbody>
</table>'

echo $telefoontabel

Volgens jullie moet ik het in meerdere queries gieten voor performance. Dat kan schelen wanneer het 2 miljoen telefoongesprekken betreft.

Als voorbeeld:

code:

$orginele_query = mysql_fetch_array($eerstequery);
$tweede_query = mysql_fetch_array($tweedequery);

while($telefoongesprekken = array_merge($originele_query,$tweede_query){

... 

Of 

$telefoongesprekken = array_merge($originele_query,$tweede_query);
while($telefoongesprekken) {

...

Dan krijg ik telkens de melding dat de eerste of tweede parameters geen arrays zijn. Wanneer ik ze eerst met $originele_query = array(); declareer, en while($telefoongesprekken) doe, dan is de server onbereikbaar omdat de PHP-interpreter proces de hele CPU belast. Dan moet ik hem telkens weer herstarten.

Dus ik hou het bij 1 query, zodat ik ze alsnog allemaal kan selecteren en het werkend kan krijgen.

[Ramon]

Twee punten:

1) Je weet de performance van mogelijke oplossingen niet omdat je nog niets werkend hebt. Het kan best zijn dat het uitvoeren van een reeks zeer simpele queries sneller is dan één zeer complexe query.

2) Ik heb altijd geleerd om zo snel mogelijk met iets werkend te komen, ongeacht eventuele performance penalty of lelijke code style. Beide kan je - indien nodig, want daar heb je nog geen idee van - achteraf optimaliseren. Het kan ook best zijn dat je eerste oplossing gewoon goed performt.

Dat array_merge hier vreemde errors geeft kan ik je niet bij helpen, ik ben niet meer zo goed met PHP, maar het lijkt me niet nodig om array_merge te gebruiken, je kan gewoon zelf de array exact maken zoals jij dat wil.

Ik zit dus zoiets te denken, pseudocode:

code:

$array = []
$namen =  // query die lijst met unieke namen teruggeeft

foreach $naam in $namen {
    $vaste_gesprekken = // query die voor deze naam de tijdsduur van vaste gesprekken ophaalt
    $mobiele_gesprekken = // query die voor deze naam de tijdsduur van mobiele gesprekken ophaalt
    $array[x]['naam'] = $naam;
    $array[x]['vast'] = $vaste_gesprekken['tijdsduur']
    $array[x]['mobiel'] = $mobiele_gesprekken['tijdsduur']
    $x++;
}

[ Voor 26% gewijzigd door Ramon op 05-08-2014 08:19 ]

[Wasp]

Of je maakt een cronjob die eens in het uur/kwartier/whatever een tabelletje vult met de geaggegreerde data.

Dan kijk je hoogstens tegen iets verouderde data aan, maar dan voorkom je dat er er request miljoenen rijen doorgeworsteld moeten worden.

[Hatsieflatsie]

Dat van cronjob is een goed idee. Ik heb al een soortgelijke cronjob lopen, dus dat zal ik hier bij voegen.

[Gomez12]

Hatsieflatsie schreef op dinsdag 05 augustus 2014 @ 00:11:
[...]
Het zijn meer dan 2 miljoen gesprekken, 9 verschillende typen gesprekken, en daarbij komen de kostencalculaties ook bij. Dus niet bepaald een huis-tuin-keuken query. .

Waarom doe je het dan niet gewoon met group_by en dat je presentatie laag het uitzoekt?

Dan heb je het het meest simpele.

Maak je query gesorteerd op naam, typegesprek en dan loop je in je presentatie gedeelte door de records heen, zodra je id/naam verandert gooi je een nieuwe regel in je tabel, zolang je id/naam gelijk blijft gooi je een nieuwe kolom in je tabel (Nog even ietwat logica nodig om de lege gaten op te vullen, maar dat is ook geen rocketscience)

P.s. Waarom zou het geen huis-tuin-keuken query zijn? Ik vind het geen schrikbarende aantallen voor een dataset.

[NMe]

Hatsieflatsie schreef op dinsdag 05 augustus 2014 @ 00:11:
[...]

Het zijn meer dan 2 miljoen gesprekken, 9 verschillende typen gesprekken, en daarbij komen de kostencalculaties ook bij. Dus niet bepaald een huis-tuin-keuken query. .

Als je je druk maakt over performance moet je juist niet gaan aanklooien met het aanmaken van arbitraire kolommen. Niet dat performance een issue zou moeten zijn: als je indexen goed staan stellen 2 miljoen records niks voor, ook niet met kostencalculaties. En die laatste zou je óók altijd nog naar je programmalogica kunnen verplaatsen, waar ze eigenlijk horen.

Hatsieflatsie schreef op dinsdag 05 augustus 2014 @ 08:57:
Dat van cronjob is een goed idee. Ik heb al een soortgelijke cronjob lopen, dus dat zal ik hier bij voegen.

Een cronjob is nergens voor nodig in dit geval. Je moet gewoon diezelfde query gebruiken die je al in je TS hebt met een group by op die extra kolom, vervolgens de overall totalen in PHP (of wat je client dan ook is) berekenen op basis van de subtotalen en dan kun je het zo in een array kwijt. Je bent echt met een schot hagel op een mug aan het schieten.

Hatsieflatsie schreef op dinsdag 05 augustus 2014 @ 00:11:
Dan krijg ik telkens de melding dat de eerste of tweede parameters geen arrays zijn. Wanneer ik ze eerst met $originele_query = array(); declareer, en while($telefoongesprekken) doe, dan is de server onbereikbaar omdat de PHP-interpreter proces de hele CPU belast. Dan moet ik hem telkens weer herstarten.

Dus ik hou het bij 1 query, zodat ik ze alsnog allemaal kan selecteren en het werkend kan krijgen.

Je houdt het bij één query omdat je array_merge verkeerd gebruikt? Als een van de twee query's geen resultaten oplevert geeft mysql_fetch_* false terug. False is geen array. Do the math. Los daarvan is die array_merge sowieso een raar idee en zijn de mysql_-functies al een flinke tijd deprecated en vervangen door mysqli_*. Lees je de manualpage van de functies die je gebruikt wel eens? Het lijkt wel alsof je at random wat functies bij elkaar gemikt hebt en hoopt dat het werkt. Zo kun je niet programmeren...

[Hatsieflatsie]

NMe schreef op dinsdag 05 augustus 2014 @ 11:43:
Een cronjob is nergens voor nodig in dit geval. Je moet gewoon diezelfde query gebruiken die je al in je TS hebt met een group by op die extra kolom, vervolgens de overall totalen in PHP (of wat je client dan ook is) berekenen op basis van de subtotalen en dan kun je het zo in een array kwijt.

[...]

Je houdt het bij één query omdat je array_merge verkeerd gebruikt? Als een van de twee query's geen resultaten oplevert geeft mysql_fetch_* false terug. False is geen array. Do the math. Los daarvan is die array_merge sowieso een raar idee en zijn de mysql_-functies al een flinke tijd deprecated en vervangen door mysqli_*. Lees je de manualpage van de functies die je gebruikt wel eens?

Ja hoor.
Ik voer een query uit, ontvang hem in PHP, en dan mysql_fetch_array gekoppeld aan een variabele. Dan heb ik een variabele met daarin resultaat van een query. Als ik meerdere resultaten van verschillende queries in een variabele wil hebben, dan zou ik toch array_merge moeten gebruiken? Wat is het alternatief dan?

Ik hanteer namelijk 1 while, waarin bepaalde elementen in HTML moeten voorkomen. Dan kun je niet 2 while's houden met 2 variabelen uit gefetch'de array's.

[NMe]

Je kan ook zonder array_merge je eigen tabel opbouwen in een array. Met twee wild verschillende recordstructuren moet dat toch al.

En ik zeg het in mijn vorige post al. mysql_fetch_* geeft niet altijd een array terug. Array_merge heeft wel altijd een array nodig. Gewoon blind zonder checks iets wat mogelijk wel en mogelijk niet een array is doorpassen gaat dus geheid een keer fout.

Je gaat trouwens niet in op de rest van mijn post.

[Hatsieflatsie]

NMe schreef op dinsdag 05 augustus 2014 @ 18:44:
Je kan ook zonder array_merge je eigen tabel opbouwen in een array. Met twee wild verschillende recordstructuren moet dat toch al.

En ik zeg het in mijn vorige post al. mysql_fetch_* geeft niet altijd een array terug. Array_merge heeft wel altijd een array nodig. Gewoon blind zonder checks iets wat mogelijk wel en mogelijk niet een array is doorpassen gaat dus geheid een keer fout.

Dat begrijp ik ook wel. Ik krijg bij var_dump een array terug, dus dat is ook uitgesloten.

Je gaat trouwens niet in op de rest van mijn post.

Excuus, ik zie niet wat ik daarop moet zeggen?

[Hatsieflatsie]

Trouwens, er gaat iets mis met m'n eerdere query. Ik wil begrijpen wat er daarin misgaat. Stel, je hebt in MySQL een varchar-kolom met daarin 2 rijen:

09000000001
09000024#

Waarom retourneert deze query alleen 1 rij? Namelijk die met een hekje erin .

MySQL:

SELECT GebeldeNummer
FROM telefoongesprekken
WHERE SUBSTRING( telefoongesprekken.GebeldeNummer, 1, 4 ) =  '0900'

[ Voor 12% gewijzigd door Hatsieflatsie op 06-08-2014 22:57 ]

[GlowMouse]

Wellicht staat er een spatie voor de eerste. Doe maar eens:

MySQL:

SELECT GebeldeNummer, SUBSTRING( telefoongesprekken.GebeldeNummer, 1, 4 )
FROM telefoongesprekken

[Ramon]

bedoel je niet

SQL:

SELECT GebeldeNummer FROM telefoongesprekken WHERE GebeldeNummer LIKE '0900%'

[Hatsieflatsie]

GlowMouse schreef op donderdag 07 augustus 2014 @ 02:43:
Wellicht staat er een spatie voor de eerste. Doe maar eens:

MySQL:

SELECT GebeldeNummer, SUBSTRING( telefoongesprekken.GebeldeNummer, 1, 4 )
FROM telefoongesprekken

Werkt gewoon, maar hoe selecteer ik de nummers die met 0900 beginnen?

Ramon schreef op donderdag 07 augustus 2014 @ 07:24:
bedoel je niet

SQL:

SELECT GebeldeNummer FROM telefoongesprekken WHERE GebeldeNummer LIKE '0900%'

Ook eerder geprobeerd, dan pakt-ie nummers bij die een 0900 bevatten, maar niet met 0900 beginnen. Dat wil ik dus niet. .

Iemand een verklaring waarom de 0900 met hash-tag werkt, en gewone 0900 niet?

[Ramon]

Hatsieflatsie schreef op donderdag 07 augustus 2014 @ 08:46:
Ook eerder geprobeerd, dan pakt-ie nummers bij die een 0900 bevatten, maar niet met 0900 beginnen. Dat wil ik dus niet. .

Iemand een verklaring waarom de 0900 met hash-tag werkt, en gewone 0900 niet?

Neehoor. Let op de locatie van de % in mijn post. En lees tegelijkertijd dit eens goed door: http://www.w3schools.com/sql/sql_like.asp

Hij pakt dus alles wat begint met "0900".

Toch wel basic SQL vraagjes hoor.....

[Russel88]

Hatsieflatsie schreef op dinsdag 05 augustus 2014 @ 00:11:

Het zijn meer dan 2 miljoen gesprekken, 9 verschillende typen gesprekken, en daarbij komen de kostencalculaties ook bij. Dus niet bepaald een huis-tuin-keuken query. .

En als in de toekomst een type erbij komt, mag je al je queries/code nalopen, omdat je queries statisch zijn.

Zoals anderen zeggen:
Group By op naam en Type. DataTable/Set afvangen in je presentatielaag.