常用SQL模板

总结一下曾经值得欣赏的SQL

一、.模板一 ：一个字段有多个可取值，先要统计每个可取值数量

demo1 统计从 常驻/流动人口

-- 统计常驻人口、流动人口
-- migration_type 人口流动 (1:常住;2:常访客;3:临时访客)
select sum(mobile) as mobile,sum(permanent) as permanent
from
(select 
case when migration_type='1'  then 1 else 0 end mobile ,
case when migration_type in('2','3')  then 1 else 0 end permanent
from sys_staff_info where domain = 'default') tmp

demo2

-- 分别统计统计独居老人、养犬人员、五保户、重点关注人群、其他
-- people_type 人群类型（0:正常;1:独居老人;2:养犬人员;3:五保户;4:疆藏;5:精神病;6:刑满释放;7:社区矫正;8:吸毒人员;9:上访人员）
select sum(olderly_alone) as olderly_alone,sum(dog_keeeper) as dog_keeeper,
sum(low_salary) as low_salary,sum(focus_crowd) as focus_crowd,sum(others) as others
from
(select 
case when people_type='1'  then 1 else 0 end olderly_alone,
case when people_type='2'  then 1 else 0 end dog_keeeper,
case when people_type='3'  then 1 else 0 end low_salary,
case when people_type in('4','5','6','7','8','9')  then 1 else 0 end focus_crowd,
case when people_type in('0','')  then 1 else 0 end others
from sys_staff_info where domain = 'default') tmp

demo3
 

-- 统计疫情级别
-- country 国籍(0:中国;1:外国)  sys_city 0:低;1:中;2:高
select sum(low) as low, sum(middle) as middle, sum(high) as high, sum(alien) as alien from (
 select case when (tb.level is null or  tb.level = '0') and ta.country = '0' then 1 else 0 end low,
 case when tb.level = '1' and ta.country = '0' then 1 else 0 end middle,
 case when tb.level = '2' and ta.country = '0' then 1 else 0 end high,
 case when ta.country = '1' then 1 else 0 end alien
 from sys_staff_info ta left join sys_city tb on ta.city = tb.name
 where domain = 'default'
) ta

备注：

-- uft下中文 能存33个中文字符。如果有空值，最好 not null default ''
desc_info                     varchar(100) not null default '' comment '数据集备注信息'

--  日期自动更新
updated_time				  datetime not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '更新日期'

-- 多种类型，可以用char(1) 。mysql不支持bool，用char(1) or tinyint 
	dataset_source_type          char(1) not null default 1 comment '数据类型：1上传图片；2上传压缩包'

二、模板二：SQL 自连接

 

 1. SQL 自连接案例（1）--  AB BA 问题

有这么一组数据

CREATE TABLE `phone` (
	`call` VARCHAR(11) NULL DEFAULT NULL,
	`called` VARCHAR(11) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

INSERT INTO phone VALUES("A","B");
INSERT INTO phone VALUES("B","A");
INSERT INTO phone VALUES("A","C");
INSERT INTO phone VALUES("A","D");
INSERT INTO phone VALUES("D","A");
INSERT INTO phone VALUES("A","B");

如图

A 给B打过电话，记作AB；B给A打过电话，记作BA。

1. 那么希望得到A给B打过电话，且B给A打过电话的数据

select t1.call,t1.called from phone t1, phone t2  WHERE t1.call=t2.called and t1.called=t2.call GROUP BY t1.call,t1.called

2. 对于A给B打过电话，且B给A打过电话的数据，只保留一条。即相同的记录只是出现一条其实这是加入 and t1.call<t1.called  完事儿。

select t1.call,t1.called from phone t1, phone t2  WHERE t1.call=t2.called and t1.called=t2.call and t1.call<t1.called GROUP BY t1.call,t1.called

 

是基于如下sql的变形

select hex(call) as hex_a ,hex(called) as hex_b from tmp where hex_a<hex_b ;

2.SQL自连接案例 -- 分组求TOPN

 

遇见一个难题：分组求组内topn，由于mysql没有row_number()查阅了许些资料，mark。

需求：取出每个班级的前两名同学(允许并列)

创建表

 

create table tmp(
id int not null auto_increment,  
sname varchar(10),
cname varchar(10),
score int,
PRIMARY key(id)
)engine=InnoDB CHARSET=UTF8;  

插入数据

insert into tmp values(null,'AAAA','C1',67);
insert into tmp values(null,'BBBB','C1',55);
insert into tmp values(null,'CCCC','C1',67);
insert into tmp values(null,'DDDD','C1',65);
insert into tmp values(null,'EEEE','C1',95);
insert into tmp values(null,'FFFF','C2',57);
insert into tmp values(null,'GGGG','C2',87);
insert into tmp values(null,'HHHH','C2',74);
insert into tmp values(null,'IIII','C2',52);
insert into tmp values(null,'JJJJ','C2',81);
insert into tmp values(null,'KKKK','C2',67);
insert into tmp values(null,'LLLL','C2',66);
insert into tmp values(null,'MMMM','C2',63);
insert into tmp values(null,'NNNN','C3',99);
insert into tmp values(null,'OOOO','C3',50);
insert into tmp values(null,'PPPP','C3',59);
insert into tmp values(null,'QQQQ','C3',66);
insert into tmp values(null,'RRRR','C3',76);
insert into tmp values(null,'SSSS','C3',50);
insert into tmp values(null,'TTTT','C3',50);
insert into tmp values(null,'UUUU','C3',64);
insert into tmp values(null,'VVVV','C3',74);

方法一：

select a.id,a.sname,a.cname,a.score
from tmp a 
left join tmp b
on a.cname=b.cname and a.score<b.score
group by a.id,a.sname,a.cname,a.score
having count(b.id)<2
order by a.cname,a.score desc

方法二：

select *
from tmp a
where 2>(select count(*) from tmp where cname=a.cname and score>a.score)
order by a.cname,a.score desc

如果是求top1

select *
from tmp a
where not exists (select 1 from tmp where cname=a.cname and score>a.score);


select a.*
from tmp a inner join (select cname, max(score) as score from tmp group by cname) b
on a.cname=b.cname and a.score=b.score

select *
from (select * from tmp order by score desc) t
group by cname

但在hive中有row_number()函数，举个例子：

需求：按照时间维度，比如，统计一天内各小时产生最多pvs的来源topN

insert into table zs.dw_pvs_refhost_topn_h partition(datestr='2016-03-18')

select t.hour,t.od,t.ref_host,t.ref_host_cnts 
from
(select ref_host,ref_host_cnts,concat(month,day,hour) as hour,
row_number() over (partition by concat(month,day,hour) order by ref_host_cnts desc) as od
from zs.dw_ref_host_visit_cnts_h
) t where od<=3;

看一下内层表实现：对每个小时内的来访host次数倒序排序标号

select ref_host,ref_host_cnts,concat(month,hour,day),
row_number() over (partition by concat(month,hour,day) order by ref_host_cnts desc) as od 
from 
dw_ref_host_visit_cnts_h

结果

+-------------------------+----------------+----------+-----+--+
|        ref_host         | ref_host_cnts  |    c2    | od  |
+-------------------------+----------------+----------+-----+--+
| blog.fens.me            | 111            | 09 0019  | 1   |
| www.fens.me             | 13             | 09 0019  | 2   |
| h2w.iask.cn             | 6              | 09 0019  | 3   |
| angularjs.cn            | 3              | 09 0019  | 4   |
| www.google.com.hk       | 3              | 09 0019  | 5   |
| www.leonarding.com      | 1              | 09 0019  | 6   |
| cnodejs.org             | 1              | 09 0019  | 7   |
| www.itpub.net           | 1              | 09 0019  | 8   |
| blog.fens.me            | 89             | 09 0119  | 1   |
| cos.name                | 3              | 09 0119  | 2   |
| www.google.com.tw       | 2              | 09 0119  | 3   |
| www.angularjs.cn        | 2              | 09 0119  | 4   |
| mp.weixin.qq.com        | 1              | 09 0119  | 5   |
| h2w.iask.cn             | 1              | 09 0119  | 6   |
| cnodejs.org             | 1              | 09 0119  | 7   |
| weibo.com               | 1              | 09 0119  | 8   |
| www.google.com.hk       | 1              | 09 0119  | 9   |
| blog.fens.me            | 311            | 09 0219  | 1   |
| www.google.com          | 3              | 09 0219  | 2   |
| www.google.com.hk       | 3              | 09 0219  | 3   |

row_number()从1开始，为每一条分组记录返回一个数字

row_number() over (order by col desc) 是先把col列降序，再为降序以后的每条col记录返回一个序号。

col row_num
999   1
888   2
777   3
666   4

row_number() over (partition by col1 order by col2) 表示根据col1分组，在分组内部根据col22排序，
而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
 

 

三、SQL模板三 求差集 [简单]

注意 on后边where条件表示过滤

A表为账期201902，B表为账期201902

当前需要求出增量手机号 即A - B的手机号

select device from A

left join

select device from B

on A.device =B.device

where B.device is null

 

 

 

 

 

附 : mysql 从 A 库导入到 B 库

mysql 从 A 库导入到 B 库
1. 测试连接mysql 
mysql -h -u -p

2.将mysql导出到文件
mysqldump -h -u -p A > A.txt

3.将文件导入到mysql

3.1 方法一：
（1）选择数据库
use B;
（2）设置数据库编码
set names utf8;
（3）导入数据（注意sql文件的路径）
source /mypath/A.txt;

3.2 方法二：
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
#mysql -u -p B < /mypath/A.txt

关于mysql的导出

选中数据库 --> 导出数据为SQL脚本 -->SQL导出 --> [ 选择对 数据库 表 数据 的操作]