CREATE TABLE product_sale_all(
year string ,
product_product_name string ,
product_category string ,
sale_sum string
);
INSERT INTO product_sale_all VALUES ('2017', 'iPhone', '手机', 110000);
INSERT INTO product_sale_all VALUES ('2018', 'iPhone', '手机', 115000);
INSERT INTO product_sale_all VALUES ('2018', 'HuaWei', '手机', 138000);
INSERT INTO product_sale_all VALUES ('2019', 'HuaWei', '手机', 160000);
INSERT INTO product_sale_all VALUES ('2018', 'Canon', '相机', 100000);
INSERT INTO product_sale_all VALUES ('2019', 'Canon', '相机', 200000);
INSERT INTO product_sale_all VALUES ('2020', 'Canon', '相机', 180000);
INSERT INTO product_sale_all VALUES ('2017', 'DELL', '笔记本电脑', 155000);
INSERT INTO product_sale_all VALUES ('2018', 'DELL', '笔记本电脑', 130000);
INSERT INTO product_sale_all VALUES ('2019', 'DELL', '笔记本电脑', 155000);
INSERT INTO product_sale_all VALUES ('2020', 'DELL', '笔记本电脑', 140000);
- 统计每年的销售额
select *, sum(sale_sum) over() from product_sale_all ;
- 统计各个产品类别的总销售额
select * , sum(sale_sum) over(partition by product_category) from product_sale_all;
3.统计各个产品类别累计的销售额
select * , sum(sale_sum) over(partition by product_category order by sale_sum desc ) from product_sale_all;
- 查找各个产品类别按销售额排序后,取其前一位置和后一位的产品名称
select * , lag(product_product_name , 1 , null ) over(partition by product_category order by sale_sum desc ) ,
lead(product_product_name,1,null ) over(partition by product_category order by sale_sum desc ) from product_sale_all;