MDX,DAX, or SQL的比较

本文通过三个实例对比了SQL、MDX和DAX三种查询语言在不同场景下的使用方式,展示了它们如何实现相似的数据分析任务,并强调了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的ADDCOLUMNSNO EXISTINGFILTER CONTEXT

SUMMARIZE EXISTING FILTER CONTEXT

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值