Re: proposal: window function - change_number

Hi Pavel (and others),

Pavel Stehule 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;

What about

select srk.reason, min(srk.start), sum(srk.km)
from start_reason_km srk
group by srk.reason, (select max(start) from start_reason_km other WHERE
other.start < srk.start and other.reason != srk.reason);

In general, I think window function are very specific in how the queryplan
must look like, leaving not much room for the optimizer. On the other hand,
if there happends to be an efficient way to get the results of the table
ordered by "start", then the window function will very likely much faster
then a join. I would be nice if the optimizer is able to add such stream
order operations.

> Do you think, so it has sense?
>
> Regards
>
> Pavel

Regards,

Mart

PS: This is my first post to the mailing list. I am a software developer
interest is performance making webapplications with a different database
server during working hours.