1, Hive一行转多行:
later view explode函数可以把一行数据通过split函数切为多片作为一个新的列呈现,示例:
CREATE TABLE MY_TABLE1 AS
SELECT MID,DEVICE,APP FROM BASE.TMP_TABLE LATER VIEW EXPLODE(SPLIT(APPLIST, ',')) R1 AS APP;
表BASE.TMP_TABLE中原有的字段是MID,DEVICE,APPLIST这三个。R1是使用EXPLODE后必须要加的,而APP是最后起的别名。
2,多行转一行
collect_set(A),其中A必须是字符串,如果不是可以用cast转为string类型,示例:
SELECT MID,DEVICE,COLLECT_SET(APP) AS APPLIST FROM MY_TABLE1
GROUP BY MID,DEVICE
3,聚合函数+over()
SELECT COOKIEID,
CREATETIME,
PV,
SUM(PV) OVER (PARTITION BY COOKIEID ORDER BY CREATETIME) AS PV1, --默认计算从起点到当前行的数据
SUM(PV) OVER(PARTITION BY COOKIEID ORDER BY CTRATETIME ROWS BWTWEEN UNBOUNED PRECEDING AND UNBOUNED FOLLOWING) AS PV2, --统计从开始到结尾的所有数据
SUM(PV) OVER(PARTITION BY COOKIEID ORDER BY CREATETIME ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS PV3 -- 统计当前行以及其前两行数据
FROM TMP_TABLE;
如果不指定order by,会将分组内所有值做运算;
如果不指定ROWS BETWEEN,会默认统计开始到当前的数据;
4,分组后数据切片
分组后的数据有时候需要按照一定的顺序拿出一定的比例。
NTILE()函数可以完成这样的功能,如果切片不均匀,会增加第一个切片的数量。
SELECT
COOKIEID,
CREATETIME,
PV,
NTILE(2) OVER(PARTITION BY COOKIEID ORDER BY CREATETIME) AS RN1, -- 分组内数据切为两份。这里不能使用ROW