generate_series()在PostgreSQL中的sum中未按预期工作

Question

我有一个名为分类的表，其中包含classification_indicator_id
我需要将这个ID求和，并放入1天的序列中
我需要添加大约20列(使用另一个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都很大，并且当我添加额外的列时会增长。在2013-03-26 segment1中有4列的第二个表中，金额应与第一个表中的金额相同。

 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    

Answer 1

正如您在前面的回答中所评论的，您正在遇到一个"代理交叉联接">
我在这个相关的回答中对此进行了更详细的解释：
两个SQL LEFT JOIN产生错误的结果

您的查询应该这样工作：

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;

Answer 2

不需要联接：

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"