用于Redshift数据库的数据透视 [英] 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)

如何实现红移?

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

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

推荐答案

我们在Ro方面做了很多工作-

We do a lot of pivoting at Ro - we built python based tool for autogenerating pivot queries. This tool allows for the same basic options as what you'd find in excel, including specifying aggregation functions as well as whether you want overall aggregates.

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