SQLRU.net
Разработка приложений баз данных

Начало » Использование СУБД » PostgreSQL » Выполнение запроса в цикле на уровне SQL запроса (Выполнение запроса в цикле на уровне SQL запроса)
Выполнение запроса в цикле на уровне SQL запроса [сообщение #5249] Fri, 19 July 2024 12:16 Переход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
Доброго времени суток!
Коллеги, требуется помощь в решении задачки.
Дано:
Имеется вот такой запрос который транспонирует временной ряд в более реляционный вид (запрос упрощен и представлен для одного тега).
Запрос вытаскивает последнее значение за сутки.
На вход получаю произвольную дату на основе которой необходимо сформировать отчет за месяц, пусть будет 19.07.2024

SELECT
'Дата' AS repdate,
CASE WHEN t1.tagname = 'ASRMB.U_AVT12.FT0002.Out.Mt' THEN t1.dvalue END AS ASRMB_U_AVT12_FT0002_Out_Mt,
FROM (
SELECT case when h.recordtype = 'inner' then h.valdouble else NULL END AS dValue, h.time, n.tagname 
FROM nodes_history h JOIN nodes n ON n.NodeId = h.NodeId 
WHERE n.TagName = 'ASRMB.U_AVT12.FT0002.Out.Mt' AND (h.time BETWEEN '2024-07-18 00:00:00.00' AND '2024-07-18 23:59:59.00') AND h.recordtype != 'ubound' ORDER BY h.time DESC LIMIT 1) t1,
Нужно:
На уровне SQL запроса, как-то разложить дату от 19 числа по 1 число с шагом в сутки.
Поочередно подставить каждую дату в запрос и в результате получить набор значений по дням с 1 по 19 число месяца.
Буду очень признателен за любую посильную помощь, времени совсем нет, а заказчик не ждет.
Спасибо!

[Обновления: Fri, 19 July 2024 12:32]

Известить модератора

Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5253 является ответом на сообщение #5249] Fri, 19 July 2024 13:16 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
Первое что приходит на ум - это сделать таблицу с записями от 1 до 31.
Выбирать из неё записи по условию <= переданного числа, ну и дальше джойниться с ней.
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5254 является ответом на сообщение #5253] Fri, 19 July 2024 13:17 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
BlackEric писал(а) Fri, 19 July 2024 13:16
Первое что приходит на ум - это сделать таблицу с записями от 1 до 31.
Выбирать из неё записи по условию <= переданного числа, ну и дальше джойниться с ней.
Пример бы увидеть). Задача для меня не профильная.
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5256 является ответом на сообщение #5254] Fri, 19 July 2024 13:25 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
У меня сейчас нет postgre sql под рукой.
В таблицу вставляете числа от 1 до 31.
Из переданной вам даты получаете число.
Имея это число делаете выборку из таблицы.
Далее в хранимке, можно в цикле сформировать нужные вам даты парся переданную и подставить в запрос.
Как-то так.
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5257 является ответом на сообщение #5256] Fri, 19 July 2024 13:31 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
BlackEric писал(а) Fri, 19 July 2024 13:25
У меня сейчас нет postgre sql под рукой.
В таблицу вставляете числа от 1 до 31.
Из переданной вам даты получаете число.
Имея это число делаете выборку из таблицы.
Далее в хранимке, можно в цикле сформировать нужные вам даты парся переданную и подставить в запрос.
Как-то так.
ХП использовать не могу (
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5258 является ответом на сообщение #5257] Fri, 19 July 2024 13:37 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
While loop. тогда в обычном цикле.
Уменьшайте значение на 1 в каждой итерации и так пока будет >=1.
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5259 является ответом на сообщение #5258] Fri, 19 July 2024 13:51 Переход к предыдущему сообщениюПереход к следующему сообщению
SD в настоящее время не в онлайне  SD
Сообщений: 391
Зарегистрирован: August 2022
Senior Member
А что, generate_series() из слона уже выпилили?
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5260 является ответом на сообщение #5259] Fri, 19 July 2024 14:20 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
SD писал(а) Fri, 19 July 2024 13:51
А что, generate_series() из слона уже выпилили?
Можно и так. Хотя способ получения последовательности не принципиален.
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5263 является ответом на сообщение #5258] Fri, 19 July 2024 15:09 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
BlackEric писал(а) Fri, 19 July 2024 13:37
While loop. тогда в обычном цикле.
Уменьшайте значение на 1 в каждой итерации и так пока будет >=1.
а как будет выглядеть структура такого запроса, вложенный запрос крутить в цикле ? Непонятен момент когда будет склеиваться в цикле таблица )
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5264 является ответом на сообщение #5263] Fri, 19 July 2024 15:26 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
qwerty5000 писал(а) Fri, 19 July 2024 15:09
BlackEric писал(а) Fri, 19 July 2024 13:37
While loop. тогда в обычном цикле.
Уменьшайте значение на 1 в каждой итерации и так пока будет >=1.
а как будет выглядеть структура такого запроса, вложенный запрос крутить в цикле ? Непонятен момент когда будет склеиваться в цикле таблица )
Можете во временную таблицу вставлять результаты и из неё уже финальную выборку делать.
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5265 является ответом на сообщение #5264] Fri, 19 July 2024 16:28 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
Спасибо. Вот такую структуру собрал, но не работает, вообще у такой структуры есть шансы на жизнь? Может укажете на ошибки которые в глаза бросаются

CREATE TEMPORARY TABLE temp_table (value double, rdate timestamp, name text);
do $$
declare iCounter intenger:= 1;
declare iIter intenger:= 0;
declare iValue double:= NULL;
declare rDate timestamp:= NULL;
declare strName text:= NULL;
begin
	while iCounter <= SELECT EXTRACT(DAY FROM TIMESTAMP '2024-07-19 00:00:00.00') loop
		iIter = iIter + 1;
		SELECT @iValue = case when h.recordtype = 'inner' then h.valdouble else NULL END AS dValue, @rDate =  h.time, @strName = n.tagname FROM nodes_history h JOIN nodes n ON n.NodeId = h.NodeId
		WHERE n.TagName = 'ASRMB.U_AVT12.FT0038.Out.Mt' AND (h.time BETWEEN (SELECT '2024-07-19'::timestamp - INTERVAL iIter + 1 DAY)  AND (SELECT '2024-07-19'::timestamp - INTERVAL iIter DAY) AND h.recordtype != 'ubound' ORDER BY h.time DESC LIMIT 1
		INSERT INTO temp_table VALUES (@iValue, @rDate, @strName);
end loop;
end $$;

Drop table temp_table;
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5266 является ответом на сообщение #5264] Fri, 19 July 2024 16:30 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
BlackEric писал(а) Fri, 19 July 2024 15:26
qwerty5000 писал(а) Fri, 19 July 2024 15:09
BlackEric писал(а) Fri, 19 July 2024 13:37
While loop. тогда в обычном цикле.
Уменьшайте значение на 1 в каждой итерации и так пока будет >=1.
а как будет выглядеть структура такого запроса, вложенный запрос крутить в цикле ? Непонятен момент когда будет склеиваться в цикле таблица )
Можете во временную таблицу вставлять результаты и из неё уже финальную выборку делать.
Спасибо. Вот такую структуру собрал, но не работает, вообще у такой структуры есть шансы на жизнь? Может укажете на ошибки которые в глаза бросаются

CREATE TEMPORARY TABLE temp_table (value double, rdate timestamp, name text);
do $$
declare iCounter intenger:= 1;
declare iIter intenger:= 0;
declare iValue double:= NULL;
declare rDate timestamp:= NULL;
declare strName text:= NULL;
begin
	while iCounter <= SELECT EXTRACT(DAY FROM TIMESTAMP '2024-07-19 00:00:00.00') loop
		iIter = iIter + 1;
		SELECT @iValue = case when h.recordtype = 'inner' then h.valdouble else NULL END AS dValue, @rDate =  h.time, @strName = n.tagname FROM nodes_history h JOIN nodes n ON n.NodeId = h.NodeId
		WHERE n.TagName = 'ASRMB.U_AVT12.FT0038.Out.Mt' AND (h.time BETWEEN (SELECT '2024-07-19'::timestamp - INTERVAL iIter + 1 DAY)  AND (SELECT '2024-07-19'::timestamp - INTERVAL iIter DAY) AND h.recordtype != 'ubound' ORDER BY h.time DESC LIMIT 1
		INSERT INTO temp_table VALUES (@iValue, @rDate, @strName);
end loop;
end $$;

Drop table temp_table;
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5270 является ответом на сообщение #5266] Fri, 19 July 2024 17:07 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
Отлаживайте по частям. Так то похоже
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5278 является ответом на сообщение #5270] Mon, 22 July 2024 12:25 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
BlackEric писал(а) Fri, 19 July 2024 17:07
Отлаживайте по частям. Так то похоже
упростил уже запрос, все равно ругается

Drop table temp_table;
CREATE  TABLE temp_table (value integer, rdate timestamp, name text);
do $$
declare iCounter integer:= 1;
declare iIter integer:= 0;
declare iValue integer:= NULL;
declare rDate timestamp:= NULL;
declare strName text:= NULL;
begin
  while iIter < (SELECT EXTRACT(DAY FROM TIMESTAMP '2024-07-19 00:00:00.00')) loop
    iIter = iIter + 1;
		SELECT iValue = (case when h.recordtype = 'inner' then h.valdouble else NULL END AS dValue), rDate =  h.time, strName = n.tagname FROM nodes_history h JOIN nodes n ON n.NodeId = h.NodeId
		WHERE n.TagName = 'ASRMB.U_AVT12.FT0038.Out.Mt' AND (h.time BETWEEN ('2024-07-15')  AND ('2024-07-19')) AND h.recordtype != 'ubound' ORDER BY h.time DESC LIMIT 1
		INSERT INTO temp_table VALUES (iValue, rDate, strName);
end loop;
end $$;
select * from temp_table;
ERROR:  ошибка синтаксиса (примерное положение: "AS")
LINE 13: ...cordtype = 'inner' then h.valdouble else NULL END AS dValue)...
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5279 является ответом на сообщение #5278] Mon, 22 July 2024 12:45 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
Теперь что-то новое получаю (как мог упростил запрос):
Drop table temp_table;
CREATE  TABLE temp_table (value integer, rdate timestamp, name text);
do $$
declare iCounter integer:= 1;
declare iIter integer:= 0;
declare iValue double precision:= NULL;
declare rDate timestamp:= NULL;
declare strName text:= NULL;
begin
  while iIter < (SELECT EXTRACT(DAY FROM TIMESTAMP '2024-07-19 00:00:00.00')) loop
    iIter = iIter + 1;
		SELECT iValue = case when h.recordtype = 'inner' then h.valdouble else NULL END AS dValue, rDate =  h.time, strName = n.tagname FROM nodes_history h JOIN nodes n ON n.NodeId = h.NodeId
		WHERE n.TagName = 'ASRMB.U_AVT12.FT0038.Out.Mt' AND (h.time BETWEEN ('2024-07-15')  AND ('2024-07-19')) AND h.recordtype != 'ubound' ORDER BY h.time DESC LIMIT 1;
		INSERT INTO temp_table VALUES (iValue, rDate, strName);
end loop;
end $$;
select * from temp_table;
ПРЕДУПРЕЖДЕНИЕ:  Обнаружен устаревший параметр конфигураци: 'aehistorianname'. Его поддержка будет прекращена в следующей версии!
ПРЕДУПРЕЖДЕНИЕ:  Обнаружен устаревший параметр конфигураци: 'historianname'. Его поддержка будет прекращена в следующей версии!
ПРЕДУПРЕЖДЕНИЕ:  Обнаружен устаревший параметр конфигураци: 'sourceae'. Его поддержка будет прекращена в следующей версии!
ПРЕДУПРЕЖДЕНИЕ:  Обнаружен устаревший параметр конфигураци: 'sourceda'. Его поддержка будет прекращена в следующей версии!
ERROR:  в запросе нет назначения для данных результата
HINT:  Если вам нужно отбросить результаты SELECT, используйте PERFORM.
CONTEXT:  функция PL/pgSQL inline_code_block, строка 10, оператор SQL-оператор 

ОШИБКА:  в запросе нет назначения для данных результата
SQL state: 42601
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5280 является ответом на сообщение #5279] Mon, 22 July 2024 13:39 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
declare strName text:= NULL;
begin

Вот этот begin, если я верно понял, не закрывается.
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5281 является ответом на сообщение #5280] Mon, 22 July 2024 15:32 Переход к предыдущему сообщениюПереход к следующему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
BlackEric писал(а) Mon, 22 July 2024 13:39
declare strName text:= NULL;
begin

Вот этот begin, если я верно понял, не закрывается.
разобрался, вот такая конструкция работает, но почему-то когда начинаю вводить переменную вместо жесткой даты материт

Drop table temp_table;
CREATE  TABLE temp_table (value integer, rdate timestamp, name text);
do $$
--declare iCounter integer:= 1;
declare iIter integer:= 0;
declare iValue double precision:= NULL;
declare reportDate timestamp:= '2024-07-19';


declare rDate timestamp:= NULL;
declare strName text:= NULL;
declare iDay int:= (SELECT EXTRACT(DAY FROM TIMESTAMP reportDate));
begin
  while iIter < iDay loop
    iIter = iIter + 1;
    SELECT  CASE WHEN h.recordtype = 'inner' THEN h.valdouble ELSE NULL END, h.time, n.tagname 
        INTO  iValue, rDate, strName
        FROM  nodes_history h 
        JOIN  nodes n ON n.NodeId = h.NodeId
        
    WHERE n.TagName = 'ASRMB.U_AVT12.FT0038.Out.Mt' AND (h.time BETWEEN ('2024-07-18')  AND ('2024-07-19')) AND h.recordtype != 'ubound' ORDER BY h.time DESC LIMIT 1;
    INSERT INTO temp_table VALUES (iIter, rDate, strName);
end loop;
end $$;
select * from temp_table ;
ERROR:  ошибка синтаксиса (примерное положение: "reportDate")
LINE 12: ...are iDay int:= (SELECT EXTRACT(DAY FROM TIMESTAMP reportDate...
                                                              ^ 

ОШИБКА:  ошибка синтаксиса (примерное положение: "reportDate")
SQL state: 42601
Character: 361
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5282 является ответом на сообщение #5281] Mon, 22 July 2024 15:43 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 333
Зарегистрирован: June 2022
Senior Member
здесь
Re: Выполнение запроса в цикле на уровне SQL запроса [сообщение #5283 является ответом на сообщение #5282] Mon, 22 July 2024 17:02 Переход к предыдущему сообщению
qwerty5000 в настоящее время не в онлайне  qwerty5000
Сообщений: 13
Зарегистрирован: July 2024
Junior Member
Спасибо!
Но уже по другому сделал.
Может кому пригодится:

declare iDay int:= (date_part('day', reportDate));
Предыдущая тема: Создать составной индекс по bigint и int[]
Следующая тема: Вычесть день из даты
Переход к форуму:
  


Текущее время: Fri Oct 04 06:27:07 GMT+3 2024

Общее время, затраченное на создание страницы: 0.01715 секунд