我的表格:
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;我的问题是:
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秒,因此我需要这个数字。
发布于 2020-02-12 20:37:53
如果我理解正确的话,您可以使用ntile()或类似的函数:
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;https://stackoverflow.com/questions/60185092
复制相似问题