Fabric丨白茶

EXCEPT是用来比较两个表之间的数据差，返回的结果集为在第一个表中存在且在第二个表中不存在的数据；INTERSECT是用来获取两个表之间的数据交集，返回的结果集为两个表中相同的部分。BOSS：哦，对了，差点忘记告诉你了，新系统没有进行过数据校验，所以切换之前，需要你处理一下，这个能整不？BOSS：哎，白茶，我们最近新上了一个系统，后续有一些数据要进行源切换，这个能整么？要知道，对于数仓来说，数据的准确性是非常重要的，也是衡量数仓是否可用的标准之一。3.被比较的两个表中不能存在不可被比较的数据类型。

通过WHERE关键字，我们可以将XML PATH的结果与表中原有UserName列进行自关联展示，但是会有很多的重复项，因此，我们需要通过GROUP BY来进行分组去重。通过上面的结果，我们不难理解，其实它就是将查询结果集以XML形式呈现，将多行的查询结果，合并在同一行展示。在上面的截图中，我们不难发现，每个结果前面都多了一个分隔符，很明显，从应用层来说，第一个分隔符是不需要的。而STUFF函数，可以在源字符中，删除给定长度的字符序列，并且从指定的起始位置，插入给定的字符序列。

这个就要从SQL Server的应用开始说起了，众所周知，SQL作为计算机的通用语言之一，在各个领域都存在广泛的应用，也有着各式各样的开发规范要求，但是并不是所有的场景下，开发者都会遵循相关的规范。这种情况下，小A和小B之间接口的对接，就会出现很大的差异性。为了解决这一现状，SQL Server引入了XML类型的字段，这样不需要针对表结构进行更改，可以将差异化类型的数据，存储在XML类型的字段中。可以看到，实际上在结果的第一行，是存在多行数据的，而nodes的作用通常是将一行中多行的数据，拆分出来。

数据库为了避免上述的三种情况，有4种特殊的隔离机制，分别是：Read uncommitted(读未提交)，Read committed(读已提交)，Repeatable read(可重复读)，Serizable(序列化)。而同时B管理员也读取了这个数据，这种情况下，B读到的是未更新的数据，也被称为脏数据。**不可重复读：**指的是某个事务中，多次对某张表进行读取操作，在A事务进程还没结束的情况下，B事务对数据进行了修改，这样A事务两次读取到的数据不一致，这种情况就是不可重复读。

用来登录数据库的访问账号，例：有些时候，我需要赋予某些用户权限，只可以对某个数据库有操作，不能对服务器有操作，这种情况下就需要使用用户名。在数仓的运维工作中，经常需要为用户开通不同权限的账号，使用户可以正常访问不同的数据，那么这就需要我们了解SQL Server的权限体系。，且白茶可以在多家企业任职，所以在服务器层面，登录名和用户名的关系是一对多，落到具体的数据库层面，登录名和用户名的关系是一对一。除了负责园区以外，白茶也在这片园区里面开办了一家企业，或在里面的某家企业任职，这时候白茶的角色是。

无论是搭建数仓，还是通过PowerBI进行数据分析，我们总会接触到各式各样的数据源，而在这些数据源中，除了标准的大型数仓外，我们很少能够拿到标准规范的数据表结构。接触最多的，往往是大宽表，这就需要我们将大宽表转换为标准的二维表结构，即实现表结构的降维操作，也就是PowerBI中的透视功能。PIVOT函数，通常用来将表结构从二维表转换为大宽表，而UNPIVOT则恰恰相反，通常是用来将大宽表转换为二维表。同理，除了UNPIVOT函数以外，列转行也是有其他办法的。将二维表，转换为大宽表。将大宽表，转换为二维表。

毕竟，日志文件中记录着对数据库所执行的操作，有时候我们需要查看日志文件进行操作复盘的。而在我们日常运维过程中，偶尔会遇到数据库执行JOB的时候，报内存不足的错误，导致JOB无法正常运行，为了解决这一问题，就需要我们定期对数据库的日志进行清理。在使用SQL Server的过程中，随着时间的变化，后台日志文件的体量也是在不断增加的。点击OK，保存即可，这样就完成数据库日志的自动清理了，当然，我们也可以选择立即执行，查看一下JOB是否能够正常运行。当然，我们也可以通过执行下面的语句，来查看日志文件的相关信息。

但是也有一些特殊情况，比如SQL Server的上游数据，有一部分线下数据，是交由用户进行维护的，那么数据就非常容易出现前后空白字符的情况。2.一些较为特殊的情况，SQL或者其他上游无法对这部分数据进行处理，我们也可以在PowerBI中利用TRIM函数进行处理，由于函数较为简单，白茶这里就不赘述了。1.通常情况下，SQL数仓在抽取上游数据进入到DW层时，都会对数据进行一下清理，这种情况下，适用上述两个SQL函数；TRIM在不同的数据库中，对应的表现形式是不一样的，这里白茶只介绍SQL Server的函数。

MEGREMEGRE语句，在SQL的生态圈中，一直都隶属于一个比较重要的位置。要知道，在实际的项目应用中，我们经常需要从上游数据源，进行原始数据的抽取、清洗、存储、分析等操作，特别是在存储这一环节，MEGRE的作用尤为突出。举个例子：我们需要完成对上游维度信息的抽取，根据我们的设定，数仓在每日执行抽取任务之前，需要对数仓已有的维度信息进行判断，如果已有的，要核对是否与最新的数据相匹配，不匹配则进行更新操作；如果是没有的，则需要执行插入操作。常规的实现方法需要分别编写SQL语句进行条件判断，分别执行更新和插入

在SQL Server中，JOB属于常用功能，我们经常需要通过JOB来执行一些定时的作业任务，例如数据备份、存储过程、SSIS任务、SSAS刷新等等。在白茶本机的数据库中，存在名为“BaiCha”的数据库，存在名为“ODS_BaiCha”的表。在Backup_Table数据库中，为“ODS_BaiCha”表创建备份表，注意各列数据格式。后面的警报面板、通知面板、目标面板，白茶这里就不配置了，实际项目中小伙伴们可以酌情进行设置。对JOB任务进行命名，这里白茶命名为“Backup_Test”，并勾选启用按钮。

注意：这里白茶在声明test1的时候，使用了SCROLL，如果不进行此表明的话，则只能进行NEXT提取操作，表明了则可以进行FIRAT、LAST、PRIOR、NEXT等所有的操作。简而言之，SQL语句返回的结果通常为数据集，而游标可以在此基础上，像一个指针一样，对某些行结果进行处理，换成PowerBI中的概念有点像迭代遍历。游标除了上面展示的，还可以对特定数据进行修改操作，也可以与存储过程和WHILE搭配使用，后面有机会的话，白茶会进行补充。2.打开游标：执行SQL语句，准备对游标进行操作。

经常会有小伙伴循环写着写着就懵掉了，执行循环无法结束，也没有结果显示出来，这种情况下，需要对BEGIN里面的增量SET进行检测，看是否是由于未设置增量导致。如果嵌套了两个或多个WHILE循环，首先运行内层循环结束之前的所有语句，然后下一个外层循环重新开始执行。答案是有的，不过此类操作我们一般都是在PowerQuery中进行的，在DAX中需要进行循环处理的情景较少。而在SQL中，有很多场景都需要使用循环语句进行处理，比如数据更新或者递增计算等。通俗来说，就是当XXX时，执行某某操作，意为循环。...

存储过程（一）存储过程，可以说是SQL中比较重要的概念了，基本上所有的数据类项目都会涉及到这块的内容。很多面试在第二轮技术面的时候，也会提及，那么什么是存储过程呢？定义存储过程类似于C语言中的函数通常可以用来执行管理任务或者应用复杂的规则存储过程既可以带参数，也可以直接返回结果存储过程可以包含数据操纵语句、变量、逻辑控制语句等语法SQL Server语法：调用存储过程语句：使用实例案例数据：在白茶本机的数据库中存在名为“CaseData”的数据库。“Dim_Date“日期表、"Dim_Product"产

截取字符串我们在做BI可视化之前，通常需要已经清洗干净的数据才能进行可视化分析。随着电商的发展，有很多数据都是从网上渠道抓取过来的，这就导致原始数据有很多对于分析来说的无效数据，那么在SQL中这部分数据该如何进行处理呢？函数介绍SUBSTRING语法：SUBSTRING ( 表达式 , 开始位置 , 长度 )返回结果为：返回字符、binary、text 或 image 表达式的一部分。CHARINDEX语法：CHARINDEX ( 目标字符串 , 被查找字符串 [ , 开始查找位置 ] .

表变量与临时表白茶在之前描述WITH AS的用法时，曾提到过，除了WITH AS以外，我们还有其他两种办法来进行类似DAX中VAR定义虚拟表的行为，本期我们来了解一下：如何使用表变量与临时表。表变量特征：1.表变量拥有特定的作用域且在批处理结束以后会被自动的清除；2.表变量相较于临时表，会产生更少的存储过程重编译；3.表变量的事务行为仅在更新数据时生效，因此锁和日志产生的数量较少；4.表变量的作用域较小，不属于数据库持久的部分，因此事务的回滚不影响表变量。表变量可以像正常的物理表一样使用.

WITH AS在DAX函数中，有些时候我们写的DAX逻辑引用次数过多，或者是性能过差，我们都会选择使用VAR来定义一个变量，减少资源的占用和方便重复的引用。而有些时候，为了进一步提高性能，我们还会选择通过VAR定义虚拟表来减少迭代遍历的次数。那么在SQL中有没有类似于VAR可以定义一个变量或者虚拟表的操作呢？答案是有的，而且方法还比较多。本期呢，我们来了解一下类似于VAR定义虚拟表的操作：WITH AS。除了WITH AS以外，还有一些操作也是类似于VAR的效果：1.我们可以定义一.

开窗函数（二）之前的文章里，白茶曾经描述过关于开窗函数的内容，本期我们来继续这个话题。通过之前的介绍，相信大家也知道了，我们经常使用的开窗函数除了排名函数以外，还有聚合函数。语法<开窗函数> over (partition by <用于分组的列> order by <用于排序的列>)本期呢，会给大家展示聚合函数在开窗函数中的应用。聚合函数：sum，avg，count，max，min。使用实例案例数据：在白茶本机的数据库中存在名为“CaseData”.

开窗函数（一）开窗函数在SQL语句中属于一种特殊的用法。开窗函数的引入，是为了既可以显示聚集前的数据，也要显示聚集后的数据。而在SQL中，开窗函数又分类两类，一类是排序函数，一类是聚合函数。语法<开窗函数> over (partition by <用于分组的列> order by <用于排序的列>)开窗函数：如果这里我们使用的是排序函数，那么输入Rank()这类；如果这里我们选择的是聚合函数，那么输入Sum(列)这类。注：用于分组的列，可以进行省略，类似于.

CASE WHENCASE WHEN是SQL中特有的查询关键字，这个组合在PowerBI中是没有的。白茶在之前介绍PowerBI中IF的时候曾经说过，SWITCH底层逻辑和IF是一样的，只不过是写法上更加的优雅。但是在SQL中比SWITCH更好用的，是CASE WHEN。语法CASE WHEN语法一共有两种。语法1：SELECT 列(*),(CASE 列名称 WHEN 值 THEN 结果 WHEN 值 THEN 结果... ELSE 其他.

IN & EXISTSIN和EXISTS，在子查询的过程中，二者可以说是SQL关键字中使用比较频繁的语句了，而且，在很多TA面试的时候，都会提问关于二者之间使用场景的区别。之前在接触这部分知识点的时候，有的人说IN的性能更快，有的说EXISTS更快，其实这两种说法都不对，要看具体的应用场景的。语法IN子查询语法：SELECT 列名称(s) FROM 表来源WHERE 列名称 IN ( SELECT 列名称(s) FROM 表来源...)EXISTS子查询语法：SELEC.

DateDate基本上是所有数据处理软件都会涉及到的一个版块，而且也是最贴近业务的一类数据类型。在PowerBI中，有很多的时间函数以及时间智能函数，而在SQL中，也有类似于时间函数的子句。为什么说Date这部分数据最贴近业务？相信业务方的小伙伴可能心有感触，日常我们关注的，就是各种时间维度的指标计算。例如：MTD、QTD、YTD、LMAmt、LQAmt、LYAmt、MOM%、QOQ%、YOY%等等。究其根本，很多企业发展到一定量级的时候，从管理层的角度更多的是关注一段时间内的各种指标，关注的方.

VIEWVIEW在数据库中是一个很重要的概念，也是数据仓库对用户开放数据的一种方式。VIEW可以理解为，是SQL语句结果集形成的一张表，内容和我们Excel中使用的表结构是一样的，包含基本的行、列等等。对于刚接触SQL的小伙伴来说，可能不太好理解，但是实际上，VIEW非常的好用。比如我们的数据仓库，底层有很多的表，也有很多的Mapping关系，我们需要构建一张大宽表来进行数据分析，这个时候VIEW的价值就体现出来了。PS：不知道大宽表的小伙伴请自行百度。优点1.安全性考虑：VIEW生成的.

AUTO-INCREMENTAuto-Increment，自动插入的意思。和DAX函数中的GENERATESERIES函数有点类似，二者都是设定一个初始值，自动的生成一组数值。区别在于，在PowerBI中，GENERATESERIES函数通常是用来生成参数使用。而在数据仓库中，我们都知道数据需要唯一主键，且根据主键的特性不可复用、不提供给用户，因此我们每张表中对应的数据都需要每次在数据更新的时候自动生成唯一主键，Auto-Increment特别适用这个场景。基础语法CREATE TABLE 表.

Drop & AlterDrop，撤销操作；Alter，更新操作。在PowerBI中，如果我们针对某些数据清洗操作进行修改的话，只需要打开高级编辑器查看M语言，对特定的语句进行修改删除就可以了。但是在数据库仓库中，我们往往需要借助Drop和Alter或者其他语句才能对数据仓库进行修改。为什么在数据仓库中我们不能像在PowerQuery中进行同样的操作呢？其实这个和二者的定位有关。PowerQuery的定位，是针对PowerView进行服务的，虽然PowerQuery自成体系.

INDEXINDEX，索引。索引在数仓中属于高级技能之一，也是很多HR面试的时候喜欢问的点。索引可以用来对数据进行排序，并以此来加快搜索和排序。这点和SUBSTITUTEWITHINDEX函数有点像。白茶在之前描述这个函数的时候曾经说过，这个函数属于高阶函数之一，出场率也不是很高，只有特定的场景可以使用。这点和SQL中的INDEX有点类似，通常可以用来为原本的维度列新增索引列，以方便某些时候对维度的计算，可以将原本的文本信息转化为可参与计算的数值。而在SQL中，INDEX表现的则更加纯粹。基础.

ConstraintsConstraints，约束的意思。通常用来约束加入表中的数据，如果与约束冲突，则行为会被终止。通常情况下，Constraints会与CREATE TABLE或ALTER TABLE搭配使用。Constraints在数据仓库中一直都是一个很重要的概念，是用来管理如何插入或处理数据库数据的规则。要知道，对于数据库而言，最重要的是数据库数据的规范化和标准化，这将决定了当前数据的延展性和复用性。CREATE TABLE：创建表语句。ALTER TABLE：更新表语句。Co.

CREATE语句CREATE语句在SQL语言中是非常重要的一环。要知道SQL语法基本划分为四大类：增、删、改、查。我们日常使用较多的是查询语句，虽然其他三类语法语句数量不是很多，但是也是不可或缺的一部分。CREATE语句的用法是非常多的，可以用来创建数据库，可以用来创建表，可以用来创建视图，也可以用来创建一些临时表用来计算使用等等。基础语法创建数据库：CREATE DATABASE 数据库名称创建表：CREATE TABLE 表名称(列名1 数据类型,列名2 数据类型,列名3 数据.

SELECT INTOSELECT INTO语句通常可以用来创建备份。相信小伙伴对备份这个概念不会陌生，我们在做BI报表的时候，或者构建DAX函数过程都喜欢预先对PBI文件备份。有时候，这是一个好习惯，方便发生错误我们能及时的进行版本回滚。同样，在SQL中，备份这个概念也是很重要的，比如我们需要定期对数据库进行备份，或者在某些时候需要对某些表某些数据备份。用途可以用来选取数据并插入另一个表中可以创建表的备份对表进行存档语法SELECT 列(*) INTO&nb.

UNION语句UNION语句类似于PowerQuery中的追加查询，可以将两个表或者两个数据集进行上下合并。DAX函数中也有UNION，而且用法上有很大的相似。UNION使用要求两个数据集列的数量相同，两个数据集列的数据类型完全一致，且列的顺序也需要保持一致。除了UNION之外，还有UNION ALL语句。二者的区别在于前者返回的数据集没有重复项，后者返回的数据集包含重复项。基础语法UNION语法：SELECT 列名称(s) FROM 表1UNIONSELECT 列名称(s) FROM 表.

不同的JOIN白茶在之前描述过JOIN的一些基本用法，但是实际上JOIN有几种类型不同的变体。不同的JOIN，其返回的结果也是不同的。JOIN的类型有以下几种：JOIN，如果表中至少有一个匹配项，则返回行；LEFT JOIN，即使右表中没有匹配，也从左表中返回所有行；RIGHT JOIN，即使左表中没有匹配，也从右表中返回所有行；FULL JOIN，只要其中一个表中有匹配，就返回行。基础语法语法：SELECT 列名称(*) FROM 表1名称 (JoinType) JOIN 表2名称.

JOINJOIN在SQL Server中是一个很重要的概念。JOIN经常用于将两个表或者多个表通过主外键关联进行组合查询。在PowerBI中也有类似的概念，比如可以通过GENERATE函数和CROSSJOIN函数可以组成笛卡尔积，可以通过RELATED返回多端的维度。最直观类似的，是白茶之前描述的《INTERSECT函数》。区别在于，事实表与维度表中的主外键通常是无冗余的，因此在SQL Server中使用JOIN可以避免出现重复数据的情况。基础语法语法：SELECT 列名称(*) FROM .

AliasAlias，别名的意思。在SQL Server的数据库中，我们可以使用AS对表或者列或者查询指定别名。请不要忽视这点，AS在某些时候特别有用，可以说使用频率非常高。基础语法表语法1：SELECT 列名称(*) FROM 表 AS 别名列语法1：SELECT 列名称 AS 别名 FROM 表使用实例案例数据：在白茶本机的数据库中，存在名为“TEST”的数据库，存在名为“产品表”的维度表和名为“销售明细”的事实表。例子1：在PowerBI中，将“产品表”中的.

BETWEEN操作符BETWEEN操作符属于WHERE语句的补充。通常与AND搭配使用，可以用来限定数据范围。可以是数值，可以是文本，也可以是日期。相较于PowerBI而言，SQL中的BETWEEN更加灵活。PowerBI中除了日期函数系列有类似的函数以外，并没有单独的BETWEEN函数。基础语法SELECT 列名称(*) FROM 表名称 WHERE 列名称 BETWEEN 条件1 AND 条件2注意事项BETWEEN…AND的语句表达属于数据库通用语句之一，在大部分的数据库中此语句都是.

通配符通配符这个概念可以说是数据库语言中很重要的一块。重要的原因想必各位小伙伴也可以猜的到，数仓的数据量级通常都是比较大的，很多时候我们做数据查询，并不能完全保证自己的查询条件是准确的，或者说我们需要模糊查询来获取一类数据，这种情况下通配符是很有用的。相较于SQL，PowerBI使用通配符的机会并不多见。根本原因在于二者的定位不同。PowerBI侧重于数据的前端展示，SQL是后端的数据清洗以及存储。SQL通配符类别通配符描述%替换一个或多个字符_替换一个字符.

LIKE操作符LIKE操作符属于对WHERE子句的补充。通常我们使用WHERE子句都是知道具体的筛选条件，但是有些时候，也需要进行模糊查询，这种情况就需要LIKE。横向对比，在PowerBI中使用SEARCH函数加上通配符也可以达到类似的效果。传送门：《SQL丨WHERE子句》《PowerBI丨SEARCH》基础语法SELECT 列名称（*） FROM 表名称 WHERE 列名称 LIKE 条件注意事项SQL中的通配符为：%PowerBI中的通配符为：？或*使用实例案例数据：.

TOP函数TOP函数，属于高级函数之一，通常可以用来返回固定数量的行数。对于一定数据量级的事实表来说，此函数可以查看数据质量，用途非常广泛。在PowerBI中，也有与之对应的函数——TOPN函数。传送门： 《TOPN函数丨环比断点》基础语法语法1：SELECT TOP 数字 FROM 表名称语法2：SELECT TOP 数字 PERCENT FROM 表名称注意事项MySQL和Oracle中也有类似TOP的使用；MySQL和Oracle作为标准的数据库，与SQL Ser.

DELETE语句DELETE这个单词小伙伴们肯定不会陌生，就是我们键盘上的Delete按键。在SQL中，DELETE语句可以用来删除表中的行。基础语法语法1：DELETE FROM 表名称 WHERE 列名称 = 条件值语法2：DELETE FROM 表名称语法3：DELETE * FROM 表名称使用实例案例数据：在白茶本机的数据库中，存在名为“TEST”的数据库，存在名为“产品表”的案例数据。例子1：在PowerBI中删除[产品表]中[商品名称]为“白茶”的数据。.

UPDATE语句UPDATE语句在SQL的查询中，通常可以用来修改表中的数据，即我们经常听说的“改”。在PowerBI中，我们主要使用SQL语言中的查询功能，白茶在上一期也提及过，除了查询功能外，增、删、改功能的语句，这些在PowerBI中都是需要慎重使用的。可能对于一些SQL的大佬们来说，这个是一个常识性的知识，但是对于类似白茶这类的SQL小白来说，往往这些不起眼的常识性知识才是我们需要注意的雷区。基础语法UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 条件值使用实.

INSERT INTO语句INSERT INTO语句通常可以用来在SQL的查询中针对表插入某些行数据。看到这里，可能小伙伴会问白茶，你不是说我们在PowerBI中使用查询函数即可么，那这个增加函数咋回事？别急，听白茶慢慢给你说。基础语法语法1：INSERT INTO 表名称 VALUES (值1, 值2,....)语法2：INSERT INTO 表名称(列1, 列2,...) VALUES (值1, 值2,....)原因白茶解释一下，为什么本期内容会去讲述一个不属于查询语句的函数。.

ORDER BY语句ORDER BY语句，在SQL中通常可以用来作为查询结果的排序依据。当然在PowerBI的DAX语法中也有ORDER BY，只不过通常是与EVALUATE语句搭配使用。在实际使用DAX函数过程中ORDER BY函数的出场率并不是特别高，只有一些特定的函数可以与此搭配使用，而且，在BI的需求中，针对返回结果有特殊排序需求的函数也就是RANKX函数和TOPN函数了。我们更常使用的是界面操作的“按列排序”功能。但是在SQL语法中，ORDER BY函数的使用频率可以说相当高了。基础语法.