Блог

Главная - Вопросы по программированию - Получить среднее значение из столбца, затем отфильтровать все строки, число которых меньше среднего, а затем подсчитать количество строк

Получить среднее значение из столбца, затем отфильтровать все строки, число которых меньше среднего, а затем подсчитать количество строк

#sql #database #sqlite

#sql #База данных #sqlite

Вопрос:

Это вопрос sql, над которым я работал некоторое время без результата.

Схема

 CREATE TABLE states (
    statecode text primary key,
    population_2010 integer,
    population_2000 integer,
    population_1950 integer,
    population_1900 integer,
    landarea real,
    name text,
    admitted_to_union text
);
CREATE TABLE counties(
    name text,
    statecode text references states(statecode),
    population_1950 integer,
    population_2010 integer
);
CREATE TABLE senators(
    statecode text references states(statecode),
    name text primary key,
    affiliation text,
    took_office integer,
    born integer);
CREATE TABLE committees(
    id text primary key,
    parent_committee text references committees(id),
    name text,
    chairman text references senators(name),
    ranking_member text references senators(name)
);

Вопрос в том:
возвращает количество штатов, в которых больше среднего числа округов

Я знаю, как рассчитать среднее:

 select avg(state_count) 
from
    (select count(*) as state_count
     from counties C
     group by C.statecode)

Но мне интересно, могу ли я повторно использовать подзапрос.

вот так (следующий не работает):

 select count(*) as state_count
from counties C
group by C.statecode
having count(*) > avg(state_count)

Этот запрос по-прежнему не учитывает количество строк, есть ли элегантный способ?

Редактировать:Только что нашел пример ответа

 SELECT COUNT(*)
FROM (SELECT statecode, COUNT(*) AS counts
      FROM counties
      GROUP BY statecode) s
WHERE s.counts > (SELECT AVG(t.counts) 
                  FROM  (SELECT COUNT(*) AS counts
                         FROM counties 
                         GROUP BY statecode) t);

Но мне все еще интересно, есть ли лучший способ?

Ответ №1:

Неэкономичная часть предоставленного вами примера ответа заключается в следующем

 SELECT COUNT(*) AS counts
FROM counties 
GROUP BY statecode

Эта часть появляется дважды, один раз с целью вычисления среднего и один раз для поиска значений, которые больше среднего. Итак, вот моя попытка с использованием CTE, которая повторно использует приведенный выше фрагмент кода для обеих целей:

 WITH c AS
(
   SELECT COUNT(*) AS counts
   FROM counties 
   GROUP BY statecode
)
SELECT COUNT(*) FROM c WHERE counts > (SELECT  Avg(CAST(counts AS decimal)) 
                                       FROM c)

Ответ №2:

ПРИВЕТ, вы можете сделать это с помощью CTE

     WITH States AS (
   SELECT COUNT(*) as state_count
     FROM counties t
 GROUP BY t.statecode)
  select count(*) from( SELECT count(*) as state_count from 
    counties t
 GROUP BY t.statecode
        having  count(*)> (select avg(x.state_count) from States x)) as g

Ответ №3:

Я думаю, вы можете упростить следующий запрос. Я использовал простой (некоррелированный) подзапрос, чтобы вычислить среднее число округов на штат, взяв общее число округов, деленное на общее число штатов.

 SELECT COUNT(*) AS states_above_average_count
FROM
(
    SELECT statecode
    FROM counties
    GROUP BY statecode
    HAVING COUNT(*) > (SELECT COUNT(*) FROM counties) / (SELECT COUNT(*) FROM states)
) t
Метки: