求和一些查询的结果，然后在SQL中找到前5名

一尘不染

求和一些查询的结果，然后在SQL中找到前5名

mysql

我有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_id按数量组合所有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;

阅读 376

2020-05-17

共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;

2020-05-17