PostgreSQL时间序列查询与外部联接
问题描述:
在PostgreSQL 9.5数据库中有一个表metrics_raw包含各种指标(类型:varchar)。 (例如):TRA,RTC。
我执行下列SQL获得年初至今每月汇总:PostgreSQL时间序列查询与外部联接
SELECT
count(*),
"ticks"."ts" AS "timestamp"
FROM
"metrics_raw"
RIGHT OUTER JOIN
generate_series('2016-01-01'::timestamp, '2016-10-10'::timestamp, '1 month'::interval) AS ticks(ts)
ON
"ticks"."ts" = date_trunc('months', "metrics_raw"."timestamp")
WHERE
"metrics_raw"."type" = 'TRA' OR
"metrics_raw"."type" IS NULL
GROUP BY "ticks"."ts"
ORDER BY "ticks"."ts"
表包含TRA型(〜10条)和RTC类型的1条的一些记录。
执行查询的TRA我得到行的结果符合市场预期,但对RTC查询我只得到行。还有一件事是没有RTC指标我也得到行。
哪里可能出错?
答
谢谢@Nemeros,我固定的查询是:
SELECT
"ticks"."ts" AS "timestamp"
FROM
generate_series('2016-01-01'::timestamp, '2016-10-10'::timestamp, '1 month'::interval) AS ticks(ts)
LEFT OUTER JOIN
(
SELECT *
FROM "metrics_raw"
WHERE "metrics_raw"."type" = 'TRA'
) as "metrics"
ON
"ticks"."ts" = date_trunc('months', "metrics"."timestamp")
GROUP BY "ticks"."ts"
ORDER BY "ticks"."ts"
的问题是在哪里的条件。您应该使用左外连接来转换查询,并将metric_raw.type =放入连接闭包中 – Nemeros