BI-SQL丨WITH AS

作者: Fabric丨白茶 | 来源:发表于2022-04-15 19:14 被阅读0次

WITH AS

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

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

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

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

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

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

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

语法

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的写法。

SELECT BranchName,TotalNum FROM 
(SELECT ProductID,BranchName,SUM(Quantity) AS TotalNum FROM Fact_Sales
GROUP BY ProductID,BranchName) AS T1

WITH AS 写法：

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语句。

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。

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