用sqlite按行平均

小编典典

用sqlite按行平均

sql

我正在使用sqlite数据库。这些表是：

   ID_TABLE          POINTS_A_TABLE       POINTS_B_TABLE
  id   number       id_a   points_a      id_a   points_a
--------------     ----------------     ----------------
 smith   1         smith     11              ...
 gordon  22        gordon    11
 butch   3         butch     11
 sparrow 25        sparrow  
 white   76        white     46

等等。这些命令之后

select id, 
    points_a_table.points_a, points_b_table.points_a, points_c_table.points_a, points_d_table.points_a
from id_table
left join points_a_table on points_a_table.id_a = id_table.id
left join points_b_table on points_b_table.id_a = id_table.id
left join points_c_table on points_c_table.id_a = id_table.id
left join points_d_table on points_d_table.id_a = id_table.id
group by id

我得到了这个结果，在每一行上我都有一个id和与该id关联的点。

现在，我想按行获取 平均点， 并按平均降序排列。我想要的是：

sparrow| 56 [(44+68)/2]
white  | 41 ([46+67+11)/3]    
smith  | 33 [(11+25+65)/3]
butch  | 24 [(11+26+11)/3]
gordon | 11 [11/1]

我怎样才能做到这一点？谢谢。

阅读 222

2021-05-30

共1个答案

小编典典

如果将所有点表混在一起，则可以简单地计算每组的平均值：

SELECT id,
       avg(points_a)
FROM (SELECT id_a AS id, points_a FROM points_a_table
      UNION ALL
      SELECT id_a AS id, points_a FROM points_b_table
      UNION ALL
      SELECT id_a AS id, points_a FROM points_c_table
      UNION ALL
      SELECT id_a AS id, points_a FROM points_d_table)
GROUP BY id
ORDER BY avg(points_a) DESC;

2021-05-30