Redshift为一列中的值创建任意长度的所有组合 [英] Redshift create all the combinations of any length for the values in one column
问题描述
如何为一列中的值创建任何长度的所有组合，并为该组合返回另一列的不重复计数?
How can we create all the combinations of any length for the values in one column and return the distinct count of another column for that combination?
表格:
+------+--------+
| Type | Name |
+------+--------+
| A | Tom |
| A | Ben |
| B | Ben |
| B | Justin |
| C | Ben |
+------+--------+
输出表:
+-------------+-------+
| Combination | Count |
+-------------+-------+
| A | 2 |
| B | 2 |
| C | 1 |
| AB | 3 |
| BC | 2 |
| AC | 2 |
| ABC | 3 |
+-------------+-------+
当组合只有A时，有Tom和Ben，所以是2.
When the combination is only A, there are Tom and Ben so it's 2.
当组合仅是B时，有2个不同的名称，所以是2.
When the combination is only B, 2 distinct names so it's 2.
当组合为A和B时，有3个不同的名字:汤姆，本，贾斯汀，所以是3.
When the combination is A and B, 3 distinct names: Tom, Ben, Justin so it's 3.
我正在Amazon Redshift中工作.谢谢！
I'm working in Amazon Redshift. Thank you!
推荐答案
在Amazon Redshift中无法生成所有可能的组合(A，B，C，AB，AC，BC等).
There is no way to generate all possible combinations (A, B, C, AB, AC, BC, etc) in Amazon Redshift.
(好吧，您可以选择每个唯一值，将它们粘成一个字符串，发送给用户定义的函数，将结果提取到多行中，然后将其连接到一个大查询中，但这实际上不是问题您想尝试.)
(Well, you could select each unique value, smoosh them into one string, send it to a User-Defined Function, extract the result into multiple rows and then join it against a big query, but that really isn't something you'd like to attempt.)
一种方法是创建一个包含所有可能组合的表-您需要编写一个小程序来做到这一点(例如，在Python中使用itertools).然后，您可以在相当容易的情况下将数据联接起来以得到所需的结果(例如IF 'ABC' CONTAINS '%A%').
One approach would be to create a table containing all possible combinations — you'd need to write a little program to do that (eg using itertools in Python). Then, you could join the data against that reasonably easy to get the desired result (eg IF 'ABC' CONTAINS '%A%').
这篇关于Redshift为一列中的值创建任意长度的所有组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
