How to get the average value of the "average" values ​​in the group?

Question

I have a table with values ​​and group identifiers (simplified example). I need to get the average value for each group of average values ​​of 3. So, if there are 1, 2 or 3 values, this is just the average value. But if there are 4 values, this excludes the highest, 5 values ​​- the highest and lowest, etc. I was thinking about some kind of window function, but I'm not sure if this is possible.

http://www.sqlfiddle.com/#!11/af5e0/1

For this data:

TEST_ID TEST_VALUE GROUP_ID 1 5 1 2 10 1 3 15 1 4 25 2 5 35 2 6 5 2 7 15 2 8 25 3 9 45 3 10 55 3 11 15 3 12 5 3 13 25 3 14 45 4 

I would like to

 GROUP_ID AVG 1 10 2 15 3 21.6 4 45 

Answer 1

Another option using analytic functions;

 SELECT group_id, avg( test_value ) FROM ( select t.*, row_number() over (partition by group_id order by test_value ) rn, count(*) over (partition by group_id ) cnt from test t ) alias where cnt <= 3 or rn between floor( cnt / 2 )-1 and ceil( cnt/ 2 ) +1 group by group_id ; 

Demo → http://www.sqlfiddle.com/#!11/af5e0/59

Answer 2

I am not familiar with the Postgres syntax for window functions, but I was able to solve your problem in SQL Server using SQL Fiddle . Perhaps you can easily port this to Postgres-compatible code. Hope this helps!

A quick guide to working with it.

• Order test scores for each group
• Get the number of items in each group
• Use this as a subquery and select only the middle 3 elements (this is the where clause in the outer query)
• Get the average value for each group

-

 select group_id, avg(test_value) from ( select t.group_id, convert(decimal,t.test_value) as test_value, row_number() over ( partition by t.group_id order by t.test_value ) as ord, g.gc from test t inner join ( select group_id, count(*) as gc from test group by group_id ) g on t.group_id = g.group_id ) a where ord >= case when gc <= 3 then 1 when gc % 2 = 1 then gc / 2 else (gc - 1) / 2 end and ord <= case when gc <= 3 then 3 when gc % 2 = 1 then (gc / 2) + 2 else ((gc - 1) / 2) + 2 end group by group_id 

Answer 3

 with cte as ( select *, row_number() over(partition by group_id order by test_value) as rn, count(*) over(partition by group_id) as cnt from test ) select group_id, avg(test_value) from cte where cnt <= 3 or (rn >= cnt / 2 - 1 and rn <= cnt / 2 + 1) group by group_id order by group_id 

demo sql

in cte, we need to get the number of elements above each group_id using the window function + calculate row_number inside each group_id . Then, if this counter is> 3, we need to get the middle of the group by dividing the score by 2, and then we will get +1 and -1 elements. If count <= 3, then we should just take all the elements.

Answer 4

It works:

 SELECT A.group_id, avg(A.test_value) AS avg_mid3 FROM (SELECT group_id, test_value, row_number() OVER (PARTITION BY group_id ORDER BY test_value) AS position FROM test) A JOIN (SELECT group_id, CASE WHEN count(*) < 4 THEN 1 WHEN count(*) % 2 = 0 THEN (count(*)/2 - 1) ELSE (count(*) / 2) END AS position_start, CASE WHEN count(*) < 4 THEN count(*) WHEN count(*) % 2 = 0 THEN (count(*)/2 + 1) ELSE (count(*) / 2 + 2) END AS position_end FROM test GROUP BY group_id) B ON A.group_id=B.group_id AND A.position >= B.position_start AND A.position <= B.position_end GROUP BY A.group_id 

Link to the script: http://www.sqlfiddle.com/#!11/af5e0/56

Answer 5

If you need to calculate group averages, you can do this:

 SELECT CASE WHEN NUMBER_FIRST_GROUP <> 0 THEN SUM_FIRST_GROUP / NUMBER_FIRST_GROUP ELSE NULL END AS AVG_FIRST_GROUP, CASE WHEN NUMBER_SECOND_GROUP <> 0 THEN SUM_SECOND_GROUP / NUMBER_SECOND_GROUP ELSE NULL END AS AVG_SECOND_GROUP, CASE WHEN NUMBER_THIRD_GROUP <> 0 THEN SUM_THIRD_GROUP / NUMBER_THIRD_GROUP ELSE NULL END AS AVG_THIRD_GROUP, CASE WHEN NUMBER_FOURTH_GROUP <> 0 THEN SUM_FOURTH_GROUP / NUMBER_FOURTH_GROUP ELSE NULL END AS AVG_FOURTH_GROUP FROM ( SELECT SUM(CASE WHEN GROUP_ID = 1 THEN 1 ELSE 0 END) AS NUMBER_FIRST_GROUP, SUM(CASE WHEN GROUP_ID = 1 THEN TEST_VALUE ELSE 0 END) AS SUM_FIRST_GROUP, SUM(CASE WHEN GROUP_ID = 2 THEN 1 ELSE 0 END) AS NUMBER_SECOND_GROUP, SUM(CASE WHEN GROUP_ID = 2 THEN TEST_VALUE ELSE 0 END) AS SUM_SECOND_GROUP, SUM(CASE WHEN GROUP_ID = 3 THEN 1 ELSE 0 END) AS NUMBER_THIRD_GROUP, SUM(CASE WHEN GROUP_ID = 3 THEN TEST_VALUE ELSE 0 END) AS SUM_THIRD_GROUP, SUM(CASE WHEN GROUP_ID = 4 THEN 1 ELSE 0 END) AS NUMBER_FOURTH_GROUP, SUM(CASE WHEN GROUP_ID = 4 THEN TEST_VALUE ELSE 0 END) AS SUM_FOURTH_GROUP FROM TEST ) AS FOO