Postgres percent_rank() across rows not column

user_9010692 0
Viewed 156

Data:

I have a postgres table where each row contain a question_id, and count values of when a user pressed a button.

 +----------+-----------+------+-----+----------+
| Question | Very Good | Good | Bad | Very Bad |
+----------+-----------+------+-----+----------+
| 1        | 23        | 12   | 23  | 67       |
+----------+-----------+------+-----+----------+
| 2        | 56        | 90   | 23  | 18       |
+----------+-----------+------+-----+----------+

Requirement:

I want to be able to convert each row value in a percentage of the total row.

+----------+-----------+-------+-------+----------+
| Question | Very Good | Good  | Bad   | Very Bad |
+----------+-----------+-------+-------+----------+
| 1        | 18.4      | 9.6   | 18.4  | 53.8     |
+----------+-----------+-------+-------+----------+
| 2        | 29.94     | 48.12 | 12.29 | 9.6      |
+----------+-----------+-------+-------+----------+

Attempt: I found that percent_rank() will show me the percentage based off columns, I'm wondering is there a similar function which works row wise?

    SELECT 
question_id,
PERCENT_RANK() OVER (
        ORDER BY Very_good
    ),
PERCENT_RANK() OVER (
        ORDER BY Good
    )
PERCENT_RANK() OVER (
        ORDER BY Bad
    )
PERCENT_RANK() OVER (
        ORDER BY Very Bad
    )
FROM Question_feedback
postgresql
2 Answers

I'm afraid the only thing that will work is to do this manually:

SELECT 
question_id,
Very_good::double precision / (Very_good + Good + Bad + Very_bad),
Good::double precision      / (Very_good + Good + Bad + Very_bad),
Bad::double precision       / (Very_good + Good + Bad + Very_bad),
Very_bad::double precision  / (Very_good + Good + Bad + Very_bad)
FROM Question_feedback

The good news is it will be faster than PERCENT_RANK because it only needs to consider that row, which is much cheaper.

user_50153560

Working Solution

WITH QUESTION_FEEDBACK
     AS (SELECT 1 AS QUESTION,
                23 VERYGOOD,
                12 GOOD,
                23 BAD,
                67 VERYBAD
         UNION ALL
         SELECT 2 AS QUESTION,
                56 VERYGOOD,
                90 GOOD,
                23 BAD,
                18 VERYBAD
           )
SELECT QUESTION,
       VERYGOOD,
       GOOD,
       BAD,
       VERYBAD,
       (CAST(VERYGOOD  AS DECIMAL) / SUM (VERYGOOD + GOOD + BAD + VERYBAD) OVER (PARTITION BY QUESTION))*100 VERYGOODPER,
       (CAST(GOOD AS DECIMAL)   / SUM (VERYGOOD + GOOD + BAD + VERYBAD) OVER (PARTITION BY QUESTION))*100 GOODPER,
       (CAST(BAD AS DECIMAL)  / SUM (VERYGOOD + GOOD + BAD + VERYBAD) OVER (PARTITION BY QUESTION) )*100 BADPER,
       (CAST(VERYBAD AS DECIMAL)  / SUM (VERYGOOD + GOOD + BAD + VERYBAD) OVER (PARTITION BY QUESTION))*100 VERYBADPER      
  FROM QUESTION_FEEDBACK
user_47013890
Related