Part 1
#原始数据
user_id platform use_cnt is_active date_8
10600 2 46 1 20190101
10600 2 49 1 20190102
10600 2 11 1 20190103
#实现去重---group by
SELECT user_id,platform,is_active
FROM app.t_od_use_cnt
WHERE date_8 >= 20190101
AND user_id = 10600
GROUP BY user_id
,platform
,is_active;
#实现去重---DISTINCT
SELECT DISTINCT user_id,platform,is_active
FROM app.t_od_use_cnt
WHERE date_8 >= 20190101
AND user_id = 10600;
#⚠️运行结果是一样的,但去重时不建议使用DISTINCT,因为当数据量很大时会产生数据倾斜,导致DISTINCT运行效率相比GROUP BY 会低很多。当数据量较小时两者差距则不大,甚至可能DISTINCT效率更高。但在实际工作中数据量动辄千万行上亿行,所以强烈推荐养成使用GROUP BY去重的习惯。
Having 子句
WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选聚合后的数据,而且HAVING 子句中可以使用SELECT语句中用户自定义的列别名。
having与where不同点
(1)where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。
(2)where后面不能写分组函数,而having后面可以使用分组函数。
(3)having只用于group by分组统计语句。
having count(1)=1就是只输出分组中只有一条记录的
SELECT class.STUDENT_CODE
FROM crm_class_schedule class
GROUP BY class.STUDENT_CODE
HAVING count(*) > 1
;
内连接可以用inner join 或者join
nvl(value,default_value) - Returns default value if value is null else returns value
Case具有两种格式。简单Case函数和Case搜索函数。
简单Case函数
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END
–Case搜索函数
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END
2种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
Part 2
Hive中表的类型:内部表,外部表,分区表,视图,桶表
1.创建表
create table 表名
(tid int, tname string, age int);
row format delimited fields terminated by ',' #用,进行分割
location '/hive/';
creat table t
row format delimited fields terminated by ','
as
select *
from XXX #将索引结果直接导入到表中
#创建分区表,以性别作为分区标准
create table partition_table(group int)
partitioned by (gender string)
row format delimited fields terminated by ','
insert into table partition_table partition(gender='M')
select * from XXX
where gender='M'
insert into table partition_table
partition(gender='F')
select * fromXXX
where gender='F'
#创建多个分区
create table t10(
id int
,name string
,hobby array<string>
,add map<String,string>
)
partitioned by (pt_d string,sex string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
;
2.#添加新列
alter table t1 add columns(group int);
3.#删除表
drop table t1
4.#加载数据
load data local inpath '/home/hadoop/Desktop/data'
overwrite into table t1 partition ( pt_d = '201701');