[SQL/MariaDB] Vreemd resultaat in query

[Sleurhutje]

[OPGELOST]

Ik hoop dat een SQL goeroe wat helderheid kan verschaffen. Ik heb van een forum een query opgeduikeld die precies deed wat ik zocht, namelijk een totaal per dag voor de resultaten van een log die om de x-minuten wordt aangevuld vanuit een toepassing.

Alles functioneerde zonder problemen tot het moment er een extra veld werd toegevoegd om onderscheid te maken vanwege meerdere bronnen. Dit is gedaan op basis van een GUID. Sindsdien ontstaat er in de resultaten op een "random" datum een tweede resultaat voor die datum met compleet verkeerde resultaten.

De query

SQL:

SELECT 
    Sub1.diffDate, 
    ((b.pwrConsT1 - a.pwrConsT1) + (b.pwrConsT2 - a.pwrConsT2)) AS pwrDiffCons, 
    ((b.pwrProdT1 - a.pwrProdT1) + (b.pwrProdT2 - a.pwrProdT2)) AS pwrDiffProd, 
    (b.wtrConsT - a.wtrConsT) AS wtrDiffCons, 
    (b.gasConsT - a.gasConsT) AS gasDiffCons 
FROM (
    SELECT 
        DATE_FORMAT(FROM_UNIXTIME(xxx), '%Y-%m-%d') AS diffDate, 
        MIN(xxx) AS MinTimeStamp, 
        MAX(xxx) AS MaxTimeStamp 
    FROM tblP1History 
    WHERE xxx >= 1498725166 AND uid = '11e73ffe-dc32-40de-b2d4-94df20aaf1e5' 
    GROUP BY diffDate
    ) Sub1 
        INNER JOIN tblP1History a ON Sub1.MinTimeStamp = a.xxx 
        INNER JOIN tblP1History b ON Sub1.MaxTimeStamp = b.xxx 
    ORDER BY a.xxx DESC;

Waar xxx is de UNIX timestamp voor de eerste tijd/datum in het verleden en uid de GUID als tekstveld.Alle overige velden zijn Long Integers.

De resultaten die ik terug krijg (momentopname)

Date=2017-10-06, pwrTotCons=5331, pwrTotProd=320, gasTotCons=770, wtrTotCons=28
Date=2017-10-05, pwrTotCons=8003, pwrTotProd=2672, gasTotCons=1813, wtrTotCons=322
Date=2017-10-04, pwrTotCons=27841, pwrTotProd=729, gasTotCons=1753, wtrTotCons=399
Date=2017-10-04, pwrTotCons=-6244074, pwrTotProd=852738, gasTotCons=-1673198, wtrTotCons=1019108
Date=2017-10-03, pwrTotCons=11576, pwrTotProd=2596, gasTotCons=1689, wtrTotCons=590
Date=2017-10-02, pwrTotCons=20343, pwrTotProd=825, gasTotCons=2055, wtrTotCons=351
Date=2017-10-01, pwrTotCons=11058, pwrTotProd=781, gasTotCons=1167, wtrTotCons=305
Date=2017-09-30, pwrTotCons=11119, pwrTotProd=216, gasTotCons=320, wtrTotCons=175
Date=2017-09-29, pwrTotCons=5109, pwrTotProd=0, gasTotCons=323, wtrTotCons=106

De dik gemarkeerde regel geeft totaal onverwachte resulaten. Dat terwijl de regel daarboven wel de juiste resultaten geeft. De datum waarvoor de verkeerde regel wordt weergegeven kan zomaar verspringen als er meer data in de database is geplaatst, soms zijn er ook meerdere regels die een dubbel resultaat geven en soms gaat het gewoon goed. (Date is overigens Sub1.dateDiff)

Iemand enig idee waar dit mis kan zitten? Hints, tips en trucs worden zeer gewaardeerd. Het is een hobby project voor een slimme meter en mijn talenten liggen bij programmeren van microcontrollers, niet op het gebied van SQL.


Jeroen

[ Voor 0% gewijzigd door Sleurhutje op 06-10-2017 13:09 . Reden: Aanvulling en typo's ]

[DigiK-oz]

Ik snap even niet wat je met die xxx bedoelt, maar :

De WHERE op het specifieke uid doe je in sub1. Daar doe je een group by, wat dus geen duplicates zou moeten kennen. Dus, de duplicate ontstaat door de inner join, oftewel er zitten in je tblP1History table dubbele records gelijk aan je MinTimeStamp of MaxTimeStamp. Vermoedelijk records van een ander uid (en dus bogus waarden want die uid heeft waarschijnlijk hele andere, ongerelateerde, waardes).

Ik vermoed dat een WHERE uid='11e73ffe-dc32-40de-b2d4-94df20aaf1e5' toevoegen NA de inner joins (voor de order) dit gaat oplossen. Neem even het uid veld op in zowel sub1 als je outer query, dan zie je of dit inderdaad het issue is.

Je zou ook in sub1 het uid veld mee kunnen selecteren, en deze dan als extra join condition opnemen (sub1.uid=a.uid in de eerste join, sub1.uid=b.uid in de 2e)

[ Voor 18% gewijzigd door DigiK-oz op 06-10-2017 12:07 ]

[CurlyMo]

Wat je nu doet is in Sub1 een subset van de resultaten selecteren horende bij de specifieke uid, maar in je inner joins koppel je alleen op timestamps xxx. Dus waar je in Sub1 alleen filtert op timestamp xxx en uid, join je vervolgens alleen op timestamp xxx, waardoor de uid filter genegeerd wordt.

Als je de where statement verplaats naar buiten de join dan zit je met het probleem dat je MIN en MAX veranderen. Je zult dus óók moeten joinen op de uid

In meer geavanceerde database systemen zoals PostgreSQL had je dit kunnen oplossen met een zogenoemde Windows Function, waarmee je een MIN en MAX op een subset kan toepassen.

code:

MAX(xxx) FILTER (WHERE xxx >= 1498725166 AND uid = '11e73ffe-dc32-40de-b2d4-94df20aaf1e5' ) OVER (PARTITION BY xxx AND uid ORDER BY xxx, uid) AS MaxTimeStamp

[Verwijderd]

Draai de subquery eens los, en kijk of daar geen gekke dingen in zitten. Als dat goed is, doe in je hoofdquery dan eens SELECT * om te kijken of je dan velden ziet die meer duidelijkheid geven over waar die foute regel vandaan kan komen.

edit: ik denk dat @CurlyMo gelijk heeft - had je inderdaad op bovenstaande manier kunnen ontdekken...

[ Voor 18% gewijzigd door Verwijderd op 06-10-2017 12:22 ]

[Sleurhutje]

Jullie hebben mijn dag gemaakt. Zoals ik al zei, ik ben geen SQL goeroe en heb dit met wat knip-en-plak-werk bij elkaar gesprokkeld. Het vermoeden dat het iets te maken zou hebben met de later toegevoegde GUID was dus wel in de juiste richting maar kennis en kunde om de query te ontleden heb ik niet. Het hele verhaal met JOIN's moet ik mijzelf toch maar een keer eigen maken.

De werkende query

SQL:

SELECT 
    Sub1.diffDate, 
    ((b.pwrConsT1 - a.pwrConsT1) + (b.pwrConsT2 - a.pwrConsT2)) AS pwrDiffCons, 
    ((b.pwrProdT1 - a.pwrProdT1) + (b.pwrProdT2 - a.pwrProdT2)) AS pwrDiffProd, 
    (b.wtrConsT - a.wtrConsT) AS wtrDiffCons, 
    (b.gasConsT - a.gasConsT) AS gasDiffCons 
FROM (
    SELECT 
        DATE_FORMAT(FROM_UNIXTIME(xxx), '%Y-%m-%d') AS diffDate, 
        MIN(xxx) AS MinTimeStamp, 
        MAX(xxx) AS MaxTimeStamp 
    FROM tblP1History 
    WHERE xxx >= 1498725166 AND uid = '11e73ffe-dc32-40de-b2d4-94df20aaf1e5' 
    GROUP BY diffDate
    ) Sub1 
        INNER JOIN tblP1History a ON Sub1.MinTimeStamp = a.xxx AND a.uid = '11e73ffe-dc32-40de-b2d4-94df20aaf1e5' 
        INNER JOIN tblP1History b ON Sub1.MaxTimeStamp = b.xxx AND b.uid = '11e73ffe-dc32-40de-b2d4-94df20aaf1e5'
    ORDER BY a.xxx DESC;

Super bedankt voor het meedenken.

[CurlyMo]

Voor het gemak kan je ook dit doen:

code:

a.uid = Sub1.uid
b.uid = Sub1.uid

Hoef je dadelijk maar op één plek je uid aan te passen.

---

Verder is zoeken op uid (char type) vrij sloom. Je kan deze beter in een tweede tabel zetten.

code:

id  uid
1   '11e73ffe-dc32-40de-b2d4-94df20aaf1e5'

Daarna in je query niet de UID als koppelsleutel gebruiken, maar gewoon het ID dat hoort bij het UID, in dit geval dus 1. Scheelt ook weer met indexes aanmaken.

[ Voor 53% gewijzigd door CurlyMo op 06-10-2017 13:19 ]

[Sleurhutje]

CurlyMo schreef op vrijdag 6 oktober 2017 @ 13:11:
Voor het gemak kan je ook dit doen:

code:

a.uid = Sub1.uid
b.uid = Sub1.uid

Hoef je dadelijk maar op één plek je uid aan te passen.

Dat resulteert al in een foutmelding.

CurlyMo schreef op vrijdag 6 oktober 2017 @ 13:11:
Verder is zoeken op uid (char type) vrij sloom. Je kan deze beter in een tweede tabel zetten.

code:

id  uid
1   '11e73ffe-dc32-40de-b2d4-94df20aaf1e5'

Daarna in je query niet de UID als koppelsleutel gebruiken, maar gewoon het ID dat hoort bij het UID, in dit geval dus 1. Scheelt ook weer met indexes aanmaken.

Ja, dat was de volgende stap. Ook om zo een GUID een naam te kunnen geven. De database heeft ook nog geen indexen. Het is allemaal war hobbywerk en kleinschalig. Vooral educatief, zeker op het gebied van SQL met wat complexere queries.

[CurlyMo]

Sleurhutje schreef op vrijdag 6 oktober 2017 @ 13:28:
[...]


Dat resulteert al in een foutmelding.

Als je dingen overneemt zonder zelf nog een beetje na te denken dan kan ik me dat voorstellen Maar voordat we het antwoord voorkauwen, waar denk je dat die foutmelding vandaag komt?

Volg daarbij het advies van @Verwijderd door van binnen naar buiten te werken. Geeft je inline view (Sub1) alles wat je nodig hebt om vervolgens de join voor te zetten?

[Sleurhutje]

Wat ik al zei, ik ben geen SQL goeroe. Ik kan een redelijk platte query maken maar alles met JOIN's etc. gaat mijn pet te boven. Vandaar ook mijn initiële vraag. Ik begrijp maar half wat het allemaal doet, het is knip-en-plak en functioneerde tot ik het stukje van meerdere GUID's ging toevoegen. Ik was in de veronderstelling dat de eerste voorwaarde voldoende zou zijn. Dat was dus kennelijk niet zo. Ik had wel wat zitten experimenteren met een extra voorwaarde her en der maar dat resulteerde ook in foutmeldingen.

Zelf had ik jouw voorbeeld als volgt geplaatst, leek mij het meest aannemelijke

SQL:

INNER JOIN tblP1History a ON Sub1.MinTimeStamp = a.xxx AND a.uid = Sub1.uid

Wat je vraagt over het weergeven van Sub1 etc. dat wordt al aardig abracadabra voor mij.

-Edit-
Vervelende is dat bijvoorbeeld MS Access aangeeft dat de JOIN's niet compleet zijn. Maar het werkt wel in MySQL/MariaDB. Ik heb verder geen andere mogelijkheden dan bij de provider de beschikbare en Office 2016.

[ Voor 12% gewijzigd door Sleurhutje op 06-10-2017 14:47 ]

[CurlyMo]

Sleurhutje schreef op vrijdag 6 oktober 2017 @ 14:45:
Ik kan een redelijk platte query maken 2016.

Begin daar dan eerst eens mee. Extraheer de meest platte query eens uit je SQL zoals ook @Verwijderd al suggereerde en kijk wat het doet? Heb je al eens wat basis lesjes gezocht over SQL op internet?

[Verwijderd]

Da's het leuke - elke database heeft zijn eigen accent!

Dat weergeven van Sub1 is best simpel - het is een query binnen een query, dus je kunt dat hele stuk tussen 'FROM (' en ') Sub1' selecteren en los uitvoeren. Dat is waar de rest van je query mee aan de slag gaat, dus zo kun je controleren of die basis deugt.

Dan kom je er achter dat uid daar niet in de resultaten terugkomt, en je die dus verderop ook niet met Sub1.uid kunt gebruiken...

[CurlyMo]

Verwijderd schreef op vrijdag 6 oktober 2017 @ 15:06:
Dan kom je er achter dat uid daar niet in de resultaten terugkomt, en je die dus verderop ook niet met Sub1.uid kunt gebruiken...

Jammer dat je TS dit zelf niet laat uitzoeken

[Verwijderd]

Ach...het is vrijdag, het bier staat koud - ik ben in een vriendelijke bui.

[CurlyMo]

Verwijderd schreef op vrijdag 6 oktober 2017 @ 15:10:
Ach...het is vrijdag, het bier staat koud - ik ben in een vriendelijke bui.

Daar leert hij/zij alleen weinig van...

[Verwijderd]

Da's elke keer weer een afweging - sommige mensen boeit het niet waarom het niet werkt, als het maar opgelost wordt, en sommige mensen willen het echt weten en leren er dus ook van als het voorgezegd wordt. Ik schat TS meer in die laatste categorie in.

[Sleurhutje]

CurlyMo schreef op vrijdag 6 oktober 2017 @ 15:11:
[...]

Daar leert hij/zij alleen weinig van...

Kwestie van interesses. Ik kan je alles vertellen over Microsoft Exchange Server en Office 365, zowel inrichting als beheer en beveiliging. Script raak in PowerShell en Visual C# op deze platformen. Jaren geleden de keuze gemaakt hierin te specialiseren en SQL te laten voor wat het is. Daarnaast veel bezig met elektronica in het algemeen, microcontrollers van Atmel, Philips, Arm etc. En als verdere hobby’s inzetten voor goede doelen (Stichting Opkikker, Wens op wielen, KWF/Alpe d’HuZes, ALS/Amsterdam City Swim), modelvliegen en part-time DJ. Veel ruimte voor een extra hobby is er dus niet meer, eerst alles utzoeken ook niet. Anders had ik er gewoon omheen geprogrammeerd in PHP door meerdere queries uit te voeren en de rest te filteren uit de resultaten. Niet de manier als het met SQL kan.

[Sleurhutje]

Verwijderd schreef op vrijdag 6 oktober 2017 @ 15:13:
Da's elke keer weer een afweging - sommige mensen boeit het niet waarom het niet werkt, als het maar opgelost wordt, en sommige mensen willen het echt weten en leren er dus ook van als het voorgezegd wordt. Ik schat TS meer in die laatste categorie in.

Zo denk ik er ook over. Zoals hiervoor gepost is het niet mijn vakgebied en respecteer de kennis en kunde van anderen. Zelf haal ik ook een kick ut delen va kennis en kunde. Maar vaak ook een inschatting of iemand verder kan met hints of gewoon op zoek is naar een “quick & dirty” oplossing om verder te kunnen met het oorspronkelijke doel. In mijn geval ging het om het laatste.

De oplossing en aanvullende suggesties alsmede tekst en uitleg worden heel erg gewaardeerd vanuit mijn kant.

[CurlyMo]

Sleurhutje schreef op vrijdag 6 oktober 2017 @ 16:02:
[...]
Jaren geleden de keuze gemaakt hierin te specialiseren en SQL te laten voor wat het is. Daarnaast veel bezig met elektronica in het algemeen, microcontrollers van Atmel, Philips, Arm etc.

Als je met microcontrollers werkt, dan neem ik aan dat je in ieder geval basis C kent en dus ook wat kennis hebt van programmeren. Of het nu over SQL gaat of een programmeertaal, de kern blijft hetzelfde.

C:

int main() {
   int a = 0, b = 1;
   return a;
}

printf("%d\n", main());

Je kan hier niet verwachten een 1 te zien, want je geeft alleen de 0 van a terug.

JavaScript:

function main() {
   var a = 0, b = 1, c = 3;

  return {'a': a, 'b': b};
}

console.log(main()['c']);

Dit geeft een error omdat 'c' niet in de return van main zit.

SQL:

SELECT 
   a, b, c
FROM
    (SELECT 
        0 AS a,
        1 AS b
    FROM
        main
    ) AS main;

Dit geeft een foutmelding omdat c niet bekend is.

Het lijkt mij zinvol om de logica te zien waarin SQL in de basis niet veel anders dan C of Javascript of welke andere taal dan ook, professioneel of hobbymatig gebruik.

Oftwel, je kan niet gebruiken wat je niet selecteert (in SQL) of returned (in een programmeertaal). Dat je niet op de oorspronkelijke constructie was gekomen met de inline view in een join snap ik. Dat is alweer wat geavanceerdere SQL.