红移数据库的枢轴 [英] Pivot for redshift database

问题描述

我知道以前有人问过这个问题，但任何答案都无法帮助我满足我想要的要求.所以在新线程中提出问题

I know this question has been asked before but any of the answers were not able to help me to meet my desired requirements. So asking the question in new thread

在 redshift 中，如何使用将数据转换为每个唯一维度集一行的形式，例如:

In redshift how can use pivot the data into a form of one row per each unique dimension set, e.g.:

id         Name               Category         count
8660     Iced Chocolate         Coffees         105
8660     Iced Chocolate         Milkshakes      10
8662     Old Monk               Beer            29
8663     Burger                 Snacks          18

到

id        Name              Cofees  Milkshakes  Beer  Snacks
8660    Iced Chocolate       105       10        0      0
8662    Old Monk             0         0        29      0
8663    Burger               0         0         0      18

上面列出的类别不断变化.Redshift 不支持枢轴运算符，case 表达式不会有太大帮助(如果不支持，请建议如何操作)

The category listed above gets keep on changing. Redshift does not support the pivot operator and a case expression would not be of much help (if not please suggest how to do it)

我怎样才能在红移中实现这个结果?

How can I achieve this result in redshift?

(以上只是一个示例，我们将有 1000 多个类别，并且这些类别不断变化)

(The above is just an example, we would have 1000+ categories and these categories keep's on changing)

推荐答案

我认为在 Redshift 中没有简单的方法可以做到这一点，

i don't think there is a easy way to do that in Redshift,

你还说你有超过 1000 个类别，而且这个数字还在增长您需要考虑到每个表有 1600 列的限制，

also you say you have more then 1000 categories and the number is growing you need to taking in to account you have limit of 1600 columns per table,

见附件[http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_usage.html][1]

您可以使用案例，但您需要为每个类别创建案例

you can use case but then you need to create case for each category

select id,
       name,
       sum(case when Category='Coffees' then count end) as Cofees,       
       sum(case when Category='Milkshakes' then count end) as Milkshakes,
       sum(case when Category='Beer' then count end) as Beer,
       sum(case when Category='Snacks' then count end) as Snacks
from my_table
group by 1,2

您可以选择将表格上传到 R，然后您可以使用 cast 函数.

other option you have is to upload the table for example to R and then you can use cast function for example.

cast(data, name~ category)

然后将数据上传回 S3 或 Redshift

and then upload the data back to S3 or Redshift

这篇关于红移数据库的枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！