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

Это вопрос 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)

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

EDIT: просто нашел образец ответа

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);

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

sql,database,sqlite,

3

Ответов: 3

2 принят

Неэкономичная часть ответа на образец, который вы предоставили, - это

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)
0

HI вы можете сделать, используя 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
0

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

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
SQL, базы данных, SQLite,
Похожие вопросы
Яндекс.Метрика