在 postgresql 中：如何创建一个可以重置的简单计数器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/367817/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In postgresql: How do I create a simple counter which I can reset?
提问by Mazen Harake
I want to achieve the following:
我想实现以下目标：
ID | Counter
------------
0 | 343
1 | 8344
Now say that I want to update counter for ID 1,,, what is the easiest way to do it? Do I use sequences? do I simply read the value and update? Is there any special type for it?
现在说我想更新 ID 1 的计数器，最简单的方法是什么？我使用序列吗？我只是简单地读取值并更新吗？它有什么特殊类型吗？
I was thinking about using sequence but then I have to create one for each ID (which potentially can be over a 1000. I will also face the problem that I don't know how many sequences I will need so I would have to check if there is a sequence for that ID and etc... and I don't want that.
我正在考虑使用序列，但后来我必须为每个 ID 创建一个（可能超过 1000。我还将面临一个问题，我不知道我需要多少序列，所以我必须检查是否该 ID 等有一个序列......我不想要那个。
Assume that the numbers are users belonging to a certain group, then an alternative I was thinking about was to enter a row for each count and when I want to get the number I perform a select group by the id or something and get the numbers of rows.
假设这些数字是属于某个组的用户，那么我正在考虑的另一种方法是为每个计数输入一行，当我想获得数字时，我通过 id 或其他东西执行选择组并获得数字行。
EDIT: Clarification I recieve a list of users in a csv that my program handles several times a day (new csv several times a day). Then depending on if the user has has sent a message today (for example) I increment the counter for the group in which this user belongs to. Now at a certain point I want to extract the groups (which can be dynamic, it depends on what I got during the day) and get the number I incremented and reset it. Hopefully this explains it more :D
编辑：澄清我收到一个 csv 中的用户列表，我的程序每天处理几次（每天几次新的 csv）。然后根据用户今天是否发送了消息（例如），我增加了该用户所属组的计数器。现在在某个时候我想提取组（这可以是动态的，这取决于我白天得到的）并获得我增加的数字并重置它。希望这能解释更多：D
Thanks for the help so far, I will experiment :D
到目前为止，感谢您的帮助，我将进行实验：D
What do you think?
你怎么认为？
回答by Frans Bouma
UPDATE Table SET Counter=Counter+1 WHERE ID=:ID;
UPDATE Table SET Counter=Counter+1 WHERE ID=:ID;
(where 'Table' is of course the table with the counter, and the parameter ':ID' is the id of the counter)
（其中'Table'当然是带计数器的表，参数':ID'是计数器的id）
Sequences are mainly used for auto-generating ID values sequentially and can have gaps. The update statement above is atomic, as update is an atomic action. However it's not said that if you issue a select right afterwards the counter is still the same value. If you want that, you need sequences but then you run the risk of having gaps.
序列主要用于按顺序自动生成 ID 值，并且可以有间隙。上面的更新语句是原子的，因为更新是一个原子操作。但是，并不是说如果您在之后立即发出选择，则计数器仍然是相同的值。如果你想要这样，你需要序列，但你会冒着出现间隙的风险。
So it might be necessary for answerers here to know what the purpose of the counters is.
所以这里的回答者可能有必要知道计数器的目的是什么。
回答by Jonathan Lonowski
This would seem simple enough.
这看起来很简单。
Increment
增量
UPDATE Table SET Counter = Counter + 1 WHERE ID = 1
Reset
重启
UPDATE Table SET Counter = 0 WHERE ID = 1
回答by annakata
It soundslike what you need is to create a viewexactly along the lines of:
这听起来像你需要的是创建一个视图正是沿着线：
select id, count(users) from foo group by id
select id, count(users) from foo group by id
