根据日期顺序进行排名 [英] Rank based on sequence of dates

问题描述

我有以下数据

**Heading    Date**
A          2009-02-01
B          2009-02-03
c          2009-02-05
d          2009-02-06
e          2009-02-08

我需要如下排名

Heading    Date           Rank
A          2009-02-01      1
B          2009-02-03      2
c          2009-02-05      1
d          2009-02-06      2
e          2009-02-07      3

因为我需要根据日期进行排名。如果日期是连续的，那么排名应该是1、2、3等。如果日期有任何中断，我需要从1、2开始... ...

As I need rank based on date. If the date is continuous the rank should be 1, 2, 3 etc. If there is any break on dates I need to start over with 1, 2, ...

有人可以帮我吗？

推荐答案

SELECT heading, thedate
      ,row_number() OVER (PARTITION BY grp ORDER BY thedate) AS rn
FROM  (
   SELECT *, thedate - (row_number() OVER (ORDER BY thedate))::int AS grp
   FROM   demo
   ) sub;

当您说等级时，您似乎想要窗口函数的结果 row_number（）

While you speak of "rank" you seem to want the result of the window function row_number().

1. 在子查询中形成连续几天的组（相同日期在 grp 中） code> sub 。


2. 用另外一个 row_number（）调用对行进行编号，这次是分区的通过 grp 。

1. Form groups of consecutive days (same date in grp) in subquery sub.
2. Number rows with another row_number() call, this time partitioned by grp.

在这里，一个子查询是最基本的，因为窗口函数不能嵌套。

One subquery is the bare minimum here, since window functions cannot be nested.

SQL Fiddle。

请注意，我使用了第二版的相互矛盾的示例数据。结果是在他的评论中 @mu建议。

另外假设没有重复的日期。在这种情况下，您必须先进行汇总。

Note that I went with the second version of your contradictory sample data. And the result is as @mu suggested in his comment.
Also assuming that there are no duplicate dates. You'd have to aggregate first in this case.

这篇关于根据日期顺序进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！