CODEWITHSUNDEEP
sqlpostgresqlboolean-logic
Daniel Cull
Daniel Cull

Reputation: 303

PostgreSQL How to create a boolean column from conditions within a query

I have the following SQL code:

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

The WHERE clause defines the conditions I would like to use to create a boolean column.

All of this data comes from a table with a jsonB column and for efficiency (I think...) I would need to add this column to that table, by the correct keys.

That table will be updated at least every 30 seconds with a new record. And the query above will be ran less often than that. So there will be nulls in that column. In a general sense how would that affect querying to that table? (new to data science)

I am in a start up and trying to lay the foundations of our data gathering (and they have ME doing it :P) so please rip my code apart and point me to better methods!

Upvotes: 2

Views: 3765

Answers (1)

joop
joop

Reputation: 4503

A condition is a boolean type, so you can just select the value you get from the boolean condition and give it a alias:

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
;

[updated] using the computed columns will need the condition to be pulled to the main query.

Upvotes: 2

Related Questions