sql - 如何知道两个计数值的算术平均值

Question

我有存储信息的表 answers。

| EMPLOYEE | QUESTION_ID | QUESTION_TEXT          | SELECTED_OPTION_ID | SELECTED_OPTION_TEXT |
|----------|-------------|------------------------|--------------------|----------------------|
| Mark     | 1           | Do you like soup?      | 1                  | Yes                  |
| Kate     | 1           | Do you like soup?      | 1                  | Yes                  |
| Jone     | 1           | Do you like soup?      | 2                  | No                   |
| Kim      | 1           | Do you like soup?      | 3                  | I don't know         |
| Alex     | 1           | Do you like soup?      | 2                  | No                   |
| Bond     | 1           | Do you like soup?      | 1                  | Yes                  |
| Ford     | 1           | Do you like soup?      | 3                  | I don't know         |
| Mark     | 2           | Do you like ice cream? | 2                  | No                   |
| Kate     | 2           | Do you like ice cream? | 1                  | Yes                  |
| Jone     | 2           | Do you like ice cream? | 1                  | Yes                  |
| Kim      | 2           | Do you like ice cream? | 1                  | Yes                  |
| Alex     | 2           | Do you like ice cream? | 2                  | No                   |
| Bond     | 2           | Do you like ice cream? | 1                  | Yes                  |
| Ford     | 2           | Do you like ice cream? | 3                  | I don't know         |

公式:

value_1 = (Number of users who answered "No" or "I don't know" to the first question) / (The total number of people who answered to the first question)

value_2 = (Number of users who answered "No" or "I don't know" to the second question) / (The total number of people who answered to the first question)

我可以根据上面的公式分别求值。例如 value_1:

select
    count(*)
from
    answers
where
    question_id = 1
    and (
        selected_option_id in (2, 3)
        or 
        selected_option_text in ('No', 'I don\'t know')
    )

我的问题是如何通过一个 sql 查询正确地算出这两个值的算术平均值？

换句话说，我需要找到平均值:

Answer 1

你可以使用条件总和

select  (sum( case when QUESTION_ID = 1 AND  
        SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 1 then 1 else 0 end)::float )*100 first_question_rate,
    (sum( case when QUESTION_ID = 2 AND  
        SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 2 then 1 else 0 end)::float)*100 second_question_rate,
    (( sum( case when QUESTION_ID = 1 AND  SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 1 then 1 else 0 end)::float +
      sum( case when QUESTION_ID = 2 AND  SELECTED_OPTION_ID in ( 2,3) THEN 1 else 0 end )::float /  
      sum( case when QUESTION_ID = 2 then 1 else 0 end) ::float)/2)*100 avg 
from answer