sql - 用户登录后立即发送消息的时间百分比是多少？

Question

我以前从未询问过这样的事情，也不知道这有多大可能。假设我有下表：

user_id   date              event
22      2012-05-02 11:02:39 login
22      2012-05-02 11:02:53 send_message
22      2012-05-02 11:03:28 logout
22      2012-05-02 11:04:09 login
22      2012-05-02 11:03:16 send_message
22      2012-05-02 11:03:43 search_run

如何计算用户登录并在 2 分钟内发送消息的时间百分比？

标签： sqlpostgresqlwindow-functions

Answer 1

对于给定的用户：

SELECT round(count(*) FILTER (WHERE sent_in_time) * 100.0 / count(*), 2) AS pct_sent_in_time
FROM  (
   SELECT (min(date) FILTER (WHERE event = 'send_message')
         - min(date)) < interval '2 min' AS sent_in_time
   FROM  (
      SELECT date, event
           , count(*) FILTER (WHERE event = 'login')
                      OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
      FROM   tbl
      WHERE  user_id = 22  -- given user
      ) sub1
   GROUP  BY grp
   ) sub2;

| pct_sent_in_time |
| ---------------: |
| 50.00 |

对于所有用户：

SELECT user_id
     , round(count(*) FILTER (WHERE sent_in_time) * 100.0 / count(*), 2) AS pct_sent_in_time
FROM  (
   SELECT user_id
        , (min(date) FILTER (WHERE event = 'send_message')
         - min(date)) < interval '2 min' AS sent_in_time
   FROM  (
      SELECT user_id, date, event
           , count(*) FILTER (WHERE event = 'login')
                      OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
      FROM   tbl
      ) sub1
   GROUP  BY user_id, grp
   ) sub2
GROUP  BY user_id;

用户 ID | pct_sent_in_time
------: | ---------------：
     22 | 33.33
     23 | 100.00

我扩展了测试用例以使其更具启发性，因此百分比不同。见：
db<>fiddle here

每次新登录后分区数据，并检查 'send_message' 是否在不到 2 分钟内发生。然后计算百分比和四舍五入。

值得注意的是，这不会被许多连续快速登录所迷惑，在我登录后不到 2 分钟就收到一条消息。

有关的：

• 每小时汇总一个小时范围内的值

另外：时间戳列的名称“日期”非常具有误导性。