关于数据分析:数据分析中的SQL如何解决业务问题

本文来自知乎问答。
发问：数据分析人员须要把握 sql 到什么水平？
请问做一名数据分析人员，在 sql 方面须要把握到什么水平呢？会增删改查就能够了吗？还是说对于开发的内容也要会？不同阶段会有不同的要求吗？

注释：

作为专一数据分析论断 / 我的项目在业务落地以实现增长的分析师，倡议在开始学习新技能前，先明确利用场景。对症下药能力不枉费致力。

翻译过去就是：先理解与 SQL 相干的数据分析工作有哪些？有了指标，能力晓得须要筹备什么常识来应答。

按我目前与 SQL 相干的工作内容，为你提供以下参考：

（食用阐明：依据以下场景，抉择须要重点学习的知识点）

SQL 利用场景及必备常识：

(星标依据应用频率标记，而非重要性)

数据查问 ★★★

业务场景

也就是常说的“提数”。

理论工作场景中，如果向 IT 提提数需要，个别都须要沟通 + 排期，所以最有效率的倡议就是本人会从数据库里提数

数据分析师除了本身的剖析工作外，有时 (甚至是常常) 还须要应酬产品、经营等部门共事的提数需要

必备常识

• 简略查问

即最简略的关键字组合 SELECT +FROM +WHERE +(BETWEEN /IN) 是 SQL 查问的地基

此简略查问能够应答局部提数需要，例如经营想查看某段时间订单

• 多表查问
  即 INNER JOIN、LEFT JOIN 等联结关键字

设想中的取数可能是间接在某个表 SELECT 想要的字段？

NO! 实际上为了查问效率，数据会散落到数据库的各个角落，例如想要理解一笔订单状况，信息存在这些表中：订单流水表、订单详情表、商品详情表、门店表、会员表等。

该局部常识的关键在于「明确业务剖析需要→抉择适合的联结形式」

数据更新 ★★☆

业务场景

即题主所说的“增删改”

该场景之所以仅两星的起因，是理论工作中，数据库运维部门给到咱们数据分析师的数据库账号多半是只读权限，也就无奈去“增删改”；

此外，还有数据管控的起因

所以此场景可能更多在于自建数据库中，如在电脑上新建虚拟机搭建数据库服务器，导入数据后不便进行下一步剖析

必备常识

• 数据库与表的创立、删除和更新

该局部知识点 关键在于「字段类型的设置」要合乎后续剖析需要，如订单商品数量就要设成数值类型、订单日期设成日期类型等。

(因为见过都设成字符类型的表，所以就简略提一下)

剖析数据 ★★★

业务场景

该局部堪称是数据分析师的外围工作

面对简单的业务问题，重点在于将其拆解、转译成简略的 SQL 问题

「案例」例如教育行业中某领导要求你“剖析某课程的成果如何”→ 翻译：

课程成果可通过学生问题反映，即是要计算问题最大值、最小值、学生问题散布 → SQL 语句

必备常识

• 汇总剖析

即 GROUP BY 关键字

解决业务问题：

如计算每个课程学生的均匀问题：

SELECT avg(问题) FROM 成绩表 GROUP BY 课程

• 简单查问

如嵌套子查问、标量子查问、关联子查问

可应答更简单的业务问题：

如找出每个课程最高分的学生 → 须要按课程分组后找到最高问题记录，能够利用关联子查问：

SELECT 学生名字 FROM 成绩表 a WHERE 问题 = (SELECT max(问题) FROM 成绩表 b WHERE a. 课程 =b. 课程)

• 窗口函数

聚合 / 排序函数 () OVER (PARTITION BY ..ORDER BY..)

此函数可解决简单业务问题，如常见的 TOP N 问题：

找出每个课程问题前三的学生 → 按课程分组对学生按成绩排名，再从中找出排名前三的学生：

SELECT 学生名字 FROM (SELECT 学生名字, dense_rank()over(partition by 课程 order by 问题 desc) as '成绩排名' FROM 成绩表) t WHERE t. 成绩排名 <4

数据产品 ★☆☆

业务场景

对于局部岗位，如我在的团体用户数据中心，须要负责搭建如 CDP 这样的数据产品，尽管少数状况下是由开发负责数据库工作，然而对于外面外围的性能如经营指标体系、模型报表等，背地的计算逻辑、数据流，要求数据分析师一目了然。

此外，对于刚开始建设数据分析团队的部门，还存在【数据同步】的需要，即要从 ERP、CRM 等零碎将须要剖析的原数据同步到本人的数据库里便于剖析，而此需要须要通过存储过程实现。

必备常识

• 存储过程

即 PROCEDURE，能够将某业务需要，或者数据产品中的报表对应的所有 SQL 语句放在一起，不便一键执行，如 RFM 模型里的语句能够写成存储过程，计算结果实时同步到前端

• 「SQL SERVER」打算

面对「数据同步」需要，有了存储过程后，还须要进行定时工作，在非业务工夫执行数据同步的存储过程。

如是应用 SQL SERVER 版本，能够通过“打算”实现定时工作。

我的项目部署 ★☆☆

业务场景

数据分析论断在业务场景测试无效后，就须要通过报表、模型等形式落地造成业务常态。

而这个我的项目落地，可能交给开发解决，但更无效的形式是分析师能够参加到部署的过程中。

而这个过程，其中一个重要的局部就是数据库的设计：

如何设计表格以进步计算效率。

必备常识

• 数据库设计与「SQL 三范式」

SQL 三范式的目标在于解决数据冗余、计算效率低等问题，另一方面对数据减少、批改更敌对。

这部分从业务场景登程，探讨业务问题的解决方案与 SQL 知识点的关系，帮忙答主解决学习了 SQL 之后能够做什么的问题。

实战如何剖析用户？

——用 SQL 做一份数据分析报告波及什么哪些知识点？

在工作中，每个数据分析师都离不开做数据分析报告，而一份可落地的报告更是要求灵便地利用工具及理论知识。接下来，咱们从工具利用的角度，看看如何用 SQL 做一份残缺的数据分析报告。

数据导入

• 新建数据库
• 用优良的数据库管理工具 Navicat 连贯数据库
• 通过 Navicat 将数据（如 Excel、SQL 脚本等格局）导入数据库

数据荡涤

数据荡涤的目标是为了将数据依照业务剖析需要，剔除异样值、离群值，使剖析后果更精确反映业务理论。

常见的步骤如下：

• 是否存在空值：

  WHERE ` 字段名 ` is null

• 是否存在反复数据：

通过 GROUP BY 关键字实现

SELECT COUNT(*) FROM 表名 GROUP BY 字段名 HAVING COUNT(*) >1

• 是否存在业务定义以外的数据：

如须要剖析华南区域数据，而数据中呈现华北数据

数据格式化

这一步是要依据后续剖析需要，调整表格构造、数据格式等，如出于数据寄存起因，拿到的数据表格可能是一维表，不满足剖析需要，须要将其调整为二维表。

常见的步骤如下：

• 工夫函数：

如将「工夫戳」格式化为日期、工夫、月份、周几（常见于周剖析）等，可通过「FROM_UNIXTIME」「DATE_FORMAT」等函数实现

• 行列调换：

如解决上述的一维表转为二维表的问题，可通过关键字「CASE WHEN」实现

• 字段的拆分与合并：

如将收货地址字段拆为省、市、镇等字段，可通过「CONCAT」「LEFT」「RIGHT」「SUBSTRING」等函数实现

整体剖析

在开始真正的剖析之前，须要进行探索性数据分析(Exploratory Data Analysis，EDA)，也就是对现有数据进行整体剖析，对现状有大体的理解。更重要的是，通过整体剖析，找出业务经营存在的问题，进而提出业务指标，开展后续的深度剖析。

常见的步骤如下：

• 漏斗剖析：

如海盗模型 AARRR，阿里营销模型 AIPL 等，通过简略的「COUNT」函数，间接数就可实现

建设视图

面对简单的业务剖析，SQL 语句也会变得复杂，往往须要一直嵌套。为了缩小剖析时语句的复杂性、防止反复执行雷同语句，能够采纳新建视图的形式，将重复性高的语句固定为视图，再在此基础上进行简单查问。

新建视图：

CREATE VIEW 视图名 AS SELECT..

用户剖析

从整体剖析中，明确业务问题、指标后，便可开始进行用户剖析。依据剖析目标的不同，采纳不同的分析方法，而常见的分析方法如下：

• 「人货场」剖析
• 「复购」剖析，外围问题在于如何计算“复购”：

用「窗口函数 +DENSE_RANK()」统计每个订单是该用户的第几次生产，命名为 ’N_CONSUME’

第一次生产即为用户“首购订单”，大于等于第二次生产的订单则为“复购订单“

针对复购订单进行统计，即可进行复购剖析

• 「RFM 模型」剖析，外围问题在于如何定义阈值及人群划分：

通过【窗口函数】可计算出每个用户的 RFM 值：

R：每个用户最初生产日期，与剖析日期相减的天数即为 R

F：通过复购剖析中得出的 N_CONSUME，计算最大生产次数即为 F

M：简略地 SUM 用户所有生产金额，即为 M

阈值：可通过计算所有用户的 R,F,M 平均值取得

所有用户的 RFM 值与阈值比拟，通过「CASE WHEN」转为 ‘ 高 ’、’ 低 ’ 两个值

依据 RFM 高下值通过「CASE WHEN」将所有用户划分到八个人群中

总结倡议

依据前文进行的剖析，即可总结得出的论断。此外，在业务剖析中，更重要的是如何联合业务场景来给出可落地的业务倡议。

愿无知者无力，愿无力者前行。
我是 @饼干哥哥，继续为您打造数字化时代的剖析能力。