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