-- 创建Product表	
CREATE TABLE Product(
	product_id		CHAR(4)			NOT NULL,
	product_name 	VARCHAR(100)	NOT NULL,
	product_type	VARCHAR(32)		NOT NULL,
	sale_price		INTEGER			DEFAULT 0,
	purchase_price	INTEGER,
	regist_date		DATE,
	PRIMARY KEY(product_id)
);
-- 插入数据
BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;

<窗口函数> OVER ([PARTITION BY <列清单>] 
 		ORDER BY <排序用列清单>)

SELECT product_name, product_type, sale_price,
		RANK () OVER (PARTITION BY product_type
					  ORDER BY sale_price)  AS ranking
	FROM Product;

SELECT product_name, product_type, sale_price,
		RANK () OVER (ORDER BY sale_price) AS ranking, /*存在相同的位次，跳过之后的位次*/
		DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking, /*即使存在相同位次的记录，也不会跳过之后的位次*/
		ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num /*唯一的连续位次*/
	FROM Product;

-- 移动窗口汇总计算，计算最近3行的和
SELECT product_id, product_name, sale_price,
 		SUM (sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS current_sum
-- 将当前记录的前后行作为汇总对象
SELECT product_id, product_name, sale_price,
 		SUM (sale_price) OVER (ORDER BY product_id ROWS
                           BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS current_sum
 	FROM Product;