Обсуждение: BUG #9010: partition by overrides order by in window functions

The following bug has been logged on the website:

Bug reference:      9010
Logged by:          Markella Skempri
Email address:      markella.skempri@onzo.com
PostgreSQL version: 9.3.2
Operating system:   Linux Centos 5.5
Description:

I am trying to get a row_number / rank of a resultset according to a value
that is ordered by date. However whenever I try to use the order by clause,
the partition by clause seems to override the ordering and produce false row
number.

My data:
householdid   previous_day  gap_finish  no_of_gap_days
1    2011-08-15    2011-08-16    1
1    2011-08-16    2011-08-17    1
1    2011-08-17    2011-08-18    1
1    2011-08-18    2011-08-19    1
1    2011-08-19    2011-08-20    1
1    2011-08-20    2011-08-21    1
1    2011-08-21    2011-08-27    6
1    2011-08-27    2011-08-28    1
1    2011-08-28    2011-08-29    1
1    2011-08-29    2011-08-30    1


my query:

select *, row_number() over (partition by no_of_gap_days order by gap_finish
asc) as no_of_Days from temptable;

the results:
householdid   previous_day  gap_finish  no_of_gap_days  no_of_days
1    2011-08-15    2011-08-16    1    1
1    2011-08-16    2011-08-17    1    2
1    2011-08-17    2011-08-18    1    3
1    2011-08-18    2011-08-19    1    4
1    2011-08-19    2011-08-20    1    5
1    2011-08-20    2011-08-21    1    6
1    2011-08-27    2011-08-28    1    7
1    2011-08-28    2011-08-29    1    8
1    2011-08-29    2011-08-30    1    9
1    2011-08-21    2011-08-27    6    1

What I expect to see
householdid   previous_day  gap_finish  no_of_gap_days  no_of_days
1    2011-08-15    2011-08-16    1    1
1    2011-08-16    2011-08-17    1    2
1    2011-08-17    2011-08-18    1    3
1    2011-08-18    2011-08-19    1    4
1    2011-08-19    2011-08-20    1    5
1    2011-08-20    2011-08-21    1    6
1    2011-08-27    2011-08-28    1    7
1    2011-08-21    2011-08-27    6    1
1    2011-08-28    2011-08-29    1    1
1    2011-08-29    2011-08-30    1    2

Thanks for your time

markella.skempri@onzo.com writes:
> I am trying to get a row_number / rank of a resultset according to a value
> that is ordered by date. However whenever I try to use the order by clause,
> the partition by clause seems to override the ordering and produce false row
> number.

AFAICS, the behavior you're complaining about is exactly what is specified
by the SQL standard.  For one thing, since you've not specified any ORDER
BY at the outer query level, there's no requirement for the rows to be
returned in any particular order.  But the main point is that the
row_number is supposed to be computed within each set of rows having
a distinct value of no_of_gap_days, which the actual output satisfies,
and your wish-list result doesn't.  So I'm pretty sure that you need
to write something else than this in order to get the result you want.

> What I expect to see
> householdid   previous_day  gap_finish  no_of_gap_days  no_of_days
> 1    2011-08-15    2011-08-16    1    1
> 1    2011-08-16    2011-08-17    1    2
> 1    2011-08-17    2011-08-18    1    3
> 1    2011-08-18    2011-08-19    1    4
> 1    2011-08-19    2011-08-20    1    5
> 1    2011-08-20    2011-08-21    1    6
> 1    2011-08-27    2011-08-28    1    7
> 1    2011-08-21    2011-08-27    6    1
> 1    2011-08-28    2011-08-29    1    1
> 1    2011-08-29    2011-08-30    1    2

Unfortunately, I can't help further, because it's not obvious to me what
calculation rule you are wishing for.  I could understand your example
if it was ordered by date, but it isn't.

            regards, tom lane

markella.skempri wrote
> 1    2011-08-20    2011-08-21    1
> 1    2011-08-21    2011-08-27    6
> 1    2011-08-27    2011-08-28    1
> 1    2011-08-28    2011-08-29    1
> 1    2011-08-29    2011-08-30    1
>
> 1    2011-08-20    2011-08-21    1
> 1    2011-08-27    2011-08-28    1    7
> 1    2011-08-21    2011-08-27    6    1
> 1    2011-08-28    2011-08-29    1    1
> 1    2011-08-29    2011-08-30    1    2

Yes, partitioning occurs before ordering.  The syntax itself suggests that.

Can you explain why you expect the 27-28 row to belong to the "first" group
and not the "third"?

If that is a typo then you are looking for contiguous blocks with the same
gap-size.  Once you've identified those blocks you can then partition on
them and performing your row_number.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-9010-partition-by-overrides-order-by-in-window-functions-tp5789343p5789377.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.