前端之家

  1. 首页
  2. 问答

SQL检查已发送消息的响应时间

• 问答

我正在尝试确定每个响应时间。信息。因此，每次SENDER向RECEIVER发送消息时，在RECEIVER做出响应之前经过了几秒钟。

我在此表中显示消息：

+------------------+----------+
|    Field         |   Type   |
+------------------+----------+
| id               | int      |
| at_date          | datetime |
| out              | bool     |  # outcoming message if true,incoming if false
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

我已经尝试过以下解决方案：https://stackoverflow.com/a/22297081/12324707

但是有一个问题。发件人可以发送两个或更多消息。

我当前的解决方法：

SELECT avg(responses.resp_time - responses.at_date)  AS avg_1
FROM (SELECT message_metrics_1.at_date      AS at_date,min(message_metrics_2.at_date) AS resp_time
      FROM message_metrics AS message_metrics_1
               JOIN message_metrics AS message_metrics_2
                    ON message_metrics_2.at_date >
                       message_metrics_1.at_date AND message_metrics_2.out AND
                       NOT message_metrics_1.out
      GROUP BY message_metrics_1.at_date) AS responses

也许我需要通过out字段将邮件分隔为“阻止”。

子查询的问题演示

数据：

date                       | out
---------------------------+-----
2019-11-05 07:07:58.000000 | false # ignore
2019-11-05 07:10:20.000000 | false # ignore
2019-11-05 07:10:27.000000 | false # ask
2019-11-05 07:10:36.000000 | true  # response
2019-11-05 06:05:13.000000 | false # ask
2019-11-05 06:05:42.000000 | true  # response

结果：

at_date ( ask )            | resp_time (response)
---------------------------+---------------------------
2019-11-05 06:05:13.000000 | 2019-11-05 06:05:42.000000
2019-11-05 07:07:58.000000 | 2019-11-05 07:10:36.000000
2019-11-05 07:10:20.000000 | 2019-11-05 07:10:36.000000
2019-11-05 07:10:27.000000 | 2019-11-05 07:10:36.000000

想要：

at_date ( ask )            | resp_time (response)
---------------------------+---------------------------
2019-11-05 07:10:27.000000 | 2019-11-05 07:10:36.000000
2019-11-05 06:05:13.000000 | 2019-11-05 06:05:42.000000

https://www.db-fiddle.com/f/paMQ99guouudPuAYtZPVyL/1

octer_liu 回答：SQL检查已发送消息的响应时间

如果我正确理解这一点，则需要第一个out = false，然后是下一个out = true的时间。

一种方法使用累积过滤的min()：

select *
from (select mm.*,min(at_date) filter (where out) over (order by at_date desc) as next_out,lag(out) over (order by at_date) as prev_out
      from message_metrics mm
     ) mm
where not out and (prev_out is null or prev_out)
order by at_date;

Here是db 小提琴。

postgresqlsql
本文链接：https://www.f2er.com/3161294.html

大家都在问