sql - 区域和城市的逐月增长

我有一张表格，格式如下-

region  country   revenue  datetime
apac     japan     1000    2002-01-01
apac     china     2000    2001-02-01
apac     japan     1500    2001-01-01
apac     china     3000    2002-02-01
emea     germany   4000    2003-04-01
emea     france    5000    2004-09-01
emea     germany   2000    2004-04-01
emea     france    6000    2005-09-01

我正在写一个查询，它将给出每个地区和县的逐月增长
我正在使用postgres中的datetrunc（'month'，datetime）和lag（）函数来获取上一个值，但无法按地区和国家分组-当我这样做时，表的粒度会发生变化，并且无法获得所需的输出。
以下是查询-

select  region,
country,
  date_trunc('month',datetime) as datum,
  sum(revenue),
         round(1.0-(cast(revenue as NUMERIC)/lag(revenue,1) OVER())*100,1)
from mytable
  GROUP BY datum,region,country,revenue
ORDER BY datum ASC;

最佳答案

你需要：

select region, country,
       date_trunc('month', datetime) as datum,
       sum(revenue),
       round(1.0-(cast(revenue as NUMERIC)/lag(sum(revenue), 1) over (partition by region, country order by date_trunc('month',datetime)))*100,1)
from mytable
group by datum, region, country
order by datum asc;

而且，partition by不应该在revenue中。
或者，这更容易通过首先进行聚合来表示：

select region, county, datum, rev,
       lag(rev) over (partition by region, country order by datum) as prev_rev
from (select region, country,
             date_trunc('month', datetime) as datum,
             sum(revenue) as rev
      from mytable
      group by datum, region, country
     ) drc
order by datum asc;