Question

我有以下数据,我正试图从前一年获得利润:

WITH tbl (year, country, product, profit) AS (
   VALUES
     (2000,  Finland ,  Computer   , 1500)
   , (2000,  Finland ,  Phone      ,  100)
   , (2001,  Finland ,  Phone      ,   10)
   , (2000,  India   ,  Calculator ,   75)
   , (2000,  India   ,  Calculator ,   75)
   , (2000,  India   ,  Computer   , 1200)
   )
select country, year, profit
     , lag(profit) over (partition by country order by year)
from tbl;

┌─────────┬──────┬────────┬──────────────────────────┐
│ country ┆ year ┆ profit ┆ sum_profit_previous_year │
╞═════════╪══════╪════════╪══════════════════════════╡
│ India   ┆ 2000 ┆     75 ┆                          │
│ India   ┆ 2000 ┆     75 ┆                       75 │
│ India   ┆ 2000 ┆   1200 ┆                       75 │
│ Finland ┆ 2000 ┆   1500 ┆                          │
│ Finland ┆ 2000 ┆    100 ┆                     1500 │
│ Finland ┆ 2001 ┆     10 ┆                      100 │
└─────────┴──────┴────────┴──────────────────────────┘

However, this just seems to get the previous row, rather than what I want, which is to get the LAG of the profit value for the previous year for that country. The expected result should be:

┌─────────┬──────┬────────┬──────────────────────────┐
│ country ┆ year ┆ profit ┆ sum_profit_previous_year |
╞═════════╪══════╪════════╪══════════════════════════╡
│ India   ┆ 2000 ┆     75 ┆                          │
│ India   ┆ 2000 ┆     75 ┆                          │
│ India   ┆ 2000 ┆   1200 ┆                          │
│ Finland ┆ 2000 ┆   1500 ┆                          │
│ Finland ┆ 2000 ┆    100 ┆                          │
│ Finland ┆ 2001 ┆     10 ┆                   1600   │
└─────────┴──────┴────────┴──────────────────────────┘

由于芬兰-2001年是同一国家前一年唯一有记录的记录。正确的<代码>RANGE 这里有这样的条款? (为测试目的,对大公国或邮政单位都处以罚款)。

Answer 1

如果您确实想到所有un-aggregatedrows,并增加aggregated 上一年利润......

这里的是一种简单的窗口功能和习惯窗口框架:

SELECT country, year, profit
     , sum(profit) OVER (PARTITION BY country ORDER BY year
                         RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) AS sum_profit_previous_year
FROM   tbl
ORDER  BY country DESC, year;  -- optional?

rel=“nofollow noreferer”>fiddle

手册详情见。

友情链接