Postgresql - 在列中有重复值的行中增加计数器
问题描述:
我已将一列(seq)添加到用于调度的表中,因此前端可以管理每个项目可以显示的顺序。是否可以创建一个SQL查询,以基于日期列中常见重复值的增量计数器填充此列?Postgresql - 在列中有重复值的行中增加计数器
之前
------------------------------------
| name | date_time | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 | |
| ABC2 | 16-01-2017 11:30:00 | |
| ABC1 | 16-01-2017 11:30:00 | |
| ABC3 | 17-01-2017 10:00:00 | |
| ABC3 | 18-01-2017 12:30:00 | |
| ABC4 | 18-01-2017 12:30:00 | |
| ABC1 | 18-01-2017 12:30:00 | |
------------------------------------
后
------------------------------------
| name | date_time | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 | 0 |
| ABC2 | 16-01-2017 11:30:00 | 0 |
| ABC1 | 16-01-2017 11:30:00 | 1 |
| ABC3 | 17-01-2017 10:00:00 | 0 |
| ABC3 | 18-01-2017 12:30:00 | 0 |
| ABC4 | 18-01-2017 12:30:00 | 1 |
| ABC1 | 18-01-2017 12:30:00 | 2 |
------------------------------------
解决了,感谢两个答案。 为了使它更容易为任何人谁发现这一点,工作代码为:
UPDATE my_table f
SET seq = seq2
FROM (
SELECT ctid, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY ctid) -1 AS seq2
FROM my_table
) s
WHERE f.ctid = s.ctid;
答
使用window functionrow_number()
:
with my_table (name, date_time) as (
values
('ABC1', '15-01-2017 11:00:00'),
('ABC2', '16-01-2017 11:30:00'),
('ABC1', '16-01-2017 11:30:00'),
('ABC3', '17-01-2017 10:00:00'),
('ABC3', '18-01-2017 12:30:00'),
('ABC4', '18-01-2017 12:30:00'),
('ABC1', '18-01-2017 12:30:00')
)
select *,
row_number() over (partition by name order by date_time)- 1 as seq
from my_table
order by date_time;
name | date_time | seq
------+---------------------+-----
ABC1 | 15-01-2017 11:00:00 | 0
ABC1 | 16-01-2017 11:30:00 | 1
ABC2 | 16-01-2017 11:30:00 | 0
ABC3 | 17-01-2017 10:00:00 | 0
ABC1 | 18-01-2017 12:30:00 | 2
ABC3 | 18-01-2017 12:30:00 | 1
ABC4 | 18-01-2017 12:30:00 | 0
(7 rows)
对类似的问题有关更新现有记录有唯一的整数读this answer。
答
退房ROW_NUMBER()。
SELECT name, date_time, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY name) FROM [table]
这提供了数字,我怎么用这些数字更新表格? – corky