Re: proposal: window function - change_number

Поиск
Список
Период
Сортировка
От David Rowley
Тема Re: proposal: window function - change_number
Дата
Msg-id CAApHDvqtofthzwkMP_B9YAD+mswGzXr+52e-gqPVXdodzehx6Q@mail.gmail.com
обсуждение исходный текст
Ответ на proposal: window function - change_number  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-hackers
On Sun, Sep 21, 2014 at 9:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi
I tried to solve following task:

I have a table

start, reason, km
=============
 2014-01-01 08:00:00, private, 10
 2014-01-01 09:00:00, commerc, 20
 2014-01-01 10:00:00, commerc, 20
 2014-01-01 11:00:00, private, 8

and I would reduce these rows to

 2014-01-01 08:00:00, private, 10
 2014-01-01 09:00:00, commerc, 20 + 20 = 40
 2014-01-01 11:00:00, private, 8

It is relative hard to it now with SQL only. But we can simplify this task with window function that returns number of change in some column. Then this task can be solved by

select min(start), min(reason), sum(km)
  from (select start, reason, km, change_number(reason) over (order by start))
  group by change_number;


I guess that might be quite useful, otherwise the only way that comes to mind to do this would be something along the lines of:

select *,sum(case when reason <> lastreason then 1 else 0 end) over (order by start) as chg_num from (select *,lag(reason) over (order by start) vnext from sometable) sometable;

This way might not be too bad as I think the outer window will have no need to perform another sort, since the inner window clause has sorted it the right way already. Though something like change_number() would make this a bit more pretty. It's almost like rank(), but with a parameter.

Regards

David Rowley

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: proposal: window function - change_number
Следующее
От: Michael Paquier
Дата:
Сообщение: Re: Help to startup