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?

If just a select query will not do, then I'm fine with sp or UDF — TheDataGuy
– TheDataGuy, Commented Feb 17, 2021 at 8:49
Should the columns be dynamic? Or are there always 2 date columns? Could you please explain how you want to create the date column? — S-Man
– S-Man, Commented Feb 17, 2021 at 8:52
Its dynamic, the actual data has 1yr date, but I'm interested in last 3 days — TheDataGuy
– TheDataGuy, Commented Feb 17, 2021 at 8:52
Do you really want text like "v1=30" or do you want an integer array? — S-Man
– S-Man, Commented Feb 17, 2021 at 8:53
Columns cannot be different for each query (one result with 2 columns and next one is 3 columns or something). You need to tell: 3 columns. Maybe: CURRENT_DATE, CURRENT_DATE + 1, CURRENT_DATE + 2. Please describe your EXACT use case in the question — S-Man
– S-Man, Commented Feb 17, 2021 at 8:56

user330315 · Accepted Answer · 2021-02-17 09:23:24Z

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.

Collectives™ on Stack Overflow

SQL - Dynamic date column with min, max values

Example output:

Update #1:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example output:

Update #1:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related