Сложный json запрос + sum

@tiny developer · Регистрация: 27.06.2013

Студворк — интернет-сервис помощи студентам

здравствуйте. Прошу вас помочь мне с запросом. Я имею две таблицы. В первой таблице хранятся данные о использовании приложения пользователем за некоторый промежуток времени.
И имею талицу гранул, которую я составила с помощью запроса по первой таблице.
Вот скрины моих таблиц:

Пример что содержит поле app_info:

Таблица гранул времени по 5 минут:

Но мне нужно получить результирующий запрос, который для каждой гранулы составит массив json по использованным программам в течении гранулы времени. Так же нужно сгруппровать полю name_app и domain_site и составить массив заголовков. Для каждого приложения нужно ещё посчитать количество секунд его использования.
Например: пользователь в течении 10 минут использовал браузер открывая разные вкладки. Нам нужно получить тогда следующий json:

JSON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
        [
      {
        "app_name": "googlechrome",
        "path": "C:\\ProgramFiles\\google\\googlechrome.exe",
        "domain_site": "stackoverfloww.com",
        "count_seconds": 540,
        "titles": [
          {
"count_seconds": 320,
            "url": "https://stackoverflow.com/questions/40978290/construct-json-object-from-query-with-group-by-sum",
            "title": "Construct json object from query with group by / sum"
          },
          {
"count_seconds": 220,
            "url": "https://stackoverflow.com/questions/43117033/aggregate-function-calls-cannot-be-nested-postgresql",
            "title": "aggregate function calls cannot be nested postgresql"
          }
        ]
      }
    ]

А если пользователь за 10 минут использовал несколько приложений, то по каждому приложению такую статистику.
Не могу никак справиться с таким запросом. Вот какой запрос я написала:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT 
employee_id,
DATE,
granula_start,
granula_end,
 
(SELECT 
    array_agg(json_build_object('seconds', SUM( CASE 
                                      WHEN end_time > granula_end 
                                      THEN ((EXTRACT(MINUTE FROM granula_end) - EXTRACT(MINUTE FROM start_time))*60) 
                                      ELSE (CASE  WHEN EXTRACT(MINUTE FROM start_time) = EXTRACT(MINUTE FROM end_time) 
                                                  THEN 
                                                   EXTRACT(SECOND FROM end_time) - EXTRACT(SECOND FROM start_time)
                                                  ELSE
                                                   (EXTRACT(MINUTE FROM end_time) - EXTRACT(MINUTE FROM start_time)) * 60 
                                           END )
                                      END ),
                      'domain_site', m.app_info::jsonb->'domain_site',
                      'app_name', m.app_info::jsonb->'app_name',
                      'app_type', m.app_info::jsonb->'app_type' )) AS app_info
 FROM pps.my_temp m
 WHERE start_time >= granula_start AND start_time <= granula_end
 GROUP BY m.app_info::jsonb->'domain_site', m.app_info::jsonb->'app_name', m.app_info::jsonb->'app_type'
 )
 
FROM granules

Но получаю ошибку...

я ожидаю получить такую вот таблицу

@Swa111 · 01.09.2025, 07:56

разбейте запрос на два вложенных в одном сначала посчитайте сумму а потом уже array_agg

Новые блоги и статьи Все статьи Все блоги /
Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .	Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США. Programma_Boinc 26.12.2025 Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .	Thinkpad X220 Tablet — это лучший бюджетный ноутбук для учёбы, точка. Programma_Boinc 23.12.2025 Рецензия / Мнение/ Перевод Нашел на реддите интересную статью под названием The Thinkpad X220 Tablet is the best budget school laptop period . Ниже её машинный перевод. Thinkpad X220 Tablet —. . .	PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Как объединить две одинаковые БД Access с разными данными VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.
Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .	Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .

@Swa111 912 / 286 / 57 Регистрация: 01.06.2023 Сообщений: 808
	01.09.2025, 07:56
	разбейте запрос на два вложенных в одном сначала посчитайте сумму а потом уже array_agg 0