SQL基础模块
SQL(二) —— join窗口函数视图
大家可以关注知乎或微信公众号的share16,我们也会同步更新此文章。
一、视图与子查询
1.1 什么是视图
视图
是一个虚拟表,不同于直接操作数据表,视图是依据SELECT语句来创建的,所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表,然后在这张虚拟表上做SQL操作。
视图存在的原因:
- 通过定义视图,可将频繁使用的SELECT语句保存以提高效率;
- 通过定义视图,可使用户看到的数据更加清晰;
- 通过定义视图,可不对外公开数据表全部字段,增强数据的保密性;
- 通过定义视图,可降低数据的冗余;
视图与表的区别:是否保存了实际的数据
1.2 视图操作
创建视图
CREATE VIEW < 视图名 > AS
< SELECT语句 >
修改视图
ALTER VIEW < 视图名 > AS
< SELECT语句 >
删除视图
DROP VIEW < 视图名 >
视图归根结底还是从表派生出来的,因此若原表更新,那么视图中的数据也可以更新;反之若视图发生了改变,而原表没有进行相应更新的话,就无法保证数据的一致性。
1.3 什么是子查询
子查询
也被称为嵌套查询,指一个查询语句嵌套在另一个查询语句内部的查询;当查询里使用子查询时,子查询首先被执行,然后主查询再根据子查询返回的结果执行。
- 嵌套子查询:子查询里面还能嵌套SELECT语句;
- 标量子查询:也叫单一子查询,即返回表中具体的“某一行的某一列”;
- 关联子查询:是依赖主查询里的信息的子查询,这意味着子查询里的表可以与主查询里的表相关联(可理解为:在主查询和子查询里,有用到相同的表);
二、函数
所谓函数,类似一个黑盒子,你给它一个输入值,它便按照预设的程序定义给出返回值,输入值称为参数。
- 算术函数:除了+ - * /外,还有 绝对值ABS()、求余MOD()、四舍五入ROUND()等;
- 字符串函数:拼接CONCAT(s1,s2,…)、长度LENGTH(s)、替换REPLACE(s,old,new)、截取(pop即索引,从1开始,python从0开始) SUBSTRING(str FROM pos FOR len)、截取(先按delim对str分割,索引从1开始) SUBSTRING_INDEX(str,delim,count)等;
- 日期函数:now()、curdate()、current_date()、current_time()、获取date的年/时等EXTRACT(unit FROM date)等;
- 转换函数:字符串转数字CAST(‘123’ AS BINARY)、数字转字符串CAST(123 AS CHAR)、返回从左开始第一个非空值COALESCE(value1,value2,value3,…)等;
- 谓词:LIKE(
%
匹配零个或多个字符串,_
匹配任意1个字符)、IN、BETWEEN、IS NULL、IS NOT NULL等; - CASE WHEN表达式:sum(case when…)、count(case when…)等;
CASE WHEN < 求值表达式 > THEN < 表达式 >
WHEN < 求值表达式 > THEN < 表达式 > ...
ELSE < 表达式 > END [AS < 字段名 >]
三、数据拼接(union/join)
数据拼接
大致可分为两类,即上下拼接(集合运算)和左右拼接(列连接)。
集合运算
- union:取并集且去重,但
union all
取并集但不去重;(上下两表的字段个数&数值类型要一致;若要order排序,只需写在最后一行即可); - intersect:取交集,但目前的mysql版本不支持运算;
- except:取差集(前面的表减去后面的表,若顺序不同,运行结果也不同),但目前的mysql版本不支持运算;
列连接
- 内连接(join或inner join):两边都有的数据才会保留,类似于交集;
- 左连接(left join):左表的数据都会保留,若右表没有可匹配的数据,则返回null;
- 右连接(right join):右表的数据都会保留,若左表没有可匹配的数据,则返回null;
- 全连接(full join):不管两边能否匹配,所有数据都会保留,若无可匹配的数据,则返回null;
- 笛卡尔积(cross join):也称交叉结合,如左表3条记录,右表4条记录,结果有12条记录;
四、窗口函数
窗口函数
也称为OLAP函数,意思是对数据库数据进行实时分析处理。常规的SELECT语句都是对整张表进行查询,而窗口函数可以让我们有选择的取某一部分数据进行汇总、计算和排序。
<窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <列名>)
partition by:用来分组,类似于group by子句;order by:用来排序,即决定按哪种规则(字段)来排序;
- 专用窗口函数:有rank、dense_rank、row_number等;
- 聚合函数的应用:有sum、avg、max等,其结果是一个累计值;
- 添加小计/总计等:group by子句后,添加
WITH ROLLUP
;
五、练习题
练习题用到的数据集,点此下载。
01. 连接(join)
请使用A股上市公司季度营收预测数据集income_statement、company_operating和market_data;以market _data为主表,将三张表中的TICKER_SYMBOL为000545和200550的信息合并在一起(只需要显示以下字段)。
select distinct a.TICKER_SYMBOL,a.END_DATE,a.CLOSE_PRICE,
b.INDIC_NAME_EN,b.VALUE,c.T_REVENUE,c.T_COGS,c.N_INCOME
from market_data a
left join company_operating b
on a.TICKER_SYMBOL = b.TICKER_SYMBOL and a.END_DATE = b.END_DATE
left join income_statement c
on a.TICKER_SYMBOL = c.TICKER_SYMBOL and a.END_DATE = c.END_DATE
where a.TICKER_SYMBOL in (000545,200550)
order by a.TICKER_SYMBOL,a.END_DATE
使用A股上市公司季度营收预测中的数据集Macro_Industry,计算’Depository Securities: Circulation Market Value of Listed Stocks’在2015年用电最高峰是发生在哪月?并且相比去年同期增长/减少了多少个百分比?
select m.*,n.value_2014,round((m.value_2015-n.value_2014)/n.value_2014,2) 'rate'
from (select month(PERIOD_DATE)'month',sum(DATA_VALUE)'value_2015'
from macro_industry
where year(PERIOD_DATE) = 2015
and name_cn = 'Depository Securities: Circulation Market Value of Listed Stocks'
group by month ) m
left join (select month(PERIOD_DATE)'month',sum(DATA_VALUE)'value_2014'
from macro_industry
where year(PERIOD_DATE) = 2014
and name_cn = 'Depository Securities: Circulation Market Value of Listed Stocks'
group by month ) n on m.month = n.month
order by m.value_2015 desc
02. 排序(rank/dense_rank/row_number)
请使用数据集winequality-red,找出pH=3.03的所有红葡萄酒,然后对其citric acid进行中式排名(相同排名的下一个名次应该是下一个连续的整数值,换句话说名次之间不应该有“间隔”)。
select pH,`citric acid`,
dense_rank() over(order by `citric acid`) '排名'
from `winequality-red`
where pH = 3.03
请使用数据集winequality-white,找出pH=3.63的所有白葡萄酒,然后对其residual sugar量进行英式排名(非连续的排名)。
select pH, `residual sugar`,
rank() over(order by `residual sugar`) '排名'
from `winequality-white`
where pH = 3.63
03. 分割字符串(substring_index)
使用数据集ccf_offline_stage1_test_revised,分别找出在2016年7月期间,发放优惠券总金额最多和发放优惠券张数最多的商家(只考虑满减,不考虑打几折的;Discount_rate:x in [0,1]代表折扣率,x:y表示满x减y)。
select Merchant_id,sum(amount),count(Coupon_id)
from (select Merchant_id,Coupon_id,Discount_rate,
SUBSTRING_INDEX(Discount_rate,':',-1) amount,
case when (Discount_rate between 0 and 1) then '折扣' else '满减' end as type
from ccf_offline_stage1_test_revised
where Date_received >= '2016-07-01' and Date_received <= '2016-07-31') k
where k.type = '满减'
group by Merchant_id
order by sum(amount) desc -- 优惠券总金额最多的商家
#order by count(Coupon_id) desc -- 优惠券张数最多的商家
limit 1
谢谢大家 🌹