sql - 'date' 列中的多个日期。 'Value' 列中的数字。拉取每个特定 'Value'前后3天的 'Date'总和

Question

Oracle 中的 SQL:

数据集示例:

Search_date  Value
01-Feb-2016      5
02-Feb-2016      4
03-Feb-2016      9
04-Feb-2016     10 
05-Feb-2016     12
06-Feb-2016     10
07-Feb-2016      7

所以......如果我设置 search_date = '04-Feb-2016' 的 where 条件，它将返回以下内容:

搜索日期、Before_Period、After_Period

2016 年 2 月 4 日 18 (9 + 4 + 5)...、29 (10 + 12 + 7)

但我想为每个不同的日期拉这个

Answer 1

使用SUM() analytical (windowed) function :

Oracle 设置:

CREATE TABLE DataSet ( Search_date, Value ) AS
SELECT DATE '2016-02-01',  5 FROM DUAL UNION ALL
SELECT DATE '2016-02-02',  4 FROM DUAL UNION ALL
SELECT DATE '2016-02-03',  9 FROM DUAL UNION ALL
SELECT DATE '2016-02-04', 10 FROM DUAL UNION ALL
SELECT DATE '2016-02-05', 12 FROM DUAL UNION ALL
SELECT DATE '2016-02-06', 10 FROM DUAL UNION ALL
SELECT DATE '2016-02-07',  7 FROM DUAL;

查询:

SELECT  search_date,
        COALESCE(
          SUM( value ) OVER (
            ORDER BY Search_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
          ),
          0
        ) AS before_period,
        COALESCE(
          SUM( value ) OVER (
            ORDER BY Search_date
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
          ),
          0
        ) AS after_period
FROM    DataSet;

输出:

SEARCH_DATE BEFORE_PERIOD AFTER_PERIOD
----------- ------------- ------------
01-FEB-16               0           52 
02-FEB-16               5           48 
03-FEB-16               9           39 
04-FEB-16              18           29 
05-FEB-16              28           17 
06-FEB-16              40            7 
07-FEB-16              50            0 

如果您只需要前后 3 天，请将 UNBOUNDED 更改为 3。