hive创建表
create table t1(
a string COMMENT '号段代码',
b string COMMENT '号段类型代码'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE ;
load data inpath '/user/000000/upload/1.txt' into table ods.t1;
取某条数据的最新记录
SELECT
COUNT(1)
FROM
(
SELECT
r.*,
ROW_NUMBER () OVER (
PARTITION BY r.ns_code
ORDER BY
r.ns_code DESC
) num
FROM
ods.t1 r
) m where num = 1 ;
SELECT
COUNT(1)
FROM
(
SELECT
r.*,
rank() OVER (
PARTITION BY r.ns_code
ORDER BY
r.ns_code DESC
) num
FROM
ods.tm_ns_cfg r
) m where num = 1 ;
grouping sets 使用
select
ns_code,
ns_type_code,
number_length
from
ods.tm_ns_cfg
group by
ns_code,
ns_type_code,
number_length
grouping sets
(
(ns_code,number_length),
(ns_code,ns_type_code),
(ns_type_code,number_length),
(number_length)
)
hive行转多列LATERAL
源表(table1)数据{A:string B:array C:string}
A B C
190 [1030,1031,1032,1033,1190] select id
191 [1030,1031,1032,1033,1190] select id
希望的结果是:
190 1030 select id
190 1031 select id
190 1032 select id
190 1033 select id
190 1190 select id
191 1030 select id
191 1031 select id
191 1032 select id
191 1033 select id
191 1190 select id
select A,B,C from table_1 LATERAL VIEW explode(B) table1 as B