mostlikelycluster5.dmx

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

--250 customers most likely to belong in Cluster 5
SELECT   TOP 250 t.FirstName, t.LastName, t.Profit
         , ClusterProbability('Cluster 5') AS ProbCluster5
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]
ORDER BY ClusterProbability('Cluster 5') DESC