postgresql - 在行上生成第 n 次出现

Question

我希望为每位顾客光顾商店生成第 n 次光顾次数。

使用以下查询:

select customer_id, store_id, date from visits order by date asc;

我得到如图所示的表格:

customer_id | store_id | date
------------------------------------------
1           | 101      | 1st November 2018
2           | 102      | 2nd November 2018
2           | 102      | 3rd November 2018
3           | 103      | 1st November 2018
2           | 102      | 4th November 2018

我想做的是按日期升序排序以附加 nth_visits 列，例如:

customer_id | store_id | date              | nth_visit
-------------------------------------------------------
1           | 101      | 1st November 2018 | 1
2           | 102      | 2nd November 2018 | 1
2           | 102      | 3rd November 2018 | 2
3           | 103      | 1st November 2018 | 1
2           | 102      | 4th November 2018 | 3

有没有办法在 Postgresql 的输出中实现这一点？我尝试过嵌套选择和分组依据，但一切都变成了梨形。

我觉得可能有一个我可以增加的标志，但我找不到这样的东西:

select customer_id, store_id, date, occurance(customer_id, store_id) as nth_visit from visits order by date asc;

Answer 1

您可以使用 window function为此:

select customer_id, 
       store_id, 
       "date", 
       row_Number() over (partition by customer_id, store_id order by "date") as nth_visit 
from visits 
order by "date" asc;