SQL入门
一、 sql语句的执行顺序
1、 sql语句一共分为11步，如下所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。
2、 其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，就只有最后一个虚拟的表才会被作为结果返回。
(8) SELECT (9) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) WITH {CUBE|ROLLUP} (7) HAVING <having_condition> (10) ORDER BY <order_by_list> (11) LIMIT <limit_number>
Ps: (1)、(2)、(3)、(4)、(5)…… 是sql的执行顺序
建议：使用sql的时候，尽量加上limit 10;
3、 下面我们来具体分析一下查询处理的每一个阶段
1). FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2). ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
3). JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4). WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
5). GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6). CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7). HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
8). SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9). DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10). ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11). LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。
二、 行转列（根据主键，进行多行合并一列）
使用函数：concat_ws(',',collect_set(column))
说明：collect_list不去重，collect_set去重。column的数据类型要求是String类型。concat_ws是带分隔符的字符串连接函数
eg: 如表：t_row_to_column数据如下，根据id，将要对tag_new进行合并
hive(default)> select * from t_row_to_column;
id tag_new
212121 1
212121 12
212121 15
212121 1
212121 12
212121 15
Time taken: 0.059 seconds, Fetched: 6 row(s)
SQL语句1：
Select id,concat_ws(‘,’,collect_set(tag_new)) as tag_col from t_row_to_column group by id;
结果如下：
id tag_col
212121 1,12,15
SQL语句2：
select id,concat_ws(‘,’,collect_list(tag_new)) as tag_col from t_row_to_column group by id;
结果如下：
id tag_col
212121 1,12,15,1,12,15
三、 列转行（对某列拆分，一列拆多行）
使用函数：lateral view explode(split(column,’,’)) xxx xxx（自定义名称）代表虚拟表，不能缺少
eg: 如表：t_column_to_row 数据如下，将要对tag列进行拆分
hive(default)> select * from t_column_to_row;
id tag
212121 9,12
212122 12,15
212123 12,15
212124 1,12,15
212125 1,12
Time taken: 0.067 seconds, Fetched: 6 row(s)
SQL语句：
select id,tag,tag_new
from t_column_to_row lateral
view explode(split(tag,’,’)) num as tag_new
where id = 212121;
结果如下：
id tag tag_new
212121 9,12 9
212121 9,12 12
四、 分组获取top N
使用函数：row_number
eg: 如表：t_select_rownumber 数据如下，根据deptid分组，显示每个部门的工资情况
hive(default)> select * from t_select_rownumber;
empid deptid visit
1 10 5500.00
2 10 4500.00
3 20 1900.00
4 20 4800.00
5 40 6500.00
6 40 14500.00
7 40 44500.00
8 50 6500.00
9 50 7500.00
Time taken: 0.061 seconds, Fetched: 6 row(s)
SQL语句：
select *, row_number() over (partition by deptid order by visit desc) rank from t_select_rownumber; select *, row_number() over (partition by 客户号 oder by 日期 desc) ranknum from table_name;
结果如下：
empid deptid visit rank
1 10 5500.00 1
2 10 4500.00 2
4 20 4800.00 1
3 20 1900.00 2
7 40 44500.00 1
6 40 14500.00 2
5 40 6500.00 3
9 50 7500.00 1
8 50 6500.00 2
eg: 如表：t_select_rownumber 数据如下，根据deptid分组，显示每个部门的最高工资
hive(default)> select * from t_select_rownumber;
empid deptid visit
1 10 5500.00
2 10 4500.00
3 20 1900.00
4 20 4800.00
5 40 6500.00
6 40 14500.00
7 40 44500.00
8 50 6500.00
9 50 7500.00
Time taken: 0.061 seconds, Fetched: 6 row(s)
SQL语句：
select *, row_number() over (partition by deptid order by visit desc) rank from t_select_rownumber where rank = 1;
结果如下：
empid deptid visit rank
1 10 5500.00 1
4 20 4800.00 1
7 40 44500.00 1
9 50 7500.00 1
五、 级联求和
要求：
有如下访客访问次数统计表 t_access_times
|
访客 |
月份 |
访问次数 |
|
A |
2015-01 |
5 |
|
A |
2015-01 |
15 |
|
B |
2015-01 |
5 |
|
A |
2015-01 |
8 |
|
B |
2015-01 |
25 |
|
A |
2015-01 |
5 |
|
A |
2015-02 |
4 |
|
A |
2015-02 |
6 |
|
B |
2015-02 |
10 |
|
B |
2015-02 |
5 |
|
…… |
…… |
…… |
需要输出报表：t_access_times_accumulate
|
访客 |
月份 |
月访问总计 |
累计访问总计 |
|
A |
2015-01 |
33 |
33 |
|
A |
2015-02 |
10 |
43 |
|
……. |
……. |
……. |
……. |
|
B |
2015-01 |
30 |
30 |
|
B |
2015-02 |
15 |
45 |
|
……. |
……. |
……. |
……. |
难点在统计累计访问总计这个指标
思路：
第一步：先求每个访客用户月的总访问次数
select username,month,sum(visit) as visit from t_access_times group by username,month
结果如下：
|
Username |
Month |
Visit |
|
A |
2015-01 |
33 |
|
A |
2015-02 |
10 |
|
……. |
……. |
……. |
|
B |
2015-01 |
30 |
|
B |
2015-02 |
15 |
|
……. |
……. |
……. |
第二步：将月总访问次数自己连接自己
select * from (select username,month,sum(visit) as visit from t_access_times group by username,month) A inner join (select username,month,sum(visit) as visit from t_access_times group by username,month) B on username=B.username where b.month <= a.month
结果如下：
|
Username |
Month |
Visit |
Username |
Month |
Visit |
|
|
A |
2015-01 |
33 |
A |
2015-01 |
33 |
|
|
A |
2015-01 |
33 |
A |
2015-02 |
10 |
|
|
A |
2015-02 |
10 |
A |
2015-01 |
33 |
|
|
A |
2015-02 |
10 |
A |
2015-02 |
10 |
|
|
B |
2015-01 |
30 |
B |
2015-01 |
30 |
|
|
B |
2015-01 |
30 |
B |
2015-02 |
15 |
|
|
B |
2015-02 |
15 |
B |
2015-01 |
30 |
|
|
B |
2015-02 |
15 |
B |
2015-02 |
15 |
|
第三步：第三步，从上一步的结果中
进行分组查询，分组的字段是a.username a.month
求当月累计值： 将b.month <= a.month的所有b.visit求和即可
有两个字段在分组函数group by里面，要想还有一个字段展示并且不报错，需要使用一个聚合函数处理这个字段max(A.visit)
select A.username,A.month,max(A.visit) as visit,sum(B.visit) as accumulate from (select username,month,sum(visit) as visit from t_access_times group by username,month) A inner join (select username,month,sum(visit) as visit from t_access_times group by username,month) B on A.username=B.username where B.month <= A.month group by A.username,A.month order by A.username,A.month;
六、 Join
1、left join on 之后and 和 where 的区别
众所周知，数据库的表都是单独存在的，但是当我们进行联合查询(多表查询)时，我们获得数据库返回的值时就好像在一张表里一样，这是因为在进行联合查询时数据库会生成一个临时表返回给我们所想要的数据信息，这时我们都是通过LEFT JOIN 等语句进行相关联，并且我们也会为我们所想查询的数据进行一个筛选，这时我们就会用到过滤语句。
LEFT JOIN ON WHERE：在临时表生成后，再对临时表的数据进行过滤，再返回左表。
LEFT JOIN ON AND：在临时表生成的过程时，ON中的条件不管是否为真，都将返回左表。
例如：
表1.id 表1.value 表2.value 表2.name
1 100 100 开心
2 200 200 很开心
3 300 300 超级开心
4 400 400 无敌开心
SQL语句如下：
[1] SELECT * FROM 表1 LEFT JOIN 表2 ON (表1.value = 表2.value) WHERE 表2.name = 开心 [2] SELECT * FROM 表1 LEFT JOIN 表2 ON (表1.value = 表2.value) and 表2.name = 开心
当执行[1]的时候得：
表1.id 表1.value 表2.value 表2.name
1 100 100 开心
当执行[2]的时候得：
表1.id 表1.value 表2.value 表2.name
1 100 100 开心
2 200 NULL NULL
3 300 NULL NULL
4 400 NULL NULL
同理，RIGHT JOIN和FULL JOIN都具备这个特性，注意，INNER JOIN不具备这个特性。
2、join前尽量减少重复关联键
join时，最好对on条件中的字段进行distinct。原因：a表的gid有3条重复的，b表只有一条和其相等，join的时候，会出现3条记录。如下：
select a.gid,b.gid from a join b on a.gid=b.gid;
a.gid b.gid
1ab 1ab
1ab 1ab
1ab 1ab
3、join时，将重复关联键少的表放在join关键字的左边，做关联时可以提高join的效率
4、 join图解
原始数据：
id name id name
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja
左连接
SQL语句：
SELECT * FROM TableA LEFT JOIN TableB ON TableA.name = TableB.name
结果如下：
id name id name
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
Ps：左连接（left join）生成表A的所有记录，包括在表B里匹配的记录。如果表B里没有匹配的，右边将是null。（如下图）
七、 case when then else end
select 字段1, 字段2, case 字段3 when 值1 then 新值 when 值2 then 新值 else 新值 end as 重新命名字段3的名字 from table where …… order by ……
eg1：
select id, case sex when '1' then '男' when '2' then '女' else '其他' end, months from table_name;
case的另一种用法：case后可无对象，而在when后添加条件判断语句，如，
case when 字段1='a' then true else false end;
select 字段1, 字段2, case when 条件1 then 值1 when 条件2 then 值2 else 字段3 end as 重新命名字段3的名字 from table where …… order by ……
eg2：
select id, case when orgname like '%营销%' then '营销' when orgname like '%物业%' then '物业' else '其他' end as org2, case when length(trim(jobgrade))=2 and trim(jobgrade) between 'G4' and 'G9' then 'G4-G9' when length(trim(jobgrade))=3 and trim(jobgrade) between 'G10' and 'G13' then 'G10-G13' when length(trim(jobgrade))=3 and trim(jobgrade) between 'G14' and 'G16' then 'G14-G16' when length(trim(jobgrade))=3 and trim(jobgrade) > 'G16' then 'G16以上' else '其他' end as grade, months from table_name;
eg3：
select guid, CONCAT('os_',os_id) AS os, case when (country_id = '' or country_id = 'NULL' or country_id isnull) and (province_id ='' or province_id = 'NULL' or province_id is null) and (city_id = ''or city_id = 'NULL' or city_id is null) then '' when (country_id = '' orcountry_id = 'NULL' or country_id is null) and (province_id<> '' or province_id <> 'NULL' or province_id is not null orcity_id <> '' or city_id <> 'NULL' or city_id is not null) then '1' else country_id end as ascountry_id, city_id, CONCAT('brand_',b.brand_id) AS brand, (CASE connection_type WHEN '2' THEN CONCAT('carrier_','wifi') ELSECONCAT('carrier_',c.element_id) END) AS carrier, uid, SUM(CASE WHEN logtype = '1' THEN 1 ELSE 0 END) AS imp_pv, SUM(CASE WHEN logtype = '2' THEN 1 ELSE 0 END) AS clk_pv from table_name;
eg4：
select managecom, subtype, count(*) loadsucc, sum(case when state in ('4', '5', '6', '7', '8', '9') then 1 else 0 end) recogsucc, sum(case when state in ('3', '12', '13') then 1 else 0 end) recogfail, sum(case when state in ('1', '2') then 1 else 0 end) waitrecog from table_name
八、 group by与聚合函数
"Group By"从字面意义上理解就是根据"By"指定的规则对数据进行分组，所谓的分组就是将一个"数据集"划分成若干个"小区域"，然后针对若干个"小区域"进行数据处理。
大部分数据库都支持的5个聚类函数
聚合函数 作用
sum(列名) 求和
max(列名) 最大值
min(列名) 最小值
avg(列名) 平均值
count(列名) 统计记录数
eg：
name class sex score
张1 1班 男 60
张2 1班 女 70
张3 1班 男 80
张4 1班 女 80
张5 2班 男 70
张6 2班 女 60
张7 2班 男 80
sql语句：
select class,sex,AVG(score) as avgscore from test group class,sex
结果如下：
class sex avgscore
1班 男 70
1班 女 75
2班 男 75
2班 女 60
九、 判断空与非空
判断某个字段非空时的四种情况（判断非空时，使用and）：
xxxx != '' and xxx is not null and xxx != ' ' and trim(xxx) != ''
判断某个字段是空的四种情况（判断是空时，使用or）：
xxxx = '' or xxx is null or xxx = ' ' or trim(xxx) = ''
十、 创建索引
1. 不要频繁更新的字段上建索引
2. 索引列通常用来做join
常常被用来join的字段，建索引可以提高join的性能。(如: 外键)
3. 索引列经常被频繁使用在where语句中
4. 批量导入数据需删除索引
导入大量数据时,先删除索引,等数据导完后再重建。这样速度会更快。
创建索引：
CREATE INDEX index_name ON table_name (column_name);
删除索引：
DROP INDEX index_name;
