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, 8It 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 byselect 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 по дате отправления: