65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 NTILE 和交叉引用值创建维度

bobleith
emptyStarIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

0/5 (0投票)

2009 年 8 月 4 日

CPOL

6分钟阅读

viewsIcon

12837

使用 NTILE 和交叉引用值创建维度

我承认我对 SQL Analysis Services (SSAS) 的深度了解不足。我阅读了不少关于其功能（包括数据挖掘）的内容，并玩过一些向导，但就是没时间深入研究端到端的场景。因此，我写这篇帖子时有一个重要的免责声明——您也许能用 Analysis Services 更轻松地完成帖子中的部分工作——我还不确定。希望您能原谅我的无知。

尽管 SQL Analysis Services 提供了很多功能，但似乎在 SQL 中预先“准备”数据（尤其是维度的来源）会获得更好的结果。因此，即使您正在大量利用 SSAS，这篇帖子也可能很有用。在我的场景中，由于数据集的规模、不同变量组合的乘法效应以及分析的简化，我会想减小与变量相关的值的范围。基本上，这意味着将值的范围汇总到不同的组中，然后分析这些分组变量值的相关性——因此，本文的标题是“NTILE”。您可以将其视为分配成绩而不是分数，这样您就可以识别出“A”、“B”等学生，而不会陷入分数细节的泥沼。一旦您按年级分组了学生，您就可以更容易地进行相关性分析——例如，“A”学生花在家庭作业上的时间与“C”学生相比如何，而不是分析家庭作业与得分 93/100 的学生与得分 74/100 的学生相比的相关性。

首先，简单介绍一下应用程序——我的需求包括能够关联股票的各种因素——例如每股收益 (EPS)、市盈增长比 (PEG)、所属板块、业绩周期等，以表明这些因素是否与特定时间购买的股票的盈利能力有关，以及何时应出售。

希望这能提供足够的背景信息，让其中一些内容有所意义。

所以，对我而言，一些假设性问题是：某些板块或行业在特定时期相对于其他股票表现更好吗？EPS 如何影响交易盈利能力？市值呢？这些组合有影响吗？例如，在一年中的某些时期，投资某些行业的市值较小的股票是否更赚钱？

本质上，能够获取所有这些因素，生成排列组合，然后分析一段时间内的股票交易模拟，以查看是否有任何组合能有效预测策略的表现，那就太好了。现在，如果我们只是考虑所有这些组合，排列的乘法效应将是无法管理的，而且会比“事实”数据还要多，因为许多值的精度足够高，可以生成数百甚至数千个不同的值。“排列”所有这些会产生很多有趣但无意义的数据。

NTILE 功能允许基于区间为数据集分配组。我想要 NTILE 的变量之一是每股收益 (EPS)。另一个是市值。还有一个可以是板块。最后是时期。通过 NTILE，我指的是分组到类别中，例如，每只股票的 EPS 或市值都将仅属于 5 个不同的分组或评级之一。您可以看到，通过将这些减少到分组，我们极大地简化了分析并减少了排列的数量，同时仍然拥有足够的粒度来区分这些分组。我们只需要找到一种方法将原始的“事实”数据值映射到它们的组（成绩）。一旦有了映射，我们就可以将分组的维度数据连接回事实数据。是的，我知道这听起来更像是一个 SSAS 场景，但请记住我已给出我的免责声明……我欢迎 BI 专家们就如何使用 SSAS 来处理这个场景的回复。

因此，以下是完成此操作的步骤

  1. 使用计算字段将事实数据四舍五入为更离散的值，特别是当原始值不精确时，例如浮点数。例如，如果我们只需要 5 个分组，我们可能不需要映射到小数点后 0.01 的精度。如果我们改为只向下舍入到 0.1 的值，我们的分组仍然应该相当准确。这减少了需要分组的事实数据中的离散值数量，并减小了映射表的大小。下面是一个包含计算四舍五入字段的 Equity Information 表示例
    CREATE TABLE [dbo].[EquityInfo](
    [TradingSymbol] [varchar](25) NOT NULL,
    [Description] [varchar](50) NULL,
    [IndustryId] [smallint] NULL,
    [SectorId] [smallint] NULL,
    [Shares] [bigint] NULL,
    [PERatio] [float] NULL,
    [EPS] [float] NULL,
    [DivYield] [float] NULL,
    [DivPerShare] [float] NULL,
    [PEG] [float] NULL,
    [PriceToSalesRatio] [float] NULL,
    [PriceToBookRatio] [float] NULL,
    [ExchangeName] [varchar](10) NULL,
    [EnabledFlag] [bit] NULL,
    [DateUpdated] [date] NULL,
    [DateCreated] [date] NULL,
    [SampleName] [varchar](50) NULL,
    [TotalBookValue]  AS (case when [PriceToBookRatio]>(0) AND [Shares]>(0) 
then [Shares]/[PriceToBookRatio]  end),
    [TotalSalesValue]  AS (case when [PriceToSalesRatio]>(0) AND [Shares]>(0) 
then [Shares]/[PriceToSalesRatio]  end),
    [PEG_Rounded]  AS (CONVERT([smallmoney],ROUND([PEG],1))) 
PERSISTED,
    [EPS_Rounded]  AS (CONVERT([smallmoney],ROUND([EPS],1))) 
PERSISTED,
    [PE_Rounded]  AS  (CONVERT([smallmoney],ROUND([PERatio],1))) 
PERSISTED,
    [PTB_Rounded]  AS (CONVERT([smallmoney],ROUND([PriceToBookRatio],1))) 
PERSISTED,
    [PTS_Rounded]  AS (CONVERT([smallmoney],ROUND([PriceToSalesRatio],1))) 
PERSISTED,
    [Shortable] [bit] NULL,
    [HistoryReloadRequired] [bit] NULL,
    [HistoryReloadedDateTime] [datetime] NULL,
 CONSTRAINT [PK_EquityInfo] PRIMARY KEY CLUSTERED 
(
    [TradingSymbol] ASC
))
  2. 创建一个视图，对每个感兴趣的维度进行分组。例如，对于 EPS，我们将有以下视图
    CREATE VIEW [Olap].[Vdim_EPS_Tiled]
AS
SELECT     NTILE(5) OVER (Order BY EPS_Rounded) as EPS_Tile, EPS_Rounded
FROM         dbo.EquityInfo
WHERE EPS_Rounded Is NOT NULL
  3. 从视图生成一个表，以将分组映射回源事实数据。该表可以通过 SELECT INTO 生成，然后在添加主键后通过 MERGE 进行维护。我们需要使用 Select DISTINCT，因为我们的第一个视图将包含重复的 EPS_Rounded 值。
    select distinct * into olap.tdim_EPS_Tiled from Olap.vdim_EPS_Tiled;

GO

ALTER TABLE Olap.tdim_EPS_Tiled ADD CONSTRAINT
    PK_tdim_EPS_Tiled PRIMARY KEY CLUSTERED 
    (EPS_Rounded) 

GO

CREATE PROCEDURE olap.Update_tdim_EPS_Tiled
AS BEGIN
  MERGE INTO olap.tdim_EPS_Tiled AS T
  USING olap.vdim_eps_Tiled AS S
    ON S.EPS_Rounded = T.EPS_Rounded
  WHEN MATCHED 
    THEN UPDATE
        SET EPS_Tile = S.EPS_Tile
  WHEN NOT MATCHED BY TARGET
    THEN INSERT
        (EPS_Tile, EPS_Rounded)
        VALUES
        (S.EPS_Tile, S.EPS_Rounded)
  WHEN NOT MATCHED BY SOURCE
    THEN DELETE; -- Might not want to do this if there is history involved
END

现在，每当我想要分析与模拟相关的股票绩效时，我都可以轻松地在 EPS_Rounded 上连接到 tdim_EPS_Tiled 表，以获得分组。然后，我可以对它们进行分组，计算模拟的平均盈利能力，并评估相关性程度。这在 SSAS 的多维数据集中也能很好地工作，以允许相关性和预测数据挖掘。我会在完成 SSAS 教程后立即尝试。

下图说明了包括自动化方面的流程

image

总结

首先，我们减少了要映射的值的数量，因此我们映射的是四舍五入后的表示，而不是映射整个宇宙。我们还将其存储为精确的数据类型（而不是浮点数）。这使得我们的交叉引用表更小。当我们想要从事实数据映射回时，这会引入一些复杂性，但这可以通过在事实数据上创建映射回源的计算字段来轻松解决。

接下来，我们创建分组数据的“Ntile”视图。现在我们可以从源数据连接回去，找到特定股票所属的组。这允许我们进行迭代模拟。通过迭代模拟，我的意思是将一次模拟的结果作为下一层模拟的输入。例如，如果我的模拟显示某个 EPS 组存在正相关，那么我可以创建另一个模拟，仅筛选该 EPS 组，以便进行深入研究并更好地探索其中的相关性。一篇关于在此链接上的迭代和非迭代模拟算法的好文章是 http://www.stat.columbia.edu/~gelman/research/published/itsim_interface.pdf。

您可能想知道为什么不直接使用索引视图——我试过了，但没能成功，当涉及聚合时，您可以索引的内容有限，而使用 NTILE 这样的函数显然是其中之一。

我今天就到这里。您可能认为这很棒，但我的所有“假设性”问题怎么办？我们如何查询我们的维度数据并链接回我们的事实数据以找到相关性。我发现了一些有趣的相关性，但我还有很多工作要做。我想我现在确实需要开始使用 SSAS 了，因为要查找相关性的查询非常复杂——是时候拿出手册并开始学习教程了……是时候开始玩数据挖掘向导了……

© . All rights reserved.