Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > [Postgres]2 JOIN одной таблицы с разными условиями

Автор: Alx 6.3.2014, 11:32

Привет! Помогите, пожалуйста составить запрос. Задача такая (кажется, очень простая):

Есть таблица users и таблица posts. В таблице posts есть поле author_id и поле accepted. Необходимо выбрать пользователей и добавить к ним по два счетчика: кол-во опубликованных постов и общее количество постов.

Пробовал все варианты INNER/LEFT/RIGHT JOIN, все время не то, что нужно возвращает, вот один из моих вариантов:

Код


SELECT users.*,
       count(posts.author_id) AS posts_count,
       count(accepted_posts.author_id) AS accepted_posts_count
   FROM users
   LEFT JOIN posts ON posts.author_id = users.id
   LEFT JOIN posts accepted_posts ON accepted_posts.author_id = users.id AND accepted_posts.accepted = 1
   GROUP BY users.id
   ORDER BY accepted_posts_count DESC

Однако, этот код возращает не то, что нужно (вообще непредсказуемый результат).

Заранее спасибо!

Автор: Akina 6.3.2014, 16:49

При решении конкретных задача СУБД указывать надо! и постить в соотв. раздел, а не в общие вопросы.

Автор: Akina 6.3.2014, 17:49

Так, в качестве примера...

Код


SELECT users.*, 
       COUNT(posts.author_id) AS posts_count, 
       SUM(posts.accepted = 1) AS accepted_posts_count 
FROM users 
LEFT JOIN posts 
  ON posts.author_id = users.id

Для MySQL - нормально, для MS SQL - бредятина.

Автор: Alx 7.3.2014, 00:20

Akina, не думал, что это будет отличаться в зависимости от СУБД. Вообще говоря в данный момент у меня Postgres. Попробую твой код, спасибо!

Автор: Alx 7.3.2014, 00:40

Ругается на SUM(posts.accepted = 1):

Цитата

No function matches the given name and argument types.

А что это должно делать? Может "= 1" лишнее? Но вообще говоря я соврал для упрощения, на самом деле мне нужно делать фильтр не по accepted = 1, а по status = 15, так как у меня есть следующие статусы:

Цитата

      waiting:  0,
      declined: 5,
      returned: 10,
      accepted: 15

Автор: Alx 7.3.2014, 01:09

Мне удалось добиться результата с помощью одного или двух вложенных запросов.
Я правда не знаю, которое из этих решений лучше и в целом, насколько они оптимальны, не будут ли такие запросы слишком "тяжелыми".

Код


SELECT DISTINCT
    COUNT(posts.author_id) AS posts_count,
    accepted_posts.count AS accepted_posts_count
FROM users
LEFT OUTER JOIN posts
    ON posts.author_id = users.id
LEFT OUTER JOIN (SELECT author_id, COUNT(*) AS count FROM posts WHERE status = 15 GROUP BY author_id)
    AS accepted_posts
    ON accepted_posts.author_id = users.id
GROUP BY users.id, accepted_posts.count;

Код


SELECT DISTINCT
    posts.count AS posts_count,
    accepted_posts.count AS accepted_posts_count
FROM users
LEFT OUTER JOIN (SELECT author_id, COUNT(*) AS count FROM posts GROUP BY author_id)
    AS posts
    ON posts.author_id = users.id
LEFT OUTER JOIN (SELECT author_id, COUNT(*) AS count FROM posts WHERE status = 15 GROUP BY author_id)
    AS accepted_posts
    ON accepted_posts.author_id = users.id
GROUP BY posts_count, accepted_posts.count;

Автор: Alx 7.3.2014, 02:26

Но тут возникает новая проблема: на эти запросы теперь нельзя наложить никакие дополнительные условия. Например, у меня есть необходимость добавить фильтр по времени:

Код


WHERE (posts.created_at >= '2014-03-02' AND posts.created_at <= '2014-03-09')

Однако, этот код придется добавлять к каждому подзапросу, что неудобно и не получится сделать, например, при использовании SQL-билдера (или, например, оформить как scope в Rails).

Автор: Akina 7.3.2014, 07:58

Цитата(Alx @ 7.3.2014, 01:20

)

не думал, что это будет отличаться в зависимости от СУБД.

Задача имеет, конечно, общее решение, которое будет работать на любой СУБД. Но оно гарантированно будет неэффективно. Эффективное решение всегда использует особенности конкретного диалекта.

Цитата(Alx @ 7.3.2014, 01:40

)

Ругается на SUM(posts.accepted = 1):

Об том и речь.

Цитата(Alx @ 7.3.2014, 01:40

)

А что это должно делать? Может "= 1" лишнее?

Здесь используется фича MySQL. Сравнение даёт булев результат. MySQL интерпретирует True как единицу, а False как ноль.
С постгрессе есть CASE? должен быть, по идее... тогда

Код


SELECT users.*,  
       COUNT(posts.author_id) AS posts_count,  
       SUM(CASE posts.accepted WHEN 15 THEN 1 ELSE 0 END) AS accepted_posts_count  
FROM users  
LEFT JOIN posts  
  ON posts.author_id = users.id

Автор: Zloxa 9.3.2014, 22:26

Цитата(Akina @ 7.3.2014, 08:58

)

по идее... тогда

Групбай нужен и users.* нельзя

Автор: tzirechnoy 10.3.2014, 10:18

Код

SELECT  users.id, 
      COALESCE(count(posts), 0) AS posts, 
      COALESCE(SUM(CASE WHEN status=0 THEN 1 ELSE 0 END), 0) AS waiting,
      COALESCE(SUM(CASE WHEN status=5 THEN 1 ELSE 0 END), 0) AS declined,
      COALESCE(SUM(CASE WHEN status=10 THEN 1 ELSE 0 END), 0) AS returned,
      COALESCE(SUM(CASE WHEN status=15 THEN 1 ELSE 0 END), 0) AS accepted
FROM users 
        LEFT JOIN posts
             ON posts.author_id=users.id
   GROUP BY users.id;

Автор: Zloxa 10.3.2014, 10:22

Цитата(tzirechnoy @ 10.3.2014, 11:18

)

COALESCE(SUM(CASE WHEN status=0 THEN 1 ELSE 0 END), 0)

эквивалентно

Код


count(CASE WHEN status=0 THEN 1 END)

То же самое, но запись покороче

Цитата(tzirechnoy @ 10.3.2014, 11:18

)

COALESCE(count(posts), 0)

здесь коалес лишний, count null не возвращает.