sql - 如何在 RIGHT JOIN 中聚合具有不同 AND 子句的字段？

Question

SELECT
    externalsource.id,
    externalsource.url,
    externalsource.last_fetch,
    SUM(scripttracking.records) AS jobs_per_week
FROM
    scripttracking
RIGHT JOIN
    externalsource
ON
    externalsource.script_name = scripttracking.script_name
AND
    scripttracking.created_on > %s
GROUP BY
    externalsource.id

知道如何修改此查询，以便它聚合具有不同“AND”条件的 scripttracking.records 吗？

本质上，我需要得到的是:

SUM(scripttracking.records) for scripttracking.created_on > now() - 1 week

SUM(scripttracking.records) for scripttracking.created_on > now() - 1 month

SUM(scripttracking.records) for scripttracking.created_on > now() - 1 year

我需要将所有这些字段包含在 1 个结果查询集中。

类似于:

ID, URL, LAST_FETCH, JOBS_PER_WEEK, JOBS_PER_MONTH, JOBS_PER_YEAR
1, http://google.ru, 25/01/2016, 7, 23, 8889

Answer 1

传统上我们会使用 CASE 语句，但 Postgres 为我们提供了更舒适的解决方案，因此更好地使用 FILTER

SELECT
    externalsource.id
    ,externalsource.url
    ,externalsource.last_fetch
    ,SUM(scripttracking.records) AS jobs_per_week
    ,SUM(scripttracking.records) FILTER (WHERE scripttracking.created_on > now() - 1 week)
    ,SUM(scripttracking.records) FILTER (WHERE scripttracking.created_on > now() - 1 month)
    ,SUM(scripttracking.records) FILTER (WHERE scripttracking.created_on > now() - 1 year)
FROM
    scripttracking
RIGHT JOIN
    externalsource
ON
    externalsource.script_name = scripttracking.script_name
AND
    scripttracking.created_on > %s
GROUP BY
    externalsource.id