0: jdbc:hive2://192.168.133.151:10000> create table t_window( . . . . . . . . . . . . . . . . . . .> name string, . . . . . . . . . . . . . . . . . . .> orderdate date, . . . . . . . . . . . . . . . . . . .> cost int . . . . . . . . . . . . . . . . . . .> ) . . . . . . . . . . . . . . . . . . .> row format delimited fields terminated by ','; 0: jdbc:hive2://192.168.133.151:10000> load data local inpath '/opt/tmp/order.csv' into table t_window; 0: jdbc:hive2://192.168.133.151:10000> select * from t_window; +----------------+---------------------+----------------+--+ | t_window.name | t_window.orderdate | t_window.cost | +----------------+---------------------+----------------+--+ | jack | 2015-01-01 | 10 | | tony | 2015-01-02 | 15 | | jack | 2015-02-03 | 23 | | tony | 2015-01-04 | 29 | | jack | 2015-01-05 | 46 | | jack | 2015-04-06 | 42 | | tony | 2015-01-07 | 50 | | jack | 2015-01-08 | 55 | | mart | 2015-04-08 | 62 | | mart | 2015-04-09 | 68 | | neil | 2015-05-10 | 12 | | mart | 2015-04-11 | 75 | | neil | 2015-06-12 | 80 | | mart | 2015-04-13 | 94 | +----------------+---------------------+----------------+--+ 14 rows selected (0.347 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,count(*) over () . . . . . . . . . . . . . . . . . . .> from t_window . . . . . . . . . . . . . . . . . . .> where substring(orderdate,1,7) = '2015-04'; +-------+---------+--+ | name | _wcol0 | +-------+---------+--+ | mart | 5 | | mart | 5 | | mart | 5 | | mart | 5 | | jack | 5 | +-------+---------+--+ 5 rows selected (28.915 seconds) 0: jdbc:hive2://192.168.133.151:10000> select distinct name,count(*) over () . . . . . . . . . . . . . . . . . . .> from t_window . . . . . . . . . . . . . . . . . . .> where substring(orderdate,1,7) = '2015-04'; +-------+---------+--+ | name | _wcol0 | +-------+---------+--+ | mart | 2 | | jack | 2 | +-------+---------+--+ 2 rows selected (40.502 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,count(*) over () . . . . . . . . . . . . . . . . . . .> from t_window . . . . . . . . . . . . . . . . . . .> where substring(orderdate,1,7) = '2015-04' . . . . . . . . . . . . . . . . . . .> group by name; +-------+---------+--+ | name | _wcol0 | +-------+---------+--+ | mart | 2 | | jack | 2 | +-------+---------+--+ 2 rows selected (38.708 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over() from t_window; 最后一列显示的内容为 所有行的第三列(cost)相加之和 +-------+-------------+-------+---------+--+ | name | orderdate | cost | _wcol0 | +-------+-------------+-------+---------+--+ | mart | 2015-04-13 | 94 | 661 | | neil | 2015-06-12 | 80 | 661 | | mart | 2015-04-11 | 75 | 661 | | neil | 2015-05-10 | 12 | 661 | | mart | 2015-04-09 | 68 | 661 | | mart | 2015-04-08 | 62 | 661 | | jack | 2015-01-08 | 55 | 661 | | tony | 2015-01-07 | 50 | 661 | | jack | 2015-04-06 | 42 | 661 | | jack | 2015-01-05 | 46 | 661 | | tony | 2015-01-04 | 29 | 661 | | jack | 2015-02-03 | 23 | 661 | | tony | 2015-01-02 | 15 | 661 | | jack | 2015-01-01 | 10 | 661 | +-------+-------------+-------+---------+--+ 14 rows selected (19.449 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from t_window; +-------+-------------+-------+---------+--+ | name | orderdate | cost | _wcol0 | +-------+-------------+-------+---------+--+ | jack | 2015-01-01 | 10 | 205 | | jack | 2015-01-08 | 55 | 205 | | tony | 2015-01-07 | 50 | 205 | | jack | 2015-01-05 | 46 | 205 | | tony | 2015-01-04 | 29 | 205 | | tony | 2015-01-02 | 15 | 205 | | jack | 2015-02-03 | 23 | 23 | | mart | 2015-04-13 | 94 | 341 | | jack | 2015-04-06 | 42 | 341 | | mart | 2015-04-11 | 75 | 341 | | mart | 2015-04-09 | 68 | 341 | | mart | 2015-04-08 | 62 | 341 | | neil | 2015-05-10 | 12 | 12 | | neil | 2015-06-12 | 80 | 80 | +-------+-------------+-------+---------+--+ 14 rows selected (19.206 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate) from t_window; +-------+-------------+-------+---------+--+ | name | orderdate | cost | _wcol0 | +-------+-------------+-------+---------+--+ | jack | 2015-01-01 | 10 | 10 | | tony | 2015-01-02 | 15 | 25 | | tony | 2015-01-04 | 29 | 54 | | jack | 2015-01-05 | 46 | 100 | | tony | 2015-01-07 | 50 | 150 | | jack | 2015-01-08 | 55 | 205 | | jack | 2015-02-03 | 23 | 23 | | jack | 2015-04-06 | 42 | 42 | | mart | 2015-04-08 | 62 | 104 | | mart | 2015-04-09 | 68 | 172 | | mart | 2015-04-11 | 75 | 247 | | mart | 2015-04-13 | 94 | 341 | | neil | 2015-05-10 | 12 | 12 | | neil | 2015-06-12 | 80 | 80 | +-------+-------------+-------+---------+--+ 14 rows selected (19.171 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over() as sample1, --所有的cost列相加 . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name) as sample2 --将按名字分组，然后把每一组的cost列相加 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | +-------+-------------+-------+----------+----------+--+ | jack | 2015-01-01 | 10 | 661 | 176 | | jack | 2015-02-03 | 23 | 661 | 176 | | jack | 2015-01-05 | 46 | 661 | 176 | | jack | 2015-04-06 | 42 | 661 | 176 | | jack | 2015-01-08 | 55 | 661 | 176 | | mart | 2015-04-13 | 94 | 661 | 299 | | mart | 2015-04-08 | 62 | 661 | 299 | | mart | 2015-04-09 | 68 | 661 | 299 | | mart | 2015-04-11 | 75 | 661 | 299 | | neil | 2015-06-12 | 80 | 661 | 92 | | neil | 2015-05-10 | 12 | 661 | 92 | | tony | 2015-01-07 | 50 | 661 | 94 | | tony | 2015-01-02 | 15 | 661 | 94 | | tony | 2015-01-04 | 29 | 661 | 94 | +-------+-------------+-------+----------+----------+--+ 14 rows selected (39.226 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over() as sample1, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name) as sample2, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate) as sample3 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | sample3 | +-------+-------------+-------+----------+----------+----------+--+ | jack | 2015-01-01 | 10 | 661 | 176 | 10 | | jack | 2015-01-05 | 46 | 661 | 176 | 56 | | jack | 2015-01-08 | 55 | 661 | 176 | 111 | | jack | 2015-02-03 | 23 | 661 | 176 | 134 | | jack | 2015-04-06 | 42 | 661 | 176 | 176 | | mart | 2015-04-08 | 62 | 661 | 299 | 62 | | mart | 2015-04-09 | 68 | 661 | 299 | 130 | | mart | 2015-04-11 | 75 | 661 | 299 | 205 | | mart | 2015-04-13 | 94 | 661 | 299 | 299 | | neil | 2015-05-10 | 12 | 661 | 92 | 12 | | neil | 2015-06-12 | 80 | 661 | 92 | 92 | | tony | 2015-01-02 | 15 | 661 | 94 | 15 | | tony | 2015-01-04 | 29 | 661 | 94 | 44 | | tony | 2015-01-07 | 50 | 661 | 94 | 94 | +-------+-------------+-------+----------+----------+----------+--+ 14 rows selected (59.151 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over() as sample1, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name) as sample2, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate) as sample3, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | sample3 | sample4 | +-------+-------------+-------+----------+----------+----------+----------+--+ | jack | 2015-01-01 | 10 | 661 | 176 | 10 | 10 | | jack | 2015-01-05 | 46 | 661 | 176 | 56 | 56 | | jack | 2015-01-08 | 55 | 661 | 176 | 111 | 111 | | jack | 2015-02-03 | 23 | 661 | 176 | 134 | 134 | | jack | 2015-04-06 | 42 | 661 | 176 | 176 | 176 | | mart | 2015-04-08 | 62 | 661 | 299 | 62 | 62 | | mart | 2015-04-09 | 68 | 661 | 299 | 130 | 130 | | mart | 2015-04-11 | 75 | 661 | 299 | 205 | 205 | | mart | 2015-04-13 | 94 | 661 | 299 | 299 | 299 | | neil | 2015-05-10 | 12 | 661 | 92 | 12 | 12 | | neil | 2015-06-12 | 80 | 661 | 92 | 92 | 92 | | tony | 2015-01-02 | 15 | 661 | 94 | 15 | 15 | | tony | 2015-01-04 | 29 | 661 | 94 | 44 | 44 | | tony | 2015-01-07 | 50 | 661 | 94 | 94 | 94 | +-------+-------------+-------+----------+----------+----------+----------+--+ 14 rows selected (59.539 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over() as sample1, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name) as sample2, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate) as sample3, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+----------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | sample3 | sample4 | sample5 | +-------+-------------+-------+----------+----------+----------+----------+----------+--+ | jack | 2015-01-01 | 10 | 661 | 176 | 10 | 10 | 10 | | jack | 2015-01-05 | 46 | 661 | 176 | 56 | 56 | 56 | | jack | 2015-01-08 | 55 | 661 | 176 | 111 | 111 | 101 | | jack | 2015-02-03 | 23 | 661 | 176 | 134 | 134 | 78 | | jack | 2015-04-06 | 42 | 661 | 176 | 176 | 176 | 65 | | mart | 2015-04-08 | 62 | 661 | 299 | 62 | 62 | 62 | | mart | 2015-04-09 | 68 | 661 | 299 | 130 | 130 | 130 | | mart | 2015-04-11 | 75 | 661 | 299 | 205 | 205 | 143 | | mart | 2015-04-13 | 94 | 661 | 299 | 299 | 299 | 169 | | neil | 2015-05-10 | 12 | 661 | 92 | 12 | 12 | 12 | | neil | 2015-06-12 | 80 | 661 | 92 | 92 | 92 | 92 | | tony | 2015-01-02 | 15 | 661 | 94 | 15 | 15 | 15 | | tony | 2015-01-04 | 29 | 661 | 94 | 44 | 44 | 44 | | tony | 2015-01-07 | 50 | 661 | 94 | 94 | 94 | 79 | +-------+-------------+-------+----------+----------+----------+----------+----------+--+ 14 rows selected (60.019 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,sum(cost) over() as sample1, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name) as sample2, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate) as sample3, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as sample6, . . . . . . . . . . . . . . . . . . .> sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as sample7 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+----------+----------+----------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | sample3 | sample4 | sample5 | sample6 | sample7 | +-------+-------------+-------+----------+----------+----------+----------+----------+----------+----------+--+ | jack | 2015-01-01 | 10 | 661 | 176 | 10 | 10 | 10 | 56 | 176 | | jack | 2015-01-05 | 46 | 661 | 176 | 56 | 56 | 56 | 111 | 166 | | jack | 2015-01-08 | 55 | 661 | 176 | 111 | 111 | 101 | 124 | 120 | | jack | 2015-02-03 | 23 | 661 | 176 | 134 | 134 | 78 | 120 | 65 | | jack | 2015-04-06 | 42 | 661 | 176 | 176 | 176 | 65 | 65 | 42 | | mart | 2015-04-08 | 62 | 661 | 299 | 62 | 62 | 62 | 130 | 299 | | mart | 2015-04-09 | 68 | 661 | 299 | 130 | 130 | 130 | 205 | 237 | | mart | 2015-04-11 | 75 | 661 | 299 | 205 | 205 | 143 | 237 | 169 | | mart | 2015-04-13 | 94 | 661 | 299 | 299 | 299 | 169 | 169 | 94 | | neil | 2015-05-10 | 12 | 661 | 92 | 12 | 12 | 12 | 92 | 92 | | neil | 2015-06-12 | 80 | 661 | 92 | 92 | 92 | 92 | 92 | 80 | | tony | 2015-01-02 | 15 | 661 | 94 | 15 | 15 | 15 | 44 | 94 | | tony | 2015-01-04 | 29 | 661 | 94 | 44 | 44 | 44 | 94 | 79 | | tony | 2015-01-07 | 50 | 661 | 94 | 94 | 94 | 79 | 79 | 50 | +-------+-------------+-------+----------+----------+----------+----------+----------+----------+----------+--+ 14 rows selected (60.411 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,ntile(3) over() as sample1 from t_window; +-------+-------------+-------+----------+--+ | name | orderdate | cost | sample1 | +-------+-------------+-------+----------+--+ | mart | 2015-04-13 | 94 | 1 | | neil | 2015-06-12 | 80 | 1 | | mart | 2015-04-11 | 75 | 1 | | neil | 2015-05-10 | 12 | 1 | | mart | 2015-04-09 | 68 | 1 | | mart | 2015-04-08 | 62 | 2 | | jack | 2015-01-08 | 55 | 2 | | tony | 2015-01-07 | 50 | 2 | | jack | 2015-04-06 | 42 | 2 | | jack | 2015-01-05 | 46 | 2 | | tony | 2015-01-04 | 29 | 3 | | jack | 2015-02-03 | 23 | 3 | | tony | 2015-01-02 | 15 | 3 | | jack | 2015-01-01 | 10 | 3 | +-------+-------------+-------+----------+--+ 14 rows selected (20.222 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,ntile(3) over() as sample1, . . . . . . . . . . . . . . . . . . .> ntile(3) over(partition by name) as sample2 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | +-------+-------------+-------+----------+----------+--+ | jack | 2015-01-01 | 10 | 3 | 1 | | jack | 2015-02-03 | 23 | 3 | 1 | | jack | 2015-01-05 | 46 | 2 | 2 | | jack | 2015-04-06 | 42 | 2 | 2 | | jack | 2015-01-08 | 55 | 2 | 3 | | mart | 2015-04-13 | 94 | 1 | 1 | | mart | 2015-04-08 | 62 | 2 | 1 | | mart | 2015-04-09 | 68 | 1 | 2 | | mart | 2015-04-11 | 75 | 1 | 3 | | neil | 2015-06-12 | 80 | 1 | 1 | | neil | 2015-05-10 | 12 | 1 | 2 | | tony | 2015-01-07 | 50 | 2 | 1 | | tony | 2015-01-02 | 15 | 3 | 2 | | tony | 2015-01-04 | 29 | 3 | 3 | +-------+-------------+-------+----------+----------+--+ 14 rows selected (41.433 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,ntile(3) over() as sample1, . . . . . . . . . . . . . . . . . . .> ntile(3) over(partition by name) as sample2, . . . . . . . . . . . . . . . . . . .> ntile(2) over(partition by month(orderdate)) as sample3 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | sample3 | +-------+-------------+-------+----------+----------+----------+--+ | jack | 2015-01-01 | 10 | 3 | 1 | 1 | | tony | 2015-01-02 | 15 | 3 | 2 | 1 | | tony | 2015-01-07 | 50 | 2 | 1 | 1 | | tony | 2015-01-04 | 29 | 3 | 3 | 2 | | jack | 2015-01-08 | 55 | 2 | 3 | 2 | | jack | 2015-01-05 | 46 | 2 | 2 | 2 | | jack | 2015-02-03 | 23 | 3 | 1 | 1 | | mart | 2015-04-09 | 68 | 1 | 2 | 1 | | mart | 2015-04-08 | 62 | 2 | 1 | 1 | | mart | 2015-04-13 | 94 | 1 | 1 | 1 | | jack | 2015-04-06 | 42 | 2 | 2 | 2 | | mart | 2015-04-11 | 75 | 1 | 3 | 2 | | neil | 2015-05-10 | 12 | 1 | 2 | 1 | | neil | 2015-06-12 | 80 | 1 | 1 | 1 | +-------+-------------+-------+----------+----------+----------+--+ 14 rows selected (59.042 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,ntile(3) over() as sample1, . . . . . . . . . . . . . . . . . . .> ntile(3) over(partition by name) as sample2, . . . . . . . . . . . . . . . . . . .> ntile(2) over(partition by month(orderdate)) as sample3, . . . . . . . . . . . . . . . . . . .> ntile(3) over(partition by name order by cost desc) as sample4 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+----------+----------+----------+----------+--+ | name | orderdate | cost | sample1 | sample2 | sample3 | sample4 | +-------+-------------+-------+----------+----------+----------+----------+--+ | jack | 2015-01-08 | 55 | 2 | 3 | 2 | 1 | | jack | 2015-01-05 | 46 | 2 | 2 | 2 | 1 | | jack | 2015-04-06 | 42 | 2 | 2 | 2 | 2 | | jack | 2015-02-03 | 23 | 3 | 1 | 1 | 2 | | jack | 2015-01-01 | 10 | 3 | 1 | 1 | 3 | | mart | 2015-04-13 | 94 | 1 | 1 | 1 | 1 | | mart | 2015-04-11 | 75 | 1 | 3 | 2 | 1 | | mart | 2015-04-09 | 68 | 1 | 2 | 1 | 2 | | mart | 2015-04-08 | 62 | 2 | 1 | 1 | 3 | | neil | 2015-06-12 | 80 | 1 | 1 | 1 | 1 | | neil | 2015-05-10 | 12 | 1 | 2 | 1 | 2 | | tony | 2015-01-07 | 50 | 2 | 1 | 1 | 1 | | tony | 2015-01-04 | 29 | 3 | 3 | 2 | 2 | | tony | 2015-01-02 | 15 | 3 | 2 | 1 | 3 | +-------+-------------+-------+----------+----------+----------+----------+--+ 14 rows selected (76.885 seconds) Rank():有相同名次，留下空位 1 2 3 3 5 6 DENSE_RANK():有相同名次，不会留下空位 1 2 3 3 4 5 ROW_NUMBER():没有相同名次 1 2 3 4 5 6 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,row_number() over() as r1, . . . . . . . . . . . . . . . . . . .> row_number() over(order by name) as r2, . . . . . . . . . . . . . . . . . . .> rank() over(order by name) as r3, . . . . . . . . . . . . . . . . . . .> dense_rank() over(order by name) as r4 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+-----+-----+-----+-----+--+ | name | orderdate | cost | r1 | r2 | r3 | r4 | +-------+-------------+-------+-----+-----+-----+-----+--+ | jack | 2015-01-01 | 10 | 14 | 1 | 1 | 1 | | jack | 2015-02-03 | 23 | 12 | 2 | 1 | 1 | | jack | 2015-01-05 | 46 | 10 | 3 | 1 | 1 | | jack | 2015-04-06 | 42 | 9 | 4 | 1 | 1 | | jack | 2015-01-08 | 55 | 7 | 5 | 1 | 1 | | mart | 2015-04-13 | 94 | 1 | 6 | 6 | 2 | | mart | 2015-04-08 | 62 | 6 | 7 | 6 | 2 | | mart | 2015-04-09 | 68 | 5 | 8 | 6 | 2 | | mart | 2015-04-11 | 75 | 3 | 9 | 6 | 2 | | neil | 2015-06-12 | 80 | 2 | 10 | 10 | 3 | | neil | 2015-05-10 | 12 | 4 | 11 | 10 | 3 | | tony | 2015-01-07 | 50 | 8 | 12 | 12 | 4 | | tony | 2015-01-02 | 15 | 13 | 13 | 12 | 4 | | tony | 2015-01-04 | 29 | 11 | 14 | 12 | 4 | +-------+-------------+-------+-----+-----+-----+-----+--+ 14 rows selected (38.716 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost,lag(orderdate,1) over(partition by name order by orderdate) as sample1, . . . . . . . . . . . . . . . . . . .> lag(orderdate,1,'1972-01-01') over(partition by name order by orderdate) as sample2, . . . . . . . . . . . . . . . . . . .> lag(orderdate,2,'1972-01-01') over(partition by name order by orderdate) as sample3, . . . . . . . . . . . . . . . . . . .> lead(orderdate,1,'1972-01-01') over(partition by name order by orderdate) as sample4, . . . . . . . . . . . . . . . . . . .> lead(orderdate,2,'1972-01-01') over(partition by name order by orderdate) as sample5 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+-------------+-------------+-------------+-------------+-------------+--+ | name | orderdate | cost | sample1 | sample2 | sample3 | sample4 | sample5 | +-------+-------------+-------+-------------+-------------+-------------+-------------+-------------+--+ | jack | 2015-01-01 | 10 | NULL | 1972-01-01 | 1972-01-01 | 2015-01-05 | 2015-01-08 | | jack | 2015-01-05 | 46 | 2015-01-01 | 2015-01-01 | 1972-01-01 | 2015-01-08 | 2015-02-03 | | jack | 2015-01-08 | 55 | 2015-01-05 | 2015-01-05 | 2015-01-01 | 2015-02-03 | 2015-04-06 | | jack | 2015-02-03 | 23 | 2015-01-08 | 2015-01-08 | 2015-01-05 | 2015-04-06 | 1972-01-01 | | jack | 2015-04-06 | 42 | 2015-02-03 | 2015-02-03 | 2015-01-08 | 1972-01-01 | 1972-01-01 | | mart | 2015-04-08 | 62 | NULL | 1972-01-01 | 1972-01-01 | 2015-04-09 | 2015-04-11 | | mart | 2015-04-09 | 68 | 2015-04-08 | 2015-04-08 | 1972-01-01 | 2015-04-11 | 2015-04-13 | | mart | 2015-04-11 | 75 | 2015-04-09 | 2015-04-09 | 2015-04-08 | 2015-04-13 | 1972-01-01 | | mart | 2015-04-13 | 94 | 2015-04-11 | 2015-04-11 | 2015-04-09 | 1972-01-01 | 1972-01-01 | | neil | 2015-05-10 | 12 | NULL | 1972-01-01 | 1972-01-01 | 2015-06-12 | 1972-01-01 | | neil | 2015-06-12 | 80 | 2015-05-10 | 2015-05-10 | 1972-01-01 | 1972-01-01 | 1972-01-01 | | tony | 2015-01-02 | 15 | NULL | 1972-01-01 | 1972-01-01 | 2015-01-04 | 2015-01-07 | | tony | 2015-01-04 | 29 | 2015-01-02 | 2015-01-02 | 1972-01-01 | 2015-01-07 | 1972-01-01 | | tony | 2015-01-07 | 50 | 2015-01-04 | 2015-01-04 | 2015-01-02 | 1972-01-01 | 1972-01-01 | +-------+-------------+-------+-------------+-------------+-------------+-------------+-------------+--+ 14 rows selected (20.223 seconds) 0: jdbc:hive2://192.168.133.151:10000> select name,orderdate,cost, . . . . . . . . . . . . . . . . . . .> first_value(orderdate) over(partition by name order by orderdate) as time1, . . . . . . . . . . . . . . . . . . .> last_value(orderdate) over(partition by name order by orderdate) as time2 . . . . . . . . . . . . . . . . . . .> from t_window; +-------+-------------+-------+-------------+-------------+--+ | name | orderdate | cost | time1 | time2 | +-------+-------------+-------+-------------+-------------+--+ | jack | 2015-01-01 | 10 | 2015-01-01 | 2015-01-01 | | jack | 2015-01-05 | 46 | 2015-01-01 | 2015-01-05 | | jack | 2015-01-08 | 55 | 2015-01-01 | 2015-01-08 | | jack | 2015-02-03 | 23 | 2015-01-01 | 2015-02-03 | | jack | 2015-04-06 | 42 | 2015-01-01 | 2015-04-06 | | mart | 2015-04-08 | 62 | 2015-04-08 | 2015-04-08 | | mart | 2015-04-09 | 68 | 2015-04-08 | 2015-04-09 | | mart | 2015-04-11 | 75 | 2015-04-08 | 2015-04-11 | | mart | 2015-04-13 | 94 | 2015-04-08 | 2015-04-13 | | neil | 2015-05-10 | 12 | 2015-05-10 | 2015-05-10 | | neil | 2015-06-12 | 80 | 2015-05-10 | 2015-06-12 | | tony | 2015-01-02 | 15 | 2015-01-02 | 2015-01-02 | | tony | 2015-01-04 | 29 | 2015-01-02 | 2015-01-04 | | tony | 2015-01-07 | 50 | 2015-01-02 | 2015-01-07 | +-------+-------------+-------+-------------+-------------+--+ 14 rows selected (19.091 seconds) 0: jdbc:hive2://192.168.133.151:10000> select from_unixtime(1636539745,'yyyy MM dd hh mm ss'); +----------------------+--+ | _c0 | +----------------------+--+ | 2021 11 10 06 22 25 | +----------------------+--+ 1 row selected (0.03 seconds) 0: jdbc:hive2://192.168.133.151:10000> select unix_timestamp(); +-------------+--+ | _c0 | +-------------+--+ | 1636719038 | +-------------+--+ 1 row selected (0.031 seconds)
