时间戳

首页 > 解决方案 > Oracle SQL: How can I sum every x number of subsequent rows for each row

sql - Oracle SQL: How can I sum every x number of subsequent rows for each row

问题描述

I have a data table that looks like this:

|Contract Date | Settlement_Prcie |
|--------------|------------------|
| 01/10/2020   |         50       |
|--------------|------------------|
| 01/11/2020   |         10       |
|--------------|------------------|
| 01/01/2021   |         20       |
|--------------|------------------|
| 01/02/2021   |         30       |
|--------------|------------------|
| 01/03/2021   |         50       |
|--------------|------------------|

I would like to write a query that sums every two rows beneath ... For example, On the first row with contract date 01/10/2020, the sum column would add 10 and 20 to give a result of 30. The next row, the sum column would add 20 and 30 to give 40 and so on. The resulting table of results would look like this:

|Contract Date | Settlement_Prcie | Sum Column |
|--------------|------------------|------------|
| 01/10/2020   |         50       |     30
|--------------|------------------|------------|
| 01/11/2020   |         10       |     50
|--------------|------------------|------------|
| 01/01/2021   |         20       |     80
|--------------|------------------|------------|
| 01/02/2021   |         30       |
|--------------|------------------|
| 01/03/2021   |         50       |
|--------------|------------------|

Could anyone please help me with the query to do this not just for 2 subsequent rows but for x subsequent rows.

So far I had tried using a SUM (Settlement_Price) Over (order by Contract_date Rows between 3 preceding and current row) - Current row of course was not ok, but that is as far as I had gone.

标签： sqloracleanalytic-functions

解决方案

您可以使用SUM分析功能：

SELECT contract_date,
       settlement_price,
       CASE COUNT(*) OVER (
              ORDER BY contract_date ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
            )
       WHEN 2
       THEN SUM( settlement_price ) OVER (
              ORDER BY contract_date ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
            )
       END AS sum_column
FROM   table_name;

或者您可以使用LEAD：

SELECT contract_date,
       settlement_price,
       LEAD( settlement_price, 1 , NULL ) OVER ( ORDER BY contract_date )
         + LEAD( settlement_price, 2 , NULL ) OVER ( ORDER BY contract_date )
         AS sum_column
FROM   table_name;

因此，对于测试数据：

CREATE TABLE table_name ( contract_date, settlement_price ) AS
SELECT DATE '2020-10-01', 50 FROM DUAL UNION ALL
SELECT DATE '2020-11-01', 10 FROM DUAL UNION ALL
SELECT DATE '2020-12-01', 20 FROM DUAL UNION ALL
SELECT DATE '2021-01-01', 30 FROM DUAL UNION ALL
SELECT DATE '2021-02-01', 50 FROM DUAL;

两个查询输出：

合同日期 | 结算价格 | SUM_COLUMN
:------------ | ---------------: | ---------:
01-OCT-20 | 50 | 30
20 年 11 月 1 日 | 10 | 50
20 年 12 月 1 日 | 20 | 80
21 年 1 月 1 日 | 30 |       空
01-FEB-21 | 50 |       无效的

db<>在这里摆弄

推荐阅读