postgresql 如何在postgres中只计算发生多次的事件?

rta7y2nd  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(133)

我有一个用户数据集和他们对订阅采取的各种操作。我想计算第二次或更多次“购买”的用户数量。例如:
| 用户标识|创建_ts|行动|
| --|--|--|
| 123 |2023年10月1日|购买|
| 123 |2023年10月2日|购买|
| 789 |2023年10月1日|购买|
预期成果:
| 用户标识|创建_ts|计数|
| --|--|--|
| 123 |2023年10月2日| 1 |
我只想计算购买时,它发生了一次以上的第二天或更多的购买发生。
现在,我有下面的查询,但它是不完全正确的,因为它不排除第一次购买。提前感谢您的任何帮助!

select
    created_ts::date as date_day
    , count(distinct user_id) as reactivations
from user_subscription_history
where user_id in
    (select 
         user_id 
     from user_subscription_history where notification_type = 'Purchase'
     group by user_id
     having count(*) > 1)
group by 1

字符串

7y4bm7vi

7y4bm7vi1#

你可以使用ROW_NUMBER()窗口函数来实现:

Schema(PostgreSQL v15)

CREATE TABLE user_subscription_history (
  "user_id" INTEGER,
  "created_ts" TIMESTAMP,
  "action" VARCHAR(8)
);

INSERT INTO user_subscription_history
  ("user_id", "created_ts", "action")
VALUES
  ('123', '10/1/2023', 'purchase'),
  ('123', '10/2/2023', 'purchase'),
  ('789', '10/1/2023', 'purchase');

字符串

查询#1

WITH numbered_subscriptions AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_ts)
  FROM user_subscription_history
  WHERE action = 'purchase'
)
SELECT created_ts::date as date_day
     , count(distinct user_id) as reactivations
FROM numbered_subscriptions
WHERE row_number > 1
GROUP BY 1;


| date_day|再活化|
| --|--|
| 2023-10-02T00:00:00.000Z| 1 |
View on DB Fiddle

相关问题