Сортировка и подсчет

@stompbox · Регистрация: 16.12.2012

Студворк — интернет-сервис помощи студентам

Есть таблица сыгранных шахматных игр между двумя игроками:

id user_id user2_id

Примеры (таблицы games):

id - 1
user_id - 1
user2_id - 2

id - 2
user_id - 1
user2_id - 2

id - 3
user_id - 1
user2_id - 3

id - 4
user_id - 3
user2_id - 1

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

user_id: 1
 count_games: 4

  user_id: 2
 count_games: 2

  user_id: 3 
count_games: 2

Пробывал таким образом:

SQL
1
2
3
4
SELECT
 COUNT(user_id) AS COUNT, user_id, user2_id 
 FROM games 
 GROUP BY user_id, user2_id;

Но GROUP BY работает по двумя полям. Не совсем понимаю как сделать в итоге

Спасибо!

@grgdvo · 28.01.2019, 13:17

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH games AS (
SELECT id, user_id, user2_id
FROM (VALUES
(1, 1, 2), 
(2, 1, 2), 
(3, 1, 3), 
(4, 3, 1)) AS t(id, user_id, user2_id)
)
SELECT uid, COUNT(uid) FROM
(SELECT user_id AS uid FROM games
UNION ALL
SELECT user2_id AS uid FROM games) AS u(uid)
GROUP BY uid
ORDER BY uid;

@stompbox · 29.01.2019, 11:19 **[ТС]**

grgdvo, пользователей может быть N-ое количество. Каким образом в таком случае поступить?

Добавлено через 2 минуты
grgdvo, т.е. эту часть

SQL
1
2
3
4
5
6
SELECT id, user_id, user2_id
FROM (VALUES
(1, 1, 2), 
(2, 1, 2), 
(3, 1, 3), 
(4, 3, 1)) AS t(id, user_id, user2_id)

где идет описание объектов (1,1,2), (2,1,2) необходимо на динамику изменить.

Каким образом поступить?

Добавлено через 3 минуты
А, все, разобрался.

SQL:

SQL
1
2
3
4
5
6
7
8
9
WITH games AS (
SELECT id, user_id, user2_id
FROM games AS t(id, user_id, user2_id))
SELECT uid, COUNT(uid) FROM
(SELECT user_id AS uid FROM games
UNION ALL
SELECT user2_id AS uid FROM games) AS u(uid)
GROUP BY uid
ORDER BY uid;

Спасибо!

@grgdvo · 29.01.2019, 20:59

Я использовал WITH ... SELECT ... VALUES потому, что у меня нет ваших данных.
И чтобы не генерировать таблицу, не заполнять ее данными, я выкрутился этой конструкцией.
Поскольку у вас есть реальная таблица games, то и WITH уже не нужна

SQL
1
2
3
4
5
6
SELECT uid, COUNT(uid) FROM
(SELECT user_id AS uid FROM games
UNION ALL
SELECT user2_id AS uid FROM games) AS u(uid)
GROUP BY uid
ORDER BY uid;

@stompbox · 30.01.2019, 14:17 **[ТС]**

grgdvo, хм, а если к данному запросу необходимо добавить еще параметры?

Скажем что user_id связан с таблицей "users" и у юзера есть "login".

Куда добавить тот самый JOIN для получения пользовательского логина вместе с выборкой текущей?

Добавлено через 20 минут
grgdvo, если сделать такого рода запрос:

SQL
1
2
3
4
5
6
SELECT uid, COUNT(uid) FROM
(SELECT g.user_id AS uid, u.name FROM games AS g LEFT JOIN public.users AS u ON u.id = g.user_id
UNION ALL
SELECT g.user2_id AS uid, u.name FROM games AS g LEFT JOIN public.users AS u ON u.id = g.user2_id) AS u(uid)
GROUP BY uid
ORDER BY uid;

То в первом изменим так:

SQL
1
SELECT uid, COUNT(uid), name FROM ...

В общем виде все получится так:

SQL
1
2
3
4
5
6
SELECT uid, COUNT(uid), name FROM
(SELECT g.user_id AS uid, u.name FROM games AS g LEFT JOIN public.users AS u ON u.id = g.user_id
UNION ALL
SELECT g.user2_id AS uid, u.name FROM games AS g LEFT JOIN public.users AS u ON u.id = g.user2_id) AS u(uid)
GROUP BY uid
ORDER BY uid;

При этом вот конечном варианте требуется сортировка по нескольким полям, т.е. просто GROUP BY uid не прокатит и нужен будет GROUP BY uid, name - что не очень и выглядит неправильно. Как сделать - ума не приложу. Третий день за SQL.

@grgdvo · 30.01.2019, 14:40

можно в подзапрос обернуть

SQL
1
2
3
4
5
6
7
8
9
10
11
12
SELECT au.uid, u.uname, au.ucnt
FROM
  (SELECT u.uid, COUNT(u.uid) AS ucnt 
   FROM
     (SELECT user_id AS uid FROM games
      UNION ALL
      SELECT user2_id AS uid FROM games) AS u(uid)
      GROUP BY u.uid
      ORDER BY u.uid) AS au(uid,ucnt)
   LEFT JOIN 
      users AS u 
   ON au.uid=u.uid;

@stompbox · 30.01.2019, 15:50 **[ТС]**

grgdvo, не поверите, минут 40 назад, читая документацию удалось самому понять как сделать

Скажите, оптимальный запрос? С Вашей точки зрения? Мало пока что понимаю, но как говориться надо встать на путь истинный.

Спасибо большое за предоставленные решения!

@grgdvo · 31.01.2019, 02:23

С моей точки зрения запрос совсем не оптимальный

)
А именно
- я бы избавился от UNION ALL, ибо это как минимум присоединение одной выборки к другой да еще и сортировка скорее всего будет. И это пока просто подготовка к подсчету количеств игроков. Путь лучше в games записи будут дублироваться, но тогда посчитать можно будет быстрее одним проходом. Так вы как минимум экономите два-три раза по O(n), где O(n) - это обход по games, а n - количество записей в games.
- я бы избавился от LEFT JOIN. Зачем он здесь? Вот по смыслу. У вас есть игроки, о них заполнена какая-то информация. Игроки участвуют в играх. Разве может в games появится ниоткуда идентификатор игрока, которого еще нет в users. Это же не логично, правда?? Соответственно и в запросе. Вы идете от подсчета игр и теперь вам надо добавить информацию об игроке. Логично же ожидать, что в users наверняка будут записи об этом игроке (по смыслу организации данных), поэтому и не нужен left join. Тогда join может произойти чуточку быстрее. На малом объеме данных вы практически не увидите разницы. А когда у вас будут 1000-ы игроков и 10000-игр, разница начнет ощущаться. Наверняка пригодятся индексы, которые должны будут работать для ускорения поиска необходимых записей.

Ну и в резюме. Я мог тут теоретизировать сколько угодно, но любой анализ запроса начинается с просмотра его плана. Ознакомьтесь (изучите!), что такое EXPLAIN, из чего он состоит, как он работает. План запроса - это нутро базы, то, как она реально выполняет Ваш запрос. Не оптимальный план приводит к потере производительности.

@stompbox · 06.02.2019, 18:02 **[ТС]**

grgdvo, понял! Спасибо вам большое!

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .	Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .	Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .
И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .

@stompbox 0 / 0 / 0 Регистрация: 16.12.2012 Сообщений: 32
	30.01.2019, 15:50 [ТС]
	grgdvo, не поверите, минут 40 назад, читая документацию удалось самому понять как сделать Скажите, оптимальный запрос? С Вашей точки зрения? Мало пока что понимаю, но как говориться надо встать на путь истинный. Спасибо большое за предоставленные решения! 0

@grgdvo 1264 / 978 / 384 Регистрация: 02.09.2012 Сообщений: 3,021
	31.01.2019, 02:23
	Сообщение было отмечено stompbox как решение Решение С моей точки зрения запрос совсем не оптимальный ) А именно - я бы избавился от UNION ALL, ибо это как минимум присоединение одной выборки к другой да еще и сортировка скорее всего будет. И это пока просто подготовка к подсчету количеств игроков. Путь лучше в games записи будут дублироваться, но тогда посчитать можно будет быстрее одним проходом. Так вы как минимум экономите два-три раза по O(n), где O(n) - это обход по games, а n - количество записей в games. - я бы избавился от LEFT JOIN. Зачем он здесь? Вот по смыслу. У вас есть игроки, о них заполнена какая-то информация. Игроки участвуют в играх. Разве может в games появится ниоткуда идентификатор игрока, которого еще нет в users. Это же не логично, правда?? Соответственно и в запросе. Вы идете от подсчета игр и теперь вам надо добавить информацию об игроке. Логично же ожидать, что в users наверняка будут записи об этом игроке (по смыслу организации данных), поэтому и не нужен left join. Тогда join может произойти чуточку быстрее. На малом объеме данных вы практически не увидите разницы. А когда у вас будут 1000-ы игроков и 10000-игр, разница начнет ощущаться. Наверняка пригодятся индексы, которые должны будут работать для ускорения поиска необходимых записей. Ну и в резюме. Я мог тут теоретизировать сколько угодно, но любой анализ запроса начинается с просмотра его плана. Ознакомьтесь (изучите!), что такое EXPLAIN, из чего он состоит, как он работает. План запроса - это нутро базы, то, как она реально выполняет Ваш запрос. Не оптимальный план приводит к потере производительности. 1

@stompbox 0 / 0 / 0 Регистрация: 16.12.2012 Сообщений: 32
	06.02.2019, 18:02 [ТС]
	grgdvo, понял! Спасибо вам большое! 0

Сортировка и подсчет

Решение