postgres query with calculate before agregate

Question

this is my structure table.

1. first table have information who person will be evaluate

Table master_object

id	name
1	mark
2	suzen

2. seconds table is list of indicators available of the person and value of answer

Table record_indikator

obejct_id	is_active	val	indicator_id
1	0	0	1
2	1	0	2
3	1	1	3
4	1	1	1
5	0	1	2
6	1	1	3

i will be calculate result with join table that result is simple

who much person aswer  all indicator with status active / how much person 

how to calculate person aswered all active indicators

example for object_id =1 they have 1 answered then 2 indikators active meaning is object 1 not answer all indikator. so that value for object 1 is zero.

------------------------------------------------------

example for object_id =2 they have 2 answered then 2 indikators active

meaning is object 2 answer all indikator and value is 1 for object 2.

so after this will be aggregate all value (0 (object 1) + 1 (object 2) )/2 Person = 0.5

Question is how to build query efficient to get this result?

and mybe have any sugestion arstiketurs or engine to support this more be fast. note this records object is more then 300 milions at Years

Answer 1

If I understand correctly, you want to know what percentage of people answered all of the indicators. This can be accomplished with some conditional aggregations :

First we need to identify total active answers per person :

select r.object_id, sum(case when is_active=1 then 1 else 0 end) as total_actives, count(*) as total_records
from master_object m
inner join records_indikator r on r.object_id = m.id
group by r.object_id

Then check to see whether the totals are equivalent and do some math

select sum(case when total_actives = total_records then 1 else 0 end)::float/ count(*) as percentage
from (
  select r.object_id, sum(case when is_active=1 then 1 else 0 end) as total_actives, count(*) as total_records
  from master_object m
  inner join records_indikator r on r.object_id = m.id
  group by r.object_id
) as s 

Demo here