generate_series()在PostgreSQL中按预期不能按预期方式工作
我有一些称为分类的表,其中包含classification_indicator_id
。
我需要总结这个ID
并放入1天系列。
我需要添加大约20列(与另一个classification_indicator_id
)。
我修改了一下回答previous question:generate_series()在PostgreSQL中按预期不能按预期方式工作
select
data.d::date as "data",
sum(c.classification_indicator_id)::integer as "Segment1",
sum(c4.classification_indicator_id)::integer as "Segment2",
sum(c5.classification_indicator_id)::integer as "Segment3"
from
generate_series(
'2013-03-25'::timestamp without time zone,
'2013-04-01'::timestamp without time zone,
'1 day'::interval
) data(d)
left join classifications c on (data.d::date = c.created::date and c.classification_indicator_id = 3)
left join classifications c4 on (data.d::date = c4.created::date and c4.classification_indicator_id = 4)
left join classifications c5 on (data.d::date = c5.created::date and c5.classification_indicator_id = 5)
group by "data"
ORDER BY "data"
但仍然无法正常工作。 sum
对于每一行都是很大的,而且我增加了额外的列。在与4列segment1
为2013年3月26日第二个表应该像第一张表等
With 3 column With 4 columns
data | Segment1 | Segment2 data | Segment1 | Segment2 | Segment3
-------------------------------- -------------------------------------------
2013-03-25 | 12 | 16 2013-03-25 | 12 | 16 | 20
-------------------------------- -------------------------------------------
2013-03-26 | 18 | 24 2013-03-26 | 108 | 144 | 180
由于commented under your previous answer,你正在运行到“代理交叉连接”相同的金额。
我在这个相关的答案解释的比较详细:
Two SQL LEFT JOINS produce incorrect result
您的查询应该像这样工作:
SELECT d.created AS data
,c3.segment1
,c4.segment2
,c5.segment3
FROM (
SELECT generate_series('2013-03-25'::date
,'2013-04-01'::date
,interval '1 day')::date AS created
) d
LEFT JOIN (
SELECT created
,sum(classification_indicator_id)::integer AS segment1
FROM classifications
WHERE classification_indicator_id = 3
GROUP BY 1
) c3 USING (created)
LEFT JOIN (
SELECT created
,sum(classification_indicator_id)::integer AS segment2
FROM classifications
WHERE classification_indicator_id = 4
GROUP BY 1
) c4 USING (created)
LEFT JOIN (
SELECT created
,sum(classification_indicator_id)::integer AS segment3
FROM classifications
WHERE classification_indicator_id = 5
GROUP BY 1
) c5 USING (created)
ORDER BY 1;
假设created
是date
,而不是一个timestamp
。
或者,甚至更快的查询,因为这已经成为一个话题:
SELECT d.created AS data
,count(classification_indicator_id = 3 OR NULL)::int * 3 AS segment1
,count(classification_indicator_id = 4 OR NULL)::int * 4 AS segment2
,count(classification_indicator_id = 5 OR NULL)::int * 5 AS segment3
FROM (
SELECT generate_series('2013-03-25'::date
,'2013-04-01'::date
,interval '1 day')::date AS created
) d
LEFT JOIN classifications c USING (created)
GROUP BY 1
ORDER BY 1;
无需连接:
select
data.d::date as "data",
sum((classification_indicator_id = 3)::integer * classification_indicator_id)::integer as "Segment1",
sum((classification_indicator_id = 4)::integer * classification_indicator_id)::integer as "Segment2",
sum((classification_indicator_id = 5)::integer * classification_indicator_id)::integer as "Segment3",
from
generate_series(
'2013-03-25'::timestamp without time zone,
'2013-04-01'::timestamp without time zone,
'1 day'::interval
) data(d)
left join
classifications c on data.d::date = c.created::date
group by "data"
ORDER BY "data"
这可能比多个连接更快。 'CASE'会更快。 – 2013-04-04 13:11:53
@Erwin _MIGHT_ ???你一定在开玩笑。或者我的英语不稳定,不明白_might_的意思:)) – 2013-04-04 13:14:18
我也会测试它,我会让你知道 – ssuperczynski 2013-04-04 13:14:18
谢谢你,现在我们正在测试这个解决方案,我将让你知道它是否对我们有帮助。但是我们认为这是:) – ssuperczynski 2013-04-04 12:57:28
它的工作!我们的速度更快:) – ssuperczynski 2013-04-04 13:13:43
我们将使用这两种解决方案,他们都很棒。从10000ms到100ms这是它! – ssuperczynski 2013-04-04 13:22:00