的PostgreSQL如何查询
问题描述:
内创建条件布尔列我有以下SQL代码:的PostgreSQL如何查询
SELECT sensor_id, reader_id, rssi, date_time, time_diff_lead, reader_diff
FROM
(
SELECT doc->>'sensorId' as sensor_id, doc->>'readerId' as reader_id, (doc->>'rssi')::int as rssi, created_at as date_time,
EXTRACT(EPOCH FROM lead(created_at) OVER w - created_at) as time_diff_lead,
(lead(doc->>'readerId') OVER w)::int - (doc->>'readerId')::int as reader_diff
FROM public.sensor_data
WINDOW w AS (PARTITION BY doc->>'sensorId' ORDER BY created_at)
) as x
WHERE rssi > 380 OR time_diff_lead > 18.0 AND reader_diff = 0
ORDER BY sensor_id, date_time
WHERE子句定义,我想用它来创建一个布尔值列的条件。
所有这些数据来自一个jsonB列的表和效率(我认为...)我需要通过正确的键将该列添加到该表。
该表将至少每30秒更新一次新记录。上面的查询将比这个运行次数少。所以这个列中会有空值。在一般意义上,这将如何影响对该表的查询? (数据科学新手)
我正在启动并试图为我们的数据收集打下基础(并且他们让我这样做:P),所以请撕开我的代码并指出我更好的方法!
答
的条件是一个boolean类型,所以你可以选择从布尔条件获得的价值,并给它一个别名:
SELECT sensor_id, reader_id, rssi, date_time
, time_diff_lead, reader_diff
, (rssi > 380 OR time_diff_lead > 18.0 AND reader_diff = 0) AS bull
FROM (
SELECT doc->>'sensorId' as sensor_id, doc->>'readerId' as reader_id
, (doc->>'rssi')::int as rssi, created_at as date_time
, EXTRACT(EPOCH FROM lead(created_at) OVER w - created_at) as time_diff_lead
, (lead(doc->>'readerId') OVER w)::int - (doc->>'readerId')::int as reader_diff
FROM public.sensor_data
WINDOW w AS (PARTITION BY doc->>'sensorId' ORDER BY created_at)
) as x
ORDER BY sensor_id, date_time
;
[更新]使用计算列将需要将条件拖到主查询中。
+0
谢谢,我觉得自己像一个白痴没有尝试过这种方式! –
既然您提到了基金会,请参阅https://en.wikipedia.org/wiki/Database_normalization。除了使用jsonb而不是普通列以外,如果您关心的是正常表单,那么您提到的其他bool列是非启动器。 –
{sensor_id,reader_id}确实看起来像这张表的自然键(的一部分),所以它们应该被保留在json-blob之外。 #2nf – joop
@DanielVérité非常感谢,正是我想听到的东西的类型:) 我没有制作数据库或表格,它是一个混乱,需要清理。目前,json没有时间戳记密钥,所以我使用created_at列只是为了获取逻辑(将有一个时间戳记密钥)。 我还使用了一个JOIN与sensor_id和reader_id(来自public.sensor_data)在一个没有jsonB(other_schema.other_table)表的普通列上。 我们使用jsonB的原因是我们所有的数据都是从源代码构建的JSON对象。你对jsonB的看法和加入正常的列 –