sql максимальное количество записей по группам

Question

Есть таблица типа:

Год  \ Имя
1948 \ Татьяна
1948 \ Наталья
1948 \ Татьяна
1949 \ Татьяна
1949 \ Наталья
1949 \ Ольга
1949 \ Ольга

Нужно получить самое распространённое имя за каждый год. Т.е. результат:

1948 \ Татьяна
1949 \ Ольга

SELECT top 1 Year(ДатаРожд) as Год, Имя, Count(Имя)
FROM Лист
where Year(ДатаРожд)=1948
group by Year(ДатаРожд), Имя
order by Count(Имя) desc;

Данный код подходит для того, чтобы выделить одно имя за каждый год, если каждый отдельный год вручную прописать. А списком я не могу понять как сделать. Подскажите пжлст!

Answer 1

В Mysql возможен такой порядок действий :

    SELECT year, name, COUNT(name) AS ca
    FROM list
    GROUP BY 1,2;

Это создана таблица, считающая кол-во имён по годам, к которой будем обращаться в подзапросе,выбирая MAX. Ей потребуется алиас, например, q. Весь запрос:

    SELECT year,name
    FROM list
    GROUP BY 1,2
    HAVING COUNT(name)=(
    SELECT MAX(ca) FROM (
    SELECT year, name, COUNT(name) AS ca
    FROM list
    GROUP BY 1,2)q
    );

Answer 2

Изучил коррелированный агрегирующий подзапрос. Но в MS Access он всё равно не работает. Запустил в MS SQL Server Management Studio. В моей базе почти 200 тыс. записей. Запрос выполняется полчаса. Раньше я такого долгого выполнения не встречал. Возможно, данный запрос можно и нужно оптимизировать.

select distinct year(ДатаРожд) as god, (
        select top 1 Имя
        from Лист
        where year(ДатаРожд)=year(list2.ДатаРожд)
        group by year(ДатаРожд), Имя
        order by count(Имя) desc
        ) as Name
from Лист as list2
order by year(ДатаРожд)

Кому интересно, это я по одному российскому городу провёл исследование, просто ради интереса. Самые распространённые имена по годам рождения выглядят так (делал 2 раза запрос с выборкой по полу):

Answer 3

Усовершенствовал код. Теперь выполняется за 6 секунд, вместо 25 минут.

   select year(ДатаРожд) as god, (
            select top 1 Имя
            from Лист
            where year(ДатаРожд)=year(list2.ДатаРожд) and Пол='ЖЕНСКИЙ'
            group by year(ДатаРожд), Имя
            order by count(Имя) desc
            ) as Name
    from Лист as list2
    group by year(ДатаРожд)
    order by year(ДатаРожд)

Distinct нельзя было использовать, до меня потом дошло.

БЛОГ НА HUSL

sql максимальное количество записей по группам

Ответы (3 шт):