数据分析SQL问题整理

最新推荐文章于 2024-12-16 14:10:06 发布

Schnappi_z

最新推荐文章于 2024-12-16 14:10:06 发布

阅读量463

点赞数

CC 4.0 BY-SA版权

分类专栏：数据分析笔试题

4 篇文章

订阅专栏

解决问题：
组内排名（如全校初三同学英语成绩按照班级排名，如100分、99分、99分、98分）
基本语法：
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>) as new_col
窗口函数类型
1. row number: 班级成绩排序生成连续无重复序列号。1,2,3,4,…
2. rank：相同成绩同学名次相同，名次数值不连续。1,2,2,4…
3. dense rank: 相同成绩同学名次相同，名次数值连续。1,2,2,3…
  图片来源：https://zhuanlan.zhihu.com/p/92654574

参考文章

inner_join：内连接，只有两个表共有的列值对应的行才能匹配出行数据。
left join/right join/all join：（左，右，全）外连接，以left join 为例，如果指定了需要匹配的列名，左表的数据都会提取出来，若右表有符合条件数据则匹配出来，若右表无符合条件数据，右表的值以空值的形式匹配出来。
cross join：交叉连接，结果是笛卡尔积，就是第一个表符合查询条件的行数乘以第二个表符合查询条件的行数。

为了提高数据库查询数据的速度而增加的标志符号（通过创建唯一性索引，可以保证表中每一行数据的唯一性）。索引主要建立在：经常搜索的列；主键所在列；外键所在列。

索引包括聚集索引与非聚集索引，它们的区别在于索引记录的顺序与表记录的顺序是否一致。

数据库在连接多张表返回记录时，都会生成一个中间临时表。

在内连接中：使用on或者where没有区别。
在外连接里：
- on是两表的连接键，left join时返回左表全部记录的中间表。
- where条件是在临时表生成好后，再对临时表进行过滤的条件。条件不为真的就全部过滤掉。

info表：

函数	用法	output	null值情况
concat()	concat(‘First name’,‘Last Name’)	First nameLast Name	concat(id, ‘NULL’, sub)=NULL
concat_ws()	concat_ws(’_’,‘First name’,‘Last Name’)	First name,Last Name	concat_ws(’,’ ,‘First name’, NULL, ‘Last Name’)=First name,Last Name
group concat()	SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN(‘AB086827’,‘AF040764’) GROUP BY locus	AB086827：1,2 AF040764:23,24
substr(string, start_position, [ length ] )	substr(‘This is a test’, 6, 2)	is
	substr(‘This is a test’, 6)	is a test
	substr(‘TechOnTheNet’, -6, 3)	The
regexp()

参考文章：

子查询过程中，In和exist函数效率比较

子查询的内表更大，则exist的效率更高
exist先查询外表，然后根据外表中的每一个记录，分别执行exist语句判断子查询的内表是否满足条件，满足条件就返回ture。
子查询的内表小，则in的效率高
in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。