CODEWITHSUNDEEP
sqlpostgresqlpivot
J86
J86

Reputation: 15237

Row value into column headings

I have a readings table. It is defined as:

   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 created_at | timestamp without time zone |           | not null |
 device     | character varying(25)       |           | not null |
 type       | character varying(25)       |           | not null |
 value      | numeric                     |           | not null |

It has data such as:

     created_at      |  device   |    type     |    value
---------------------+-----------+-------------+-------------
 2021-03-16 07:46:47 | 465125783 | temperature |        36.5
 2021-03-16 07:51:48 | 465125783 | temperature | 36.40000153
 2021-03-16 07:52:47 | 465125783 | temperature | 36.40000153
 2021-03-16 07:53:47 | 465125783 | temperature | 36.29999924
 2021-03-24 17:53:47 | 123456789 | pressure    |          79
 2021-03-24 17:54:48 | 123456789 | pressure    |          77
 2021-03-28 05:38:48 | 123456789 | flow        |          12
 2021-03-28 05:45:48 | 123456789 | flow        |          14
 2021-03-28 05:49:47 | 123456789 | pressure    |          65
 2021-03-28 05:50:47 | 123456789 | flow        |          32
 2021-03-28 05:51:47 | 123456789 | flow        |          40

I am trying to write a query where for a specified device, I make each unique value in type become a heading and work out a daily average for the value over the past two weeks only (as time-period from now()).

So basically, I pass my query the device id, and get:

   date     |  device   | pressure | flow
------------+-----------+----------+-------
 2021-03-28 | 123456789 | 65       | 24.5
 2021-03-24 | 123456789 | 78       | 0

Notice the desc sort on date and time information is no longer needed, only the date is required.

I have created a db-fiddle here. The concept of getting column headings from rows is not something I have done before.

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is conditional aggregation, which in Postgres uses FILTER:

select created_at::date, device,
       avg(value) filter (where type = 'pressure') as pressure,
       avg(value) filter (where type = 'flow') as flow
from readings
group by created_at::date, device;

Note: This represents missing values as NULL rather than 0, which makes sense to me. You can use COALESCE() if you want 0.

Here is the db-fiddle.

Upvotes: 1

Related Questions