Geek asks and answers

Several kinds of JOIN in one table

I have data on the storage of db sensors.
Purchases (acq) come from different control units (cu) at fixed intervals (datetime)
Each Acquisition has many different measures stored in the data table

acq
    id  
    datetime 
    id_cu     

data          
    id        
    id_acq  
    id_meas
    value

I need a view like this:

+---------------------+------+----+-----+
|      datetime       |  v1  | v2 |  v3 |
+---------------------+------+----+-----+
| 2010-09-13 00:05:00 | 40.9 |  1 | 0.3 |
| 2010-09-13 00:10:00 | 41.0 |  2 | 0.3 |
| 2010-09-13 00:15:00 | 41.1 |  4 | 0.3 |
+---------------------+------+----+-----+

AS:

  • v1 - data.value value (for example, humidity)
    WHERE acq.id_cu = 1 AND data.id_meas = 100

  • v2 is data.value (e.g. counter)
    WHERE acq.id_cu = 2 AND data.id_meas = 200

  • v3 - data.value (e.g. temperature)
    WHERE acq.id_cu = 3 AND data.id_meas = 300

etc. up to dozens of combinations selected by the user

, ,

SELECT a1.datetime, d1.value, d2.value, d3.value
FROM
    acq a1, data d1
    JOIN acq a2, data d2
        ON a2.id=d2.id_acq AND a2.datetime=a1.datetime
    JOIN acq a3, data d3
        ON a3.id=d3.id_acq AND a3.datetime=a1.datetime
WHERE a1.id=d1.id_acq
    AND a1.id_cu=1 AND d1.id_meas=100
    AND a2.id_cu=2 AND d2.id_meas=200
    AND a3.id_cu=3 AND d3.id_meas=300

, a1.id_centr=x AND d1.id_meas=y, , , , .

( ) ?

edit: , :

SELECT datetime, value
FROM acq, data
WHERE acq.id=data.id_acq
    AND (
        id_cu=1 AND id_meas=100
        OR id_cu=2 AND id_meas=200
        OR id_cu=3 AND id_meas=300
    )
ORDER BY id_cu, id_meas

id_cu/id_meas (, python + numpy) - hundreths ... ?

+2
2

* , DATETIME data.id_acq cu id_meas *, UNION kludgey MAX(). , . ( , , ):

    select FOO.datetime, max(FOO.v1), max(FOO.v2), max(FOO.v3)
    from
     (

     select acq.datetime, data.value as v1,0 as v2, 0 as v3
     from acq inner join data on acq.id = data.id_acq
     where acq.id_cu=1 and data.id_meas=100

     UNION

    select acq.datetime, 0 as v1, data.value as v2, 0 as v3
    from acq inner join data on acq.id = data.id_acq
    where acq.id_cu=2 and data.id_meas=200

    UNION

    select acq.datetime, 0 as v1, 0 v2, data.value as v3
    from acq inner join data on acq.id = data.id_acq
    where acq.id_cu=3 and data.id_meas=300
    ) as FOO
    group by FOO.datetime
+2

JOINS ( ); :

SELECT a1.datetime, d1.value, d2.value, d3.value
FROM
    acq a1 
       INNER JOIN data d1 ON a1.id=d1.id_acq
       INNER JOIN acq a2  ON a2.datetime=a1.datetime
       INNER JOIN data d2 ON a2.id=d2.id_acq
       INNER JOIN acq a3  ON a3.datetime=a1.datetime
       INNER JOIN data d3 ON a3.id=d3.id_acq
WHERE 1=1
    AND a1.id_centr=1 AND d1.id_meas=100
    AND a2.id_centr=2 AND d2.id_meas=200
    AND a3.id_centr=3 AND d3.id_meas=300
+1

More articles:


All Articles