PostgreSQL 5 самых густонаселенных округов в каждом штате

У меня проблема с моим классом баз данных, которую я не могу понять.

У меня есть таблица под названием countries, которая выглядит следующим образом:

state_code | name | population
           |      |
           |      |

(в каждом state_code есть несколько округов)

Есть еще одна таблица с названием states, которая просто

state_code | name 
           |
           |

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

У меня есть запрос, который выдает правильную схему, но показывает слишком много результатов, а не только первые 5:

SELECT state.name AS state_name, county.name AS county_name, county.population 
FROM state JOIN county ON state.code = county.state_code 
GROUP BY state.name, county.name, county.population
ORDER BY state.name, county.population DESC

Я пробовал более сложные решения, связанные с ранжированием, однако в нашей школе есть только версия PostgreSQL 8.3, в которой нет PARTITION OVER или RANK(), что очень усложняет задачу.

Спасибо


postgresql sql database rank
person ddriver1    schedule 27.10.2012    source источник
comment
8.3 довольно древний. Вам нужно начать планировать обновление. Версия 8.3 прекращает свое существование в феврале 2013 года, после чего не будет выпущено никаких будущих исправлений ошибок, исправлений безопасности или исправлений совместимости. См. postgresql.org/support/versioning . Обновление с 8.3 до 9.2 не является большим скачком с точки зрения совместимости; вам нужно обработать standard_conforming_strings и изменение по умолчанию bytea_output, но не более того. См. примечания к выпуску. PostgreSQL бесплатен, поэтому нет причин не обновлять его.   -  person Craig Ringer    schedule 28.10.2012
comment
У OP может не быть административных полномочий для обновления...   -  person didierc    schedule 28.10.2012
comment
ты прав, Дидьерек, к сожалению, на компьютерах в классе установлен устаревший postgreSQL, и я ничего не могу с этим поделать. У меня есть решение, использующее оконные функции, но эти решения не принимаются.   -  person ddriver1    schedule 28.10.2012

Ответы (2)


arrow_upward
2
arrow_downward 

select state_name, county_name, population
from (
    select
        s.name state_name,
        c.name county_name,
        c.population,
        row_number() over(partition by s.state_code order by population desc) rn
    from
        states s
        inner join
        counties c on s.state_code = c.state_code
) s
where rn <= 5
order by state_name, population desc

Оконная функция row_number нумерует строки внутри каждого состояния. Во внешнем запросе я ограничиваю количество строк до 5 или меньше.

person Clodoaldo Neto    schedule 27.10.2012
comment
Возможно, вы захотите использовать dense_rank() вместо row_number(), чтобы иметь дело со связями. - person a_horse_with_no_name; 28.10.2012
comment
ОП сказал, что у него нет доступа к конструкции OVER PARTITION, потому что его сервер postgresql версии 8.3 - person didierc; 28.10.2012

arrow_upward
0
arrow_downward

Это решение невероятно медленное и болезненное, но я считаю, что оно должно дать вам то, что вы хотите. Мне любопытно посмотреть, может ли кто-нибудь придумать более элегантное решение.

 SELECT state.name AS state_name, county.name AS county_name, county.population
 FROM state JOIN county ON state.code = county.state_code
 WHERE  county.name IN (SELECT county.name, COUNT(*) FROM county
          INNER JOIN county AS second_county ON (county.name = second_county.name) AND 
          (county.state_code = second_county.state_code) AND (county.population < state.population) GROUP BY county.name HAVING COUNT(*) <=5)
GROUP BY state.name, county.name, county.population
ORDER BY state.name, county.population DESC
person Abraham P    schedule 27.10.2012