从同一张表中提取两个查询 [英] Sustract two queries from same table
问题描述
假设我有2个表:
表 device
是一组测量设备,表 data
是由设备测量的一组不同类型的值。
Let's say I have 2 tables:
table device
is a set of measuring devices, table data
is a set of values of different types measured by the devices.
Table data = (no, id,value_type,value, dayhour) no is PK, id refer to table device
Table device = (id, name) id is PK
我当前有一个查询,该查询将获取由 id $ c $生成的特定
value_type
的所有值的总和c>在特定日期,例如:
I currently have a query that will obtain the sum of all values of a specific value_type
generated by an id
on a specific date, something like:
SELECT SUM(cast(a.value as int)),b.name FROM data a INNER JOIN device b
ON a.id=b.id
AND a.id=1
AND a.value_type=2
AND date(a.dayhour)='2015-12-12'
GROUP BY b.name
问题。现在,我希望能够减去不同值类型的总和。我目前正在做的是两个查询,一个用于获取 value_type
2的总和,另一个用于 value_type
3,然后在上层处理中进行减法。
The query works without problems. Now I want to be able to subtract the sum of different value types. What I'm currently doing is two queries, one for obtaining the sum for value_type
2 and another for the sum for value_type
3 and then doing the subtraction at an upper layer process.
但是,我想从单个查询中执行此操作,例如查询将检索一个列的查询加上 value_type
2,另一个加上 value_type
3,第三个减去这2
However, I would like to do this from a single query, something like a query that will retrieve one column with the sum of value_type
2, another with the sum of value_type
3 and a third one with the subtraction of these 2 columns.
推荐答案
SELECT b.name, a.*, a.sum2 - a.sum3 AS diff
FROM (
SELECT id
, sum(CASE WHEN value_type = 2 THEN value::int END) AS sum2
, sum(CASE WHEN value_type = 3 THEN value::int END) AS sum3
FROM data
WHERE id = 1
AND value_type IN (2, 3)
AND dayhour >= '2015-12-12 0:0'::timestamp
AND dayhour < '2015-12-13 0:0'::timestamp
GROUP BY 1
) a
JOIN device b USING (id);
- 假设
dayhour
的数据类型为时间戳(信息丢失)。
到目前为止的转换将禁用基本索引。这就是为什么我将其转换为可精谓词的原因。更多详细信息:
- 在日期语句之间优化
- Assuming
dayhour
is of data type timestamp (information is missing). The cast to date would disable basic indexes. That's why I transformed it to a sargable predicate. More details:- Optimize BETWEEN date statement
这篇关于从同一张表中提取两个查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!