将值过多的行转换为列

Question

假设我有一个这样的数据集。

ID  Item
1   A
2   B
3   A
1   C
2   B
2   B
1   A
3   B

实际数据集有50K行和8.5K个不同的项值。

现在，我想要将每个Id的行转置为列，这样我们就可以计算每个用户的每个项目的值。如下所示

ID  A   B   C
1   2   0   1
2   0   3   0
3   1   1   0

现在，如果我有像这样的集合。然后我可以在case聚合语句中对它们进行硬编码。

就像这样

select ID, count(case when Item ='A' then 1 else Null end) A, 
count(case when Item='B' then 1 else Null end) B... and so on. 

但在这种情况下，我有8.5K的唯一项。在sql中，这将是一项太多的任务。

我如何实现这一点是Sql和ii) Python。请记住，我有50K行和8.5k个唯一项，所以python循环可能会变得有点慢。

但我的第一个目标是用sql实现这一点，因为这是一个50K的样本数据。当数据变得更大时，sql仍然可以很好地容纳它，但是python会变得很慢i feel.In python我可以再次尝试一些代码too.But当数据超过50K b时，那么python也会变慢。

请指教

不使用Postgresql和使用HANA，因此特定于Postgres的功能可能无法工作。请推荐通用的sql方式。

Answer 1

如果您确实想在python中做到这一点，则应该查看pandas库

df = pd.DataFrame({
    'ID': [1, 2, 3, 1, 2, 2, 1, 3],
    'Item': ['A', 'B', 'A', 'C', 'B', 'B' , 'A', 'B']
})

pd.crosstab(df['ID'], df['Item'])

输出：

Item A  B   C
ID          
1   2   0   1
2   0   3   0
3   1   1   0

Answer 2

对于SQL解决方案，使用您的SQL技术但编写脚本如何？我不确定它将如何支撑，但如果你还没有尝试过，你可以试一试。运行下面的代码来生成您需要的所有列的脚本：

select distinct 'sum(case when ITEM = ''' + ITEM + ''' then 1 else 0 end) ' + ITEM + ',' from YOUR_TABLE;

然后复制并粘贴结果以得出以下结果：

select 
  ID,
  **<COPY AND PASTE RESULTS HERE>**
from TEST_DATA
group by ID;

Answer 3

此查询可以生成所需的查询：

SELECT CONCAT("SELECT ID"
    , GROUP_CONCAT(DISTINCT 
        CONCAT(", COUNT(CASE Item WHEN '", Item, "' THEN 1 ELSE NULL END) AS `", Item, "`")
        ORDER BY Item
        )
    , "FROM `theTable` "
    , "GROUP BY ID"
  ) AS theQuery
FROM `theTable`
;

我只知道GROUP_CONCAT肯定存在于MySQL中，而不存在于....but中；我不知道其他数据库系统。此外，在MySQL中，您需要通过在此查询前添加如下语句来增加函数所允许的最大长度

SET SESSION group_concat_max_len = 1000000;

...or更改服务器配置以使默认最大值更大。...where "1000000“是一个足够长的长度，不会截断结果字符串。

如果查询太大以至于无论设置什么，group_concat都无法满足它:您可以运行多个这样的查询，使用WHERE减少处理的COUNT值，生成更小的"，Item (...)，COUNT(“列表；然后手动合并这些结果。

当然，即使这生成了想要的查询，我也不敢肯定MySQL会接受这么长的查询。