sql - How to lag() the previous data of specific category only?

Question

Sample date:

Date          Type    Revenue
2019/01/01    A       100 
2019/02/01    A       200
2019/03/01    A       200

2019/01/01    B       100
2019/02/01    B       50

2019/01/01    C       500
2019/02/01    C       600

I want to know how many percentage the revenue increase or decrease comparing to the last month. Here is my sample code

select to_date(date, 'yyyy-mm') as date, type, sum(revenue) as revenue
       lag(revenue, 1) over (order by service_type, date) as last_month
       round(((revenue - last_month::float)/last_month)::float*100,2) as percentage_growth

from #a
group by 1,2
order by 2,1;

The result of my code

Date          Type    Revenue     Last_month    Percentage_growth
2019/01/01    A       100         None          None
2019/02/01    A       200         100           100%
2019/03/01    A       200         200           100%

2019/01/01    B       100         200           -100%  --Here is the matter
2019/02/01    B       50          100           -50%

2019/01/01    C       500         50            900%   --Here is the matter
2019/02/01    C       600         500           20%

As you already seen, in the new type, lag() function still fetch the previous revenue of different type.

How to deal with it many thanks.

标签： sqlpostgresqlwindow-functions

Answer 1

您似乎正在联合使用聚合函数和窗口函数。鉴于您想返回原始表，您应该在这里只使用窗口函数。这是您的查询的工作版本：

WITH cte AS (
    SELECT *,
        LAG(revenue, 1) OVER (ORDER BY Type, Date) AS last_month
    FROM a
)

SELECT *,
    100.0 * (revenue - COALESCE(last_month, revenue)) / COALESCE(last_month, revenue) AS pct_growth
FROM cte;

演示