【数据库】SQL（HQL）使用 - hippowc/hippowc.github.io GitHub Wiki

SQL使用

子查询 ： 一切select的结果都是表，这个是做复杂查询最基础的技巧

  • 将一个select语句结果作为一个表：通过()将select括起来，并命名一个表名
select * from
(
 select * from table1
) table2

重命名

  • 列重命名：select as <other_name>
select id as 编号, name ‘名称’, sex 性别 from student;
  • 表重命名：select * from <table_name> <other_name>
select id, name, s.id, s.name from student s;

常用关键词

join

  • inner join （join）：返回两表交集
  • left join：返回左表全集
  • right join：返回右表全集
  • full outer join：返回两表全集

union

合并两个或多个 SELECT 语句的结果。UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

union在进行表求并集后会去掉重复的元素，所以会对所产生的结果集进行排序运算，删除重复的记录再返回结果。union all则只是简单地将两个结果集合并后就返回结果。因此，如果返回的两个结果集中有重复的数据，那么返回的结果就会包含重复的数据。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

group by

group by是对数据进行分组，执行后，上下文会切换为每组而不是每行，结合聚合函数使用。group by按照某些列的值进行分组，即，该列的值相同的为一组。然后其他列需要以聚合函数的方式进行展示，如果其他列未指定相应的聚合函数，则取该组的第一条数据。

如果仅仅作为去重处理的化，与distinct类似

聚合函数

聚合函数是对一组值执行计算并返回单一的值的函数，它经常与SELECT语句的GROUP BY子句一同使用，大概有这么几类

  • avg 返回组中的平均值
  • count 返回组中项目的数量
  • max 返回组中最大值
  • min 返回组中最小值
  • sum 返回组中和

等等，不同数据库提供的聚合函数有所不同。

SELECT语句执行顺序

  • from子句组装来自不同数据源的数据
  • where子句基于制定的条件对记录进行筛选
  • group by子句将数据划分多个分组
  • 使用聚集函数进行计算
  • having子句筛选分组
  • 计算所有表达式
  • order by对结果集进行排序
  • select截取相应列
  • limit截取结果集

having语句用法

having的功能类似与where，但是筛选的时机和对筛选对象的要求有所不同：

  • where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
  • having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。

一个例子：

select user_id, count(*) from user_info where xxx group by user_id having count(*) > 1;

这个语句的作用是：筛选出user_info表中user_id重复的记录，首先使用where条件过滤，然后根据user_id分组（使用group by之后，上下文变为分组）,所以聚合函数count（）是针对每组进行count，然后使用having对得到的信息进行筛选，选出count（）大于1的数据，即重复的user_id记录

mysql函数

mysql 有很多强大的函数，可以做很多事情

  • datediff 函数返回两个日期之间的天数
SELECT DATEDIFF(‘2018-07-01‘,‘2018-07-04‘);
  • concat() 将多个字符串连接成一个字符串。
concat(str1, seperator,str2,seperator,...)
  • JSON_EXTRACT() json解析
JSON_EXTRACT (data, '$.filed'),

HQL高级使用

行转列（列数据--array、map拆多行）

  • explode：将hive中一列的array或map拆分为多行
  • lateral view：与类似explode这种udtf一起使用，将udtf结果放到虚拟表，然后与原本数据表每个id做join
A              B                          C
190     [1030,1031,1032,1033,1190]      id1
191     [1030,1031,1032,1033,1190]      id2

# 使用explode 拆分 B列
select explode(B) as D from t  
# explode函数时不能有其他列，错误例子
select A,explode(B),C as D from t # 这样写是错误的
# 正确的做法，lateral view udtf(expression) <table_name> as <column_name>
select A,B,C from test lateral view explode(B) tmp as B

列转行（多行数据转为一行）

  • collect_set 对group by的数据进行去重，并形成一个集合
  • concat_ws 对集合元素进行合并
⚠️ **GitHub.com Fallback** ⚠️