BI-SQL丨WITH AS

2022-04-15 19:21:24 浏览数 (4)

WITH AS

在DAX函数中,有些时候我们写的DAX逻辑引用次数过多,或者是性能过差,我们都会选择使用VAR来定义一个变量,减少资源的占用和方便重复的引用。而有些时候,为了进一步提高性能,我们还会选择通过VAR定义虚拟表来减少迭代遍历的次数。

那么在SQL中有没有类似于VAR可以定义一个变量或者虚拟表的操作呢?

答案是有的,而且方法还比较多。

本期呢,我们来了解一下类似于VAR定义虚拟表的操作:WITH AS。

除了WITH AS以外,还有一些操作也是类似于VAR的效果:

1.我们可以定义一个变量;

2.我们可以创建一个临时表。

语法

代码语言:txt复制
WITH
命名1 AS (子查询),
命名2 AS (子查询),
命名3 AS (子查询),
SQL语句

WITH AS短语也叫子查询部分,可以定义一个SQL的片段,该片段会被整个SQL查询使用到。

这个是WITH AS的官方定义,从字面上看与DAX中通过VAR定义一个变量的方式是比较类似的。

但是与上面提到的临时表和SQL定义变量的方式有很大的区别。

例:临时表的话会将数据插入到数据库中,往往我们在使用后需要将临时表进行删除,可以提高代码运行的速度,属于典型的空间换时间;而WITH AS只是将子查询当作了一个表,真实的数据没有插入到数据库中(可以理解为DAX定义的虚拟表没有物理模型),可以提高代码的可读性以及维护。

参数

1.WITH AS可以定义多个片段,但是只能使用一个WITH;

2.如果使用多个片段,那么你需要为不同的片段进行命名;

3.WITH AS语法结束后,后面必须直接跟调用的SQL语句,不然无法生效。

使用实例

案例数据:

在白茶本机的数据库中存在名为“CaseData”的数据库。

“Dim_Date“日期表、"Dim_Product"产品表、"Fact_Sales"销售事实表。

例子1:

将下面的代码换成WITH AS的写法。

代码语言:txt复制
SELECT BranchName,TotalNum FROM 
(SELECT ProductID,BranchName,SUM(Quantity) AS TotalNum FROM Fact_Sales
GROUP BY ProductID,BranchName) AS T1

WITH AS 写法:

代码语言:txt复制
WITH T1 AS
(SELECT ProductID,BranchName,SUM(Quantity) AS TotalNum 
FROM Fact_Sales
GROUP BY ProductID,BranchName)
SELECT BranchName,TotalNum FROM T1

结果如下:

从结果上看,二者的结果完全一致。

这样做的好处是提高了代码的阅读效率,也方便我们对子查询T1进行维护。

例子2:

一个错误的示范:WITH AS后面没有直接使用调用的SQL语句。

代码语言:txt复制
WITH T1 AS
(SELECT ProductID,BranchName,SUM(Quantity) AS TotalNum 
FROM Fact_Sales
GROUP BY ProductID,BranchName)
SELECT BranchName FROM Fact_Sales
SELECT BranchName,TotalNum FROM T1

结果如下:

从结果上看,因为我们没有直接使用SQL调用WITH AS定义的片段,因此这个语法结果是错误的。

例子3:

使用WITH AS语法,匹配日期表,来计算销售数量的YTD。

代码语言:txt复制
WITH TotalTable AS
(SELECT Dim_Date.DATEKEY,
        Dim_Date.Year,
    CurrentTable.Quantity
FROM Dim_Date
RIGHT JOIN(
SELECT Fact_Sales.DATEKEY,
       SUM(Fact_Sales.Quantity) AS Quantity
FROM Fact_Sales
GROUP BY Fact_Sales.DATEKEY
           )AS CurrentTable
    ON Dim_Date.DATEKEY=CurrentTable.DATEKEY
)
SELECT DATEKEY,YEAR,Quantity,
       SUM(Quantity) OVER (PARTITION BY YEAR ORDER BY DATEKEY) AS QuantityYTD
FROM TotalTable

结果如下:

可以看到,结果按照日期升序的方式,将销售数量进行汇总,同时完成了事实表与维度表的联合查询,结果符合我们的预期。

小彩蛋:

如果我们将PARTITION BY YEAR这段去掉的话,结果会发生什么呢?

感兴趣的小伙伴可以自己动手测试一下。

这里是白茶,一个PowerBI的初学者。

0 人点赞