Блог

Главная - Вопросы по программированию - Отображение всех отдельных строк ГРУППЫ ПО, отсортированных по количеству групп

Отображение всех отдельных строк ГРУППЫ ПО, отсортированных по количеству групп

#sql #sqlite #group-by #sql-order-by

Вопрос:

Учитывая такую таблицу, как эта:

 User     A
---------------
Erik     1278
Bob      16287
Alice    9723
Daniel   7
Erik     8
Bob      162
Erik     126

как выбрать все строки, сгруппированные/упорядоченные пользователем с наибольшим количеством строк?

Результатом было бы:

 Erik     1278            # Erik is first because 3 rows with him
Erik     8
Erik     126
Bob      16287           # Bob is 2nd because 2 rows
Bob      162
Alice    9723
Daniel   7

Ни

 SELECT * FROM t ORDER BY user

ни

 SELECT *, COUNT(1) as frequency FROM t GROUP BY user ORDER BY frequency DESC

работает, действительно, последний отображает только одну строку для Эрика, одну строку для Боба, одну строку для …

Похоже , мне нужно GROUP BY , но все же иметь возможность видеть «каждую строку» группы. Как это сделать?

Ответ №1:

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

 order by count(*) over (partition by user) desc,
         user

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

Редактировать:

В более старых версиях вы можете использовать подзапрос:

 order by (select count(*) from user u2 where u2.user= u.user) desc,
         user

Комментарии:

1. Спасибо @GordonLinoff за ваш ответ. Похоже OVER (PARTITION ...) , требуется последняя версия Sqlite3 (см. sqlite.org/windowfunctions.html#history ). Как вы думаете, есть ли решение без этой недавней функции? Я попытаюсь обновить свою версию Sqlite, но, поскольку это может привести к поломке старых инструментов, использующих более старую версию, я также ищу другое решение.

Метки: ,