Блог

У меня есть таблица в PostgreSQL (12), как показано ниже.

 |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 |

 

Я планирую создать таблицу отчетов для каждого дня и разницу между минимальными и максимальными значениями для v1,v2,v3 .

Пример вывода:

 |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 — будет динамически извлекаться как имена столбцов (только часть даты)

Но я изо всех сил пытаюсь написать логику. Должно быть, для каждого имени мы должны вычислить разницу между min (v1), max (v1), аналогично v2 и v3.

Из таблицы примеров,

• для даты есть 2 строки 2020-02-15
• Для v1 мы должны найти разницу между min(v1), max(v1) where date=2020-02-15
  • min (v1) = 10, max (v1) = 30, поэтому разница составляет 20
  • min (v2) = 150, max (v2) = 170, разница = 20
  • min (v3) = 5, max (v3) = 15, разница = 10
• Тогда в течение дня 2020-02-15 строка вывода будет {v1=20,v2=20,v3=10}
• Повторите те же действия на следующий день.

Обновление # 1:

• в ts столбце много значений даты, но меня интересуют только 3 дня. Это может быть текущая дата, текущая дата — 1 день, текущая дата — 2 дня
• Меня не волнует формат, потому {v1=20,v2=20,v3=10} что он может быть разделен пробелом или чем-то еще. Я просто хочу увидеть эти 3 значения, вот и все.

Может кто-нибудь помочь мне написать логику для этого?

Первая часть — агрегирование различий за день — довольно проста:

 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
 

С вашими образцами данных это возвращает:

 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}}
 

Возможно, этого вывода уже достаточно для обработки в вашем приложении.

Но невозможно создать запрос, который возвращает разное количество столбцов каждый раз, когда вы его запускаете, или где имена столбцов вычисляются во время выполнения запроса. Количество, тип и имя всех столбцов запроса определяются при анализе запроса сервером.

Если вы можете использовать значение даты для каждого результата в отдельном столбце, вы можете сделать что-то вроде этого:

 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
 

Это вернет что-то вроде этого:

 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}
 

Вы можете сделать это несколько динамичным, используя «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
 

Вы не можете получить значение current_date - 1 (например, 2021-02-17) в качестве имени столбца.