用户登录后立即发送消息的时间百分比是多少？ [英] What percent of the time does a user login, immediately followed by sending a message?

问题描述

我以前从未询问过这种事情，也不确定是否可能。假设我有下表：

I have never queried for such a thing before and not sure how possible it is. Let's say I have the following table:

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分钟内发送消息的时间百分比？

How can I calculate the percent of time a user logs in and within 2 minutes sends a message?

推荐答案

对于给定用户：

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;

user_id | pct_sent_in_time
------: | ---------------:
     22 |            33.33
     23 |           100.00

我扩展了测试用例，以使其更加显眼，因此百分比有所不同。请参阅：

db<>小提琴这里

I extended the test case to make it more revealing, hence a different percentage. See:
db<>fiddle here

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

Partition data after every new login, and check whether 'send_message' happens within less than 2 minutes. Then calculate percentage and round.

值得注意的是，这并没有被许多快速连续的登录所欺骗，随后我在不到2分钟的时间内输入了一条消息。

Notably, this is not fooled by many logins in quick succession, followed my a login with a message in under 2 minutes.

相关：

• 每小时每小时的汇总值汇总

• Aggregate values over a range of hours, every hour

在旁边：时间戳列的名称日期很容易引起误解。

Aside: The name "date" for a timestamp column is quite misleading.

这篇关于用户登录后立即发送消息的时间百分比是多少？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！