【Hive】基础语法记录hivesql

一、基础语法

1.1 Hive查询语句

1.GROUP BY 分组查询：

select s_id ,avg(s_score) avgscore 
from score group
by s_id having avgscore > 85; 

对分组后的数据进行筛选，使用 having。

2.join 连接：inner join 内连接；left join 左连接；right join 右链接；full join 全外链接。

3.order by 排序：ASC（ascend）: 升序（默认） DESC（descend）:

4.distribute by 分区排序：类似 MR 中 partition，进行分区，结合 sort by 使用。

1.2 Hive函数

1.2.1 聚合函数

1 指定列值的数目：count()
2. 指定列值求和：sum()
3. 指定列的最大值：max()
4. 指定列的最小值：min()
5. 指定列的平均值：avg()
6. 非空集合总体变量函数：var_pop(col)
7. 非空集合样本变量函数：var_samp (col)
8. 总体标准偏离函数：stddev_pop(col)
9. 分位数函数：percentile(BIGINT col, p)
10. 中位数函数：percentile(BIGINT col, 0.5)

1.2.2 关系函数

1 A LIKE B： LIKE 比较，如果字符串 A 符合表达式 B 的正则语法，则为TRUE
2. A RLIKE B：JAVA 的 LIKE 操作，如果字符串 A 符合 JAVA 正则表达式 B 的正则语法，则为 TRUE
3. A REGEXP B：功能与 RLIKE 相同

1.2.3 数值运算

1. 取整函数：round(double a)
2. 指定精度取整函数：round(double a, int d)
3. 向下取整函数：floor(double a)
4. 向上取整函数：ceil(double a)
5. 取随机数函数：rand(),rand(int seed)
6. 自然指数函数：exp(double a)
7. 以 10 为底对数函数：log10(double a)
8. 以 2 为底对数函数：log2()
9. 对数函数：log()
10. 幂运算函数：pow(double a, double p)
11. 开平方函数：sqrt(double a)
12. 二进制函数：bin(BIGINT a)
13. 十六进制函数：hex()
14. 绝对值函数：abs()
15. 正取余函数：pmod()

1.2.4 条件函数

1 if
2 case when
3.coalesce(c1,c2,c3)
4.nvl(c1，c2)

1.2.5 日期函数

1.获得当前时区的 UNIX 时间戳: unix_timestamp()
2. 时间戳转日期函数：from_unixtime()
3. 日期转时间戳：unix_timestamp(string date)
4. 日期时间转日期函数：to_date(string timestamp)
5. 日期转年函数：year(string date)
6. 日期转月函数：month (string date)
7. 日期转天函数: day (string date)
8. 日期转小时函数: hour (string date)
9. 日期转分钟函数：minute (string date)
10.日期转秒函数: second (string date)
11.日期转周函数: weekofyear (string date)
12.日期比较函数: datediff(string enddate, string startdate)
13.日期增加函数: date_add(string startdate, int days)
14.日期减少函数：date_sub (string startdate, int days)

1.2.6 字符串函数

1.字符串长度函数：length(string A)
2. 字符串反转函数：reverse(string A)
3. 字符串连接函数: concat(string A, string B…)
4. 带分隔符字符串连接函数：concat_ws(string SEP, string A, stringB…)
5. 字符串截取函数: substr(string A, int start, int len)
6. 字符串转大写函数: upper(string A)
7. 字符串转小写函数：lower(string A)
8. 去空格函数：trim(string A)
9. 左边去空格函数：ltrim(string A)
10.右边去空格函数：rtrim(string A)
11.正则表达式替换函数： regexp_replace(string A, string B, string C)
12.正则表达式解析函数: regexp_extract(string subject, string pattern,
int index)
13.URL 解析函数：parse_url(string urlString, string partToExtract [,
string keyToExtract]) 返回值: string
14.json 解析函数：get_json_object(string json_string, string path)
15.空格字符串函数：space(int n)
16.重复字符串函数：repeat(string str, int n)
17.首字符 ascii 函数：ascii(string str)
18.左补足函数：lpad(string str, int len, string pad)
19.右补足函数：rpad(string str, int len, string pad)
20.分割字符串函数: split(string str, string pat)
21.集合查找函数: find_in_set(string str, string strList)

1.2.7 窗口函数

1.分组求和函数：sum(pv) over(partition by cookieid order by createtime)
有坑，加不加 order by 差别很大，具体详情在下面第二部分。
2. 分组内排序，从 1 开始顺序排：ROW_NUMBER() 如：1234567
3. 分组内排序，排名相等会在名次中留下空位：RANK() 如：1233567
4. 分组内排序，排名相等不会在名次中留下空位：DENSE_RANK() 如：1233456
5. 有序的数据集合平均分配到指定的数量（num）个桶中：NTILE()
6. 统计窗口内往上第 n 行值：LAG(col,n,DEFAULT)
7. 统计窗口内往下第 n 行值：LEAD(col,n,DEFAULT)
8.分组内排序后，截止到当前行，第一个值：FIRST_VALUE(col)
9.分组内排序后，截止到当前行，最后一个值: LAST_VALUE(col)
10.小于等于当前值的行数/分组内总行数：CUME_DIST()
以下函数建议看第二部分详细理解下，此处仅简写，！
11.将多个 group by 逻辑写在一个 sql 语句中: GROUPING SETS
12.根据 GROUP BY 的维度的所有组合进行聚合：CUBE
13.CUBE 的子集，以最左侧的维度为主，从该维度进行层级聚合：ROLLUP