求和和生成序列在 PostgreSQL 中不起作用

Question

在classification_indicator_id列我有一些数字。我想把这些数字按1天的级数求和。我在下面写query

select
a.data_start::date,
a.segment1::integer as "Segment1"
from (
select
    data as data_start,
    (select sum(classification_indicator_id) from classifications where classification_indicator_id = 3)::integer as segment1
from
    generate_series('2013-03-25'::timestamp without time zone,
    '2013-04-01'::timestamp without time zone,
    '1 day'::interval) data
) a
group by
a.data_start,
a.segment1
ORDER BY data_start

但是我总是得到这样的东西:

date start|segment1
-------------------
2013-03-25|39
2013-03-26|39
2013-03-27|39
2013-03-28|39
2013-03-29|39
2013-03-30|39
2013-03-31|39
2013-04-01|39

我确定应该是这样的:

date start|segment1
-------------------
2013-03-25|3
2013-03-26|4
2013-03-27|7
2013-03-28|9
2013-03-29|15
2013-03-30|22
2013-03-31|19
2013-04-01|5

SQL小提琴

Answer 1

select
    data.d::date,
    coalesce(sum(classification_indicator_id), 0)::integer as "Segment1"
from 
    classifications c
    right join
    generate_series(
        '2013-03-25'::timestamp without time zone,
        '2013-04-01'::timestamp without time zone,
        '1 day'::interval
    ) data(d) on data.d::date = c.data_start::date
where classification_indicator_id = 3
group by 1
ORDER BY 1

Answer 2

我需要添加一些其他列(与另一个classification_indicator_id我稍微修改了一下你的答案:

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为每行是大的，并增长时，我添加额外的列

With 4 columns                          With 3 column
data      |Segment1|Segment2|Segment3   data      |Segment1|Segment2
-------------------------------------   ----------------------------
2013-03-25|12      |16      |20         2013-03-25|12      |16      
-------------------------------------   ----------------------------
2013-03-26|108     |144     |180        2013-03-26|18      |24