SQL复习(2)

SQL面试题

• 是否会SQL、Python、R等分析工具，分别有什么用？

  • EXCEL用于小样本量中基本的数据处理操作
  • SQL用于从数据库中取数操作，做一些简单的数据处理工作，通过表连接、嵌套查询等动作完成最终的数据统计工作。SQL基本上可以完成大部分的数据分析工作，对当前公司运营的成效进行数据呈现及分析
  • Python和R则属于更高阶的分析工作，可以借助多种多样的工具库，可以通过数据建模，可用于有监督或无监督模型的训练，解决分类、聚类和其他预测问题。

• SQL的掌握程度？

  • 熟悉基本操作（增删改查）
    • 增 INSERT INTO <表名> （字段名） VALUES （值）
    • 删 DELETE FROM <表名> WHERE <筛选条件>
    • 改 UPDATE <表名> SET <字段名=值> WHERE <筛选条件>
    • 查 SELECT * FROM <表名> WHERE <筛选条件> GROUP BY <字段名> HAVING <条件>
      • 嵌套查询：将（SELECT-FROM-WHERE）的查询语句作为子查询嵌套进去
      • 组合查询：使用UNION/UNION ALL对多个查询结果进行组合【union去重】
      • 表连接：表连接中根据使用场景选择INEER/LEFT/RIGHT/FULL JOIN
      • 聚合函数：使用MAX/MIN/SUM/AVG/COUNT对查询数据进行聚合
      • 窗口函数：多查询数据排序或多样性聚合【对组内进行操作】

• SQL如何优化？

  数据库：更针对数据存储、更新

  数据仓库：更针对数据的查询

  • mysql（查询优化）

    • 用groupby代替distinct去重（有时groupby分组后聚合就能达到目的
    • 表连接前先用where过滤
    • 建立合适的索引、少使用Like模糊查询、可以使用(临时表)with as、

  • Hive（基于Mapreduce）

    • 针对业务需求，选择合适的数据粒度

    • 针对数据倾斜

      其原因：

      • group by维度过小，某值的数量过多，导致处理某值的reduce⾮常耗时
      • 去重 distinct的某特殊值过多，导致处理此特殊值的reduce耗时
      • 单点问题，连接 join,count(distinct),group by,join等操作，这些都会触发Shuffle动作，⼀旦触发，所有相同key的值就会拉到⼀个或⼏个节点上，就容易发⽣单点问题

      解决方案：

      • group by时某些key对应数据量很⼤，可以单独对这些key做计算，再与其他key进行join
      • Hive参数设置:
        • 设置hive.map.aggr = true：在map中会做部分聚集操作，效率更高但需要更多的内存设置
        • 设置hive.groupby.skewindata=true 确保数据倾斜时负载均衡
      • 查询语句优化：
        • 在count(distinct) 操作前先进行⼀次group by,把key先进⾏⼀次reduce,去重
        • map join:使⽤map join让小的维度表（1000 条以下的记录条数）先进内存,在map端完成reduce

• SQL窗口函数？举例？如何去重？

  • 与聚合函数的区别：

    • 聚合函数是将每个分组聚合为一条结果，而窗口函数是对每个分组内的所有记录执行。
    • 聚合函数可以用于窗口函数。
    • 聚合窗口函数：sum、count、avg、max、min；排序窗口函数：rank、rank_dense、row_number；偏移窗口函数：lag、lead

  • 基本用法：over关键字用来指定函数执行的窗口范围

    语法结构：函数 OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])

    • PARTITION BY：对哪些字段进行分组，在不同分组上分别执行。
    • ORDER BY：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。

  • 排序窗口函数row_number(),rank(),dense_rank()的区别？

    • ROW_NUMBER()：顺序排序——1、2、3
    • RANK()：并列排序，跳过重复序号——1、1、3
    • DENSE_RANK()：并列排序，不跳过重复序号——1、1、2

  • 分布函数：percent_rank(),cume_dist() percent_rank()

  • 前后函数：lag(expr,n),lead(expr,n)

  • 头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)

    • 返回第⼀个（ FIRST_VALUE(expr) ）或最后⼀个（ LAST_VALUE(expr) ）expr的值

• like的用法？

  • WHERE 子句中搜索列中的指定模式（正则表达式）。

• 如何利用replace函数统计给定重复字段在字符串中的出现次数？

  • All_String表示完整字符串，Target_String表示待统计的目标字段

    select (length('All_String')- length(replace('All_String', 'Target_String','')))/length('Target_String') as cnt from table
    

• 销售运营这个岗位上会涉及到一些报表的出具，包括日报/月报等等这些工作，用到的工具除了EXCEL，像SQL这些用得到吗？

  • 会用到，比如提取销售情况的时候，需要先用sql将数据从数据库中取出，在一些较复杂的场景中，会将提出的数据导入python中进行数据处理，然后再用excel产出。

以上是牛客网里整理的内容，后续将持续更新！