您的位置: 首页  >  文章  >  数据分析SQL问题整理

数据分析SQL问题整理

分类: 文章 • 2023-03-24 19:52:01

1.窗口函数

  • 解决问题：
    组内排名（如全校初三同学英语成绩按照班级排名，如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…数据分析SQL问题整理
      图片来源：https://zhuanlan.zhihu.com/p/92654574

参考文章

2. 各种连接方式的区别？

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

3. 索引的作用？

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

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

  • 聚集索引：索引记录的顺序与表记录的顺序一致。（字典中字母查找：索引表按照拼音a-z排序，实际汉字信息也按拼音顺序排）
  • 非聚集索引：索引记录的顺序与表记录的顺序不一致一致。（部首查找：索引按部首笔画排序，实际汉字信息不按照索引表中相邻顺序排序）

4. on 和 where 的区别？

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

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

5. 连接多个select

  • union（去重并集）: 列数据相同时，上下去重拼接。（拼接多个班的学生id与英语成绩）
  • union all（直接并集）: 作用同union,但是允许重复值。
  • Intersect（去重交集）：作用同union,但是取两表重复部分。
  • B minus C（C-B）：作用同union,但是取C特有但B没有的数据。

6. 主键与外键

  • 主键是一张表中能够确定一条记录的唯一标志。
  • 外键用于和另一张表进行关联。例如，A字段是A表的主键，那么出现在B表中的A字段能够作为B表的外键，实现A,B表的连接查询。

7. 字符串常见操作

info表：

locus id journal
AB086827 1 Unpublished
AB086827 2 Submitted (20-JUN-2002)
AF040764 23 Unpublished
AF040764 24 Submitted (31-DEC-1997)
函数 用法 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()

参考文章：

8. In/exist的联系与区别

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

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

9.表中插入数据？

  • insert into tablename values: 普通插入数据模式
  • insert or ignore into: 如果没有则插入数据，如果有则忽略
  • insert or replace into: 如果不存在就插入，存在就更新

10.删除表中数据

  • delete : 删除表中数据，可以指定具体数据（where）
  • drop column/ drop table : 删除列数据以及表的结构全部删除。
  • truncate: 删除所有数据，保留表结构

参考文章：

SQL提高查询效率

  1. 多表左连接时，最左表选择数据量最小的数据表。
  2. union all 比union快：因为union all直接拼接两表，不去重，union去重耗时。
  3. 避免复杂SQL语句，必要时创建中间表，提高代码可读性。
  4. 避免select *，避免where=1

相关推荐