经管之家App
让优质教育人人可得
立即打开
在 CDA（Certified Data Analyst）数据分析师的工作流中，“获取数据→处理数据→分析数据→输出结论” 的每一步都离不开对数据载体的操作。而表与视图，正是分析师处理数据时最核心的两种载体：表是 “数据的永久仓库”，用于存储清洗后、预处理后的结构化数据；视图是 “数据的临时窗口”，用于简化复杂查询、复用分析逻辑。掌握表与视图的创建方法，不仅能让分析师摆脱 “重复写长 SQL” 的低效困境，更能确保数据处理的规范性与可复用性，是从 “初级取数” 迈向 “高效分析” 的关键技能。
对 CDA 分析师而言，表与视图并非 “二选一” 的工具，而是 “分工不同、协同配合” 的搭档。要高效使用两者，需先明确其本质差异与适用场景。
表（Table）是数据库中用于永久存储结构化数据的载体，具有固定的字段结构（列）与数据记录（行），数据一旦插入，除非主动删除或更新，否则会长期保留。其核心特征是 “物理存储、独立存在、可直接修改”：
物理存储：表的数据以文件形式存储在数据库服务器的磁盘中，占用实际存储空间；
独立存在：表不依赖其他对象，可单独进行查询、插入、更新、删除（CRUD）操作；
数据可控：分析师可通过INSERT插入新数据、UPDATE修改错误数据、DELETE清理冗余数据。
CDA 分析师常用表类型：
原始数据表：存储业务系统直接生成的原始数据（如电商的订单原始表、用户注册原始表），结构与业务系统一致，需后续清洗；
中间表：存储数据预处理（清洗、关联、聚合）后的中间结果（如 “清洗后的订单中间表”“用户 - 订单关联中间表”），用于后续分析复用，避免重复处理；
结果表：存储最终分析结论（如 “2024 年 Q3 各渠道用户消费报表”“月度 GMV 趋势表”），可直接用于可视化或业务汇报。
视图（View）是基于一个或多个表的虚拟查询结果集，本身不存储数据，仅保存查询逻辑（SQL 语句）。当调用视图时，数据库会动态执行其背后的 SQL，返回实时数据。其核心特征是 “逻辑存储、依赖表、不可直接修改数据”：
逻辑存储：视图仅保存查询语句，不占用实际存储空间，数据来源于关联的基础表；
依赖基础表：若基础表结构变更（如字段删除、重命名），视图可能失效，需同步调整；
数据只读性：分析师可通过视图查询数据，但无法直接用INSERT/UPDATE/DELETE修改视图数据（需修改基础表）。
CDA 分析师常用视图类型：
简化查询视图：将复杂的多表关联、嵌套查询封装为视图，后续分析直接调用（如 “用户消费视图”，关联用户表与订单表，避免每次写多表JOIN）；
数据筛选视图：筛选特定业务场景的数据（如 “北京地区新用户视图”“高客单价订单视图”），聚焦目标分析范围；
权限控制视图：隐藏敏感字段（如手机号、身份证号），仅展示非敏感信息（如 “脱敏用户视图”，手机号显示为 “138****1234”），保障数据安全。
| 对比维度 | 表（Table） | 视图（View） |
|---|---|---|
| 数据存储 | 物理存储，占用磁盘空间 | 逻辑存储，仅保存查询语句，不占空间 |
| 数据独立性 | 独立存在，不依赖其他对象 | 依赖基础表，基础表变更可能导致失效 |
| 数据操作 | 支持 INSERT/UPDATE/DELETE（可修改数据） | 仅支持 SELECT（查询），无法直接修改数据 |
| 性能 | 查询速度快（数据预存储） | 查询速度依赖基础表与视图逻辑（动态计算） |
| 适用场景 | 存储中间结果、最终分析结论 | 简化复杂查询、复用逻辑、数据脱敏 |
创建表是 CDA 分析师处理数据的 “基础动作”，核心目标是 “设计合理的表结构，存储高质量数据，为后续分析提供支撑”。完整流程分为 4 个步骤，需结合业务需求与数据特征精准设计。
创建表前需先明确表的用途与数据来源，避免 “盲目建表导致数据冗余”：
若需 “存储清洗后的电商订单数据，用于后续各维度分析”，则创建 “订单清洗中间表”；
若需 “存储 2024 年各月 GMV 统计结果，用于季度汇报”，则创建 “月度 GMV 结果表”；
表结构设计是核心，需重点关注 “字段名、字段类型、约束条件”，确保数据准确性与可用性：
字段名设计：遵循 “见名知意、统一规范” 原则，用小写字母 + 下划线命名（如 “order_id”“user_id”“order_amount”），避免中文或特殊字符；
字段类型选择：根据数据特征选择合适类型，避免 “大材小用” 或 “类型不匹配”（如用INT存储订单金额会丢失小数，用VARCHAR存储日期无法按时间排序）：
| 数据类型 | 适用场景 | 示例字段 |
|---|---|---|
| INT/BIGINT | 整数型数据（无小数） | 订单 ID（order_id）、用户 ID（user_id） |
| DECIMAL(p,s) | 小数型数据（需精确计算） | 订单金额（order_amount，DECIMAL (10,2)）、折扣率（discount_rate，DECIMAL (5,2)） |
| VARCHAR(n) | 字符串型数据（长度可变） | 用户名（user_name）、商品品类（product_category） |
| DATETIME | 日期时间型数据（精确到秒） | 下单时间（order_time）、支付时间（pay_time） |
| BOOLEAN/TINYINT | 布尔型数据（是 / 否、0/1） | 支付状态（is_paid，1 = 已支付，0 = 未支付） |
主键（PRIMARY KEY）：唯一标识一条记录（如 “order_id” 设为主键，避免重复订单）；
非空（NOT NULL）：核心字段不允许为空（如 “order_amount”“order_time” 设为 NOT NULL，避免数据缺失）；
默认值（DEFAULT）：非核心字段设置默认值（如 “备注（remark）” 默认值为 “无”）。
基于结构设计，用CREATE TABLE语句创建表，不同数据库（MySQL、Hive）语法略有差异，核心逻辑一致。
实战案例 1：MySQL 创建 “电商订单清洗中间表”
需求：存储清洗后的订单数据，包含订单基本信息、支付状态，排除冗余字段，订单 ID 为主键，金额非空。
SQL 语句：
全部版块
我的主页
收藏
\-- 创建电商订单清洗中间表（MySQL）
CREATE TABLE IF NOT EXISTS order\_clean\_mid (
order\_id BIGINT PRIMARY KEY COMMENT '订单ID（主键，唯一标识订单）',
user\_id BIGINT NOT NULL COMMENT '用户ID（关联用户表）',
product\_id BIGINT NOT NULL COMMENT '商品ID（关联商品表）',
order\_amount DECIMAL(10,2) NOT NULL COMMENT '订单金额（单位：元，保留2位小数）',
order\_time DATETIME NOT NULL COMMENT '下单时间（格式：YYYY-MM-DD HH:MM:SS）',
pay\_time DATETIME DEFAULT NULL COMMENT '支付时间（未支付则为NULL）',
is\_paid TINYINT NOT NULL DEFAULT 0 COMMENT '支付状态（0=未支付，1=已支付）',
product\_category VARCHAR(50) NOT NULL COMMENT '商品品类（如女装、电子产品）'
) COMMENT '电商订单清洗中间表（存储清洗后的订单数据，用于后续分析）';
IF NOT EXISTS避免表已存在时报错；COMMENT添加字段 / 表注释，便于后续理解；DEFAULT NULL设置可选字段默认值。实战案例 2：Hive 创建 “月度 GMV 结果表”（按月份分区）
需求：存储 2024 年各月 GMV 统计结果，按 “月份（month）” 分区，便于按月份查询。
SQL 语句：
\-- 创建月度GMV结果表（Hive，按月份分区）
CREATE TABLE IF NOT EXISTS monthly\_gmv\_result (
year INT NOT NULL COMMENT '年份（如2024）',
month INT NOT NULL COMMENT '月份（如9）',
total\_gmv DECIMAL(15,2) NOT NULL COMMENT '月度总GMV（单位：元）',
avg\_order\_amount DECIMAL(10,2) NOT NULL COMMENT '月度平均订单金额',
order\_count INT NOT NULL COMMENT '月度订单总数'
)
PARTITIONED BY (month\_part STRING COMMENT '分区字段：格式YYYY-MM（如2024-09）')
COMMENT '月度GMV结果表（按月份分区存储，用于季度/年度汇报）';
PARTITIONED BY用于分区表，按 “month_part” 分区后，查询 “2024-09” 数据时仅扫描该分区，提升效率。表创建后需插入数据，并验证数据准确性，避免 “表结构正确但数据错误”：
INSERT INTO（追加数据）或INSERT OVERWRITE（覆盖数据）插入数据，来源可为基础表查询结果：\-- 向order\_clean\_mid插入清洗后的数据（从原始订单表筛选有效数据）
INSERT INTO order\_clean\_mid (order\_id, user\_id, product\_id, order\_amount, order\_time, pay\_time, is\_paid, product\_category)
SELECT
order\_id,
user\_id,
product\_id,
order\_amount,
order\_time,
pay\_time,
CASE WHEN pay\_time IS NOT NULL THEN 1 ELSE 0 END AS is\_paid, -- 计算支付状态
product\_category
FROM order\_original -- 原始订单表
WHERE order\_amount > 0 -- 过滤异常订单（金额≤0）
AND order\_time >= '2024-01-01'; -- 仅保留2024年数据
SELECT查询表数据，验证字段值、数据量是否符合预期：\-- 验证order\_clean\_mid：查询前10条数据，检查支付状态与金额
SELECT \* FROM order\_clean\_mid LIMIT 10;
\-- 验证数据量：统计2024年9月订单数
SELECT COUNT(order\_id) AS sep\_2024\_order\_count
FROM order\_clean\_mid
WHERE DATE\_FORMAT(order\_time, '%Y-%m') = '2024-09';
创建视图的核心目标是 “简化复杂查询、复用分析逻辑”，尤其适合 “频繁使用多表关联、固定筛选条件” 的场景。完整流程分为 3 个步骤，需聚焦 “逻辑清晰、易于维护”。
创建视图前需明确视图的使用场景与查询逻辑，避免 “视图逻辑复杂导致查询缓慢”：
若 “需频繁分析‘用户消费情况’，每次都要关联用户表与订单表”，则创建 “用户消费视图”；
若 “业务部门需查看‘北京地区新用户数据’，但不能接触手机号”，则创建 “北京新用户脱敏视图”；
用CREATE VIEW语句创建视图，核心是封装查询逻辑，语法简洁，不同数据库（MySQL、Hive）通用。
实战案例 1：MySQL 创建 “用户消费视图”（简化多表关联）
需求：封装用户表与订单表的关联逻辑，后续分析直接调用视图，无需重复写JOIN。
SQL 语句：
\-- 创建用户消费视图（关联用户表与订单表）
CREATE VIEW user\_consume\_view AS
SELECT
u.user\_id AS 用户ID,
u.user\_name AS 用户名,
u.region AS 地域,
u.register\_time AS 注册时间,
o.order\_id AS 订单ID,
o.order\_amount AS 订单金额,
o.order\_time AS 下单时间,
o.is\_paid AS 支付状态
FROM user\_table u
LEFT JOIN order\_clean\_mid o ON u.user\_id = o.user\_id -- 关联用户与订单
WHERE o.order\_time >= DATE\_SUB(CURDATE(), INTERVAL 3 MONTH); -- 仅保留近3个月订单
LEFT JOIN与筛选条件，调用时直接查询user_consume_view，即可获取用户与订单的关联数据，例如：\-- 用视图分析“北京地区近3个月用户平均消费金额”
SELECT
region AS 地域,
AVG(order\_amount) AS avg\_consume,
COUNT(DISTINCT user\_id) AS user\_count
FROM user\_consume\_view
WHERE region = '北京' AND is\_paid = 1
GROUP BY region;
实战案例 2：MySQL 创建 “用户脱敏视图”（数据安全）
需求：隐藏用户表中的手机号、身份证号，仅展示脱敏信息，供非技术部门使用。
SQL 语句：
\-- 创建用户脱敏视图（隐藏敏感字段）
CREATE VIEW user\_desensitized\_view AS
SELECT
user\_id AS 用户ID,
user\_name AS 用户名,
region AS 地域,
register\_time AS 注册时间,
CONCAT(LEFT(phone, 7), '\*\*\*\*') AS 脱敏手机号, -- 手机号脱敏：1381234\*\*\*\*
CONCAT(LEFT(id\_card, 6), '\*\*\*\*\*\*\*\*', RIGHT(id\_card, 4)) AS 脱敏身份证号 -- 身份证脱敏：110101\*\*\*\*\*\*\*\*1234
FROM user\_table;
CONCAT+LEFT/RIGHT实现字段脱敏，非技术部门查询视图时无法获取完整敏感信息，保障数据安全。视图依赖基础表，当基础表结构变更（如字段删除、重命名）时，视图会失效，需及时维护：
DESCRIBE查看视图结构，用SHOW CREATE VIEW查看视图背后的 SQL 逻辑：\-- 查看user\_consume\_view的结构
DESCRIBE user\_consume\_view;
\-- 查看user\_consume\_view的创建语句
SHOW CREATE VIEW user\_consume\_view;
ALTER VIEW修改视图逻辑：\-- 修改用户消费视图：适配order\_amount字段改名
ALTER VIEW user\_consume\_view AS
SELECT
u.user\_id AS 用户ID,
u.user\_name AS 用户名,
u.region AS 地域,
u.register\_time AS 注册时间,
o.order\_id AS 订单ID,
o.amount AS 订单金额, -- 字段名从order\_amount改为amount
o.order\_time AS 下单时间,
o.is\_paid AS 支付状态
FROM user\_table u
LEFT JOIN order\_clean\_mid o ON u.user\_id = o.user\_id
WHERE o.order\_time >= DATE\_SUB(CURDATE(), INTERVAL 3 MONTH);
DROP VIEW删除，避免视图冗余：\-- 删除无用的“旧用户视图”
DROP VIEW IF EXISTS old\_user\_view;
在实际分析场景中，表与视图通常协同使用：先创建中间表存储预处理数据，再基于中间表创建视图简化查询，最终用视图快速开展多维度分析。
分析 “2024 年 Q3（7-9 月）新注册用户的消费行为”，需完成：
计算新用户首单转化率、平均首单金额；
按注册渠道（抖音、淘宝）对比新用户消费差异；
输出高价值新用户（首单金额 > 500 元）列表。
先从用户表筛选 Q3 新用户，存储为中间表，避免重复筛选：
\-- 创建Q3新用户中间表
CREATE TABLE IF NOT EXISTS q3\_new\_user\_mid (
user\_id BIGINT PRIMARY KEY COMMENT '用户ID',
user\_name VARCHAR(50) NOT NULL COMMENT '用户名',
region VARCHAR(50) NOT NULL COMMENT '地域',
register\_time DATETIME NOT NULL COMMENT '注册时间',
register\_channel VARCHAR(50) NOT NULL COMMENT '注册渠道（抖音/淘宝）'
) COMMENT '2024年Q3新用户中间表';
\-- 插入Q3新用户数据
INSERT INTO q3\_new\_user\_mid
SELECT
user\_id,
user\_name,
region,
register\_time,
register\_channel
FROM user\_table
WHERE register\_time BETWEEN '2024-07-01' AND '2024-09-30'
AND register\_time IS NOT NULL;
基于 “Q3 新用户中间表” 与 “订单清洗表” 创建视图，封装关联逻辑：
\-- 创建Q3新用户消费视图
CREATE VIEW q3\_new\_user\_consume\_view AS
SELECT
u.user\_id AS 用户ID,
u.user\_name AS 用户名,
u.region AS 地域,
u.register\_channel AS 注册渠道,
u.register\_time AS 注册时间,
o.order\_id AS 订单ID,
o.order\_amount AS 订单金额,
o.order\_time AS 下单时间,
o.is\_paid AS 支付状态
FROM q3\_new\_user\_mid u
LEFT JOIN order\_clean\_mid o ON u.user\_id = o.user\_id
WHERE o.is\_paid = 1; -- 仅保留已支付订单
直接调用视图，快速完成多维度分析，无需重复写表关联：
\-- 1. 计算Q3新用户首单转化率、平均首单金额
SELECT
COUNT(DISTINCT u.user\_id) AS 新用户总数,
COUNT(DISTINCT v.user\_id) AS 有消费新用户数,
ROUND(COUNT(DISTINCT v.user\_id)/COUNT(DISTINCT u.user\_id), 4)\*100 AS 首单转化率,
ROUND(AVG(v.order\_amount), 2) AS 平均首单金额
FROM q3\_new\_user\_mid u
LEFT JOIN q3\_new\_user\_consume\_view v ON u.user\_id = v.user\_id;
\-- 2. 按注册渠道对比新用户消费差异
SELECT
register\_channel AS 注册渠道,
COUNT(DISTINCT user\_id) AS 渠道用户数,
SUM(order\_amount) AS 渠道总消费,
ROUND(AVG(order\_amount), 2) AS 渠道平均消费
FROM q3\_new\_user\_consume\_view
GROUP BY register\_channel;
\-- 3. 筛选高价值新用户（首单金额>500元）
SELECT
user\_id AS 用户ID,
user\_name AS 用户名,
register\_channel AS 注册渠道,
order\_amount AS 首单金额,
order\_time AS 首单时间
FROM q3\_new\_user\_consume\_view
WHERE order\_amount > 500
ORDER BY order\_amount DESC;
效率提升：中间表存储 Q3 新用户数据，避免每次分析都筛选原始用户表；视图封装关联逻辑，分析语句从 “多表 JOIN” 简化为 “单表查询”；
逻辑复用：若后续需补充 “Q3 新用户地域消费分析”，直接调用视图即可，无需重新设计表关联。
避免过度设计：表字段仅保留 “核心必要字段”，避免冗余（如 “订单表” 无需存储 “商品详情描述”，可通过 “product_id” 关联商品表获取）；
重视字段类型：金额用DECIMAL（避免FLOAT精度丢失）、日期用DATETIME（便于时间筛选）、字符串长度按需设置（如 “地域” 用VARCHAR(50)足够，无需VARCHAR(255)）；
大表需分区：数据量超过 100 万行的表，按 “时间（如月份）” 或 “地域” 分区（如 Hive 的分区表、MySQL 的分区表），提升查询速度；
添加注释：表与字段均需添加COMMENT，说明用途与含义（如 “order_amount DECIMAL (10,2) COMMENT ' 订单金额（单位：元）'”），便于他人理解。
逻辑简洁：避免视图嵌套（如 “视图 A 依赖视图 B，视图 B 依赖视图 C”），嵌套过多会导致查询速度变慢，且难以排查问题；
避免复杂计算：视图中不建议包含大量聚合函数（如SUM/AVG）或子查询，复杂计算可在查询视图时动态执行，而非封装在视图中；
权限控制：脱敏视图仅授予 “非敏感数据查看权限”，避免业务部门获取敏感信息；
定期检查：每月检查视图有效性，若基础表结构变更（如字段删除），及时用ALTER VIEW修改视图逻辑，避免失效。
对 CDA 数据分析师而言，创建表与视图并非 “单纯的 SQL 技术操作”，而是 “优化数据处理流程、提升分析效率” 的核心策略：表是 “数据的基石”，确保高质量数据的永久存储与复用；视图是 “分析的窗口”，简化复杂逻辑，让分析师聚焦业务洞察而非重复写 SQL。
在数据量日益庞大、业务需求愈发复杂的今天，熟练掌握表与视图的创建方法，能让 CDA 分析师摆脱 “低效取数、重复处理” 的困境，将更多精力投入 “深度分析与决策支撑”。无论是中间表的结构化存储，还是视图的逻辑化封装，最终都指向一个目标 —— 让数据处理更高效、分析更精准，真正实现 “数据驱动业务增长”。
扫码加好友，拉您进群
