时间戳

首页 > 解决方案 > 组合一个 CROSS JOIN 和一个 LEFT JOIN

sql - 组合一个 CROSS JOIN 和一个 LEFT JOIN

问题描述

我有两个名为authorand的表commit_metrics。他们都有一个id领域。作者有author_name和author_email。Commit_metrics 有author_id和author_date。

我正在尝试编写一个查询，该查询将获取每个作者在给定一周内的提交次数，即使该数字是 0。这是我到目前为止所拥有的：

SELECT a.id, a.author_name, a.author_email, c.week_num, COUNT(c.id)
FROM author AS a
     CROSS JOIN generate_series(1, 610) AS s(n)
     LEFT JOIN  (SELECT c.id,
                        c.author_id,
                        c.author_date,
                        WEEK_NUMBER(c.author_date) AS week_num
                 FROM commit_metrics c) AS c ON s.n = c.week_num AND a.id = c.author_id
WHERE c.week_num IS NOT NULL
GROUP BY a.id, a.author_name, a.author_email, c.week_num
ORDER BY c.week_num DESC, a.author_name;

WEEK_NUMBER是我为此查询编写的函数：

CREATE OR REPLACE FUNCTION WEEK_NUMBER(date TIMESTAMP) RETURNS INTEGER AS
$$
SELECT TRUNC(DATE_PART('day', date - '2008-01-01') / 7)::INTEGER;
$$ LANGUAGE SQL;

目前，该查询就像一个具有一个主要警告的魅力。当作者在给定的一周内没有提交时，它不会正确计算 0。我不确定为什么没有。当我只使用FROMand进行查询时CROSS JOIN，它会正确打印数千个组合作者/周。但是，当我添加 时LEFT JOIN，它会丢失作者未提交的任何一周。

任何帮助将不胜感激。generate_series如果没有必要，我愿意取消通话。

另外，我发现了这篇文章，但我认为这对我的情况没有帮助。

标签： sqlpostgresqlcross-join

解决方案

尽管您使用的是左连接，但“WHERE c.week_num IS NOT NULL”会过滤掉所有没有帖子的情况。尝试这个：

SELECT a.id, a.author_name, a.author_email, s.n as week_num, COUNT(c.id) as post_count
FROM author AS a
     CROSS JOIN generate_series(1, 610) AS s(n)
     LEFT JOIN  (SELECT c.id,
                        c.author_id,
                        c.author_date,
                        WEEK_NUMBER(c.author_date) AS week_num
                 FROM commit_metrics c) AS c ON s.n = c.week_num AND a.id = c.author_id
GROUP BY a.id, a.author_name, a.author_email, s.n
ORDER BY s.n DESC, a.author_name;

推荐阅读