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.