MySql - 连接表，计算 "distict"id 出现次数并计算平均值

Question

我需要一些帮助，请加入两个查询。

我有两个(或更多)表(结构相同)，如下:

table_a
+-----------------+-------+
| id |     date   | value |
+----+------------+-------+
|  1 | 01-05-2018 |    8  |
|  1 | 03-05-2018 |    1  |
|  1 | 04-05-2018 |    1  |
|  1 | 05-05-2018 |    2  |
+----+------------+-------+

table_b
+-----------------+-------+
| id |     date   | value |
+----+------------+-------+
|  2 | 01-05-2018 |   10  |
|  2 | 03-05-2018 |    5  |
|  2 | 04-05-2018 |   10  |
|  2 | 05-05-2018 |    6  |
+----+------------+-------+

我正在“加入”它们并逐行、逐日计算 AVG 值。这就是我不想要我的结果的方式:

Result
+-----------------+--------+--------+
| cnt | id |   date        | average|
+----+------------+--------+--------+
|  1   | 1  | 01-05-2018   |   8    | <-- (8)
|  2   | 1  | 03-05-2018   |  4.5   | <-- (8+1=9/2)
|  3   | 1  | 04-05-2018   |  3.3   | <-- (8+1+1=10/3)
|  4   | 1  | 05-05-2018   |    3   | <-- (8+1+1+2=12/4)
|  5   | 2  | 01-05-2018   |  4.4   | <-- (8+1+1+2+10=22/5)
|  6   | 2  | 03-05-2018   |  4.5   | <-- (8+1+1+2+10+5=27/6)
|  7   | 2  | 04-05-2018   |  5.2   | <-- (8+1+1+2+10+5+10=37/7)
|  8   | 2  | 05-05-2018   |  5.3   | <-- (8+1+1+2+10+5+10+6=37/8)
+------+----+--------------+--------+

它应该是这样的:

Result
+-----------------+-------+---------+
| cnt | id |   date       | average |
+----+------------+-------+---------+
|  1   | 1  | 01-05-2018   |   8    | <-- (8)
|  2   | 1  | 03-05-2018   |  4.5   | <-- (8+1=9/2)
|  3   | 1  | 04-05-2018   |  3.3   | <-- (8+1+1=10/3)
|  4   | 1  | 05-05-2018   |    3   | <-- (8+1+1+2=12/4)
|  1   | 2  | 01-05-2018   |   10   | <-- (10)
|  2   | 2  | 03-05-2018   |  7.5   | <-- (10+5=15/2)
|  3   | 2  | 04-05-2018   |  8.3   | <-- (10+5+10=25/3)
|  4   | 2  | 05-05-2018   |  7.7   | <-- (10+5+10+6=25/4)
+------+----+--------------+--------+

我正在研究这个查询:

select t.cnt,t.id,t.date, (average / cnt) as average 
from (select t.*, (@rn := @rn + 1) as cnt, (@s := @s + value) as average 
      from table_a t 
      cross join (select @rn := 0, @s := 0) params) t 
 UNION ALL 
 select t.cnt,t.id,t.date, (average / cnt) as average 
 from (select t.*, (@rn := @rn + 1) as cnt, (@s := @s + value) as average
       from table_b t 
       cross join (select @rn := 0, @s := 0) params) t 

基本上，我需要连接表，计算不同的 id 出现次数并计算值的平均值，逐行(日期)为每个 id。请问我该如何调整我的查询？提前致谢。

Answer 1

您的查询似乎可以完成您想要的工作。只需为两个子查询使用不同的变量名:

select t.cnt,t.id,t.date, (average / cnt) as average 
from (select t.*, (@rn1 := @rn1 + 1) as cnt, (@s1 := @s1 + value) as average 
      from table_a t 
      cross join (select @rn1 := 0, @s1 := 0) params) t 
 UNION ALL 
 select t.cnt,t.id,t.date, (average / cnt) as average 
 from (select t.*, (@rn2 := @rn2 + 1) as cnt, (@s2 := @s2 + value) as average
       from table_b t 
       cross join (select @rn2 := 0, @s2 := 0) params) t 

Demo here