HQL组合集合函数 [英] HQL combining aggregate functions

问题描述

我的问题是，我不能像在查询中一样使用这两个聚合函数：AVG（MIN（首先，我需要从该实体termResultCurrent获取最小值，然后是所有最小值的平均值。可能它必须通过改变group并包含一些其他子句来获取最小值（我不知道这是否可能）。否则，它可能使用子查询，但是我遇到了与group有关的问题...我知道它

$ p $ SELECT AVG（MIN（termResultCurrent.position）），count（*）
FROM TermSubscription订阅，
竞争对手竞争者，
TermQuery termQueryPrev，IN（termQueryPrev.results）termResultPrev，
TermQuery termQueryCurrent，IN（termQueryCurrent.results）termResultCurrent
WHERE订阅。 account.id = 274
和competitor.id = 379
AND termQueryPrev.term = subscription.term
AND te rmQueryCurrent.term = subscription.term
AND termQueryCurrent.provider.id = 1
AND termQueryCurrent.provider.id = termQueryPrev.provider.id
AND termQueryPrev.queryDate.yearWeek = YEARWEEK（FROM_DAYS（ TO_DAYS（'2013-01-01'） - 7），1）
AND termQueryCurrent.queryDate.yearWeek = YEARWEEK（'2013-01-01'，1）
AND termResultPrev.url.hostname MEMBER competitor.domains
AND termResultCurrent.url.hostname MEMBER competitor.domains
group by subscription.term.id


  select avg（s.minimum）
 
 ）从
（SELECT MIN（termResultCurrent.position）最小
 FROM TermSubscription订阅，... 
 WHERE subscription.account.id = 274和... 
按订阅组。 term.id 
）s; 
  

My problem is that I cannot use those two aggregate functions together like in the query: AVG(MIN(, First I need to obtain the minimum value from that entity termResultCurrent and after that the average of all the minimum values. Probably it must be changed the group by and include some other clause to fetch just the minimum value (I don't know if that is possible). Otherwise, it might be using subqueries but I had problems with the group by... I know it is quite challenging but I do appreciate your help.

SELECT  AVG(MIN(termResultCurrent.position)), count(*)
FROM TermSubscription subscription,
     Competitor competitor,     
     TermQuery termQueryPrev, IN(termQueryPrev.results) termResultPrev,     
     TermQuery termQueryCurrent,IN(termQueryCurrent.results) termResultCurrent     
 WHERE subscription.account.id= 274
      and competitor.id = 379 
      AND termQueryPrev.term=subscription.term      
      AND termQueryCurrent.term=subscription.term       
      AND termQueryCurrent.provider.id= 1  
      AND termQueryCurrent.provider.id=termQueryPrev.provider.id       
      AND termQueryPrev.queryDate.yearWeek = YEARWEEK(FROM_DAYS(TO_DAYS('2013-01-01') - 7), 1)       
      AND termQueryCurrent.queryDate.yearWeek = YEARWEEK('2013-01-01',1)      
      AND termResultPrev.url.hostname   MEMBER competitor.domains       
      AND termResultCurrent.url.hostname   MEMBER competitor.domains  
 group by  subscription.term.id

解决方案

Use this:

 select avg(s.minimum) from
    (SELECT MIN(termResultCurrent.position) minimum
        FROM TermSubscription subscription, ...
        WHERE subscription.account.id= 274 and ...  
        group by  subscription.term.id
    ) s;

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