Postgres Windows函数与聚合分组依据 [英] Postgres windows function with aggregate group by

问题描述

我想获取电子邮件域的列表以及每个域中的顶级用户。我的方法是对按域分组的每封电子邮件的问题求和，然后使用窗口功能让顶级用户使用。但这不起作用：

 选择
域，将
 sum（questions_per_email）作为questions_per_domain，
 first_value（email）作为top_user 
 FROM（
 SELECT电子邮件，
较低（substring（u.email from position（'@'in u） .email）+1））作为域，
 count（*）作为questions_per_email 
来自问题q 
 JOIN标识符i ON（q.owner_id = i.id）
 JOIN用户u以（per.user）
 GROUP BY网域，top_user 
   GROUP BY电子邮件
） p> 
 
 
而Postgres给出以下消息：
 错误：每个用户列。 questions_per_email必须出现在GROUP BY子句中或在聚合函数
中使用第5行：... t_value（email）OVER（按域划分的订单顺序ORDER BY BY Problems _... 
 ^ 
  
我真不明白为什么。我很确定一个人应该能够对汇总结果使用window函数。 
 
 
 
谢谢，
 Kristoffer 
解决方案
您可以像这样更改查询：
 ，cte1为（
 SELECT电子邮件，
低（子字符串（u.email来自位置（u.email中的'@'+ 1））作为域
来自问题q 
 JOIN标识符i ON（q.owner_id = i.id）
 JOIN用户u ON（u.identifier_id = i.id）
），cte2 as（
选择
域，电子邮件，
 count（*）作为questions_per_email，
 first_value （电子邮件）作为（top_user 
从cte1 
按电子邮件，域
分组的top_user 
）（按域顺序按域划分） ）作为question_per_domain 
，来自cte2 
按域分组，top_user 
  
    sql小提琴演示   
 
I want to get a list of email domains and the top user within each domain. My approach is to sum the questions per email grouped by the domain and then get the top user with a window function. However this does not work:
SELECT 
  domain,
  sum(questions_per_email) as questions_per_domain,
  first_value(email) OVER (PARTITION BY domain ORDER BY questions_per_email DESC) as top_user
FROM (
    SELECT email,
           lower(substring(u.email from position('@' in u.email)+1)) as domain,
           count(*) as questions_per_email
      FROM questions q
      JOIN identifiers i ON (q.owner_id = i.id)
      JOIN users u ON (u.identifier_id = i.id)
    GROUP BY email
  ) as per_user
GROUP BY domain, top_user
And Postgres gives the following message: 
ERROR:  column "per_user.questions_per_email" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5: ...t_value(email) OVER (PARTITION BY domain ORDER BY questions_...
                                                             ^
I can't really see why this is. I pretty sure that one should be able to use a window function on the aggregated result. Please advice!

Thanks,
Kristoffer
解决方案
you can change your query like this:
with cte1 as (
    SELECT email,
           lower(substring(u.email from position('@' in u.email)+1)) as domain
      FROM questions q
      JOIN identifiers i ON (q.owner_id = i.id)
      JOIN users u ON (u.identifier_id = i.id)
), cte2 as (
    select
        domain, email,
        count(*) as questions_per_email,
        first_value(email) over (partition by domain order by count(*) desc) as top_user
    from cte1
    group by email, domain
)
select domain, top_user, sum(questions_per_email) as questions_per_domain
from cte2
group by domain, top_user
sql fiddle demo

                        
这篇关于Postgres Windows函数与聚合分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！