两个具有相同架构的bigjoin表

+---------+--------+----------------------+
|  value  |  city  |   timestamp          |
+---------+--------+----------------------+
| 50      |  LA    |  2019-02-6 03:05pm   |
| 163     |  NYC   |  2019-02-5 03:06pm   |
| 681     |  SF    |  2019-02-4 06:41pm   |
| 35      |  LA    |  2019-02-3 05:12pm   |
+---------+--------+----------------------+

第一张表是定期付款,第二张是收费表。我想加入表格并按如下方式分组:

+------------+--------+----------+--------------+
|  regular   |  fees  |   city   |  timestamp   |
+------------+--------+----------+--------------+
| 50         | 20     | LA       |  2019-02-6   |
| 163        | NULL   | NYC      |  2019-02-5   |
| 681        | ..     | SF       |  2019-02-4   |
| 35         | ..     | LA       |  2019-02-3   |
+------------+--------+----------+--------------+

SELECT t1.city, regular, fees, t1.day
FROM
(
  SELECT city, SUM(value) AS regular, FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP(timestamp)) as day
  FROM `payments`
  GROUP BY day, city
) t1
FULL JOIN (
  SELECT city, SUM(value) AS fees, FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP(timestamp)) as day
  FROM `fees`
  GROUP BY day, city
) t2
ON t1.day = t2.day
ORDER BY t1.day DESC

这会生成正确的输出模式,但不会正确地合计费用:

+------------+--------+----------+--------------+
|  regular   |  fees  |   city   |  timestamp   |
+------------+--------+----------+--------------+
| 26500      | 6300   | LA       |  2019-02-6   |
| 26500      | 8500   | LA       |  2019-02-6   |
| 26500      | 1000   | LA       |  2019-02-6   |
+------------+--------+----------+--------------+

正如你所看到的,我得到的是同一天和城市不同的费用价值。你知道我做错什么了吗?