数据分析师SQL数据库操作试卷及答案

数据分析师SQL数据库操作试卷及答案一、单项选择题（共10题，每题1分，共10分）关于标准SQL查询语句的默认执行顺序，以下描述正确的是A.先执行select子句筛选字段，再执行where子句筛选行B.先执行from子句完成表关联，再执行where子句做行级筛选C.先执行groupby子句做分组，再执行from子句加载表数据D.执行顺序和用户书写SQL的先后顺序完全一致答案：B解析：标准SQL的执行逻辑是先加载from子句关联的所有表的数据，再逐层做筛选、分组、聚合等操作。选项A错误，where子句执行优先级远高于select子句，不会先筛选字段再筛选行；选项C错误，groupby的执行顺序排在where子句之后，不可能在from加载数据前执行；选项D错误，SQL书写顺序是select、from、where，和实际执行顺序完全不同。关于where子句和having子句的差异，以下描述正确的是A.having子句可以在任意位置使用，没有执行顺序限制B.where子句可以直接使用聚合函数作为筛选条件C.having子句通常配合groupby分组逻辑使用，用来筛选分组后的聚合结果D.二者完全等价，可以任意互相替换答案：C解析：having子句的设计目标就是针对分组聚合之后的结果做筛选，无法脱离分组逻辑独立实现同类功能。选项A错误，having子句的执行顺序在分组聚合阶段，不能独立使用；选项B错误，where子句执行在聚合操作之前，无法识别聚合函数的计算结果；选项D错误，二者执行阶段和适用场景完全不同，无法等价替换。统计用户表中符合条件的记录总条数，想要得到包含所有null值记录的准确总数量，以下写法效果正确的是A.count(*)B.count(用户id)C.count(用户昵称)D.count(用户手机号)答案：A解析：count(*)会扫描所有行的存在性，完全不会判断字段内容，哪怕所有字段都是null也会正常统计该行，得到的结果是全表总行数。选项B、C、D错误，这几种count写法都会跳过对应字段值为null的记录，统计结果会比实际总条数少。以下关于关联子查询的描述，正确的是A.关联子查询不需要引用外层查询的任何字段B.关联子查询的内层查询会依赖外层查询的字段值逐行执行C.关联子查询的执行效率一定比普通非关联子查询更高D.关联子查询不能和join操作等价改写答案：B解析：关联子查询的核心特征就是内层查询会引用外层查询的字段，外层查询每扫描一行，内层子查询就会执行一次。选项A错误，关联子查询必须引用外层字段才能形成关联逻辑；选项C错误，关联子查询逐行执行的特性会在数据量大的时候产生大量IO，执行效率通常不如提前计算好的非关联子查询；选项D错误，绝大多数关联子查询都可以通过innerjoin、leftjoin等关联方式实现完全等价的逻辑。如果想要匹配字段中任意位置包含下划线的字符串，在标准SQL的like语法中需要转义下划线，原因是A.下划线是like语法的通配符，代表匹配任意单个字符B.下划线是like语法的通配符，代表匹配任意0个或多个字符C.下划线是SQL语句的保留关键字，必须全程转义D.下划线本身没有特殊含义，不需要做任何转义答案：A解析：标准SQL的like语法中，下划线_是单字符通配符，可以匹配任意一个位置的单个字符，想要匹配实际的下划线字符必须用escape关键字指定转义字符。选项B错误，代表匹配任意多个字符的通配符是百分号%；选项C错误，下划线不是SQL的保留关键字，只在like语法中存在特殊含义；选项D错误，下划线在like场景下有特殊含义必须转义。数据库事务的ACID特性中，“一致性”指的是A.事务一旦提交，对数据的修改是永久生效的B.多个事务并发执行时，相互之间不会产生干扰C.事务执行前后，数据库的所有数据约束都不会被破坏D.事务中的所有操作要么全部成功执行，要么全部回滚答案：C解析：一致性的核心定义就是事务执行前后，数据库的完整性约束（包括主键唯一、字段非空、自定义校验规则等）始终保持合法状态。选项A对应的是事务的持久性特性；选项B对应的是事务的隔离性特性；选项D对应的是事务的原子性特性。关于union和unionall的差异，以下描述正确的是A.union的执行速度比unionall更快B.unionall会自动对合并后的结果集执行去重操作C.union会自动对合并后的结果集执行排序和去重操作D.二者输出的结果集完全没有区别答案：C解析：union操作会在合并两个结果集之后自动执行全局排序和去重，保证返回的所有行都是唯一的。选项A错误，union因为多了排序去重的步骤，性能远低于不需要做去重排序的unionall；选项B错误，unionall的核心特性就是直接合并结果不会做任何额外处理，不会执行去重；选项D错误，二者在去重逻辑、性能、结果顺序上都有明显差异。关于表的主键约束，以下描述正确的是A.一张表可以同时设置多个主键约束B.主键约束的字段允许为空值C.主键约束会自动为对应字段创建唯一索引D.主键字段的内容允许出现重复值答案：C解析：设置主键约束的时候，数据库会自动在对应字段上创建唯一索引，用来保证主键的唯一性和查询效率。选项A错误，一张表有且只能有一个主键约束；选项B错误，主键约束强制字段非空，不能接受任何null值；选项D错误，主键约束的核心规则就是字段值全局唯一，不能出现重复。在百万级用户行为日志大表中执行模糊查询，想要查找所有昵称包含“张”的用户，以下写法效率最高的是A.where用户昵称like‘%张%’B.where用户昵称like‘张%’C.where用户昵称like‘%张’D.以上三种写法效率完全一致答案：B解析：当通配符出现在匹配字符串的末尾时，是可以利用字段上的索引进行快速前缀匹配的，不需要全表扫描。选项A的前后都加通配符、选项C的通配符在开头，都无法利用索引，必须全表逐行扫描比对，效率低很多。以下关于insert语句的描述，符合标准SQL规则的是A.insert语句只能一次插入一行数据B.insert语句插入的字段顺序必须和表定义的字段顺序完全对应，不能自定义调整C.insert语句可以通过select子查询的结果批量插入多行数据D.插入数据时所有字段都必须赋值，不能留空答案：C解析：insert…select语法支持直接将子查询返回的整个结果集批量插入到目标表中，是数据分析师日常做数据临时表落地最常用的写法。选项A错误，标准SQL支持多值insert，也支持通过子查询批量插入，不是只能插入单行；选项B错误，用户可以在insert后自定义指定插入字段的顺序，只要值的顺序和指定字段顺序对应即可，不需要和表定义顺序一致；选项D错误，只要字段没有设置非空约束，插入时可以不给字段赋值，自动填充为null。二、多项选择题（共10题，每题2分，共20分）以下属于SQL数据操纵语言（DML）范畴的语句有A.select查询语句B.update更新语句C.delete删除语句D.create建表语句答案：ABC解析：数据操纵语言的作用是操作表中的具体数据，查询、更新、删除都属于这个范畴。选项D的create建表语句属于数据定义语言（DDL），不属于DML的覆盖范围。在SQL查询中，可以实现结果集自动去重效果的写法有A.在select子句前添加distinct关键字B.对需要去重的字段执行groupby分组，不配置任何聚合函数C.使用开窗函数row_number()对重复行编号后筛选编号等于1的行D.对结果集执行orderby排序答案：ABC解析：三种写法都可以实现完全去重的效果。选项A的distinct是最直接的全结果去重；选项B的groupby会自动合并相同字段的行，天然具备去重效果；选项C的开窗函数编号后筛选第一行可以把重复的行只保留一条。选项D的orderby只做排序，不会改变结果集的行数，没有去重效果。针对数据分析师日常高频的查询场景，以下场景中适合为字段创建普通索引的有A.经常被用在where子句中作为筛选条件的用户id字段B.经常被用来做join关联条件的订单号字段C.基数极低、99%的记录取值都是同一个值的性别字段D.经常被用来做排序和范围筛选的订单创建时间字段答案：ABD解析：高基数的筛选字段、关联字段、时间排序字段创建索引都可以大幅提升查询效率。选项C的性别字段基数极低，创建索引的收益极低，甚至还会增加写入的开销，完全没有必要创建索引。以下关于SQL中null值的运算规则，描述正确的有A.任何值和null执行算术运算，返回结果都是nullB.null和null做比较运算，返回结果为unknown，不会判定为相等C.使用isnull关键字才可以正确判断字段值是否为nullD.null和任何字符串拼接，返回结果都是原字符串，完全不受影响答案：ABC解析：标准SQL的null运算规则就是任何和null相关的算术运算、比较运算结果都不会返回布尔值的真，必须使用isnull来判断空值。选项D错误，任何字符串和null拼接，返回的结果都会是null，不会保留原字符串的内容。以下属于SQL开窗函数常见的应用场景的有A.对每个分组内的用户按照消费金额做排名B.计算每个用户的消费记录在全量数据中的累计求和值C.实现分组聚合统计，返回每个分组的聚合结果D.提取每个用户最近一条行为日志的完整字段信息答案：ABCD解析：四个场景都是开窗函数的典型使用场景，排名、累计求和、分组内聚合、取分组topN行都可以通过开窗函数非常方便的实现。数据分析师在编写SQL实现多表关联逻辑时，容易出现笛卡尔积错误的场景有A.关联条件字段存在大量重复值，没有设置足够细粒度的关联规则B.关联的时候完全不写任何on关联条件，直接写AjoinBC.关联的时候on子句的字段名写反，两边完全没有匹配到相等的行D.使用leftjoin关联，左表的记录数量远大于右表的记录数量答案：ABC解析：前三种场景都可能出现笛卡尔积，也就是返回的行数是两个表行数的乘积，直接把结果集膨胀到超出预期。选项D的普通leftjoin场景只要关联条件正确，不会触发笛卡尔积错误。以下属于DDL数据定义语言范畴的操作有A.修改表的字段类型，调整字段长度B.删除整张表的所有数据，保留表结构C.在表中新增一个自定义字段D.给表添加新的索引约束答案：ACD解析：修改表结构、新增字段、创建索引都是针对数据库对象结构的操作，属于DDL范畴。选项B的清空全表数据操作属于DML范畴，不属于数据定义语言。关于子查询的特征，以下描述正确的有A.子查询可以返回单行单列的标量结果，直接作为外层查询的一个常量值使用B.子查询可以返回多行单列的结果，供外层查询用in关键字做匹配判断C.子查询可以返回多行多列的结果集，作为临时表供外层查询继续关联查询D.子查询的嵌套层级没有任何限制，可以无限制嵌套多层答案：ABC解析：子查询支持返回标量、单列集合、结果集三种形态，对应不同的使用场景。选项D错误，绝大多数主流数据库都对子查询的嵌套层级做了上限限制，不能无限制嵌套，嵌套过深也会大幅降低SQL的可读性和执行效率。编写数据统计类SQL时，为了避免产生数据统计偏差，需要注意规避的常见错误有A.用内连接关联用户表和订单表，统计所有用户的订单数B.在左外连接之后的where子句中对右表的字段加非空筛选条件C.统计聚合总数的时候忘记做去重，把重复关联的行重复计数D.使用count(非空主键字段)统计总用户数答案：ABC解析：选项A用内连接统计所有用户会自动过滤没有订单的用户，统计结果偏差；选项B左连接之后对右表字段加非空筛选会自动把左连接退化为内连接，丢失右表匹配不到的左表数据；选项C重复关联导致的重复计数是分析师统计时最常见的错误。选项D用非空主键统计总用户数是完全正确的写法，不会产生偏差。关于SQL执行计划的作用，以下描述正确的有A.可以查看当前SQL的扫描行数、关联顺序等核心执行逻辑B.可以判断SQL有没有正确命中预设的索引C.可以直接帮用户自动优化所有慢SQL，完全不需要人工调整D.可以发现SQL执行过程中出现的全表扫描、临时表排序等性能风险点答案：ABD解析：执行计划是数据分析师优化SQL的核心工具，能清晰展示SQL的实际执行路径，帮助定位性能问题。选项C错误，执行计划只是展示执行逻辑，不会自动优化SQL，所有优化动作都需要人工结合业务场景调整实现。三、判断题（共10题，每题1分，共10分）使用count(指定字段名)统计行数时，会自动忽略该字段取值为null的所有记录。答案：正确解析：这是标准SQL的count函数的默认规则，只有count(*)不会判断字段内容，其他指定字段的count写法都会跳过null值行。可以直接在where子句中使用sum()等聚合函数作为行级筛选条件。答案：错误解析：where子句的执行优先级远高于groupby聚合阶段，无法识别聚合函数的计算结果，聚合函数的筛选只能放在having子句中实现。同一条SQL语句，只要业务逻辑完全等价，使用join关联的写法性能一定比多层嵌套子查询的写法更好。答案：正确解析：绝大多数主流数据库的优化器对join逻辑的优化成熟度远高于子查询嵌套逻辑，等价改写为join后可以获得更优的执行路径，性能普遍更好。对同一个字段创建联合索引的时候，把区分度高的字段放在索引的最左侧，可以获得更好的查询效率。答案：正确解析：联合索引的最左匹配原则决定了最左侧字段的区分度越高，索引过滤掉无效数据的速度越快，整体查询性能就越好。使用distinct关键字对多个字段同时去重的时候，会把多个字段的组合取值完全一致的行判定为重复行进行去重。答案：正确解析：多字段distinct的去重逻辑是同时比对所有指定字段的取值，只有全部相等才会判定为重复行进行删除。左外连接查询会返回右表的所有行，以及左表中和右表关联条件匹配上的行。答案：错误解析：左外连接的核心规则是返回左表的所有行，以及右表中和左表关联条件匹配上的行，右表匹配不到的字段自动填充为null。SQL中的select语句默认是不会修改任何表中的数据的，属于只读操作。答案：正确解析：标准select语句只会读取数据做计算返回结果，完全不会修改底层表的存储数据，不会产生数据变更风险。开窗函数的执行优先级高于groupby聚合函数，所以可以把开窗函数写在普通groupby的select子句中，不需要做特殊处理。答案：错误解析：开窗函数的执行阶段在groupby聚合之后，不能直接写在groupby的聚合select子句中，必须把聚合逻辑和开窗函数逻辑分两层嵌套实现。当两个表做全外连接的时候，最终返回的结果集行数等于两个表的行数之和减去两边关联条件完全匹配的行数。答案：正确解析：全外连接的逻辑是保留两个表的所有行，两边关联上的行合并为一行，所以最终行数就是两个表行数相加减去匹配成功的行数，和描述的计算逻辑完全一致。索引创建的越多，查询速度就越快，所以给表的所有字段都创建索引是最优实践。答案：错误解析：索引虽然可以提升查询速度，但是会大幅降低表的写入、更新、删除操作的性能，字段上的所有索引在数据变更时都需要同步更新，索引过多会导致写入效率极低，反而影响整体使用。四、简答题（共5题，每题6分，共30分）请简述SQL中内连接、左外连接、右外连接的核心差异。答案：第一，内连接的核心规则是仅保留两张表关联条件完全匹配的行，两边匹配失败的行都会被直接过滤，返回的行数是两边关联条件匹配成功的总数量；第二，左外连接的核心规则是完整保留左表的所有行，不管右表有没有匹配到对应数据，右表匹配不到的行对应的字段全部自动填充为null，返回的行数和左表的总行数保持一致；第三，右外连接的核心规则是完整保留右表的所有行，不管左表有没有匹配到对应数据，左表匹配不到的行对应的字段全部自动填充为null，返回的行数和右表的总行数保持一致。解析：三种连接逻辑是数据分析师做多表关联统计最常用的基础操作，核心差异就在于是否保留单边的不匹配行，分析师需要根据业务统计目标选择对应的连接类型，避免因为连接类型选错导致统计结果偏差。请简述数据分析师日常编写查询SQL时，常见的慢查询诱因有哪些。答案：第一，使用了前缀不带索引的模糊查询，比如where字段like‘%关键词%’，导致数据库无法利用索引，触发全表扫描逐行比对数据；第二，多表关联时没有设置合理的关联条件，或者关联字段的类型不匹配，导致数据库无法命中关联字段上的索引，产生大量的临时数据计算；第三，对千万级以上的大表不加任何分区筛选条件，直接做全表聚合计算，扫描的总数据量超出数据库的内存承载能力，触发磁盘临时表排序操作；第四，在where子句的筛选字段上套了函数计算，比如wheredate(创建时间)=‘xxxx-xx-xx’，导致字段上的索引失效，触发全表扫描。解析：慢查询是数据分析师日常工作中最常遇到的性能问题，提前识别这些常见的诱因，在编写SQL的时候主动规避，可以大幅提升查询运行速度，减少资源消耗。请简述SQL中null值的特殊运算规则，以及实际统计场景中的注意事项。答案：第一，任何值和null执行算术运算、字符串拼接运算，最终返回的结果都是null，不会返回常规的数值或者字符串结果；第二，任何值和null执行等于、大于、小于等比较运算，返回的结果都是unknown，不会判定为布尔真，哪怕是null=null的判断也不会返回真；第三，判断字段值是否为null必须使用isnull关键字，不能使用普通的等于null的写法，否则永远匹配不到任何结果；第四，聚合函数中除了count(*)之外，其他所有聚合函数都会自动忽略取值为null的行，统计的时候要注意是否符合业务逻辑的预期，避免统计结果出现偏差。解析：null的特殊规则是新手分析师最容易踩坑的知识点，很多统计结果异常的问题本质上都是没有注意到null的运算特性，在编写统计逻辑的时候提前处理好空值，可以避免大量不必要的结果错误。请简述窗口函数over子句中的partitionby，和普通groupby的核心区别。答案：第一，groupby分组之后，最终返回的结果集每个分组只会对应一行聚合结果，无法保留分组内各行的原始明细字段；第二，partitionby分区之后，不会合并分组内的明细行，所有原始明细行都会完整保留，每一行都可以拿到对应分区的聚合计算结果；第三，groupby的使用场景是做维度聚合统计，输出维度粒度的汇总指标，partitionby的使用场景是在保留全量明细的基础上，做分组内的排序、累计求和、topN提取等计算，灵活性远高于普通groupby。解析：这两个概念很容易被新手混淆，本质上是两种完全不同的计算逻辑，掌握二者的区别可以大幅拓宽SQL的统计能力，实现很多传统groupby很难完成的复杂统计需求。请简述CTE公共表表达式相比传统多层嵌套子查询的优势。答案：第一，CTE可以给临时结果集设置明确的自定义名称，整个SQL的层级逻辑更清晰，可读性远高于多层嵌套子查询，后续维护和修改的成本更低；第二，同一个CTE结果可以在主查询中多次被引用，不需要像普通子查询那样重复编写多次相同的逻辑，减少代码冗余；第三，部分主流数据库的优化器可以对重复引用的CTE自动做物化缓存，相同的子逻辑只需要计算一次，相比多次重复执行的普通子查询性能更高。解析：CTE是现代SQL开发中的最佳实践，数据分析师编写复杂统计SQL的时候优先使用CTE替代多层嵌套子查询，可以让代码的可维护性和运行效率都得到明显提升。五、论述题（共3题，每题10分，共30分）请结合电商行业统计30天用户复购率的实际场景，论述如何通过多层级SQL逻辑实现准确的统计，同时说明容易出现错误的节点和对应的优化方案。答案：论点：用户复购率统计是电商分析师最常见的统计需求，编写逻辑合理的SQL才能得到准确结果，同时通过合理优化可以避免大表运行超时。论据部分首先梳理复购率的业务定义，统计逻辑是统计所有在统计周期内首次下单的用户中，后续30天内再次产生第二笔及以上下单行为的用户占比，正确的SQL实现逻辑分为三层：第一层先从订单表中过滤掉订单状态为取消的无效订单，提取所有有效订单的用户id、订单创建时间两个核心字段；第二层使用min()聚合函数配合groupby用户id，计算每个用户的首次下单时间，作为该用户的首购时间；第三层使用左连接关联首购用户表和全量有效订单表，筛选出所有订单创建时间大于首购时间、且时间差小于等于30天的用户，把这类用户标记为复购用户，最后用distinct统计总首购用户数和复购用户数，相除得到复购率指标。实际场景中容易出错的节点主要有三个：第一是忘记过滤取消的无效订单，把退款取消的订单也计入复购订单，导致统计结果虚高；第二是直接用内连接关联两张表，统计的时候自动过滤掉首购后完全没有复购的用户，导致总用户数统计缺失，计算出来的复购率远高于实际真实值；第三是两张表关联的时候用户id字段出现大量重复值，产生笛卡尔积导致行数膨胀，后续去重的时候统计错误。对应的优化方案也非常明确，首先提前给订单创建时间、用户id字段建好联合索引，在最外层的筛选条件中指定统计时间范围，不要全量扫描数年的大表数据；其次在第一层数据提取阶段就提前做去重处理，避免后续关联的时候产生重复行；最后用CTE拆分每一层的计算逻辑，单独验证每一层的输出结果是否符合预期，避免多层嵌套之后难以定位错误。结论：按照分层校验的思路编写复购率统计SQL，既可以保证结果的准确性，又可以大幅提升查询运行效率，完全满足日常业务分析的需求。解析：这道题结合真实业务场景，覆盖了多表关联、聚合统计、错误规避、SQL优化多个知识点，符合数据分析师日常的核心工作场景，能够考察应试人员对SQL综合应用能力的掌握程度。论述索引在数据分析师日常查询工作中的利与弊，结合大表查询的实际案例说明什么场景下适合使用索引，什么场景下反而要避免使用索引。答案：论点：索引是数据库优化最核心的手段，但并不是所有场景下添加索引都能收益大于成本，需要分析师根据场景判断是否合理利用索引。论据部分先说明索引的优势：索引本质上是提前排序好的有序数据结构，在百万级以上的大表中，使用索引可以把原本需要几十万次的全表扫描，降低到几十次的索引树查询，原本需要运行几十分钟的查询可以在几秒内返回结果，比如分析师需要从千万级的用户订单表中提取某个特定用户的全部历史订单，如果订单表没有索引，需要扫描全表千万行数据，运行十几分钟才能返回结果，给订单id字段创建索引之后，查询只需要几十毫秒就可以拿到全部结果。接下来说明索引的弊端：索引本身会占用额外的存储空间，每次表中的数据做新增、更新、删除操作的时候，所有关联的索引都需要同步更新，会大幅增加数据写入的开销，比如如果分析师给一张每秒写入上千条用户行为日志的表的10个字段都创建索引，每一条新日志写入的时候都需要同步更新10个索引结构，写入的速度会大幅下降，甚至出现日志写入延迟、丢失的问题。然后分场景给出判断标准：适合使用索引的场景包括高频用做where筛选条件的用户id、订单号等唯一字段，高频用来做范围筛选的时间字段，高频用来做join关联的关联键字段，这些场景下索引带来的查询效率提升远大于写入的开销；需要避免使用索引的场景包括基数极低的字段比如性别、状态这类取值只有2-3种的字段，写入频率远高于查询频