distinct op 1 kolom in een query ipv op de hele result ?

[twiekert]

IK heb een query waar ik de data in een kolom wil omzetten naar bits om deze vervolgens te SUMen. Het probleem wil echter dat de data in de records niet uniek is. Er kan bijvoorbeeld 123435 in voorkomen (dubbel 3 dus) wat dus het bitwise optellen in de war stuurt.

M'n tabel ziet er zo uit.

id	case_id	oldstatus	newstatus	employee_id	dateadded
1	1	0	1	1	17-5-2005 10:43:00
2	1	1	8	1	17-5-2005 11:45:02
3	5	0	1	1	30-5-2005 15:11:37
4	5	1	6	1	31-5-2005 13:56:00
5	5	6	5	1	31-5-2005 16:15:20
6	5	5	6	1	31-5-2005 17:38:09

Dit is een history tabel welke de oude status en de nieuwe status toont (oldstatus is dus de newstatus van het vorige record) op welke datum de statuswijziging is uitgevoerd, op welke case het betrekking heeft en wie het heeft gedaan.

Wat ik nu wil doen is de gemiddelde doorlooptijd berekenen van 1 status naar een andere status,
waarbij de statussen die daartussen zijn doorlopen ook moet worden meegenomen. Bij case_id 5 is dat dus status: 0 > 1 > 6 > 5 > 6.


zo'n query heb ik al die laat zien wat de doorlooptijd is van 1 status naar een andere status voor alle cases:

code:

SELECT
      t1.case_id AS t1_case_id,
      t2.case_id AS t2_case_id,
      t1.oldstatus AS t1_oldstatus,
      t1.newstatus AS t1_newstatus,
      t2.oldstatus AS t2_oldstatus,
      t2.newstatus AS t2_newstatus,
      (UNIX_TIMESTAMP(t2.dateadded) - UNIX_TIMESTAMP(t1.dateadded)) AS timestamp_diff
FROM
    casestatus t1,
    casestatus t2
WHERE
     t2.case_id = t1.case_id
     AND t1.newstatus = t2.oldstatus
     AND t2.dateadded >= t1.dateadded

Deze query geeft als resultaat:


negeer case_id 1, de status klopt hier van niet!

Dat is dus OK, wat er nu nog moet gebeuren is dat de timestamp_diff gesummed moet worden en daar een gemiddelde van wordt berekend. Wat je hier dus ziet is de status wijziging met het verschil in tijd (seconden) tussen die 2 statussen. CASE_ID 2, eerste row bijvoorbeeld: van status 0 > 1 > 2 duurde 4710 seconden. Daarna volgt nog een status wijziging voor case_id 2.

Het probleem ligt echter dat ik alleen cases wil berekenen die een bepaalde status doorlopen hebben, bijvoorbeeld status tot en met 5, dit betreft dus case_id 4 geheel en case_id 5 GEDEELTELIJK, alleen de eerste 2 rows dus (0 > 1 > 6 > 5). de resterende cases daarvan moet dus niet het gemiddelde meegerekend worden.

Nu dacht ik dat op te lossen door de status naar bits om te zetten (1 << t2.newstatus), deze vervolgens te summen zodat ik met een HAVING clause kan checken of een bepaalde status er in voorkomt (HAVING sum_of_bits & (1 << 5)). Dit lost trouwens nog niet het probleem op dat ik alleen de rows wil hebben tot die juiste laatste status.

de query:

code:

SELECT
      t2.case_id,
       SUM(1 << t2.newstatus) AS bitwise
FROM
    casestatus t1,
    casestatus t2
WHERE
     t2.case_id = t1.case_id
     AND t1.newstatus = t2.oldstatus
     AND t2.dateadded >= t1.dateadded
GROUP BY
      t1.case_id,
      t2.case_id

resultaat:



De sum van de bits van case_id 5 klopt nu niet meer, wat er nu gesummed wordt is 64 (status 6) + 32 (status 5) + 64 status (6) = 160.

De laatste status wil ik niet summen omdat deze al voorkomt, hoe doe ik dat ?
Ik heb al de halve dag if constructies geprobeerd met subqueries zodat ik kon berekenen of de bits al gezet waren, maar het is me niet gelukt.

heeft er iemand nog heldere ideeen ?

/edit:
owja, in MYSQL 5 kan dit dus met een DISTINCT op 1 kolom uit te voeren (wat mijn dubbele rows dus elimineerd), ik heb echter mysql 4.1

[ Voor 102% gewijzigd door twiekert op 02-06-2005 09:41 ]

[twiekert]

Nou ik heb het opgelost. dat bitwise gedoe heb ik maar laten varen, was veel te omslachtig.
de werkende query:

code:

    SELECT
        from_status, /* this is value that comes from the query in the FROM part  */
        to_status, /* this is value that comes from the query in the FROM part  */
        AVG(sum_timediff) AS average /* calculate the average of the sum of all status changes using the sum_timediff value from the query in the FROM part */
    FROM
        (   SELECT
                (SELECT newstatus FROM casestatus WHERE case_id = t2.case_id AND dateadded = (SELECT MIN(dateadded) FROM casestatus WHERE case_id = t2.case_id)) AS from_status, /* get the start date for the current case */
                (SELECT newstatus FROM casestatus WHERE case_id = t2.case_id AND dateadded = (SELECT MAX(dateadded) FROM casestatus WHERE case_id = t2.case_id) ) AS to_status, /* get the end date for the current case */
                SUM((UNIX_TIMESTAMP(t2.dateadded) - UNIX_TIMESTAMP(t1.dateadded))) AS sum_timediff /* sum all time differences (from 1 status to the next) */
            FROM
                casestatus t1,
                casestatus t2
            WHERE
                t2.case_id = t1.case_id
                AND t1.newstatus = t2.oldstatus 
                AND t2.dateadded >= t1.dateadded /* only get status changes that follow-up (next status date lies in the future). if a status change occurs twice, the time difference is calculated twice (positive number and a negative number */
            GROUP BY 
                t1.case_id
            HAVING 
                to_status IN (6,8)
        ) AS t3
    GROUP BY 
        to_status, 
        from_status

Ik SUM de status veranderingen op en bereken het gemiddelde. Dit voer ik uit door het summen in een query in de FROM clause uit te voeren. Dit geeft een resultaat terug en ik pak hier weer het gemiddelde van.

http://dev.mysql.com/doc/mysql/en/unnamed-views.html

[ Voor 9% gewijzigd door twiekert op 02-06-2005 11:31 ]