MDX,DAX, or SQL的比较

最新推荐文章于 2025-02-11 01:31:17 发布

翻译最新推荐文章于 2025-02-11 01:31:17 发布 · 3.5k 阅读

文章标签：

#sql #internet #calendar #date #join #filter

DAX 同时被 2 个专栏收录

20 篇文章

订阅专栏

MDX

7 篇文章

订阅专栏

本文通过三个实例对比了SQL、MDX和DAX三种查询语言在处理相同业务需求时的不同实现方式，并展示了它们各自的特点及如何得到一致的结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MDX,DAX, or SQL,同样的问题,一样的结果.

看一个例子

SQL

SELECT    pc.EnglishProductCategoryName,    SUM(SalesAmount) AS [Internet Total Sales]FROM    dbo.FactInternetSales sLEFT JOIN dbo.DimProduct p    ON s.ProductKey = p.ProductKeyLEFT JOIN DimProductSubcategory ps    ON p.ProductSubcategoryKey = ps.ProductSubcategoryKeyLEFT JOIN dbo.DimProductCategory pc    ON ps.ProductCategoryKey = pc.ProductCategoryKeyGROUP BY    pc.EnglishProductCategoryNameORDER BY EnglishProductCategoryName ASC

MDX

SELECT [Measures].[Internet Total Sales] ON COLUMNS,       NON EMPTY [Product Category].[Product Category Name].[Product Category Name] ON ROWSFROM [Internet Sales]

DAX

EVALUATESUMMARIZE( 'Internet Sales',           'Product Category'[Product Category Name],           "Internet Total Sales", 'Internet Sales'[Internet Total Sales]           )ORDER BY 'Product Category'[Product Category Name]

实际这三个,返回的结果完全相同.

再来看一个,

MDX

WITH    MEMBER Measures.[Sales 2003]        AS ([Measures].[Internet Total Sales], [Date].[Calendar Year].&[2003] )SELECT    {Measures.[Internet Total Sales], Measures.[Sales 2003]} ON COLUMNS,    NON EMPTY [Product Category].[Product Category Name].[Product Category Name] ON ROWSFROM [Internet Sales]WHERE ([Date].[Calendar Year].&[2002])

DAX

DEFINE    MEASURE 'Internet Sales'[Sales 2003] = CALCULATE(        SUM( 'Internet Sales'[Sales Amount] ),        'Date'[Calendar Year] = 2003 )EVALUATEFILTER(    CALCULATETABLE(        ADDCOLUMNS(            ALL( 'Product Category'[Product Category Name] ),            "Internet Total Sales", 'Internet Sales'[Internet Total Sales],            "Sales 2003", 'Internet Sales'[Sales 2003] ),        'Date'[Calendar Year] = 2002    ),    [Internet Total Sales] <> 0 || [Sales 2003] <> 0)ORDER BY 'Product Category'[Product Category Name]

这2个一样

最后来看一个CROSSJOIN的

MDX

WITH    MEMBER Measures.[Sales 2003]        AS ([Measures].[Internet Total Sales], [Date].[Calendar Year].&[2003] )SELECT    {Measures.[Internet Total Sales], Measures.[Sales 2003]} ON COLUMNS,    NON EMPTY [Product Category].[Product Category Name].[Product Category Name]      * [Product Sub-Category].[Product Subcategory Name].[Product Subcategory Name] ON ROWSFROM [Internet Sales]WHERE ([Date].[Calendar Year].&[2002])

DAX

DEFINE    MEASURE 'Internet Sales'[Sales 2003] = CALCULATE(        SUM( 'Internet Sales'[Sales Amount] ),        'Date'[Calendar Year] = 2003 )EVALUATEFILTER(    CALCULATETABLE(        ADDCOLUMNS(                  CROSSJOIN( ALL( 'Product Category'[Product Category Name] ),                             ALL( 'Product Sub-Category'[Product Subcategory Name] ) ),            "Internet Total Sales", 'Internet Sales'[Internet Total Sales],            "Sales 2003", 'Internet Sales'[Sales 2003] ),        'Date'[Calendar Year] = 2002    ),    [Internet Total Sales] <> 0 || [Sales 2003] <> 0)ORDER BY 'Product Category'[Product Category Name],         'Product Sub-Category'[Product Subcategory Name]

也许, 要主意的是:

DAX的ADDCOLUMNS NO EXISTING FILTER CONTEXT

SUMMARIZE EXISTING FILTER CONTEXT