mysql - 加入结果并进行算术平均

Question

有两个表:“saldos_one”和“saldos_two”，两个表都有以下字段:CLIENTID、DATE、VALUE。

我必须从某些时间段内每个 CLIENTID 的算术平均值中得到 TOTAL 算术平均值。

让我们举几个例子:

> SELECT * FROM saldos_one;
+----------+------------+---------+
| CLIENTID | DATE       | VALUE   |
+----------+------------+---------+
| 1        | 2009-08-01 | 1000.00 |
| 1        | 2009-09-01 | 2000.00 |
| 1        | 2009-10-01 | 3000.00 |
| 2        | 2009-08-01 | 1000.00 |
| 2        | 2009-09-01 | 2000.00 |
| 2        | 2009-10-01 | 3000.00 |
| 3        | 2009-08-01 | 1000.00 |
| 3        | 2009-09-01 | 2000.00 |
| 3        | 2009-10-01 | 3000.00 |
| 4        | 2009-08-01 | 1000.00 |
| 4        | 2009-09-01 | 2000.00 |
| 4        | 2009-10-01 | 3000.00 |
+----------+------------+---------+

> SELECT * FROM saldos_two;
+----------+------------+---------+
| CLIENTID | DATE       | VALUE   |
+----------+------------+---------+
| 1        | 2009-08-01 | 10.00   |
| 1        | 2009-09-01 | 20.00   |
| 1        | 2009-10-01 | 30.00   |
| 2        | 2009-08-01 | 100.00  |
| 2        | 2009-09-01 | 200.00  |
| 2        | 2009-10-01 | 300.00  |
| 3        | 2009-08-01 | 1000.00 |
| 3        | 2009-09-01 | 2000.00 |
| 3        | 2009-10-01 | 3000.00 |
| 5        | 2009-08-01 | 1.00    |
| 5        | 2009-09-01 | 2.00    |
| 5        | 2009-10-01 | 3.00    |
+----------+------------+---------+

对每个表进行QUERY算术平均后:

> SELECT CLIENTID, TRUNCATE(SUM(VALUE)/COUNT(VALUE), 2) 
FROM saldos_one 
WHERE (DATE BETWEEN '2009-08-01' AND '2009-10-01')
GROUP BY CLIENTID;
+----------+---------+
| CLIENTID | VALUE   |
+----------+---------+
| 1        | 2000.00 |
| 2        | 2000.00 |
| 3        | 2000.00 |
| 4        | 2000.00 |
+----------+---------+

> SELECT CLIENTID, TRUNCATE(SUM(VALUE)/COUNT(VALUE), 2) 
FROM saldos_two
WHERE (DATE BETWEEN '2009-08-01' AND '2009-10-01')
GROUP BY CLIENTID;
+----------+---------+
| CLIENTID | VALUE   |
+----------+---------+
| 1        | 20.00   |
| 2        | 200.00  |
| 3        | 2000.00 |
| 5        | 2.00    |
+----------+---------+

我想从不同表的算术平均值中得到每个客户端的算术平均值，即:

+----------+---------+
| CLIENTID | VALUE   |
+----------+---------+
| 1        | 1010.00 | = 2000.00 + 20.00 / 2
| 2        | 200.00  | = 200.00 + 200.00 / 2
| 3        | 2000.00 | = 2000.00 + 2000.00 / 2
| 4        | 1000.00 | = 2000.00 + 0 / 2
| 5        | 1.00    | = 2.00 + 0 / 2
+----------+---------+

解决方案:查看@bvr 的回复

Answer 1

试试这个

SELECT CLIENTID,SUM(VALUE)/2 VALUE FROM
(
    SELECT CLIENTID, TRUNCATE(SUM(VALUE)/COUNT(VALUE), 2) VALUE 
    FROM saldos_one 
    WHERE (DATE BETWEEN '2009-08-01' AND '2009-10-01')
    GROUP BY CLIENTID;
    UNION ALL
    SELECT CLIENTID, TRUNCATE(SUM(VALUE)/COUNT(VALUE), 2) VALUE
    FROM saldos_two
    WHERE (DATE BETWEEN '2009-08-01' AND '2009-10-01')
) t
 GROUP BY CLIENTID