sql - 地区和城市的月环比增长

Question

我有以下格式的表格 -

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;

Answer 1

您需要分区依据:

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;

此外，revenue 不应在group by 中。

或者，首先进行聚合更容易表达:

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;