计算每个{国家，城市，年，月}组的运行总计

Question

我需要通过两个表的连接来计算每个国家、城市、年份和月份的运行总数。包含所有数据的查询是这样的：

编辑：与TD_MONTH表的连接操作不是必需的。

SELECT costs.CITY,
         costs.COUNTRY,
         months.ID,
         costs.ID_YEAR,
         SUM (costs.AMOUNT) AMOUNT
    FROM    TH_COSTS costs
         JOIN
            TD_MONTH months
         ON (costs.ID_MES = months.YEARMONTH)
GROUP BY costs.COUNTRY,
         costs.CITY,
         costs.ID_YEAR,
         months.ID
ORDER BY costs.COUNTRY,
         costs.CITY,
         costs.ID_YEAR,
         months.ID;

此网格上的结果：

+---------+--------+-------+------+-----------+
| Country |  City  | Month | Year |  Amount   |
+---------+--------+-------+------+-----------+
| Spain   | Madrid |     4 | 2014 | 396061,7  |
| Spain   | Madrid |     5 | 2014 | 315725,25 |
| Spain   | Madrid |     6 | 2014 | 16390     |
| Spain   | Madrid |     7 | 2014 | 85296     |
| Spain   | Madrid |     8 | 2014 | 10443,25  |
| Spain   | Madrid |    12 | 2014 | 17500     |
| Spain   | Madrid |     1 | 2015 | 353566    |
| Spain   | Madrid |     2 | 2015 | 382729    |
| Spain   | Madrid |     3 | 2015 | 261026    |
| Spain   | Madrid |     4 | 2015 | 304258    |
| Spain   | Madrid |     5 | 2015 | 259893    |
| Spain   | Madrid |     6 | 2015 | 217296    |
| Spain   | Madrid |     7 | 2015 | 461769    |
| Spain   | Madrid |     8 | 2015 | 456990,5  |
| Spain   | Madrid |     9 | 2015 | 219020    |
| Spain   | Madrid |    10 | 2015 | 242796    |
| Spain   | Madrid |    11 | 2015 | 134197    |
| Spain   | Madrid |    12 | 2015 | 91901,9   |
+---------+--------+-------+------+-----------+

从上面的一个中，我需要下一个结果：

+---------+--------+-------+------+-----------+
| Country |  City  | Month | Year |  Amount   |
+---------+--------+-------+------+-----------+
| Spain   | Madrid |     1 | 2014 | 0         |
| Spain   | Madrid |     2 | 2014 | 0         |
| Spain   | Madrid |     3 | 2014 | 0         |
| Spain   | Madrid |     4 | 2014 | 396061,7  |
| Spain   | Madrid |     5 | 2014 | 711786,95 |
| Spain   | Madrid |     6 | 2014 | 728176,95 |
| Spain   | Madrid |     7 | 2014 | 813472,95 |
| Spain   | Madrid |     8 | 2014 | 823916,2  |
| Spain   | Madrid |     9 | 2014 | 841416,2  |
| Spain   | Madrid |    10 | 2014 | 1194982,2 |
| Spain   | Madrid |    11 | 2014 | 1577711,2 |
| Spain   | Madrid |    12 | 2014 | 1595211,2 |
| Spain   | Madrid |     1 | 2015 | 353566    |
| Spain   | Madrid |     2 | 2015 | 736295    |
| Spain   | Madrid |     3 | 2015 | 997321    |
| Spain   | Madrid |     4 | 2015 | 1301579   |
| Spain   | Madrid |     5 | 2015 | 1561472   |
| Spain   | Madrid |     6 | 2015 | 1778768   |
| Spain   | Madrid |     7 | 2015 | 2240537   |
| Spain   | Madrid |     8 | 2015 | 2697527,5 |
| Spain   | Madrid |     9 | 2015 | 2916547,5 |
| Spain   | Madrid |    10 | 2015 | 3159343,5 |
| Spain   | Madrid |    11 | 2015 | 3293540,5 |
| Spain   | Madrid |    12 | 2015 | 3385442,4 |
+---------+--------+-------+------+-----------+

编辑：起始表上的字段：

+----------+----------+--------+--------+--------+--------+-----------+---------+------+
| AMOUNT_1 | AMOUNT_2 | STREET | CITY 1 | MONTH  | CITY 2 | CONTINENT | COUNTRY | YEAR |
+----------+----------+--------+--------+--------+--------+-----------+---------+------+

首先想到的是使用四个嵌套循环，但这是一个非常耗时的解决方案。

有什么想法吗？

Answer 1

您可以尝试如下方法：

with test(Country, City, Year, Month,amount) as
(
  select 'Spain', 'Madrid', 2014, 4, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 5, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 6, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 7, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 8, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 9, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 10, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 11, 1 from dual union all
  select 'Spain', 'Madrid', 2014, 12, 1 from dual union all
  select 'Spain', 'Madrid', 2015, 1, 1 from dual union all
  select 'Spain', 'Madrid', 2015, 2, 1 from dual union all
  select 'UK', 'London', 2014, 7, 10 from dual union all
  select 'UK', 'London', 2014, 8, 10 from dual union all
  select 'UK', 'London', 2014, 9, 10 from dual union all
  select 'UK', 'London', 2014, 10, 10 from dual union all
  select 'UK', 'London', 2014, 11, 10 from dual union all
  select 'UK', 'London', 2014, 12, 10 from dual union all
  select 'UK', 'London', 2015, 1, 10 from dual union all
  select 'UK', 'London', 2015, 2, 10 from dual 
)
select country, city, year, month,
       sum(amount) over ( partition by country, city, year order by month)
from test

它计算运行总计，按月排序，按年份、国家和城市分组