cluster5probabilitydistflattened.dmx

来自「< SQL Server2005程序设计>」· DMX 代码 · 共 38 行

--Cluster assignment and probability distribution for 250 customers
--Using FLATTENED keyword
SELECT   FLATTENED TOP 1250 t.FirstName, t.LastName, t.Profit
         , ClusterProbability(), PredictHistogram(Cluster())
FROM     CustomerProfitCategory_CL5
PREDICTION JOIN
	SHAPE {OpenQuery([Adventure Works DW],
           'SELECT     c.FirstName, c.LastName, tc.*
            FROM       vCustomerProfitability tc
            INNER JOIN dimCustomer c ON
             tc.CustomerKey = c.CustomerKey
            ORDER BY   tc.CustomerKey')
          }
	APPEND({OPENQUERY([Adventure Works DW],
            'SELECT   CustomerKey, ProductCategory
             FROM     vCustomerPurchases
             ORDER BY CustomerKey')
            }
            RELATE CustomerKey To CustomerKey
           )
           AS PurchaseCategory 
    AS t
    ON t.Age = CustomerProfitCategory_CL5.Age AND
       t.CommuteDistance = CustomerProfitCategory_CL5.[Commute Distance] AND
       t.EnglishEducation = CustomerProfitCategory_CL5.[English Education] AND
       t.Gender = CustomerProfitCategory_CL5.Gender AND
       t.HasKidsAtHome = CustomerProfitCategory_CL5.[Has Kids At Home] AND
       t.IncomeGroup = CustomerProfitCategory_CL5.[Income Group] AND
       t.IsCarOwner = CustomerProfitCategory_CL5.[Is Car Owner] AND
       t.IsHomeOwner = CustomerProfitCategory_CL5.[Is Home Owner] AND
       t.IsNewCustomer = CustomerProfitCategory_CL5.[Is New Customer] AND
       t.MaritalStatus = CustomerProfitCategory_CL5.[Marital Status] AND
       t.NumProdGroup = CustomerProfitCategory_CL5.[Num Prod Group] AND
       t.RecencyGroup = CustomerProfitCategory_CL5.[Recency Group] AND 
       t.Region = CustomerProfitCategory_CL5.Region AND
       t.PurchaseCategory.ProductCategory = CustomerProfitCategory_CL5.[v Customer Purchases].[Product Category]