Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / нарастающий итог / 11 сообщений из 11, страница 1 из 1
18.04.2007, 15:52
    #34470187
Вадим Прудивус
Вадим Прудивус
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Подскажите, пожалуйста, эффективный алгоритм расчета нарастающего итога средствами PostgreSQL.

Пусть дана таблица:
Код: plaintext
1.
2.
3.
4.
5.
6.
create table testtable (id integer, amount dec( 15 , 2 ))
insert into testtable (id, amount) values ( 1 ,  12 . 10 )
insert into testtable (id, amount) values ( 2 ,   2 . 15 )
insert into testtable (id, amount) values ( 3 ,   1 . 23 )
insert into testtable (id, amount) values ( 4 ,  22 . 05 )
insert into testtable (id, amount) values ( 5 ,  42 . 84 )
Нужно получить на выходе эту же таблицу с добавленным полем, значение которого равно сумме по полю "amount" предыдущих записей + текущая (сортировка по id).

Вот "нормальный", классический SQL:

Код: plaintext
1.
2.
3.
4.
5.
select t1.id, t1.amount, sum(t2.amount) as sum_amount
 from testtable t1, testtable t2
 where t1.id >= t2.id
 group by t1.id, t1.amount
 order by t1.id, t1.amount

На больших объемах такой код будет жутко тормозить...

На MSSQL можно применить трюк с хранением промежуточного результата в переменной:
Код: plaintext
1.
2.
3.
4.
create table testtable (id integer, amount dec( 15 , 2 ), sum_amount dec( 15 , 2 ))
---
declare @t
set @t =  0 
update testtable set @t = @t + amount, sum_amount = @t

Подобное не проходит в PG. Есть ли какая-то возможность ускорить этот расчет средствами PG?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2007, 16:54
    #34470444
СергейК
СергейК
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Вадим ПрудивусПодскажите, пожалуйста, эффективный алгоритм расчета нарастающего итога средствами PostgreSQL.

Вот "нормальный", классический SQL:

Код: plaintext
1.
2.
3.
4.
5.
select t1.id, t1.amount, sum(t2.amount) as sum_amount
 from testtable t1, testtable t2
 where t1.id >= t2.id
 group by t1.id, t1.amount
 order by t1.id, t1.amount

На больших объемах такой код будет жутко тормозить...

Подобное не проходит в PG. Есть ли какая-то возможность ускорить этот расчет средствами PG?

Mojno PL/PgSQL'koi sdelat' -- vpolne bystro poluchaetsia.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE OR REPLACE FUNCTION func (OUT idd integer, out aamount dec( 15 , 2 ), out summ dec( 15 , 2 )) RETURNS SETOF RECORD AS '
DECLARE
zz record;
BEGIN
summ=0;
FOR zz IN SELECT id,amount from testtable ORDER BY id LOOP
   summ = summ+zz.amount;
   idd=zz.id;
   aamount=zz.amount;
   RETURN NEXT;
END LOOP;
RETURN;
END'
language plpgsql;

SELECT * from func();
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.04.2007, 19:20
    #34470990
domanix
domanix
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
"трюк" с хранением промежуточного результата в переменной работает и PG..
Причем PG позволяет для этого воспользоваться несколькими путями.
Я покажу только один из них - наиболее родной для PG.

В настройках файла postgresql.conf - находим секцию
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------

в ней прописываем
custom_variable_classes = 'myVarClass'

После чего в любом месте мы можем создавать любые переменные в рамках этого класса и их использовать.
Через функции 
select set_config('myVarClass.x', 100 ,false) - присвоить переменной x из класса myVarClass( или создать переменную x и ей присвоить значение)  '100'

select current_config('myVarClass.x') - получить значение из переменной...
Согласен- запись немного длинновата..
Да и хранится в переменной только текст НО:
Вот как в итоге будет выглядеть запрос:
Т.е. заявленный результат получается за один проход по индексу(ID - если он есть)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT set_config('myVarClass.amount', 0 ,false);

select t1.id, t1.amount,
    set_config('myVarClass.amount',
        current_setting('myVarClass.amount'')::dec( 15 , 2 )+t2.amount,false) as sum_amount
 from testtable t1, testtable t2
   where t1.id = t2.id
 order by t1.id

Второй вариант использование возможностей подключаемых языков - Python,Perl,Tcl,Java..
Но по моему достаточно и вышеизложенного варианта..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2007, 04:37
    #34471428
Бабичев Сергей
Бабичев Сергей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Вадим ПрудивусВот "нормальный", классический SQL:
На больших объемах такой код будет жутко тормозить...Сделай вместо SELF-JOIN-а коррелированный скалярный подзапрос в секции селект.
Серверу легче станет...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2007, 11:30
    #34472065
Вадим Прудивус
Вадим Прудивус
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Спасибо всем ответившим. Попробую сравнить скорость выполнения первых двух вариантов.

авторСделай вместо SELF-JOIN-а коррелированный скалярный подзапрос в секции селект.
Вот так?
Код: plaintext
1.
2.
3.
select t1.id, t1.amount,
  (select sum(t2.amount) from testtable t2 where t1.id >= t2.id) as sum_amount
 from testtable t1
 order by t1.id, t1.amount

Это вроде как то же самое, что и в первом варианте. Или в PG оптимизатор подготовит его как-то иначе чем первый вариант?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.04.2007, 13:14
    #34472557
domanix
domanix
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Вариант с накоплением результата в переменной однозначно будет быстрее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2007, 10:31
    #34475079
Paramedic
Paramedic
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Вариант с переменной рулит!!! Но вот такой вопрос: почему функции current_setting и set_config находятся в секции System Administration Functions?

Не вредно ли использовать функции системного администрирования для подобных целей?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.04.2007, 11:53
    #34475446
domanix
domanix
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Потому, что эти функции предназначены для установки и считывания конфигурационных значений.
По большому счету конечно, конечному юзеру давать доступ к этим функциям грешно - ибо в неумелых руках они могут оказаться дубиной с которой можно поломать всю систему.
В принципе можно расположить эти вызовы внутри специальных функций ( типа GET и SET) и ограничить доступ к телу функций из вне дав им соответсвующие права..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.04.2007, 12:24
    #34489282
Вадим Прудивус
Вадим Прудивус
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Вариант с циклом, как самый простой и понятный/предсказуемый, оказался и достаточно быстрым. Похоже что придется пересмотреть некоторые подходы (по сравнению с MS SQL, где циклы серьезно тормозят).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.04.2007, 07:43
    #34494384
glebofff
glebofff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
Может, проще это делать во время вставки?

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE TABLE test.increment
(
  id serial NOT NULL,
  amount numeric( 15 , 2 ) NOT NULL DEFAULT  0 ,
  total numeric( 15 , 2 ) NOT NULL DEFAULT  0 ,
  CONSTRAINT increment_pkey PRIMARY KEY (id)
) 
WITHOUT OIDS;

CREATE OR REPLACE FUNCTION test.increment_total()
RETURNS "trigger" AS
$BODY$
begin 
  NEW.total = coalesce((select total from test.increment order by id desc limit  1 ), 0 ) + NEW.amount;
  return NEW;
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;

CREATE TRIGGER increment_before
  BEFORE INSERT
  ON test.increment
  FOR EACH ROW
  EXECUTE PROCEDURE test.increment_total();

Что касается переменных сессии: тынц.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.04.2007, 12:59
    #34495227
Вадим Прудивус
Вадим Прудивус
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нарастающий итог
авторМожет, проще это делать во время вставки?
Скорости это наверняка не прибавит. Задача расчета нарастающего итога вспомогательная, хранить так данные не нужно. Цикл справляется с ней хорошо, и, похоже, сам способ расчета будет изменен, необходимость вычисления нарастающего итога отпадает (это была вынужденная мера для ускорения работы логики в MS SQL).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / нарастающий итог / 11 сообщений из 11, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]