Netezza SQL问题：按ID统计指定日期区间内各颜色出现次数不符预期

现有数据表（my_table） #

id    my_date color
1  1 2009-01-01   red
2  1 2010-05-05  blue
3  1 2011-01-01   red
4  1 2012-01-01   red
5  2 2009-01-01  blue
6  2 2009-05-05   red
7  2 2010-01-01 green
8  2 2010-05-05   red
9  2 2020-01-01   red

需求与预期结果 #

按ID统计2010-01-01至2015-01-01区间内各颜色的出现次数，预期结果：

id color count
1  1   red     2
2  1  blue     1
3  2   red     2
4  2 green     1

尝试的SQL及输出 #

SELECT id, color, COUNT(*) as count
FROM my_table
WHERE my_date BETWEEN '2010-01-01' AND '2015-01-01'
GROUP BY id, color
ORDER BY id, color;

输出结果：

id color count
1  1  blue     1
2  1   red     2
3  2 green     1
4  2   red     1

问题分析 #

根据提供的原始数据，2010-01-01至2015-01-01区间内，id=2的color为red的记录仅1条（行8：2010-05-05），因此你的SQL输出是正确的，预期结果存在误差。

如果你的实际需求是统计2009-01-01至2015-01-01区间的数据（即把2009年的记录纳入统计），那么当前SQL的日期筛选条件不符合需求——原条件排除了2009年的记录，导致id=2的red少统计了1条（行6：2009-05-05）。

修正方案 #

若实际需求为统计2009-01-01至2015-01-01的区间数据，修改WHERE子句的起始日期：

SELECT id, color, COUNT(*) as count
FROM my_table
WHERE my_date BETWEEN '2009-01-01' AND '2015-01-01'
GROUP BY id, color
ORDER BY id, color;

执行后得到的结果将与你的预期一致：

id color count
1  1  blue     1
2  1   red     2
3  2 green     1
4  2   red     2

另外，可确认my_date字段的数据类型是否为日期类型，若为字符串类型需保证格式为YYYY-MM-DD，避免字符串比较导致的筛选错误。

内容的提问来源于stack exchange，提问作者stats_noob