Как получить количество вхождений из 2 таблиц SQL

#sql #sqlite #count #subquery #left-join

#sql #sqlite #количество #подзапрос #левое соединение

Вопрос:

Я использую python в созданной мной базе данных SQlite3. Я создал базу данных и в настоящее время просто использую командную строку, чтобы попытаться получить правильную инструкцию sql.

У меня есть 2 таблицы.

 Table 1 - users
user_id, name, message_count

Table 2 - messages
id, date, message, user_id

Когда я настраивал вторую таблицу, я добавил это утверждение в создание моей таблицы сообщений, но я понятия не имею, что оно делает, если вообще что-либо делает:

 FOREIGN KEY (user_id) REFERENCES users (user_id)

Что я пытаюсь сделать, так это вернуть список, содержащий имя и количество сообщений в течение 2020 года. Я использовал это утверждение, чтобы получить ОБЩЕЕ количество сообщений в 2020 году, и оно работает:

 SELECT COUNT(*) FROM messages WHERE substr(date,1,4)='2020';

Но я изо всех сил пытаюсь выяснить, должен ли я присоединиться к таблицам или есть способ получить только ту информацию, которая мне нужна. Оператор, который я хочу, будет выглядеть примерно так:

 SELECT name, COUNT(*) FROM users JOIN messages ON messages.user_id = users.user_id WHERE substr(date,1,4)='2020';

Ответ №1:

Один из вариантов использует коррелированный подзапрос:

 select u.*,
    (
        select count(*) 
        from messages m 
        where m.user_id = u.user_id and m.date >= '2020-01-01' and m.date < '2021-01-01'
    ) as cnt_messages
from users u

В этом запросе будет использоваться индекс on messages(user_id, date) .
Вы могли бы также join и агрегировать. Если вы хотите разрешить пользователям, у которых нет сообщений, a left join является подходящим:

 select u.name, count(m.user_id) as cnt_messages
from users u
left join messages m 
    on m.user_id = u.user_id and m.date >= '2020-01-01' and m.date < '2021-01-01'
group by u.user_id, u.name

Обратите внимание, что более эффективно фильтровать date столбец по буквенным датам, чем применять к нему функцию (что исключает использование индекса).

1. Откуда вы получаете u. *? Или вы создаете это в этом запросе?

2. @Lzypenguin: from предложение определяет u как псевдоним для таблицы users .

3. БОЛЬШОЕ ВАМ СПАСИБО! Я только что добавил order by count(m.user_id) desc; к вашему второму редактированию, и оно ОТЛИЧНО работает!!!!! Большое вам спасибо!!! И сортировка по дате работает намного лучше.

Ответ №2:

Вам не хватает предложения GROUP BY для группировки по пользователю:

 SELECT u.user_id, u.name, COUNT(*) AS counter 
FROM users u JOIN messages m
ON m.user_id = u.user_id 
WHERE substr(m.date,1,4)='2020'
GROUP BY u.user_id, u.name