获取每组的第一行，加上去年的相关行

Question

基本上我有以下数据集来自查询，'rn'是一个分区，为下一个需求添加行号:

WITH dataset AS (...)
-- gives
business_id | amount |  year |  month |   row_num
--------------------------------------------
1000          2000      2021      9       1
1000          1000      2021      9       2
1000          1500      2021      8       3
1000          1000      2020      9       4
1000          1000      2020      8       5
1043          4000      2021      8       1
1043          4500      2021      7       2
1043          4000      2021      6       3
1043          4100      2021      5       4
1043          4200      2021      4       5
1043          5000      2020      12      6
1043          5500      2020      11      7
1043          5600      2020      10      8
1043          5100      2020      9       9
1043          5300      2020      8       10
1043          5100      2020      7       11
1043          5000      2020      6       12
1139          4000      2021      9       1
1139          3000      2021      8       2
1139          2000      2021      7       3
1139          1000      2020      6       4

我的第一个要求是获得每个业务的最近一年/月份的值，我可以通过选择rn = 1来实现，如下所示:

WITH dataset AS (...)
SELECT * FROM dataset WHERE row_num = 1
-- gives
business_id | amount |  year |  month | row_num
-----------------------------------------------
1000          2000      2021      9       1
1043          4000      2021      8       1
1139          4000      2021      9       1

到目前为止都很好，第二个要求，我不确定如何做，是从去年获得相同的记录来比较今年，所以我需要这个数据集:

business_id | amount |  year |  month | row_num
-----------------------------------------------
1000          2000      2021      9       1
1000          1000      2020      9       4
1043          4000      2021      8       1
1043          5300      2020      8       10
1139          4000      2021      9       1

有什么办法吗?请注意，最后一条记录没有2020的值，这也是一种情况。

Answer 1

WITH dataset AS (...)
, base    AS (SELECT * FROM dataset WHERE row_num = 1)
TABLE base
UNION ALL
SELECT d.*
FROM   base b
JOIN   dataset d ON d.business_id = b.business_id
AND d.year = b.year - 1
AND d.month = b.month

我添加了另一个CTE，并在外部SELECT中引用它两次，使用UNION ALL。您可以添加任何您想要的ORDER BY，也许…

ORDER BY business_id, row_num

UNION ALL只添加去年存在的相关行。

很有可能，查询可以被优化。使用row_number()获取第一行通常不是性能之王。看到:

• 选择每个GROUP BY组的第一行?

关于TABLE简写:

• 是否有快捷键SELECT * FROM?