[MySQL] Subquery Count results As result

[ajakkes]

Ik wil van items die in de afgelopen week gemeld zijn weten hoe vaak ze de afgelopen maand al gemeld zijn als ze meer dan 10 keer gemeld zijn. Om te bepalen of ze op dezelfde locatie liggen kijk ik alleen naar het eerste deel van de `location`.
Op dit moment toont mijn query het aantal keer dat ze de afgelopen week gemeld zijn als ze de afgelopen maand vaker dan 10 keer gemeld zijn.

Ik denk dat ik een 2e subquery nodig heb op de plek van COUNT(*) as `numerror`, maar ik weet niet hoe deze er uit moet zien.

MySQL:

SELECT SUBSTR( `location` , 1, 9 ) as `kast`, MAX(`pos_enddate`) as `pos_enddate`,COUNT(*) as `numerror`
            FROM `mtmdet2`
            WHERE `pos_enddate` > DATE_SUB( NOW( ) , INTERVAL 7 DAY )
            AND SUBSTR( `location` , 1, 9 )
            IN (
                SELECT SUBSTR( `location` , 1, 9 )
                FROM `mtmdet2`
                WHERE `pos_enddate` > DATE_SUB( NOW( ) , INTERVAL 30 DAY )
                GROUP BY SUBSTR( `location` , 1, 9 ) HAVING COUNT(*)>10
            ) GROUP BY SUBSTR( `location` , 1, 9 );
            
            

Tabelstructuur:

MySQL:

| id | location      | pos_enddate         |
|  1 | AB_12-000_R_3 | 2013-10-31 07:00:00 |
|  2 | AB_12-000_L_3 | 2013-09-31 07:00:00 |
|  3 | AC_12-000_R_3 | 2013-10-31 07:00:00 |

[edeboeck]

Hoi ajakkes,
kan je een export van je tabel geven, dan test ik hier even...

Edit 1: je 2de record bevat een foutje: september telt 30 dagen.

Edit 2: Hier heb ik een mogelijke oplossing (maar wel niet getest met degelijke testset)

MySQL:

SELECT SUBSTR( `location` , 1, 9 ) as `kast`, MAX(`pos_enddate`) as `pos_enddate`, COUNT(*) as `numerror`
            FROM `mtmdet2`
            WHERE SUBSTR( `location` , 1, 9 )
                IN (
                SELECT SUBSTR( `location` , 1, 9 )
                FROM `mtmdet2`
                     WHERE `pos_enddate` > DATE_SUB( NOW( ) , INTERVAL 7 DAY )
                )
            AND SUBSTR( `location` , 1, 9 )
            IN (
                SELECT SUBSTR( `location` , 1, 9 )
                FROM `mtmdet2`
                WHERE `pos_enddate` > DATE_SUB( NOW( ) , INTERVAL 30 DAY )
                GROUP BY SUBSTR( `location` , 1, 9 ) HAVING COUNT(*)>10
            ) GROUP BY SUBSTR( `location` , 1, 9 );

Bedenkingen:

1. heb me niet beziggehouden met deze query te optimaliseren... heb mijn twijfels bij performantie
2. je kijkt enkel in de laatste 30 dagen... is niet altijd hetzelfde als de afgelopen maand...
3. let ook op het meenemen van het tijdsdeel: als je de query runt om 12:00, vind je het resultaat van 11:59:59 van 30 dagen voordien niet terug
4. idem voor afgelopen week

[ Voor 110% gewijzigd door edeboeck op 02-11-2013 20:18 . Reden: Edit 1: 2de testrecord / Edit 2: mogelijke oplossing+bedenkingen ]

[DJMaze]

Heb je de snelheid van je query getest met 1.000.000 records?

Je kan meer in de trand van het volgende testen:

SQL:

SELECT
    SUBSTR( location , 1, 9 ) as kast,
    MAX(pos_enddate) as pos_enddate,
    COUNT(*) as numerror,
    (SELECT COUNT(*) FROM mtmdet2 WHERE ... AND pos_enddate > DATE_SUB( NOW( ) , INTERVAL 30 DAY )) as numerror_month
FROM mtmdet2
WHERE pos_enddate > DATE_SUB( NOW( ) , INTERVAL 7 DAY )

[ajakkes]

Bedankt voor het meedenken. Ik heb het nog niet kunnen testen maar volgens mij geeft de query van edeboeck mij de count(*) van de locations van afgelopen week en niet van afgelopen maand.

De query van DJMaze werpt een nieuw inzicht op het geheel.

MySQL:

SELECT
    SUBSTR( location , 1, 9 ) as `kast`,
    MAX(pos_enddate) as `pos_enddate`,
       (SELECT COUNT(*) FROM mtmdet2 
        WHERE ... 
        AND `pos_enddate` > DATE_SUB( NOW( ) , INTERVAL 30 DAY ) 
        GROUP BY SUBSTR( `location` , 1, 9 )) as `numerror_month`
FROM mtmdet2
WHERE `pos_enddate` > DATE_SUB( NOW( ) , INTERVAL 7 DAY )
AND `numerror_month` > 10

Maar hoe zorg ik ervoor dat ik in de subquery de kasten geteld terugkrijg die in de hoofdquery getoond worden? In kolom 1 krijg ik een kast waarvan ik in de subquery het resultaat wil.

[ Voor 15% gewijzigd door ajakkes op 03-11-2013 04:09 . Reden: Was nog niet klaar ]

[DJMaze]

Je probleem zit hem in de GROUP BY, die zat er bij mij niet in.
Daarnaast gaf ik je een hint over een probleem met je query bij veel records: zeer traag

Idealer zou zijn:

SQL:

CREATE TABLE mtmdet2_kasten (
    kast_id INT NOT NULL AUTO_INCREMENT,
    kast_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (kast_id)
);

CREATE TABLE mtmdet2 (
    mtmdet2_id INT NOT NULL AUTO_INCREMENT,
    kast_id INT NOT NULL,
    pos_enddate INT NOT NULL,
    PRIMARY KEY (mtmdet2_id),
    KEY i_mtmdet2_kast_enddate (kast_id, pos_enddate)
);

SELECT
    kast_name               kast,
    MAX(week.pos_enddate)   pos_enddate,
    COUNT(month.mtmdet2_id) numerror_month
FROM mtmdet2_kasten l
INNER JOIN mtmdet2 week ON (week.kast_id = l.kast_id AND week.pos_enddate > UNIX_TIMESTAMP()-604800) /* day = 86400 seconds */
INNER JOIN mtmdet2 month ON (month.kast_id = l.kast_id AND month.pos_enddate > UNIX_TIMESTAMP()-2592000)
GROUP BY kast_id
HAVING numerror_month > 10

P.S. ipv kast_id kan je de SUBSTR() gebruiken.
De tweede INNER JOIN kan ook als sub-query ipv COUNT(month.mtmdet2_id) numerror_month
Nu heb ik wel je probleem opgelost ipv alleen hints, dit is niet gebruikelijk in het forum.

Je mag zelf uitzoeken welke van de 3 methodes het snelst is.

[ Voor 23% gewijzigd door DJMaze op 03-11-2013 13:54 ]