How to compute overlap percentage of agreement between people in Hive table

user_11228246 0
Viewed 110

Suppose I have a survey where each question has 4 possible answers, and surveyed people can choose at least one answer (multiple answers allowed). I want to compute per question per answer, how many people chose that answer. For example, if I have the hive table:

question_id | answer_id | person_id
-------------------------------------
1           | A         |    1        
1           | B         |    1        
1           | C         |    1        
1           | D         |    1        
1           | A         |    2        
1           | B         |    2        
1           | C         |    2        
2           | D         |    1        
2           | A         |    1

Then the resulting table would be:

question_id | answer_id | Percentage
-------------------------------------
1           | A         |    100        
1           | B         |    100       
1           | C         |    100        
1           | D         |    50
2           | D         |    50        
2           | A         |    50

For question 1, both people put A,B,C giving 100% for all three, but one person put D as well, giving 50%. For question 2, one person put D and one person put A, giving 50% and 50%.

I've been really stuck and I haven't been able to find anything online that accomplishes what I'm looking for. Any help would be amazing!

hiveql hive sql
3 Answers

Hmmm . . . If I understand correctly, you want the number of people who chose one particular question/answer combination divided by the people who chose the question. If so, I think

select qa.*, qa.num_persons * 100.0 / q.num_persons
from (select question_id, answer_id, count(*) as num_persons
      from t
      group by question_id, answer_id
     ) qa join
     (select question_id, count(distinct person_id) as num_persons
      from t
      group by question_id
     ) q
     on qa.question_id = q.question_id;
user_11440350

Also you can use analytic functions and size(collect_set) for counting distinct. This will allow to eliminate join and will work fine if the number of distinct person per question is not too big (array produced by collect_set can fit in memory)

select qa.question_id, qa.answer_id, 
       qa.num_persons * 100.0 / size(qa.question_persons) as Percentage
from (select question_id, answer_id, 
             count(*) over (partition by question_id, answer_id) as num_persons,
             collect_set(person_id) over(partition by question_id) as question_persons
      from t
     ) qa;
user_27003440

I'm not familiar with prestoDB but below is a SQL script that will have the same result as what you posted.

The 2.0 is the number of person. You might want to select that first and store it in a vairable.

select 
     question_id, answer_id, (count(answer_id)/2.0) * 100.0
from Sample
group by question_id, answer_id
order by question_Id, answer_id
user_32596230
Related