主页 / dba / 问题 / 138311
Accepted
Leftium
Asked: 2016-05-13 00:35:47 +0800 CST

用于分析查询的 MDX 与 SQL 的好例子

  • 772

在进行分析查询时，任何人都可以向我展示 MDX 相对于常规 SQL 的优势的一个很好的例子吗？我想将 MDX 查询与给出相似结果的 SQL 查询进行比较。

维基百科说：

虽然可以将其中的一些转换为传统 SQL，但即使对于非常简单的 MDX 表达式，也经常需要合成笨拙的 SQL 表达式。

但是既没有引用也没有例子。我完全清楚底层数据必须以不同的方式组织，并且 OLAP 每次插入都需要更多的处理和存储。（我的建议是从 Oracle RDBMS 迁移到Apache Kylin + Hadoop）

背景：我试图说服我的公司，我们应该查询 OLAP 数据库而不是 OLTP 数据库。大多数 SIEM 查询大量使用分组、排序和聚合。除了性能提升之外，我认为 OLAP (MDX) 查询会比等效的 OLTP SQL 更简洁、更容易读/写。一个具体的例子可以说明这一点，但我不是 SQL 专家，更不用说 MDX ......

如果有帮助，以下是针对过去一周发生的防火墙事件的示例 SIEM 相关 SQL 查询：

SELECT   'Seoul Average' AS term, 
         Substr(To_char(idate, 'HH24:MI'), 0, 4) 
                  || '0'        AS event_time , 
         Round(Avg(tot_accept)) AS cnt 
FROM     ( 
                SELECT                     * 
                FROM   st_event_100_#yyyymm-1m# 
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query#
                UNION ALL 
                SELECT * 
                FROM   st_event_100_#yyyymm# 
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query# ) pm
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4) 
                  || '0' 
UNION ALL 
SELECT   'today' AS term , 
         substr(to_char(idate, 'HH24:MI'), 0, 4) 
                  || '0'        AS event_time , 
         round(avg(tot_accept)) AS cnt 
FROM     st_event_100_#yyyymm# cm 
WHERE    idate >= trunc(sysdate) #stat_monitor_group_query# 
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4) 
                  || '0' 
ORDER BY term DESC, 
         event_time ASC
olap mdx

3 个回答

  1. Best Answer

    MDX并且在查询和分别SQL时绝不相同，甚至通常甚至不具有可比性。您不能使用 MDX 查询现有的关系数据库。multidimensionalrelational databases

    使用多维模型并使用 MDX 查询它的主要优点是您正在查询预聚合数据，并且 MDX 已优化为以统计方式而不是关系方式查询。您不再查询行和表来生成平面结果集，而是使用元组和集合来切片和聚合多维多维数据集。

    可以这样想：如果您使用 SQL 查询来获取特定项目组的总销售额，则需要编写一个查询来汇总项目组中所有项目的所有发票行。如果您使用多维数据集并在项目组级别上进行聚合，则在处理期间计算结果并为每个项目组存储聚合，从而使查询即时进行。

    多维和 MDX 是与基于关系集的 SQL 完全不同的概念。

    您的示例可能会变得更简单，因为您将在数据加载过程中进行日期解析等转换，并且您上个月的比较可能是calculated measure. 你的首尔平均水平和今天可能是calculated members

    如果您的多维数据集设计得很好，可以满足您的要求，我相信您可以对示例的数据集进行切片和切块，甚至不需要编写查询，而是在数据透视表或其他分析工具中进行。

    再说一遍，没有“只是在 MDX 中重写 SQL”。正确地做这件事需要相当多的知识和不同的心态。想想维恩图而不是结果集。

    为了给您提供一个使用 Adventureworks 数据库的示例，假设需要在自行车类别中按客户列出销售订单的数量。

    如果您使用 SQL 执行此操作，则需要编写一个查询来计算销售订单的数量，该订单包含一条恰好属于自行车类别的产品，并将其连接到客户表中，因此这将成为一个相当复杂的查询.

    -- need distinct count, we're counting orders, not order lines
SELECT count(DISTINCT soh.salesorderid)
    ,pers.FirstName + ' ' + pers.LastName
FROM sales.SalesOrderDetail sod
-- we need product details to get to the category
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
-- but we need to pass via subcategories
INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
-- we finally get to the category
INNER JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
-- we also need the headers because that's where the customer is stored
INNER JOIN sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
-- finally the customer, but we don't have his name here
INNER JOIN sales.Customer c ON soh.CustomerID = c.CustomerID
-- customers
INNER JOIN Person.Person pers ON c.PersonID = pers.BusinessEntityID
-- filter on bikes
WHERE pc.Name = 'bikes'
-- but the customers table doesn't contain the concatenated name
GROUP BY pers.FirstName + ' ' + pers.LastName;

    在 MDX 中（如果您的多维数据集针对此要求设计得很好），您可以直接编写，因为逻辑和复杂性已经转移到其他地方：

    SELECT [Measures].[Internet Order Count] ON COLUMNS,
[Customer].[Customer].Members ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Category].[Bikes]
    • 11

  2. OLAP 多维数据集/数据库具有以下特征：

    • 根据用户的需要获取已经聚合的信息。
    • 轻松快速的访问
    • 能够操作不同维度的聚合数据
    • 多维数据集使用经典的聚合函数 min、max、count、sum、avg，但也可以使用特定的聚合函数。

    MDX 与 SQL：

    MDX 用于导航多维数据库并定义对其所有对象（维度、层次结构、级别、成员和单元格）的查询，以获取（简单地）数据透视表的表示。

    MDX 使用许多与 SQL 关键字相同的关键字，例如SELECT, FROM, WHERE。不同之处在于 SQL 生成关系视图，而 MDX 生成数据的多维视图。

    两种语言的一般结构也存在差异：

    SQL 查询：SELECT column1, column2, ..., column FROM table
    MDX 查询：SELECT axis1 ON COLUMNS, axis2 ON ROWS FROM cube

    FROM指定数据源：
    在 SQL 中：一个或多个表
    在 MDX 中：一个多维数据集

    SELECT表示查询希望恢复的结果：

    在 SQL 中：

    • 二维（行和列）视图数据
    • 行具有由列定义的相同结构

    在 MDX 中：

    • 任意数量的维度来形成查询结果。
    • 术语轴用于避免与立方体尺寸混淆。
    • 行和列没有特殊含义，但必须定义每个轴：axe1 定义水平轴，轴 2 定义垂直轴。

    MDX 查询示例： 在此处输入图像描述

    度量：单价、数量、折扣、销售额、运费
    维度：时间
    层次结构：年 > 季度 > 月 > 成员：

    • 年份：2010、2011、2012、2013、2014

    • 季度：Q1、Q2、Q3、Q4

    • 月份：一月，二月，三月，...

    维度：客户
    层次结构：大陆 > 国家 > 州 > 城市与成员：

    • 城市：巴黎、里昂、柏林、科隆、马赛、南特……

    • 州：Loire atlantique、Bouches du Rhône、Bas Rhin、都灵……

    • 国家：奥地利，比利时，丹麦，法国，...

    • 大陆层面：欧洲、北美、南美、亚洲

    维度：产品
    层次结构：类别>子类别>具有成员的产品：

    • 类别：食品、饮料……
    • 食品类别：Baked_food …
    • …
    • 7

  3. 更新：这个例子更好：

    查询目标：获取 2010 年第一季度在加利福尼亚州销售的所有产品系列（按行）的销售额和单位（按列）数量

    MDX

    SELECT  {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
      {[Products].children} ON ROWS
FROM  [Sales]
WHERE ([Time].[2010].[Q1], [Customers].[USA].[CA])

    SQL

    SELECT SUM(unit_sales) unit_sales_sum, SUM(store_sales) store_sales_sum
FROM sales
  LEFT JOIN products ON sales.product_id = products.id
  LEFT JOIN product_classes ON products.product_class_id = product_classes.id
  LEFT JOIN time ON sales.time_id = time.id
  LEFT JOIN customers ON sales.customer_id = customers.id
WHERE time.the_year = 2010 AND time.quarter = 'Q1'
  AND customers.country = 'USA' AND customers.state_province = 'CA'
GROUP BY product_classes.product_family
ORDER BY product_classes.product_family

    来源：Modrian 的使用说明（翻译 MDX 查询以用于关系数据库）

    我找到了一个不错的例子，尽管 SQL 并没有那么复杂（与 SaasBase 而不是 MDX 相比）：

    在此处输入图像描述

    来源：大数据的实时“OLAP”（+用例）- bigdata.ro 2013

    • 1

相关问题