Question

I am trying to select data from multiple tables which uses an AVG in the WHERE clause.

SELECT company_metrics.*, companies.company_name, companies.permalink 
FROM company_metrics LEFT JOIN companies 
     ON companies.company_id = company_metrics.company_id
WHERE MONTH(date) =  04  AND YEAR(date) =  2011  
HAVING (SELECT avg(company_unique_visitors) 
        FROM (SELECT company_metrics.company_unique_visitors 
              FROM company_metrics  
              ORDER BY company_metrics.date DESC LIMIT 3)
        average ) > 2000  
ORDER BY date DESC

Example Data:

###Company Metrics#### Table
company_id       company_unique_visitors       date
-----------      -----------------------       ----
     604                    2054               2011-04-01
     604                    3444               2011-03-01
     604                    2122               2011-02-01
     604                    2144               2011-01-01
     604                    2001               2010-12-01
     602                    2011               2011-04-01
     602                    11                 2011-03-01
     602                    411                2011-02-01
     602                    611                2011-01-01
     602                    111                2010-12-01

EDIT I would like only the 3 latest numbers from company_unique_visitors AVG ed /EDIT

因此,询问会选择公司_id 604,但会选择公司_id 602,因为602吨的AVG公司比2000年多。

I need help writing the correct query to do as I have described. I can clarify if needed. Thanks for your help!

Answer 1

Ok based off of Jared Harding s answer and this post: Moving average - MySQL I was able to figure out the query.

SELECT metrics.*,companies.company_name,companies.permalink
FROM (SELECT company_id,AVG(company_unique_visitors) AS met_avg
      FROM company_metrics
     WHERE `date` BETWEEN DATE_SUB(NOW(), INTERVAL 4 MONTH) AND NOW()
  GROUP BY company_id HAVING met_avg>2000) AS metrics
LEFT JOIN companies ON companies.company_id=metrics.company_id

Thanks Jared for all your help!

Answer 2

你的问询有好几项问题。我并不完全清楚所有表格的结构,但我认为,我理解根据你提出的问题。你在所问问题中的第一个问题是,在你重新使用《哈维尔顿条款》的询问中,你不重新组合或使用任何聚合物。你们在一个分局使用总合,但现在就算的“HAVING”则没有意义。

我认为,你想由公司进行分类,然后是总合平均数,因此我把主要集团放在外围。你们也利用太多的nes问来完成似乎只是选择最近3项测量的简单任务。我把这种排序推向了初级阶段,因此,数据只是一次以合乎逻辑的方式挑选的。

如果没有举行进一步仪式,这里就提出固定问题:

SELECT limited_metrics.*, companies.company_name, companies.permalink,
       avg(limited_metrics.company_unique_visitors) AS avg_visitors 
FROM 
  (SELECT *
   FROM company_metrics
   ORDER BY company_metrics.date DESC LIMIT 3) AS limited_metrics
  LEFT JOIN companies 
  ON companies.company_id = limited_metrics.company_id
WHERE MONTH(limited_metrics.date) =  04  AND YEAR(limited_metrics.date) =  2011 
GROUP BY companies.company_id
HAVING avg_visitors > 2000

Example Data:

友情链接