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.