SQL -客户满意度影响(需要子查询吗？)

Question

我一直遇到一些SQL问题，试图向我的表中添加一个新列，我认为它依赖于子查询才能工作。

下面，我与一些示例数据共享了一个到谷歌工作表的链接，以演示我正在努力实现的目标。https://docs.google.com/spreadsheets/d/1qaWZg4BEs8xOxG3jNZwKRcaOGfMiMtCb15IU3FQ9W0o/edit?usp=sharing

我的基本布局是：

select
 team_member,
 language,
 channel,
 month,
 sum(total_customers),
 sum(happy_customers),
 safe_divide(sum(happy_customers),sum(total_customers)) AS Satisfaction_Score
FROM
 Table1
GROUP BY 1,2,3,4

我知道上面的每一行team_member，语言，频道，月份都能给我满足感。我试图在结尾添加新的列，这可能会让我：

• sum(total_customers)由language
• Satisfaction_Score卷起，用于team_member

支持的语言

然后，我可以用它来计算满意度的影响，其布局如下：

(team member satisfaction score-language satisfaction score)*(team member total customers/language total customers)

遗憾的是，我甚至还没有接近答案，希望这里的巫师知道该怎么做？

Answer 1

您可以使用窗口函数：

select team_member, language, channel, month,
       sum(total_customers), sum(happy_customers),
       sum(sum(total_customers)) over (partition by team_member, language) as total_team_language,
       safe_divide(sum(sum(happy_customers)) over (partition by team_member, language),
                   sum(total_customers)over (partition by team_member, language)
                  ) AS Satisfaction_Score_team_language
FROM Table1
GROUP BY 1,2,3,4