sql - 同一张表上的多个JOIN View

Question

我有这个数据库存储传感器采集数据，
采集 (acq) 以固定间隔 (datetime) 来自不同的控制单元 (cu)
每个 Acquisition 都有许多不同的度量值存储在数据表中

acq
    id  
    datetime 
    id_cu     

data          
    id        
    id_acq  
    id_meas
    value

我需要这种观点:

+---------------------+------+----+-----+
|      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 |
+---------------------+------+----+-----+

作为:

v1 是 data.value (例如湿度)
WHERE acq.id_cu=1 AND data.id_meas=100

v2 是 data.value (例如计数器)
WHERE acq.id_cu=2 AND data.id_meas=200

v3 是 data.value (例如温度)
WHERE acq.id_cu=3 AND data.id_meas=300

等等多达数十种用户选择的组合

我最终得到了这个查询，但与生产中的数据相比，它需要的数据量非常少

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 分别获取数据会更快。条件，然后以我想要的应用程序打印数据。

实现这一目标的最佳(和正确)方法是什么？

编辑:假设不缺乏收购我的意思是运行这个:

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)并排显示结果是数百秒与......分钟的问题？

Answer 1

*假设 DATETIME 和 data.id_acq 以及 cu 和 id_meas 都有索引*，您可以尝试使用虚拟列占位符和 kludgey MAX() 的 UNION 查询。如果您的 data.values 不是负数，这应该可以工作(如果是，您可以简单地选择一个非常大的负数而不是零作为虚拟占位符值，一个远远超出可能范围的数字):

    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