简介这是一份面向数据仓库初学者与相关从业者的PPT教学课件聚焦数据库基本概念、关系模型、关系代数与SQL基础练习帮助学习者从零建立数据管理思维为后续数据仓库实践打基础。内容涵盖数据、数据库、数据库系统与DBMS的定义梳理从IDS、IMS到关系模型的演进脉络并介绍概念模型、数据模型、二维表关系及主键等核心知识同时结合选择、投影、连接、除运算等关系代数实例以及SELECT、聚合函数、GROUP BY等SQL实操便于边学边练。压缩包仅1个pptx文件大小3.06MB主题集中、篇幅精炼适合培训、自学或课程讲解直接使用。该资源已被125人学习适合希望快速理解数据库原理并掌握SQL入门的数据分析、数据科学及IT人员。1. 数据仓库实践系列第1课先把数据库和SQL当底层基本功一个做数据仓库的同事最容易翻车的时刻往往不是建模设计阶段而是被人问一句你这条SQL跑出来的数为什么跟业务那边对不上——这时候你会发现问题常常不在数仓架构而在最基础的数据库原理和SQL写法上。数据仓库实践系列课程(1)这个标题之所以把数据库基础与SQL放在第一课是因为它想解决一个普遍痛点很多人从大数据框架入门建了Hive表、写了MapReduce却搞不清事务、索引、约束、连接这些数据库底层概念导致写出的SQL要么慢得离谱要么口径一塌糊涂。这一课适合刚转数仓的开发者、写SQL靠复制粘贴的分析师以及那些想系统梳理数据库知识但没有耐心翻大部头的从业者。它不是让你背概念而是让你知道在建数仓、抽数据、跑报表之前哪些数据库基本功必须补上哪些SQL习惯能让你少熬夜。2. 数据库基础关系模型、事务与索引第一课真正要建立的三块认知2.1 关系模型为什么是数据仓库的底座数据仓库实践系列课程(1)的第一课通常不会一上来就讲Hive或者Spark而是回到关系型数据库的源头。这个选择很务实——不管数仓底层是CDH还是云上数仓最终面向分析的都是关系这种结构一张张表行是记录列是字段主键唯一确定一行。你只有把关系模型想清楚才能理解为什么数仓里要分层、为什么要做维度建模。我在带新人的时候会让他们先做一件事拿学校的学生选课系统举例画出学生表、课程表、选课表三张表标出主键和外键。这一步看起来简单但能筛掉一批人。因为很多人会问为什么选课表不能直接放学生姓名和课程名称——这就是没有理解关系模型的本质减少冗余通过键来关联。数仓里的维度表和事实表本质上也是这种关系的延伸只是把冗余又适度加了回来用于换取查询性能。这个取舍恰恰是数仓和传统OLTP数据库最大的区别。回到数据仓库实践的第一课你需要建立的认知是关系模型告诉你数据怎么组织而SQL是你操作这种组织的语言。两者缺一不可。如果你连主键和外键的语义都没吃透后面写JOIN的时候一定会出现笛卡尔积或者把一对多关系当成一对一。我曾经见过一个报表因为事实表和维度表关联时漏了维度表的版本条件结果数据膨胀了三倍排查了整整两天。根源就是对关系模型的粒度不敏感。2.2 事务ACID与数仓场景的取舍数据库基础里绕不开的是事务的ACID原子性、一致性、隔离性、持久性。第一课讲这些不是为了让你背定义而是让你知道数仓里的ETL为什么不能照搬OLTP的事务思维。举例说你在MySQL里做一次转账必须保证扣款和入账同时成功要么都发生要么都不发生这是原子性。但在数据仓库里通常的做法是分区覆盖先写入一个临时表校验没问题后再把分区数据整体替换掉。这个过程没有数据库事务保护靠的是任务调度和幂等设计。如果你没有数据库事务的概念就很难理解为什么数仓的ETL脚本里要写先drop临时表再insert overwrite partition。另一个典型是隔离性。数仓查询一般不在乎脏读因为数据本来就是批量的快照。但在ODS层抽取业务库数据时如果源库是MySQL你会关心binlog或者CDC工具的同步位置这里就涉及事务快照的概念。数据库基础第一课里讲事务其实是给你打预防针将来你写同步任务时看到事务日志、binlog、隔离级别这些词才不会懵。2.3 索引与约束第一课就该建立的微观认知索引可能是数据库基础里最容易被初学者跳过的部分因为写SELECT的时候感觉不到它。但到了数仓场景慢SQL的排查十有八九和索引有关。虽然数仓里的大表通常靠分区和分桶代替索引但你用SQL做数据探查或者从OLTP源库取数时索引的理解直接决定你写得对不对。我一般会让学员做一个实验在一张十万行的表上分别用主键查询和非索引字段查询对比执行时间。这个实验不复杂但能直观感受到索引为什么是B树而不是线性扫描。第一课不需要你去调执行计划但至少要明白唯一约束会创建索引组合索引有最左前缀原则以及索引不是越多越好——因为写入会变慢。数据仓库实践系列课程(1)把数据库基础放在最前面不是无意义的铺垫。我见过太多数仓工程师工作三年还在用SELECT * FROM table LIMIT 100来确认数据连EXPLAIN都不看。这种习惯的根源就是没有在入门时建立对索引和存储结构的直觉。所以这一课请你务必把索引当作核心知识点来对待而不是让它默默飘过。3. 用SQL跑通数仓前的三件套DDL、DML与SELECT查询3.1 DDL建库建表与字段类型的坑数据仓库实践的第一课SQL部分通常会从DDL开始。因为你要分析数据先得有表。但建表这件事在数仓场景下比想象中容易踩坑。最常见的坑是类型选错。比如用字符串存日期或者用FLOAT存金额。前者会导致时间函数全部失效后者会产生精度误差。我建议从第一课就养成习惯日期用DATE或TIMESTAMP金额用DECIMAL(10,2)状态用TINYINT或STRING但要统一。下面是一个标准的建表语句注释里会说明为什么这么写。-- 创建一张订单明细表用于数仓DWD层 CREATE TABLE dwd_order_detail ( order_id STRING COMMENT 订单ID来自业务系统, user_id STRING COMMENT 用户ID, product_id STRING COMMENT 商品ID, order_amount DECIMAL(10,2) COMMENT 订单金额保留两位小数, order_status TINYINT COMMENT 订单状态0待支付 1已支付 2已取消, create_time TIMESTAMP COMMENT 订单创建时间, dt STRING COMMENT 分区字段格式yyyy-MM-dd ) COMMENT 订单明细事实表 PARTITIONED BY (dt) -- Hive/数仓表通常按天分区 STORED AS PARQUET;这段DDL有几个关键点。第一金额字段用DECIMAL而不是FLOAT因为FLOAT在累加统计时会出现0.10.2不等于0.3的问题。第二分区字段dt单独放在最后它不是业务字段而是数仓里用于裁剪数据的物理分隔。第三PARQUET是列式存储适合分析场景的按列读取。如果你是第一次接触数仓不要用业务数据库的思维去建表——OLTP表不需要分区但数仓大表没有分区查询会变成全表扫描几十亿条记录扫下来慢SQL就诞生了。还有一个坑是字段注释。做数仓的人一定有这种经历看到一个字段名叫flag没人知道它是什么意思。所以第一课就要养成写COMMENT的习惯。数据治理不是后补的是从第一张表开始。3.2 DML增删改查的正确姿势数据库基础里的DML是INSERT、UPDATE、DELETE、SELECT。但到了数仓场景UPDATE和DELETE的语义会发生变化。传统关系库里你可以直接 UPDATE 某一行但在Hive或大部分分析型数仓中UPDATE是低效的正确做法是覆写或重刷分区。我在第一课会强调一个观念数仓里的表是不可变的数据变更靠重跑任务。所以你写INSERT时要习惯用INSERT OVERWRITE而不是INSERT INTO确保幂等。这个习惯能避免重复数据。-- 把业务库同步过来的订单数据写入数仓ODS层 INSERT OVERWRITE TABLE ods_order_biz PARTITION (dt 2025-06-01) SELECT order_id, user_id, product_id, amount, status, create_time FROM source_order_table WHERE create_time 2025-06-01 00:00:00 AND create_time 2025-06-02 00:00:00;这段逻辑里INSERT OVERWRITE会先清掉指定分区的数据再写入保证同一天的数据无论跑多少次任务结果一致。WHERE条件限定了抽取时间范围避免把全量数据重刷一遍。这里有一个参数要特别注意在Hive 2.3以上版本默认是strict模式防止用户写INSERT OVERWRITE不带分区这个设计是保护机制别试图绕过。而在MySQL这类OLTP数据库里学习DML的重点反而是增删改查的SQL语法本身。比如UPDATE语句忘了加WHERE会把整张表改掉——这种事故在数据库课程里是经典案例。数仓从业者虽然不常直接改业务库但在做数据同步工具配置、写临时脚本修数据时INSERT、UPDATE、DELETE的基本功依然会用到。所以要两手抓既会传统DML的语法又知道数仓模式下哪些操作被推荐、哪些被禁止。3.3 SELECT查询过滤、聚合与多表连接SELECT是SQL的核心也是数据仓库实践里使用频率最高的操作。第一课通常会从单表查询讲到聚合、分组、连接。我自己带人的时候最看重的不是谁会写复杂嵌套子查询而是谁能在写SQL前先讲清楚我要查什么粒度、需要哪几张表、关联条件是什么。先看最基础的单表聚合。你可以没有维度建模概念但必须理解GROUP BY和聚合函数的关系。-- 统计每日每个用户的订单金额、订单数 SELECT user_id, TO_CHAR(create_time, YYYY-MM-DD) AS order_date, -- 把时间截到天 COUNT(DISTINCT order_id) AS order_cnt, SUM(order_amount) AS total_amount FROM dwd_order_detail WHERE dt 2025-06-01 GROUP BY user_id, TO_CHAR(create_time, YYYY-MM-DD) HAVING SUM(order_amount) 100 ORDER BY total_amount DESC;这段SQL的要点有三个。WHERE是过滤分区在数仓里必须写否则会扫描全表GROUP BY的字段决定了聚合粒度你要算每人每天就必须同时带上user_id和日期HAVING是在聚合结果上做二次过滤不能用WHERE代替因为WHERE在GROUP BY之前执行。还有一个细节是COUNT(DISTINCT order_id)在高基数场景下这个操作很耗资源如果只是为了排查重复可以先GROUP BY再COUNT效率更高。连接是另一个大坑。内连接、左连接、右连接、全连接很多人能说出来但一遇到实际数据就翻车。我见过最常见的问题是用LEFT JOIN时右表因为一对多导致左表数据膨胀而写SQL的人没有意识到。第一课我会用一个极端的例子订单表LEFT JOIN 支付流水表一条订单有两次部分支付结果订单金额被翻倍统计。解决方式是先按订单ID聚合支付流水再和订单表关联。这种教训比背十遍LEFT JOIN语义有用得多。数据仓库实践系列(1)的SQL部分不是让你把语法全学完而是让你具备一个能力面对一堆表和业务需求能够用SELECT、JOIN、GROUP BY组合出正确的数据。这个能力是后续学习建模、ETL、性能调优的地基。地基不牢后面全是还债。4. 从SQL到数仓建模先用查询把数据质量摸清再谈星型模型4.1 为什么第一课要讲SQL而不是直接讲建模很多人会觉得奇怪数据仓库课程的第一课为什么不直接讲维度建模、星型模型而要讲数据库基础和SQL其实答案很简单建模是基于对数据的理解而SQL是理解数据的唯一方式。你连数据里有哪些字段、主键是否唯一、空值率多高都不知道怎么可能设计出合理的维度表和事实表我的做法是在讲任何建模理论之前先给学员一张真实的业务表让他们用SQL回答一系列问题这张表有多少行主键是否唯一哪些字段有大量空值时间字段的格式统一吗状态字段有哪些取值做完这些探查你自然会知道这张表能不能作为事实表的来源或者是否需要清洗。这个环节就是数仓实践的第一课和后续建模课的衔接点。4.2 用SQL观察数据质量空值、重复、分布数据探查的SQL看似简单但参数和细节很讲究。我先给一个标准的数据质量检查脚本它可以套用在任何一张业务表上。-- 数据探查检查重复主键、空值率、字段取值分布 WITH base AS ( SELECT order_id, user_id, amount, status, create_time, COUNT(1) OVER (PARTITION BY order_id) AS pk_cnt FROM ods_order_biz WHERE dt 2025-06-01 ) SELECT SUM(CASE WHEN pk_cnt 1 THEN 1 ELSE 0 END) AS dup_pk_count, -- 重复主键数量 AVG(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS user_id_null_rate, -- 空值率 COUNT(DISTINCT status) AS status_kind_count, MIN(create_time) AS min_create_time, MAX(create_time) AS max_create_time FROM base;这段脚本用了窗口函数COUNT(1) OVER (PARTITION BY order_id)来判断主键是否重复比用GROUP BY再JOIN回去要简洁。空值率用AVG配合CASE计算因为在SQL里AVG(布尔表达式)会自动把TRUE转成1、FALSE转成0这样空值率就是0到1之间的小数。这个写法不常见但很实用适合第一课阶段就掌握。当你跑完这段SQL会发现很多意外订单ID居然有重复说明源表是明细表而不是订单表user_id空值率高达20%说明有匿名下单create_time有1970年的数据说明同步工具时区配置有问题。这些发现就是后续建模时决定字段怎么处理、要不要做清洗的依据。如果跳过这一步直接建模模型建得再漂亮数据也是错的。4.3 从查询反推星型模型事实表与维度表怎么定数据仓库实践系列课程(1)不会把星型模型作为第一课的主角但会在最后用SQL的视角做一个引子。我通常会告诉学员一个反推方法你写SELECT查询时哪些字段放在WHERE过滤、GROUP BY分组、SELECT展示它们大概率就是维度属性哪些字段被SUM、COUNT、AVG包裹它们大概率就是事实度量。-- 反推模型从查询视角识别维度和事实 SELECT d.region_name, -- 维度区域名称用于分组 p.category_name, -- 维度商品分类用于筛选 SUM(f.sale_amount) AS total_sale, -- 事实销售额 COUNT(DISTINCT f.order_id) AS order_cnt -- 事实订单数 FROM dwd_sale_fact f LEFT JOIN dim_region d ON f.region_id d.region_id LEFT JOIN dim_product p ON f.product_id p.product_id WHERE d.region_name 华东 -- 维度过滤 GROUP BY d.region_name, p.category_name;从这段SQL你可以看到事实表放的是可加的数值维度表放的是描述性的文本和分组属性。查询里JOIN的方向是事实表先关联维度表维度表之间很少直接JOIN。这就是星型模型在SQL层面的样子。第一课不要求你会设计但要求你能看懂这个结构并且理解为什么事实表要大、维度表要小、维表通常用缓慢变化维来处理历史。这里有一个实战经验在写这种SQL时如果维度表的关联字段不是唯一键结果会出现重复。所以第一课就要养成习惯在跑JOIN之前先验证维度表的维度ID是否唯一。我见过有人因为维表里有历史版本导致事实表JOIN后行数翻倍最后还是在数据探查阶段用SQL查出来的。你有了第四节这个习惯就能少踩很多坑。5. 写SQL踩过的5个坑从乱用SELECT *到慢查询排查5.1 乱用SELECT * 导致的内存溢出现象在数据仓库里跑一个简单查询表只有几千万行结果报错Java heap space或者任务直接OOM。原因你写了SELECT *把每一行的所有列都加载进来。数仓表单行可能有几百个字段其中包含超大字符串、数组、MAP等复杂类型全列扫描会把内存撑爆。在开发调试时可以SELECT * LIMIT 100但正式任务绝对不能这么写。解决明确指定需要的列或者只读分区裁剪后的子集。养成按需取列的习惯同时检查是否漏掉分区过滤条件。我一般要求开发环境也写显式列名避免上线前一改再改。5.2 WHERE里对分区字段做函数运算现象跑了很长时间才出结果但明明只查一天的数据。执行计划显示扫描了全部历史分区。原因WHERE语句里写了类似WHERE dt TO_CHAR(create_time, YYYY-MM-DD)或者WHERE dt DATE_FORMAT(now(), yyyy-MM-dd)。数仓的优化器无法在分区裁剪阶段识别这种表达式只能全表扫描。解决把函数运算放到等号另一边或者提前算好分区值。最安全的写法是WHERE dt 2025-06-01。如果你要取最近七天动态生成分区字符串再拼接不要对分区列本身套函数。这个坑在慢SQL优化里非常常见第一课就要记住。5.3 JOIN时关联字段类型不一致导致的数据倾斜现象两个表JOIN后某个reduce任务跑得特别慢其他任务都结束了就它还卡在那。原因关联字段类型不一致比如一个表是INT另一个表是STRING优化器会隐式转换但数据分布不均。更常见的是关联字段在字符串类型下有脏值比如空字符串、NULL、或超长前缀导致大量数据落在同一个哈希桶。解决先用数据探查SQL看关联字段的取值分布把NULL单独过滤或补一个默认值再JOIN。如果类型不同统一用CAST转换到同类型但要注意CAST不会解决倾斜真正的解决是找出倾斜值并隔离处理。5.4 UPDATE语句忘记带WHERE条件现象本想修一条数据结果整张表被改全了。这种事故在OLTP数据库里经常出现数仓临时表也一样。原因编写更新脚本时先写了SET部分漏了WHERE条件或者WHERE条件写错没有匹配到任何行导致全表更新。解决执行UPDATE前先用SELECT把WHERE条件查一遍确认会影响的行数。例如先跑SELECT COUNT(*) FROM table WHERE target_id123看到结果是预期的1再执行UPDATE。另外在MySQL里可以设置SQL_SAFE_UPDATES1禁止没有WHERE条件的UPDATE。数仓命令脚本里最好在更新前备份目标表或分区。5.5 慢SQL排查时只盯着索引忽略了数据量级现象同一条SQL在测试库秒出到生产库跑十几分钟。原因测试库数据量小生产库到了几十亿行索引不一定被选择或者查询本身就要扫描大量数据。慢SQL优化不是加索引就完事要先看执行计划和数据分布。解决第一课就要学会看EXPLAIN。重点看三件事是否扫描全表、是否走了错误索引、JOIN顺序是否合理。对数仓SQL来说更直接的优化手段是分区裁剪、过滤条件前置、减少JOIN字段的复杂计算。记住一个原则先过滤再聚合先聚合再JOIN。这个顺序能解决大部分慢查询。6. 把第一课变成可验证的练习一份自测SQL清单数据仓库实践系列课程(1)学完你不能只是知道了数据库基础和SQL要能动手验证自己是否真的掌握。我给你一份自测清单它覆盖了第一课最核心的四个能力DDL建表、DML幂等写入、查询统计、数据质量探查。建议在一个真实或模拟的电商数据集上完成。第一题创建一张用户维度表包含user_id、注册日期、会员等级、所在地区采用分区表存储分区字段为dt。要求给每个字段加注释金额或数量字段用DECIMAL。写完以后用SHOW CREATE TABLE检查自己的建表语句观察分区定义和注释是否有遗漏。第二题用INSERT OVERWRITE把源订单表某一天的数据写入目标表要求重跑同一条SQL两次目标表行数不变。这个练习专门验证你对幂等写入的理解。如果用了INSERT INTO重跑后行数翻倍说明你还没建立数仓写入的直觉。第三题统计每个地区、每个商品分类的订单金额和订单数要求同时过滤出金额大于100的订单并按金额降序输出。完事后对比你的SQL和执行结果能不能用一句话解释WHERE和HAVING在各自步骤里起的作用。说不清楚就回头看书。第四题检查某张业务表的重复主键和空值率并输出异常字段的样例数据。这一步是后面学习建模的敲门砖——你会开始不信任看起来没问题的源表。这套自测我每次带人都用它能在半小时内暴露一个人的短板有人建表时把分区字段和普通字段混在一起有人写JOIN时漏了维表去重还有人不会用AVG(CASE WHEN)统计空值率。发现短板不是坏事第一课的目的就是让这些问题尽早暴露而不是等数仓上线后在大半夜排障。我自己带数据仓库实践系列课程的时候最深的教训是不要觉得数据库基础简单就跳过去直接讲Hive或Spark。越是后面遇到的数据问题越是从这几张表、几条SQL里出来的。底层的涨潮会掀翻所有上层建筑。现在带新人我会先让他们做完这份自测再碰数仓框架省下的排障时间足以覆盖多出来的教学时间。希望帮到你。本文还有配套的精品资源点击获取