Hive HQL面试题(窗口函数over)（二）

Hive HQL面试题(窗口函数over)（二）

1. 数据准备1

1. 文本文件

a,2020-02-10,600
a,2020-03-01,200
a,2020-03-02,300
a,2020-03-03,200
a,2020-03-04,400
a,2020-03-05,600
a,2020-02-05,200
a,2020-02-06,300
a,2020-02-07,200
a,2020-02-08,400
b,2020-02-05,200
b,2020-02-06,300
b,2020-02-08,200
b,2020-02-09,400
b,2020-02-10,600
c,2020-01-31,200
c,2020-02-01,300
c,2020-02-02,200
c,2020-02-03,400
c,2020-02-10,600

2. hive上建表，先建立database

create database db_window2;

create table  tb_shop(
name string ,
ctime string ,
cost double 
)
row format delimited fields terminated by ',';

load data local inpath '/root/hive_sql/hive_window2/hivewindow2.txt' into table tb_shop;

+---------------+----------------+---------------+
| tb_shop.name  | tb_shop.ctime  | tb_shop.cost  |
+---------------+----------------+---------------+
| a             | 2020-02-10     | 600.0         |
| a             | 2020-03-01     | 200.0         |
| a             | 2020-03-02     | 300.0         |
| a             | 2020-03-03     | 200.0         |
| a             | 2020-03-04     | 400.0         |
| a             | 2020-03-05     | 600.0         |
| a             | 2020-02-05     | 200.0         |
| a             | 2020-02-06     | 300.0         |
| a             | 2020-02-07     | 200.0         |
| a             | 2020-02-08     | 400.0         |
| b             | 2020-02-05     | 200.0         |
| b             | 2020-02-06     | 300.0         |
| b             | 2020-02-08     | 200.0         |
| b             | 2020-02-09     | 400.0         |
| b             | 2020-02-10     | 600.0         |
| c             | 2020-01-31     | 200.0         |
| c             | 2020-02-01     | 300.0         |
| c             |