【23.9.7】Hive DML语句

Hive DML语句

1. 聚合函数
不论多少条数据，最后只返回一行。比如 sum(), count(), max(), min(), avg()。注意，count是统计有多少行，sum是统计行中数据之和。
2. group by
把group by的那个字段值相同的所有行都当成一组。
经常和聚合函数一起使用。使用group by时，前面select的字段有限制条件：

• 是可以被使用聚合函数的字段
• 或者，是GROUP BY分组的字段
  解释：要是一列可以被group by，另一列没法 被聚合or group by，结果的group by怎么聚合化简呢？不行，还是要拆开。比如下面，group by category，同时前面select value。category是可以聚合，但value没法被合并为一行，group by语句没有意义，但如果使用sum(value)，就可以进行合并。
  

3. having
   where是在group by之前运行的，where中不能使用聚合函数（在有group by的情况下）。于是引入having，在group by之后聚合。

--注意：where条件中不能使用聚合函数
-- --报错 SemanticException:Not yet supported place for UDAF 'count'
--聚合函数要使用它的前提是结果集已经确定。
--而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数。
select state,sum(deaths) from t_usa_covid19 where sum(deaths) >100 group by state;
--可以使用Having实现
select state,sum(deaths) from t_usa_covid19  group by state having sum(deaths) > 100;

4. limit
   只返回 limit后面数字的行数。
   limit 5，就只返回5行结果。
   limit 2,3。第一个参数指定要返回的第一行的偏移量（从 Hive 2.0.0开始），第二个参数指定要返回的最大行数。

5. join。从多表进行连接查询。

• 内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略：inner join == join ；
• 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

--1、inner join, 默认就是inner join 。取交集，左右两边都有并且on的字段值相等
select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id =e_a.id;

举例：

6. left join。以左表为准，左表所有行都会返回，右表有就对应填充，没有就以 null 展示。

7. 常用内置函数

下面这些比较常用的，要记住。

• 日期比较函数

--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);

• 条件函数

-----Conditional Functions 条件函数------------------
--使用之前课程创建好的student表数据
select * from student limit 3;

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

--空值转换函数: nvl(T value, T default_value)。
--担心结果出现空值（或者其他无效值），无效，就转换成指定的
select nvl("allen","itcast");
select nvl(null,"itcast");