Postgresql 编号行组

Question

我有下一个数据表:

  id    date               another_info   
  1     2014-02-01         lala
  1     2014-03-11         ajskj
  1     2014-05-13         kgfd
  2     2014-02-01         SADA
  3     2014-02-01         sfdg
  3     2014-06-12         fdsA
  4     2015-01-03         lilo
  4     2015-01-07         kjlk

我想附加“sequence_number”列:

  id    date           sequance_number    another_info
  1     2014-02-01          1              lala
  1     2014-03-11          2              ajskj
  1     2014-05-13          3              kgfd
  2     2014-02-01          1              SADA
  3     2014-02-01          1              sfdg
  3     2014-06-12          2              fdsA
  4     2015-01-03          1              lilo
  4     2015-01-07          2              kjlk

对于每个 id，我计算行。表格应该按id、日期排序

Answer 1

使用 window function .

select id,"date",another_info, row_number() OVER (ORDER BY id,"date") as sequence_number
from data_table

[编辑] 没有意识到您需要按 id 进行分区。

SELECT id,"date",another_info, 
row_number() OVER (PARTITION BY id ORDER BY "date") as sequence_number
    FROM data_table