Pivot Table with Redshift (PostgreSQL) with Count

Question

我面临着Redshift的挑战： 我正在尝试将行动态移动到列中并按计数聚合，但是我注意到数据透视表功能仅在 PostgreSQL 9 中可用。

知道如何执行以下操作吗？

index   fruit     color 
1       apple     red           
2       apple     yellow           
2       banana    blue           
2       banana    blue           
3       banana    blue     
3       banana    green     
3       pear      green     
3       pear      red           

自：

index   red       yellow    blue    green 
1       1         0         0       0
2       0         1         2       0
3       1         0         1       2

本质上，对每个 id 的颜色出现次数进行分组和计数(水果不是那么重要，尽管我稍后会将其用作过滤器(。

注意：我可能还想稍后进行二进制转换(即 0 表示 0，如果> 0，则为 1(

编辑：如果上述是不可能的，有什么方法可以做到这一点吗？

index   color     count     
1       red       1        
1       yellow    0           
1       blue      0
1       green     0 
2       red       0        
2       yellow    1           
2       blue      2
2       green     0
3       red       1         
3       yellow    0           
3       blue      1
3       green     2

(同样，蓝色，黄色，蓝色和绿色应该是动态的(

Answer 1

对于编辑，你可以做

select x.index, x.color, sum(case when y.index is not null then 1 else 0 end) as count
from 
((select index
from [table]
group by index
order by index) a
inner join 
(select color
from [table]
group by color
order by color) b
on 1 = 1) x
left outer join
[table] y
on x.index = y.index
and x.color = y.color
group by x.index, x.color
order by x.index, x.color

Answer 2

如果 Redshift 中PIVOT不可用，则始终可以使用标准透视查询：

SELECT
index,
SUM(CASE WHEN color = 'red'    THEN 1 ELSE 0 END) AS red,
SUM(CASE WHEN color = 'yellow' THEN 1 ELSE 0 END) AS yellow,
SUM(CASE WHEN color = 'blue'   THEN 1 ELSE 0 END) AS blue,
SUM(CASE WHEN color = 'green'  THEN 1 ELSE 0 END) AS green
FROM yourTable
GROUP BY index