row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
1 2 3 4 5
SELECT * FROM ( SELECT partner_hotel_id, hotel_seq, task_date, link_status, row_number() OVER(PARTITION BY partner_hotel_id ORDER BY task_date DESC) AS num FROM clues_mapping.qunar_mapping_houses ) tmp WHERE tmp.num = 1 and link_status = 'LINKED'
NTILE() OVER
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值, 假如统计一个cookie，pv数最多的前1/3的天，下边的数据其实我们只需rn2 = 1,如果是前2/3那rn2 in (1, 2)
SELECT cookieid, createtime, pv, NTILE(2) OVER(PARTITION BY cookieid ORDER BY pv) AS rn1, --分组内将数据分成2片 NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv) AS rn2, --分组内将数据分成3片 NTILE(4) OVER(ORDER BY pv) AS rn3 --将所有数据分成4片 FROM lxw1234 ORDER BY cookieid,createtime;
SELECT cookieid, createtime, pv, SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行 FROM lxw1234;
hive中的行列转换函数
hive 中的行转列
关键点：使用到explode()、split()和LATERAL VIEW
1 2 3
SELECT rateable_id AS house_id, ids FROM vdayu_production.rates AS h lateral view explode(split(sight_photo_ids_str, ",")) adtable AS ids
SELECT memberid, regexp_replace( concat_ws('-', sort_array( collect_set( concat_ws(':',cast(legcount as string),airways) ) ) ),'\\d\:','') hs from ( select 1 as memberid,'A' as airways,2 as legcount union ALL select 1 as memberid,'B' as airways,3 as legcount union ALL select 2 as memberid,'C' as airways,4 as legcount union ALL select 2 as memberid,'D' as airways,1 as legcount union ALL select 2 as memberid,'D' as airways,1 as legcount ) as t group by memberid
create table tmp as select 'A' as student, 'english' as class_name, 'answer_num' as key, 9 as value from dual union all select 'A' as student, 'chinese' as class_name, 'answer_num' as key, 15 as value from dual union all select 'B' as student, 'english' as class_name, 'answer_num' as key, 12 as value from dual union all select 'A' as student, 'english' as class_name, 'homework_num' as key, 5 as value from dual union all select 'B' as student, 'english' as class_name, 'homework_num' as key, 7 as value from dual union all select 'B' as student, 'chinese' as class_name, 'homework_num' as key, 10 as value from dual; --------------------- 作者：SK_Lavender 来源：CSDN 原文：https://blog.csdn.net/u010705209/article/details/53164118
Pivot using Hivemall to_map function
1 2 3 4 5 6 7 8 9 10
SELECT uid, kv['c1'] AS c1, kv['c2'] AS c2, kv['c3'] AS c3 FROM ( SELECT uid, to_map(key, value) kv FROM vtable GROUP BY uid ) t
uid
c1
c2
c3
A
11
10
10
B
12
11
10
Unpivot
1 2 3 4 5 6 7
SELECT t1.uid, t2.key, t2.value FROM htable t1 LATERAL VIEW explode (map( 'c1', c1, 'c2', c2, 'c3', c3 )) t2 as key, value
