计算透视表内的标准偏差(calculate standard deviation inside pi

我有一个支点查询，看起来像下面，得到了count和AVG的作品就好了行，而不是标准偏差， STD 。

我如何可以修改下面的SQL来获取STD ？

SELECT mid                             as mID,
   round((x.qty_sum / x.qty_count), 5) as qtAVG,
   round(x.qty_stddev, 5)              as qtSTDDEV,
   x.qty_count                         as qtCOUNT,
   round((x.rel_sum / x.rel_count), 5) as relAVG,
   round(x.rel_stddev, 5)              as relSTDDEV,
   x.rel_count                         as relCOUNT,
FROM (SELECT mid,
         SUM(CASE WHEN (mt = "qt") THEN 1 ELSE 0 END)   as qty_count,
         SUM(CASE WHEN (mt = "qt") THEN rt ELSE 0 END)  as qty_sum,
         STD(CASE WHEN (mt = "qt") THEN rt ELSE 0 END)  as qty_stddev
         SUM(CASE WHEN (mt = "rel") THEN 1 ELSE 0 END)  as rel_count,
         SUM(CASE WHEN (mt = "rel") THEN rel ELSE 0 END) as rel_sum,
         STD(CASE WHEN (mt = "rel") THEN rel ELSE 0 END) as rel_stddev
  FROM t_r
  GROUP BY mid) x;

Answer 1:

我认为你唯一的问题是ELSE 0 。 你只是想NULL值，因为它们将被忽略：

SELECT mid                                 as mID,
       round((x.qty_sum / x.qty_count), 5) as qtAVG,
       round(x.qty_stddev, 5)              as qtSTDDEV,
       x.qty_count                         as qtCOUNT,
       round((x.rel_sum / x.rel_count), 5) as relAVG,
       round(x.rel_stddev, 5)              as relSTDDEV,
       x.rel_count                         as relCOUNT,
FROM (SELECT mid,
             SUM( mt = 'qt' )   as qty_count,
             SUM(CASE WHEN mt = 'qt' THEN rt END)  as qty_sum,
             STD(CASE WHEN mt = 'qt' THEN rt END)  as qty_stddev,
             SUM( mt = 'rel' ) as rel_count,
             SUM(CASE WHEN mt = 'rel' THEN rel END) as rel_sum,
             STD(CASE WHEN mt = 'rel' THEN rel END) as rel_stddev
      FROM t_r
      GROUP BY mid
     ) x;

注意其他一些变化：

• 我简化了逻辑计数，以除去CASE表达。 这将使用MySQL的扩展，它把布尔为数字与1为真， 0为假。
• 我替换单引号的双引号。 单引号是字符串的标准分隔符。
• 我删除了ELSE条款。 聚合函数忽略NULL值，所以这应该解决您的问题。

Answer 2:

好像你正试图智取的MySQL与子查询。 至于担心，你并不需要复杂的这种额外的水平，只使用一个简单聚合的查询与过滤器具有MT =“QT”记录的WHERE子句。

SELECT 
    mid as mID,
    ROUND(AVG(rt), 5) as qtAVG,
    ROUND(STD(rt), 5) as qtSTDDEV,
    COUNT(*) as qtCOUNT,
FROM t_r
WHERE mt =  "qt"
GROUP BY mt

文章来源: calculate standard deviation inside pivot table