Блог

мой код похож :

 SELECT    
    number,
    name,
    count(*) as "the number of correct answer"
FROM
    table1 NATURAL JOIN table2
WHERE
    answer = 'T'
GROUP BY
    number,
    name
HAVING
    count(*) < avg(count(*))
ORDER BY
    count(*);
  

Здесь я хочу найти группу с количеством меньше среднего количества для каждой группы, но здесь я не смог использовать HAVING или WHERE, кто-нибудь может мне помочь?
Как я могу выбрать только 1 name1 2 среднее значение, поскольку среднее значение count равно (2 6 7)/3 = 5 и только 2 меньше среднего значения.

 number name count
1      name1 2
2      name2 6
3      name3 7
  

Я бы посоветовал вам никогда не использовать естественные соединения. Они запутывают запрос и делают запрос более сложным для обслуживания.

Вы можете использовать оконные функции:

 SELECT t.*
FROM (SELECT number, name,
             COUNT(*) as num_correct,
             AVG(COUNT(*)) OVER () as avg_num_correct
      FROM table1 JOIN
           table2
           USING (?). -- be explicit about the column name
      WHERE answer = 'T'
      GROUP BY number, name
     ) t
WHERE num_correct < avg_num_correct;
  

Как и в вашей версии запроса, это отфильтровывает все группы, у которых нет правильных ответов.

Я бы поместил вашу текущую логику запроса в CTE, а затем пометил среднее количество в процессе:

 WITH cte AS (
    SELECT number, name, COUNT(*) AS cnt,
           AVG(COUNT(*)) OVER () AS avg_cnt
    FROM table1
    NATURAL JOIN table2
    WHERE answer = 'T'
    GROUP BY number, name
)

SELECT number, name, cnt AS count
FROM cte
WHERE cnt < avg_cnt;
  

Здесь мы используем AVG() функцию как аналитическую функцию, а окном является вся агрегированная таблица. Это означает, что он найдет среднее значение количества для каждой группы по всем группам (после агрегирования). Оконные функции (почти) всегда оцениваются последними.