Duncan's Blog

Hive SQL 学习

Veröffentlicht am | in

partition by

partition by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组

example: 一个班有学生id，成绩，班级，现在将学生根据班级按照成绩排名。(partition by)

1
select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student

lateral view

explode / posexplode

explode 拆分一行称多行，而posexplode是根据多行匹配行号进行拆分多行。

窗口函数

a. first_value

​ 取分组内排序后，截止到当前行，第一个值

b.last_value

​ 取分组内排序后，截止到当前行，最后一个值

c.lead(col,n,default)

​ 用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

d.lag(col,n,default)

​ 与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

c.聚集函数 + over + (partition by col1 [order by col (rows | range) between (UNBOUNDED | [num]) preceding and (num FOLLOWING | current ROW))

d.ROW_NUMBER()

​ 从1开始，按照顺序，生成分组内记录的序列

e.RANK()

​ 生成数据项在分组中的排名，排名相等会在名次中留下空位

f.DENSE_RANK()

​ 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

g.CUME_DIST()

​ 小于等于当前值的行数/分组内总行数

h.PERCENT_RANK ()

​ 分组内当前行的RANK值-1/分组内总行数-1

i.NTILE(n)

​ 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布

Note:

  • From子句：执行顺序自上而下，从左到右，从后往前，所以数据量少的表尽量放后
  • where子句：执行顺序自下而上，从右到左，可以过滤掉大量记录的条件写在where子句的末尾
  • group by子句：通过将不需要的记录在group by之前过滤掉，避免使用having来过滤
  • having子句：尽量少用
  • select子句：尽量少用*，取字段名称
  • order by子句：执行顺序为从左到右排序
  • join：尽量把数据量大的表放在最右边来进行关联
分享