postgres如何在值改变时递增计数器

3okqufwl 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(317)

我有一张按时间和地点分类的动物位置表。当位置从一个id变为下一个id时，我想增加一个计数器。所以动物的每个“停留”都应该得到一个唯一的id（计数器）。我有一个非sql描述：

counter = 0
CASE (location at ID = location at ID-1) SET counter+1

我尝试使用dense_rank（），但未能正确设置分区。这是一个带有所需计数器的样品。

ID Location Counter
1    3          1
2    3          1
3    2          2
4    2          2
5    3          3 
6    1          4 
7    3          5 
8    3          5 
9    3          5

CREATE TABLE locations (idn serial PRIMARY KEY, location integer);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (2);
INSERT INTO locations (location ) VALUES (2);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (1);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (3);
INSERT INTO locations (location ) VALUES (3);

sql postgresql Increment

来源：https://stackoverflow.com/questions/62018906/postgres-how-to-increment-counter-only-if-value-changes

1条答案

按热度按时间

nfeuvbwi1#

你可以试试lag函数。
下面是一个适用于大型sql的示例：

select id, location,
    sum(case when previousVal = location then 0 else 1 end) over(order by id) as Counter
from (
    select Id, location, lag(location, 1) over(order by Id) as previousVal
    from temp.locations
) tbl
order by Id;

输出：

ID LOCATION COUNTER1
1       3       1
2       3       1
3       2       2
4       2       2
5       3       3
6       1       4
7       3       5
8       3       5
9       3       5

赞(0）回复(0）举报 2021-07-26

我来回答

postgres如何在值改变时递增计数器

1条答案

相关问题

热门标签

最新问答