SQL - Dynamic date column with min, max values

Question

I have a table in PostgreSQL(12) like below.

|Name|ts                |v1 |v2 |v3 |
|----|------------------|---|---|---|
|aaa |2020-02-15 0:00:00|10 |150|5  |
|bbb |2020-02-15 0:00:00|20 |160|10 |
|aaa |2020-02-15 1:00:00|30 |170|15 |
|bbb |2020-02-15 1:00:00|40 |180|20 |
|aaa |2020-02-16 0:00:00|50 |190|25 |
|bbb |2020-02-16 0:00:00|60 |200|30 |
|aaa |2020-02-16 1:00:00|70 |210|35 |
|bbb |2020-02-16 1:00:00|80 |220|40 |

Im planning to create a report table for each day and the difference between the min and max values for v1,v2,v3.

Example output:

|Name|2020-02-15         |2020-02-16         |
|----|-------------------|-------------------|
|aaa |{v1=20,v2=20,v3=10}|{v1=20,v2=20,v3=10}|
|bbb |{v1=20,v2=20,v3=10}|{v1=20,v2=20,v3=10}|

ts - will be dynamically extracted as the column names(only date part)

But I'm struggling to write the logic. It should be, For each name, we should calculate the difference between the min(v1), max(v1), similarly v2 and v3.

From the example table,

• there are 2 rows for the date 2020-02-15
• For v1 we have to find the difference between the min(v1), max(v1) where date=2020-02-15
  • min(v1) = 10, max(v1) = 30, so the difference is 20
  • min(v2) = 150, max(v2) = 170, difference = 20
  • min(v3) = 5, max(v3) =15, difference = 10
• Then for the day 2020-02-15 the output row will be {v1=20,v2=20,v3=10}
• Repeat the same steps for the next day.

Update #1:

• the ts column has many date values, but I'm only interested in only 3 days. It can be current date, current date - 1DAY, current date - 2 days
• I don't care about the format for {v1=20,v2=20,v3=10} It can be space separated or anything. I just want to see those 3 values, that's it.

Can someone help me to write the logic for this?

Answer 1

The first part - aggregating the differences per day - is quite easy:

select name, 
       jsonb_object_agg(date, v) as vals
from (       
  select name, 
         ts::date as date,
         jsonb_build_object('v1', max(v1) - min(v1),
                            'v2', max(v2) - min(v2),
                            'v3', max(v3) - min(v3)) as v
  from the_table
  where .... --<<< limit the dates here
  group by name, ts::date       
) t
group by name

With your sample data this returns:

name | vals                                                                                        
-----+---------------------------------------------------------------------------------------------
aaa  | {"2020-02-15": {"v1": 20, "v2": 20, "v3": 10}, "2020-02-16": {"v1": 20, "v2": 20, "v3": 10}}
bbb  | {"2020-02-15": {"v1": 20, "v2": 20, "v3": 10}, "2020-02-16": {"v1": 20, "v2": 20, "v3": 10}}

Maybe that output is already enough to be processed in your application.

But it's not possible to create a query that returns a different number of columns each time you run it or where the column names are evaluated while running the query. The number, type and name of all columns of a query are determined when the query is parsed by the server.

---

If you can live with the date value per result in a separate column, you can do something like this:

select name, 
       vals #>> '{0,date}' as date_1, 
       vals #>> '{0,values}' as date_1_values, 
       vals #>> '{1,date}' as date_2, 
       vals #>> '{1,values}' as date_2_values
from (       
  select name, 
         jsonb_agg(jsonb_build_object('date', date, 'values', v) order by date) as vals
  from (       
    select name, 
           ts::date as date,
           jsonb_build_object('v1', max(v1) - min(v1),
                              'v2', max(v2) - min(v2),
                              'v3', max(v3) - min(v3)) as v
    from the_table
    where .... --<<< limit the dates here
    group by name, ts::date       
  ) t
  group by name
) x

That would return something like this:

name | date_1     | date_1_values                  | date_2     | date_2_values                 
-----+------------+--------------------------------+------------+-------------------------------
aaa  | 2020-02-15 | {"v1": 20, "v2": 20, "v3": 10} | 2020-02-16 | {"v1": 20, "v2": 20, "v3": 10}
bbb  | 2020-02-15 | {"v1": 20, "v2": 20, "v3": 10} | 2020-02-16 | {"v1": 20, "v2": 20, "v3": 10}

---

You can make that somewhat dynamic using "current_date":

select name, 
       vals ->> (current_date - 2)::text as "current_date - 2",
       vals ->> (current_date - 1)::text as "current_date - 1"
from (       
  select name, 
         jsonb_object_agg(date, v) as vals
  from (       
    select name, 
           ts::date as date,
           jsonb_build_object('v1', max(v1) - min(v1),
                              'v2', max(v2) - min(v2),
                              'v3', max(v3) - min(v3)) as v
    from the_table
    where ts::date in (current_date - 2, current_date - 1)
    group by name, ts::date       
  ) t
  group by name
) x

You can not get the value of current_date - 1 (e.g. 2021-02-17) as the column name.