![59aef228fef86f7fa37a43a847515a1f.png](https://img-blog.csdnimg.cn/img_convert/59aef228fef86f7fa37a43a847515a1f.png)
vipkid的数据分析笔试题:30道选择*2,2道SQL编程题*2,总分100,时长120mins
使用的是塞码网提供的编程环境,仅仅支持SQLite,不支持主流的Mysql,Oracle,同样也不支持Hive!
第一题:
1、Table_A(左),Table_B(右)的表结构如下:
![b4afe03f7274434fefbc4ba1cce0985e.png](https://img-blog.csdnimg.cn/img_convert/b4afe03f7274434fefbc4ba1cce0985e.png)
最终的实现结果,如下图,请编写SQL完成
![55d0186c80f27e9087d695d92216f6c1.png](https://img-blog.csdnimg.cn/img_convert/55d0186c80f27e9087d695d92216f6c1.png)
思路:
因为Table_A出现了null字段,所以Table_B作为主表,左连接;
SQLlite只支持左连接,不支持右连接!塞码网SQLite目前不支持中文!
select t2.col_1,t2.col_2,t1.col_3,t1.col_1 as col_11,t1.col_4
from (
select * from Table_B
)t1
left join(
select * from Table_A
)t2
on t1.col_1=t2.col_1
![8974a7b720963ef789e5ea32ea80af19.png](https://img-blog.csdnimg.cn/img_convert/8974a7b720963ef789e5ea32ea80af19.png)
对照上图,发现还差Table_B含有null的一行;
那么同样的道理,Table_A做主表,采用左连接,然后两者union all合并(不去除重复值)!
SELECT a.col_1,a.col_2,b.col_3,b.col_1,b.col_4
FROM (
SELECT col_1,col_2
FROM Table_A
) a
LEFT JOIN(
SELECT col_3,col_1,col_4
FROM Table_B
) b
ON a.col_1=b.col_1
UNION ALL
SELECT a.col_1,a.col_2,b.col_3,b.col_1,b.col_4
FROM (
SELECT col_3,col_1,col_4
FROM Table_B
) b
LEFT JOIN(
SELECT col_1,col_2
FROM Table_A
) a
ON a.col_1=b.col_1
WHERE a.col_1 IS NULL
![355c68e99865f80ddc2ed8e3dbb8fa71.png](https://img-blog.csdnimg.cn/img_convert/355c68e99865f80ddc2ed8e3dbb8fa71.png)
这题没有业务意义,就是纯粹考语法的题目!
如果采用order by排序,那么null总会置顶出现!(left join没有匹配上出现的null会置尾,order by字段后,null会置顶)!
补充:
•Union/unon all 联合/完全联合
• Union/union all 联合/完全联合:相当于新生成了一个表,行数增加
•前提:
列数相同
列的数据类型要相同
•Union 与 union all的区别:union 去掉重复行;union all不去重复行
第二题:
数据结构如下:
![5ce3360e17e53b6ad09a77080a0de28f.png](https://img-blog.csdnimg.cn/img_convert/5ce3360e17e53b6ad09a77080a0de28f.png)
最终实现的结果如下:
![80e0bcb84256401166759a3a8f0285cf.png](https://img-blog.csdnimg.cn/img_convert/80e0bcb84256401166759a3a8f0285cf.png)
思路:
要使用分析函数sum()over()
select t.month,t.sum_amount,sum(t.sum_amount)over( order by t.month) as 'add_amount'
from
(select left(date(paid_time),7) as 'month',sum(amount) as 'sum_amount'
from order_record
group by left(date(paid_time),7)
)t
倒霉的是:mysql可以跑出结果,Sqlite却报语法错误,也不知道哪里有问题
请各位看官指正!!!
补充:
select empno,ename,sal,sum(sal) over( partition by deptno order by sal) ,deptno from emp;
![75a333561b160a559d3cc0fc7a200b8c.png](https://img-blog.csdnimg.cn/img_convert/75a333561b160a559d3cc0fc7a200b8c.png)
所有的分析函数:都是先执行over后的分组排序,再对分组排序结果(内存中,不可见)进行聚合(求和,求平均,求最小,最大)
partiton by 与order by都会对sum()over()产生影响!