Postgresql count the value for top n % rows

Question

My table:

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;

My query:

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;

My question is how to get in this query also time (received_time-sent_time) in seconds, which includes 90% of all requests for a date and doctype(for 260 or 980). For example 10 requests in 12.02.2020, response time for 9 of them is less than 12s, so i need this number.

It appears that what you're looking for is the 90% percentile response time. Postgres supports the PERCENTILE window function, so I'd check that out. https://stackoverflow.com/questions/14316562/nth-percentile-calculations-in-postgresql — Simon Notley, Feb 12 '20 at 09:57
Does this answer your question: https://stackoverflow.com/questions/24626036/postgresql-how-do-i-select-top-n-percent-entries-from-each-group-category — VBoka, Feb 12 '20 at 10:27
I know how to separately calculate what I need. the question is how to insert this into my query — Сергей, Feb 12 '20 at 10:37

Gordon Linoff · Accepted Answer · 2020-02-12T13:24:24.590

0

If I understand correctly, you can use ntile() or a similar function:

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;

edited Feb 12 '20 at 13:24

answered Feb 12 '20 at 12:37

Gordon Linoff

1,242,037
58
646
786

I don’t quite understand how this should work. what argument does the function ntile(?) have? – Сергей Feb 12 '20 at 13:02
@Сергей . . . In this case, "10". I fixed the answer. – Gordon Linoff Feb 12 '20 at 13:24
With ntile(10) the query returns null for almost all days, which cannot be according to my data. Something wrong – Сергей Feb 12 '20 at 13:35
@Сергей . . . This could happen if you have `null` values in the data. – Gordon Linoff Feb 12 '20 at 14:00
I know, but i need min(len) only for complete requests(received_time not null) – Сергей Feb 12 '20 at 14:48
@Сергей . . . Can you just filter them out of the subquery? – Gordon Linoff Feb 12 '20 at 15:29

Postgresql count the value for top n % rows

1 Answers1