Re: proposal: window function - change_number

2014-09-21 14:30 GMT+02:00 Mart Kelder <mart(at)kelder31(dot)nl>:

> 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);
>

This query is Cartesian product, so for some large data it is significantly
slower then window function (required only sorts without joins)

My motivation was a) to implement described task without Cartesian product.
b) introduce some tool for this kind of problems. I seen more times a
request .. reduce a time series, and a window function "change_number" (or
maybe "consistent_series_number") can be good candidate.

>
> 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.
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>