-- 增加、减少、没有变化
CREATE TABLE Sales
(year INTEGER NOT NULL , 
 sale INTEGER NOT NULL ,
 PRIMARY KEY (year));

INSERT INTO Sales VALUES (1990, 50);
INSERT INTO Sales VALUES (1991, 51);
INSERT INTO Sales VALUES (1992, 52);
INSERT INTO Sales VALUES (1993, 52);
INSERT INTO Sales VALUES (1994, 50);
INSERT INTO Sales VALUES (1995, 50);
INSERT INTO Sales VALUES (1996, 49);
INSERT INTO Sales VALUES (1997, 55);

-- 时间轴有间断时：和过去最临近的时间进行比较
CREATE TABLE Sales2
(year INTEGER NOT NULL , 
 sale INTEGER NOT NULL , 
 PRIMARY KEY (year));

INSERT INTO Sales2 VALUES (1990, 50);
INSERT INTO Sales2 VALUES (1992, 50);
INSERT INTO Sales2 VALUES (1993, 52);
INSERT INTO Sales2 VALUES (1994, 55);
INSERT INTO Sales2 VALUES (1997, 55);


-- 求与上一年营业额一样的年份(1)：使用关联子查询
SELECT year,sale
  FROM Sales S1
 WHERE sale = (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1)
 ORDER BY year;


-- 求与上一年营业额一样的年份(2)：使用窗口函数
SELECT year, current_sale
  FROM (SELECT year,
               sale AS current_sale,
               SUM(sale) OVER (ORDER BY year
                               RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING) AS pre_sale
          FROM Sales) TMP
 WHERE current_sale = pre_sale
 ORDER BY year;




SELECT year,
       sale AS current_sale,
       SUM(sale) OVER (ORDER BY year
                       RANGE BETWEEN 1 PRECEDING
                                 AND 1 PRECEDING) AS pre_sale
  FROM Sales;


-- 求出是增加了还是减少了，或是没有变化(1)：使用关联子查询
SELECT year, current_sale AS sale,
       CASE WHEN current_sale = pre_sale
              THEN '→'
            WHEN current_sale > pre_sale
              THEN '↑'
            WHEN current_sale < pre_sale
              THEN '↓'
            ELSE '-' END AS var
  FROM (SELECT year,
               sale AS current_sale,
               (SELECT sale
                  FROM Sales S2
                 WHERE S2.year = S1.year - 1) AS pre_sale
          FROM Sales S1) TMP
 ORDER BY year;


-- 求出是增加了还是减少了，或是没有变化（2）：使用窗口函数
SELECT year, current_sale AS sale,
       CASE WHEN current_sale = pre_sale
             THEN '→'
            WHEN current_sale > pre_sale
             THEN '↑'
            WHEN current_sale < pre_sale
             THEN '↓'
            ELSE '-' END AS var
  FROM (SELECT year,
               sale AS current_sale,
               SUM(sale) OVER (ORDER BY year
                               RANGE BETWEEN 1 PRECEDING
                                         AND 1 PRECEDING) AS pre_sale
          FROM Sales) TMP
 ORDER BY year;




-- 查询与过去最临近的年份营业额相同的年份(1)：使用关联子查询
SELECT year, sale
  FROM Sales2 S1
 WHERE sale =
         (SELECT sale
            FROM Sales2 S2
           WHERE S2.year =
                  (SELECT MAX(year) -- 条件2：在满足条件1的年份中，年份最早的一个
                     FROM Sales2 S3
                    WHERE S1.year > S3.year)) -- 条件１：与该年份相比是过去的年份
 ORDER BY year;


-- 查询与过去最临近的年份营业额相同的年份(2)：使用窗口函数
SELECT year, current_sale
  FROM (SELECT year,
               sale AS current_sale,
               SUM(sale) OVER (ORDER BY year
                                ROWS BETWEEN 1 PRECEDING
                                         AND 1 PRECEDING) AS pre_sale
          FROM Sales2) TMP
         WHERE current_sale = pre_sale
 ORDER BY year;




-- 查询重叠的时间区间
CREATE TABLE Reservations
(reserver    VARCHAR(30) PRIMARY KEY,
 start_date  DATE  NOT NULL,
 end_date    DATE  NOT NULL);

INSERT INTO Reservations VALUES('木村', '2018-10-26', '2018-10-27');
INSERT INTO Reservations VALUES('荒木', '2018-10-28', '2018-10-31');
INSERT INTO Reservations VALUES('堀',   '2018-10-31', '2018-11-01');
INSERT INTO Reservations VALUES('山本', '2018-11-03', '2018-11-04');
INSERT INTO Reservations VALUES('内田', '2018-11-03', '2018-11-05');
INSERT INTO Reservations VALUES('水谷', '2018-11-06', '2018-11-06');

-- 山本的入住日期为4日时
DELETE FROM Reservations WHERE reserver = '山本';
INSERT INTO Reservations VALUES('山本', '2018-11-04', '2018-11-04');


-- 求重叠的住宿时间(1)：使用关联子查询
SELECT reserver, start_date, end_date
  FROM Reservations R1
 WHERE EXISTS
        (SELECT *
           FROM Reservations R2
          WHERE R1.reserver <> R2.reserver -- 与自己以外的客人进行比较
            AND ( R1.start_date BETWEEN R2.start_date AND R2.end_date
                   -- 条件(1)：自己的入住日期在他人的住宿期间内
               OR R1.end_date BETWEEN R2.start_date AND R2.end_date));
                   -- 条件(2)：自己的离店日期在他人的住宿期间内


-- 求重叠的住宿期间(2)：使用窗口函数
SELECT reserver, next_reserver
  FROM (SELECT reserver,
               start_date,
               end_date,
               MAX(start_date) OVER (ORDER BY start_date
                                      ROWS BETWEEN 1 FOLLOWING 
                                               AND 1 FOLLOWING) AS next_start_date,
               MAX(reserver)   OVER (ORDER BY start_date
                                      ROWS BETWEEN 1 FOLLOWING 
                                               AND 1 FOLLOWING) AS next_reserver
          FROM Reservations) TMP
 WHERE next_start_date BETWEEN start_date AND end_date;



-- 山本、内田、水谷3人的住宿时间重叠
DELETE FROM Reservations;
INSERT INTO Reservations VALUES('木村', '2018-10-26', '2018-10-27');
INSERT INTO Reservations VALUES('荒木', '2018-10-28', '2018-10-31');
INSERT INTO Reservations VALUES('堀',   '2018-10-31', '2018-11-01');
INSERT INTO Reservations VALUES('山本', '2018-11-03', '2018-11-04');
INSERT INTO Reservations VALUES('内田', '2018-11-03', '2018-11-05');
INSERT INTO Reservations VALUES('水谷', '2018-11-04', '2018-11-06');


-- 山本当日往返（关联子查询的结果中不显示内田）
DELETE FROM Reservations;
INSERT INTO Reservations VALUES('木村', '2018-10-26', '2018-10-27');
INSERT INTO Reservations VALUES('荒木', '2018-10-28', '2018-10-31');
INSERT INTO Reservations VALUES('堀',   '2018-10-31', '2018-11-01');
INSERT INTO Reservations VALUES('山本', '2018-11-04', '2018-11-04');
INSERT INTO Reservations VALUES('内田', '2018-11-03', '2018-11-05');
INSERT INTO Reservations VALUES('水谷', '2018-11-06', '2018-11-06');


-- 练习题1-7-1
CREATE TABLE Accounts
(prc_date DATE NOT NULL , 
 prc_amt  INTEGER NOT NULL , 
 PRIMARY KEY (prc_date)) ;

DELETE FROM Accounts;
INSERT INTO Accounts VALUES ('2018-10-26',  12000 );
INSERT INTO Accounts VALUES ('2018-10-28',   2500 );
INSERT INTO Accounts VALUES ('2018-10-31', -15000 );
INSERT INTO Accounts VALUES ('2018-11-03',  34000 );
INSERT INTO Accounts VALUES ('2018-11-04',  -5000 );
INSERT INTO Accounts VALUES ('2018-11-06',   7200 );
INSERT INTO Accounts VALUES ('2018-11-11',  11000 );