php - 如何从 mysql 列中减去最高值和最低值得到平均值

Question

我被难住了。我想从“帖子”中获得平均“点击次数”，但丢弃最高值，该值通常很高，以至于当我想显示点击次数高于平均值的所有帖子时，它会扭曲阈值。出于同样的原因，我也想丢弃最低值。我的代码是:

//get the sum of the clicks from all posts    

$gettotalsum = mysql_query("SELECT sum(clicks) AS click_total FROM posts WHERE clicks > '0' "); 

$clicksum = @mysql_fetch_array($gettotalsum);


//get the number of posts that have clicks                      
$postcount = mysql_query("SELECT postid FROM posts WHERE isactive = 'y' AND clicks > '0' "); 

$clickcount = mysql_num_rows($postcount);   

我怀疑上面的两个查询是我需要修改以过滤最高和最低点击值的内容 - 但是如何呢？

下面是我的其余代码:

//multiply to get the average, then round it off
$average = $clicksum['clicks'] / $clickcount;
$average = round($average); 

//display posts that are equal to or above average

$getposts = mysql_query("SELECT postid, posturl, posttitle, clicks FROM posts WHERE clicks >= $average order by clicks DESC");

    while ($aboveaverageposts = @mysql_fetch_array($getposts)) {

    //code to format output goes here

    }

我确实怀疑所有这些代码都可以简化，但现在最重要的是我正在寻找过滤最高值和最低值的方法。

提前致谢。

Answer 1

要获得平均值减去最高值和最低值(如您的标题所述)有点棘手。我希望它能工作:

SELECT (sum(clicks) - max(clicks) - min(clicks)) / (count(*) - 2) as avgclicks
FROM posts
WHERE clicks > '0'

不幸的是，您可能有具有最大值和最小值的重复记录，因此计数会被关闭。这种方法应该有效:

select avg(clicks) as avgclicks
from posts p cross join
     (select max(clicks) as maxclicks, min(clicks) as minclicks
      from posts p
      where clicks > 0
     ) minmax
where clicks > minmax.minclicks and clicks < minmax.maxclicks;

要获得点击次数“高于平均水平”的所有内容，您可以将其放在 where 子句中:

select p.*
from posts p
where clicks > (select avg(clicks) as avgclicks
                from posts p cross join
                     (select max(clicks) as maxclicks, min(clicks) as minclicks
                      from posts
                      where clicks > 0
                     ) minmax
                where clicks > minmax.minclicks and clicks < minmax.maxclicks
               )