用一个查询聚合两个不同的事件表

Question

我有一个简单的模式，其中一个表用于posts，两个表用于跟踪views和upvotes:

CREATE TABLE posts (
    id integer NOT NULL PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);
CREATE TABLE views (
    post_id integer,
    value integer
);
CREATE TABLE upvotes (
    post_id integer,
    value integer
);

请注意，views和upvotes中的元组表示视图和支持票的一般数量。我想汇总帖子，显示浏览量和支持率的总量。

我原以为这很简单，但不幸的是我失败了。

这是我的问题：

SELECT posts.title,
       SUM(views.value) AS views,
       SUM(upvotes.value) AS upvotes
FROM posts
    LEFT OUTER JOIN views ON (posts.id = views.post_id)
    LEFT OUTER JOIN upvotes ON (posts.id = upvotes.post_id)
GROUP BY posts.id;

我理解它失败的原因，但我真的不知道该怎么做。如果我需要运行两个不同的查询并在应用程序级别聚合结果，我可以接受。我只想了解是否可以使用单一查询

我为我的测试用例创建了一个sqlfiddle

附言：我设法只使用一个通用表来跟踪事件，但我想把表分开。sqlfiddle在这里。

Answer 1

您可以安全地计算子查询中的值。这将避免您对重复的值进行求和，从而导致无效的结果。

SELECT  p.id,
        p.title,
        COALESCE(v.totalView, 0) totalView,
        COALESCE(u.totalUpvote, 0) totalUpvote
FROM    posts p
        LEFT JOIN
        (
            SELECT  post_id,
                    SUM(v.value) totalView
            FROM    views v
            GROUP   BY post_id
        ) v ON p.id = v.post_ID
        LEFT JOIN
        (
            SELECT  post_id,
                    SUM(u.value) totalUpvote
            FROM    upvotes u
            GROUP   BY post_id
        ) u ON p.ID = u.post_ID

• SQLFiddle演示

Answer 2

SQL Fiddle

在两个单独的选择之间进行JOIN

SELECT * FROM(SELECT posts.title,
       SUM(views.value) AS views
FROM posts
    LEFT OUTER JOIN views ON (posts.id = views.post_id)
GROUP BY posts.id)as x
JOIN(
SELECT posts.title,      
       SUM(upvotes.value) AS upvotes
FROM posts
    LEFT OUTER JOIN upvotes ON (posts.id = upvotes.post_id)
GROUP BY posts.id)as y on x.title=y.title