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

Question

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!

Since you mention foundations, have a look at en.wikipedia.org/wiki/Database_normalization. Aside from the questionable use of jsonb instead of normal columns, the additional bool column you mention is a non-starter if you care about normal forms. — Daniel Vérité
– Daniel Vérité, Commented Jul 7, 2017 at 15:25
{sensor_id, reader_id} indeed look like (part of) the natural key for this table, so they should be kept out of the json-blob. #2nf — joop
– joop, Commented Jul 7, 2017 at 15:36
@DanielVérité Thanks very much, exactly the type of things I wanted to hear :) I didn't make the DB or Tables, and it is a mess and needs cleaned. Currently the json has no timestamp key, so I was using the created_at column just to get the logic there (there will be a timestamp key). I have also used a JOIN with sensor_id and reader_id (from public.sensor_data) that are on normal columns from a table with no jsonB (other_schema.other_table). The reason we use jsonB is that all our data is built as a JSON object from its source. Whats your opinion on jsonB and joins on normal columns — Daniel Cull
– Daniel Cull, Commented Jul 10, 2017 at 12:11

joop · Accepted Answer · 2017-07-07 15:04:02Z

2

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.

edited Jul 7, 2017 at 15:04

answered Jul 7, 2017 at 14:45

joop

4,5431 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related