Postgres Find % value of field against different row in result set-CodePudding

I have a table that contains a numeric value for the # of times an action has happened, an a key to describe that action and the time that action occurred.

Table: action
Id | Data | Key | timestamp 
1 | 10 | 'accepted' | '2022-01-16 19:00:00.000 -0500'
1 | 5  | 'rejected' | '2022-01-16 19:00:00.000 -0500'
1 | 22 | 'accepted' | '2022-01-17 19:00:00.000 -0500'
1 | 3  | 'rejected' | '2022-01-17 19:00:00.000 -0500'

I'd like to query against that an find the % of a particular action (in this case we'll say accepted), against the rejected actions on the same timestamp.

So in the above case I'd ideally like to take get a result of

accepted | 2022-01-16 | 66% (10 / 15)
accepted | 2022-01-17 | 88% (22 / 25)

How would I construct the query to do the division on 1 row against the total from multiple rows?

CodePudding user response：

You can use conditional aggregation for the total of the rejected.
And divide that by the total of both.

SELECT
  MIN(Key) AS Key
, CAST(a.timestamp AS DATE) AS "Date" 
, CONCAT(100*SUM(CASE WHEN a.Key = 'rejected' THEN a.Data ELSE 0 END) / NULLIF(SUM(a.Data),0), '%') AS PercentageRejected
FROM action a
WHERE a.Id = 1
GROUP BY Id, CAST(a.timestamp AS DATE) 
ORDER BY Id, CAST(a.timestamp AS DATE);

CodePudding user response：

select  id,
        key, 
        date(t_timestamp), 
        concat((data*100/sum(data) over w),'%') as accept_rate
from action
WINDOW w as 
    (partition by t_timestamp, id order by id)
order by id asc, date asc;

windows function manual link: https://www.postgresql.org/docs/current/tutorial-window.html.
concat string fucntion link: https://www.postgresql.org/docs/current/functions-string.html

You may want to change column name timestamp to something else, since SQL-standard all set timestamp as reserved, even though it's not reserved in PostgreSQL.
https://www.postgresql.org/docs/current/sql-keywords-appendix.html