drop table Products;
CREATE TABLE Products
(products_id         CHAR(4) NOT NULL,
 products_name       VARCHAR(100) NOT NULL,
 products_type       VARCHAR(32) NOT NULL,
 sale_price          INTEGER ,
 incoming_price      INTEGER ,
 registration_date   DATE ,
     PRIMARY KEY (products_id));

INSERT INTO Products VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Products VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Products VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Products VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Products VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Products VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Products VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Products VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');


SELECT products_id, products_name, sale_price,
       AVG (sale_price) OVER (ORDER BY products_id                         
                              ROWS BETWEEN 2 PRECEDING                   
                                       AND CURRENT ROW) AS moving_avg    
  FROM Products;


SELECT products_id, products_name, sale_price,
       AVG(sale_price) OVER W AS moving_avg
  FROM Products
WINDOW W AS (ORDER BY products_id
                 ROWS BETWEEN 2 PRECEDING 
                          AND CURRENT ROW);

SELECT products_id, products_name, sale_price,
       AVG(sale_price)   OVER W AS moving_avg,
       SUM(sale_price)   OVER W AS moving_sum,
       COUNT(sale_price) OVER W AS moving_count,
       MAX(sale_price)   OVER W AS moving_max
  FROM Products
WINDOW W AS (ORDER BY products_id
                 ROWS BETWEEN 2 PRECEDING 
                          AND CURRENT ROW);



# 例题
CREATE TABLE LoadSample
(sample_date   DATE PRIMARY KEY,
 load_val      INTEGER NOT NULL);

INSERT INTO LoadSample VALUES('2018-02-01',   1024);
INSERT INTO LoadSample VALUES('2018-02-02',   2366);
INSERT INTO LoadSample VALUES('2018-02-05',   2366);
INSERT INTO LoadSample VALUES('2018-02-07',    985);
INSERT INTO LoadSample VALUES('2018-02-08',    780);
INSERT INTO LoadSample VALUES('2018-02-12',   1000);

# 计算各行的  过去最临近的日期
# 注意理解   BETWEEN 1 PRECEDING AND 1 PRECEDING  表示帧的范围为  上一行  至  上一行
#   可以同时使用PRECEDING   和 FOLLOWING 扩大帧的范围
SELECT sample_date AS cur_date,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date
  FROM LoadSample;


SELECT sample_date AS cur_date,
       load_val AS cur_load,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_date,
       MIN(load_val)
          OVER (ORDER BY sample_date ASC
                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_load
  FROM LoadSample;


SELECT sample_date AS cur_date,
       load_val AS cur_load,
       MIN(sample_date) OVER W AS latest_date,
       MIN(load_val) OVER W AS latest_load
  FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC
              ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING);



# 计算各行的  下一个的日期
SELECT sample_date AS cur_date,
       MIN(sample_date)
           OVER (ORDER BY sample_date ASC
               ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS latest_date
FROM LoadSample;





SELECT sample_date AS cur_date,
       load_val AS cur_load,
       MIN(sample_date) OVER W AS next_date,
       MIN(load_val) OVER W AS next_load
  FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC
                 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING);


-- 执行结果与使用MIN函数时相同
SELECT sample_date AS cur_date,
       load_val AS cur_load,
       MAX(sample_date) OVER W AS latest_date,
       MAX(load_val) OVER W AS latest_load
  FROM LoadSample
WINDOW W AS (ORDER BY sample_date
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING);

# 获取前一天数据
SELECT sample_date AS cur_date,
       load_val AS cur_load,
       MIN(sample_date)
          OVER (ORDER BY sample_date
              RANGE BETWEEN interval '1' day PRECEDING
                          AND interval '1' day PRECEDING
                ) AS day1_before,
       MIN(load_val)
          OVER (ORDER BY sample_date
              RANGE BETWEEN interval '1' day PRECEDING
                          AND interval '1' day PRECEDING
                ) AS load_day1_before
  FROM LoadSample;

# 使用LAG()         LEAD()
SELECT sample_date AS cur_date,
       load_val AS cur_load,
       MIN(sample_date)
           OVER (ORDER BY sample_date ASC
               RANGE BETWEEN interval '1' day PRECEDING
                   AND interval '1' day PRECEDING
               ) AS day1_before
FROM LoadSample;


SELECT sample_date AS cur_date,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                 ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
       MIN(sample_date)
          OVER (ORDER BY sample_date ASC
                 ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
  FROM LoadSample;



# 练习题
# 没有定义窗口  则为普通的聚合函数
SELECT server, sample_date,
  SUM(load_val) OVER () AS sum_load
 FROM ServerLoadSample;


#按照server进行划分
SELECT server, sample_date,
       SUM(load_val) OVER (PARTITION BY server) AS sum_load
  FROM ServerLoadSample;


# 按照服务器进行划分   查找前一天的采样数据
select server,
    sample_date,
    load_val,
    LAG(load_val,1) over (PARTITION BY server order by sample_date desc) as pre_sample_date
from ServerLoadSample;


# 查找前一天的采样数据
select server,
       sample_date,
       load_val,
       LAG(load_val,1) over (order by sample_date desc) as pre_sample_date
from ServerLoadSample;

# partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录。
# partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。
# partition by与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

# 常见函数：

# https://wangxiaoxi.cn/posts/sql-partition/

# row_number() over(partition by ... order by ...)
# rank() over(partition by ... order by ...)
# dense_rank() over(partition by ... order by ...)
# count() over(partition by ... order by ...) 求分组后的总数
# max() over(partition by ... order by ...) 求分组后的最大值
# min() over(partition by ... order by ...) 求分组后的最小值
# sum() over(partition by ... order by ...) 求分组后的总和
# avg() over(partition by ... order by ...) 求分组后的平均值
# first_value() over(partition by ... order by ...) 求分组后的第一个值
# last_value() over(partition by ... order by ...) 求分组后的最后一个值
# lag() over(partition by ... order by ...) 取出分组后前n行数据
# lead() over(partition by ... order by ...) 取出分组后后n行数据