Суммируйте результаты нескольких запросов и затем найдите топ-5 в SQL

Question

У меня 3 запроса:

table: pageview
SELECT event_id, count(*) AS pageviews 
FROM pageview 
GROUP BY event_id
ORDER BY pageviews DESC, rand()
LIMIT 1000

table: upvote
SELECT event_id, count(*) AS upvotes 
FROM upvote
GROUP BY event_id
ORDER BY upvotes DESC, rand()
LIMIT 1000

table: attending
SELECT event_id, count(*) AS attendants
FROM attending
GROUP BY event_id
ORDER BY attendants DESC, rand()
LIMIT 1000

Я хотел бы объединить event_ids из всех 3 запросов, упорядоченных по количеству, а затем выберите топ 5. Как мне это сделать?

РЕДАКТИРОВАТЬ: ЗДЕСЬ ТО, ЧТО Я ДЕЛАЛ, ЧТОБЫ ЭТО СЛУЧИЛОСЬ:

SELECT event_id, sum(amount) AS total
FROM (
(SELECT event_id, count(*) AS amount
FROM   pageview 
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*) as amount
FROM   upvote
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*) as amount
FROM   attending
GROUP  BY event_id
ORDER  BY amount DESC, rand()
LIMIT  1000)
) x
GROUP  BY 1
ORDER  BY  sum(amount) DESC
LIMIT  5;

Answer 1

Вопрос оставляет место для толкования. к UNION результирующие строки всех трех запросов, а затем выбрать 5 строк с наибольшим "количеством":

(SELECT event_id, count(*) AS amount
FROM   pageview 
GROUP  BY event_id
ORDER  BY pageviews DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*)
FROM   upvote
GROUP  BY event_id
ORDER  BY upvotes DESC, rand()
LIMIT  1000)

UNION ALL
(SELECT event_id, count(*)
FROM   attending
GROUP  BY event_id
ORDER  BY attendants DESC, rand()
LIMIT  1000)

ORDER  BY 2 DESC
LIMIT  5;

Руководство:

Применять ORDER BY или же LIMIT для человека SELECT, поместите предложение в круглые скобки, которые включают SELECT,

UNION ALL, поэтому дубликаты не удаляются.

---

Если вы хотите добавить количество для каждогоevent_idэтот запрос должен сделать это:

SELECT event_id, sum(amount) AS total
FROM (
   (SELECT event_id, count(*) AS amount
    FROM   pageview 
    GROUP  BY event_id
    ORDER  BY pageviews DESC, rand()
    LIMIT  1000)

    UNION ALL
    (SELECT event_id, count(*)
    FROM   upvote
    GROUP  BY event_id
    ORDER  BY upvotes DESC, rand()
    LIMIT  1000)

    UNION ALL
    (SELECT event_id, count(*)
    FROM   attending
    GROUP  BY event_id
    ORDER  BY attendants DESC, rand()
    LIMIT  1000)
    ) x
GROUP  BY 1
ORDER  BY sum(amount) DESC
LIMIT  5;

Сложность в том, что не каждый event_id будет присутствовать во всех трех базовых запросах. Таким образом, вы должны позаботиться о том, чтобы JOIN не терял строки полностью и дополнения не оказывались NULL,

использование UNION ALLне UNION, Вы не хотите удалять идентичные строки, вы хотите добавить их.

x это сокращение для AS x - псевдоним таблицы. Для подзапроса требуется имя. Здесь может быть любое другое имя.

SOL-функция FULL OUTER JOIN не реализован в MySQL (в прошлый раз я смотрел), поэтому вам придется обойтись с UNION. FULL OUTER JOIN объединит все три базовых запроса без потери строк.

Ответ на следующий вопрос

SELECT event_id, sum(amount) AS total
FROM (
   (SELECT event_id, count(*) / 100 AS amount
    FROM   pageview ... )

    UNION ALL
    (SELECT event_id, count(*) * 5 
    FROM   upvote ... )

    UNION ALL
    (SELECT event_id, count(*) * 10
    FROM   attending ... )
    ) x
GROUP  BY 1
ORDER  BY  sum(amount) DESC
LIMIT  5;

Или, если вы хотите использовать базовые значения несколькими способами:

SELECT event_id
      ,sum(CASE source
              WHEN 'p' THEN amount / 100
              WHEN 'u' THEN amount * 5
              WHEN 'a' THEN amount * 10
              ELSE 0
           END)  AS total
FROM (
   (SELECT event_id, 'p'::text AS source, count(*) AS amount
    FROM   pageview ... )

    UNION ALL
    (SELECT event_id, 'u'::text, count(*)
    FROM   upvote ... )

    UNION ALL
    (SELECT event_id, 'a'::text, count(*)
    FROM   attending ... )
    ) x
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  5;