sql - 枢轴内的窗口函数，这可能吗？

Question

我有这张 table

我需要创建一个枢轴，将情绪显示为列，平均 emotion_level 按 user_id、user_date、emotion 分组。例如，对于 user_id = 1、user_date = 2011-07-13 和 emotion = 'Anger'，平均 emotion_level 应为 4.0。

我创建一个枢轴:

select USER_ID, user_date,  
AVG(case emotion when 'Anger' then convert(float, emotion_level) else 0 end)  as Anger,
AVG(case emotion when 'Sadness' then convert(float, emotion_level) else 0 end) as Sadness,
AVG(case emotion when 'Interest' then convert(float, emotion_level) else 0 end) as Interest
from emotions group by USER_ID, user_date;

其中一半工作，但计算所有情绪中的平均 emotion_level，但不计算按用户、日期和情绪分组的情绪。

第一个用户的结果 + 情绪 = '愤怒' = 2，但它应该是 4。

我想，我应该使用窗口函数(over (partition by user_id, user_date, emotion))，但无法运行语法。

有可能吗？

我在产品中使用 PostgreSQL 9，但上面的示例是用 SQL Server 编写的。

Answer 1

WITH    q (id, user_id, user_date, emotion, emotion_level) AS
        (
        VALUES
        (1, 1, '2011-07-13'::DATE, 'Anger', 3),
        (2, 1, '2011-07-13'::DATE, 'Anger', 5),
        (3, 1, '2011-07-13'::DATE, 'Sadness', 2),
        (4, 1, '2011-07-13'::DATE, 'Interest', 2),
        (5, 2, '2011-07-13'::DATE, 'Anger', 1),
        (6, 2, '2011-07-13'::DATE, 'Sadness', 4),
        (7, 2, '2011-07-13'::DATE, 'Sadness', 5),
        (8, 2, '2011-07-13'::DATE, 'Interest', 3),
        (9, 3, '2011-07-13'::DATE, 'Anger', 1),
        (10, 3, '2011-07-13'::DATE, 'Sadness', 3),
        (11, 3, '2011-07-13'::DATE, 'Interest', 4),
        (12, 3, '2011-07-13'::DATE, 'Interest', 5)
        ) 
SELECT  user_id, user_date,
        AVG(CASE emotion WHEN 'Anger' THEN emotion_level END)::numeric(3, 2) AS Anger,
        AVG(CASE emotion WHEN 'Sadness' THEN emotion_level END)::numeric(3, 2) AS Sadness,
        AVG(CASE emotion WHEN 'Interest' THEN emotion_level END)::numeric(3, 2) AS Interest
FROM    q
GROUP BY
        user_id, user_date
ORDER BY
        user_id, user_date

问题是您最初使用的表达式:

AVG(case emotion when 'Interest' then convert(float, emotion_level) else 0 end)

对给定用户在给定日期的所有记录取平均值并将非兴趣条目视为0，而应将其视为NULL 所以他们不会对 Interest 平均值做出贡献。