代码之家 › 专栏 › 技术社区 › guyts

按分组以查找SQL中特定索引的平均差异

group-by sql mysql

guyts · 技术社区 · 6 年前

我有下表:

person_index   score   year
3              76      2003
3              86      2004
3              86      2005
3              87      2006
4              55      2005
4              91      2006

我想 按人分组\指数,取连续年平均得分差 ,这样,我最终每人一行,表示平均增加/减少:

person_index   avg(score_diff)   
3              3.67      
4              36

因此,对于指数为3的人,三年来有变化,一个是10分,一个是0分,一个是1分。因此,他们的平均得分差为3.67。

编辑:为了澄清,分数也可以降低。年份不一定是连续的(一个人可能在某一年得不到分数,所以可能是2013年,然后是2015年)。

2 回复 | 直到 6 年前

Lukasz Szozda 6 年前

最简单的方法是 LAG (MySQL 8 +):

WITH cte AS (
  SELECT *, score - LAG(score) OVER(PARTITION BY person_index ORDER BY year) AS diff
  FROM tab
)
SELECT person_index, AVG(diff) AS avg_diff
FROM cte
GROUP BY person_index;

db<>fiddle demo

输出:

+---------------+----------+
| person_index  | avg_diff |
+---------------+----------+
|            3  |   3.6667 |
|            4  |  36.0000 |
+---------------+----------+

guyts 6 年前

如果分数只会增加——就像你的例子一样——你可以简单地做到:

select person_id,
       ( max(score) - min(score) ) / nullif(max(year) - min(year) - 1, 0)
from t
group by person_id;

如果他们不只是增加,这是有点棘手,因为你必须计算第一和最后的分数:

select t.person_id,
       (tmax.score - tmin.score) / nullif(tmax.year - tmin.year - 1, 0)
from (select t.person_id, min(year) as miny, max(year) as maxy
      from t
      group by person_id
     ) p join
     t tmin
     on tmin.person_id = p.person_id and tmin.year = p.miny join
     t tmax
     on tmax.person_id = p.person_id and tmax.year = p.maxy join

推荐文章