postgresql:根据其他不完整数据排除数据

Question

在此数据中 - 有多个与时间序列数据关联的 DATA_ID 值。我试图从任何 DATA_ID 值中排除所有数据，这些值对于任何时间戳值返回 NULL 值。

换句话说，我只想返回 DATA_ID 值(及其数据)，前提是它们具有所有时间戳值的完整(不是任何 NULL)值。

示例查询如下:

SELECT   
My.Table.DATA_ID,   
MY.Table.timestamp,   
My.Table.USE

FROM   
My.TABLE

WHERE timestamp BETWEEN '2012-06-01 00:00:00' AND '2012-06-02 23:59:59'

-- Something here that says exclude all data from DATA_ID(s)
-- with any missing USE data,   i.e. USE=NULL

ORDER BY DATA_ID, timestamp

---

Answer 1

假设我正确理解了您的问题，并且您想排除包含空值的整批样本(由相等的 data_id 和时间戳确定)。

SELECT   
My.Table.DATA_ID,
MY.Table.timestamp,
My.Table.USE

FROM
My.TABLE o

WHERE timestamp BETWEEN '2012-06-01 00:00:00' AND '2012-06-02 23:59:59'

and not exists (select 1 from my_table i
  where i.use is null
  and i.data_id = o.data_id
  and i.timestamp BETWEEN '2012-06-01 00:00:00' AND '2012-06-02 23:59:59')

ORDER BY DATA_ID, timestamp