Считайте, включая 0, при объединении двух таблиц с разными строками postgres

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

Мои две таблицы:

Users Table:

Name       Place
----------------------
James      Supermarket
Alex       Bank 
Ignatius   BookShop

Action table:

Name   Action
--------------------
James  Buy
James  Buy
Alex   Complaint
Alex   Buy
Alex   Buy

Я хочу что-то вроде этого:

Name       Transactions   Place
-------------------------------
James      2              Supermarket
Alex       3              Bank
Ignatius   0              BookShop

Я пробовал с:

SELECT users.name, 
   (SELECT Count(action.name) 
    FROM   action, 
           users 
    WHERE  action.name = users.name 
    GROUP  BY action.name) 
    users.place
FROM 
action, 
users 
WHERE  action.name = users.name

Но всегда дает мне эту ошибку:

ОШИБКА: подзапрос, используемый в качестве выражения, возвращает более одной строки. Состояние SQL: 21000.

Любая идея о том, как это сделать?


postgresql sql postgresql-9.6
person Joan Triay    schedule 11.12.2017    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Никогда не используйте запятые в предложении FROM. Всегда используйте правильный, явный синтаксис JOIN. Тем не менее, вам вообще не нужны соединения для этого запроса, если вы хотите использовать коррелированные подзапросы:

SELECT u.name, 
       (SELECT Count(*) 
        FROM action a 
        WHERE a.name = u.name 
       ) as cnt,
       u.place
FROM users u;

Для производительности вам нужен индекс action(name). С таким индексом это может быть быстрее, чем альтернативные версии, использующие left join и group by.

Заметки:

  • Все таблицы имеют псевдонимы таблиц, которые являются сокращениями имени таблицы.
  • Для этой версии запроса соединения не требуются.
  • Нет group by в подзапросе.
  • Предложение where во внешнем запросе не требуется.
person Gordon Linoff    schedule 11.12.2017
comment
Ваше решение работает нормально, медленнее, но это хорошо. большое спасибо - person Joan Triay; 11.12.2017

arrow_upward
2
arrow_downward

Сначала вы должны выполнить левое соединение, а затем применить group by к его результату:

SELECT
    u.name
,   COUNT(a.name) AS Transactions
,   u.place
FROM users u
LEFT OUTER JOIN action a ON a.name = u.name 
GROUP BY u.name, u.place

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

person Sergey Kalinichenko    schedule 11.12.2017
comment
привет, @dasblinkenlight это работает нормально, но я не хочу включать u.place в группу, когда я удаляю, это дает мне ошибку, которая говорит, что я должен включить, я не могу включить. Как я мог это сделать? - person Joan Triay; 11.12.2017
comment
@JoanTriay Предполагая, что name однозначно идентифицирует пользователя, не имеет значения, используете ли вы place в group by, потому что оба поля взяты из одной таблицы, а одно из полей (имя) однозначно идентифицирует строку. - person Sergey Kalinichenko; 11.12.2017

arrow_upward
1
arrow_downward

Как и в предыдущем ответе, левое соединение поможет вам. Если вы хотите включить место в результаты, выполните левое соединение после подведения итогов.

SELECT a.name,
        ISNULL(b.cnt, 0) AS Transactions,
        a.place
FROM users a
LEFT JOIN
   (SELECT name,
            Count(name) cnt
    FROM   action 
    GROUP  BY name) b
ON a.name =  b.name
person E.Abbott    schedule 11.12.2017