首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Postgresql计算前n%行的值

问Postgresql计算前n%行的值
EN

Stack Overflow用户
提问于 2020-02-12 17:31:18
回答 1查看 98关注 0票数 0

我的表格:

代码语言:javascript
复制
CREATE TABLE etp_msg_log
(
  id bigint NOT NULL,
  sent_time timestamp with time zone,
  received_time timestamp with time zone,
  doctype integer,
  CONSTRAINT etp_msg_log_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE etp_msg_log
  OWNER TO postgres;

我的问题是:

代码语言:javascript
复制
select
to_char(dt, 'dd.MM.yyyy') as "Date",
round(100.0*sum(case when len is null and t.doctype= 260 then 1 else 0 end)/SUM(CASE WHEN t.doctype=260 THEN 1 ELSE 0 END)) as "% for 260",
round(100.0*sum(case when len is null and t.doctype= 980 then 1 else 0 end)/SUM(CASE WHEN t.doctype=980 THEN 1 ELSE 0 END)) as "% for 980",
to_char(avg(len), 'HH24:MI:SS.MS') as "Avg answer time"
from (
     select date_trunc('day',sent_time) dt, received_time-sent_time len, doctype
     from etp_msg_log
 ) t
group by dt
order by dt;

我的问题是如何在这个查询中获取时间(received_ time -sent_time) (以秒为单位),这包括对日期和doctype(对于260或980)的所有请求的90%。例如,2020年2月12日有10个请求,其中9个请求的响应时间小于12秒,因此我需要这个数字。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-02-12 20:37:53

如果我理解正确的话,您可以使用ntile()或类似的函数:

代码语言:javascript
复制
select to_char(dt, 'dd.MM.yyyy') as "Date",
       round(100.0*sum(case when len is null and t.doctype = 260 then 1 else 0 end)/SUM(CASE WHEN t.doctype = 260 THEN 1 ELSE 0 END)) as "% for 260",
       round(100.0*sum(case when len is null and t.doctype = 980 then 1 else 0 end)/SUM(CASE WHEN t.doctype = 980 THEN 1 ELSE 0 END)) as "% for 980",
       to_char(avg(len), 'HH24:MI:SS.MS') as "Avg answer time",
       min(len) filter (where tiling = 10)
from (select date_trunc('day', sent_time) as dt, received_time-sent_time as len, doctype,
             ntile(10) over (partition by date_trunc('day', sent_time), doctype order by received_time-sent_time) as tiling
      from etp_msg_log
     ) t
group by dt
order by dt;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/60185092

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档