将Pandas操作转换为SQL查询

Question

table = (df.groupby('name')['activity'].value_counts().unstack().fillna(0))

我有一个表，我想创建我可以在python中创建它，但我想在sql中创建它以获得如下内容：

activity tv football cooking chatting
name
chandler 0   0        3       2
monica   1   1        2       0
ross     3   2       1        1

我想应该是这样的，但我不能更进一步了。

SELECT name,
COUNT(CASE WHEN END) AS ,
COUNT(CASE WHEN END) AS
FROM dataset
GROUP BY name;

Answer 1

因此，您需要条件聚合：

SELECT name,
SUM(CASE WHEN activity = 'tv' THEN 1 ELSE 0 END) AS tv,
SUM(CASE WHEN activity = 'football' THEN 1 ELSE 0 END) AS football,
SUM(CASE WHEN activity = 'cooking' THEN 1 ELSE 0 END) AS cooking,
. . . 
FROM dataset
GROUP BY name;