i want show other products when somebody choose a product,
ect:
product1 product2
A B, C ,D
B C,D,E
C F, G
.......
i create model:
I create a mining model:
CREATE MINING MODEL [bh'S]
([Cusid] LONGKEY,
[Products] TABLE PREDICT ([Productid] LONGKEY
)) USING Microsoft_Decision_Trees
INSERT INTO [bh'S]
(SKIP, [Products] (SKIP, [Productid]))
SHAPE
{OPENROWSET(…, 'SELECT DISTINCT "dbo"."customers"."cusid" AS "Cusid" FROM "dbo"."customers" ORDER BY "dbo"."customers"."cusid"')}
APPEND
( {OPENROWSET(…, 'SELECT DISTINCT "dbo"."orderdata"."cusid" AS "UI_GENERATED_PARENT_KEY_COLUMN", "dbo"."products"."productid" AS "Productid" FROM "dbo"."orderdata", "dbo"."orderdetails", "dbo"."products" WHERE (("dbo"."orderdata"."orderid"="dbo"."orderdetails"."orderid") AND ("dbo"."orderdetails"."productid"="dbo"."products"."productid")) ORDER BY "dbo"."orderdata"."cusid"')}
RELATE [Cusid] TO [UI_GENERATED_PARENT_KEY_COLUMN]) AS [Products]
I use DTS to select data:
SELECT FLATTENED
[T1].[Cusid] AS PRODUCTID ,topcount(PREDICT ( [bh].[Products],INCLUDE_STATISTICS),$probability,5) AS ReProductid
FROM
[bh]
PREDICTION JOIN
SHAPE
{
OPENROWSET
(
…,'SELECT "productid" AS "Cusid" FROM "products" ORDER BY "productid"'
)
}
APPEND
(
{
OPENROWSET
(
…,'SELECT "productid" AS "Cusid_1", "productid" AS "Productid" FROM "products" ORDER BY "productid"'
)
}
RELATE [Cusid] TO [Cusid_1]
)
AS [Products]
AS [T1]
ON
[bh].[Cusid] = [T1].[Cusid] AND
[bh].[Products].[Productid] = [T1].[Products].[Productid]
My result
productid1 productid2
1 2372
1 5422
1 1223
...
2 2372
2 5422
2 1223
.....
3 2372
3 5422
3 1223
...
how can i do ?
i can't write predicts(products,5) or predicts([model name].[products],5) . when i run -> error
If you are useing SQL Server 2000, you cannot use the syntax Predict(Products, 5) as that syntax was introduced in SQL Server 2005.
Your query is correct - I would only say that you don't need to bind "cusid" in the prediction query - you don't really need to alias it to "cusid" either.
Is there something else needed? If you want product names rather than ID's, simply build the models using names
|||
i don't need name products . but productid to recomend is loop and almost it only recomend 5 products when you choose any products in market. ( same set in productid_2, when i use predict(....,5) ).
|||
Here's a few things to try:
1: Look at the trees generated by the model - do they show any patterns? It may be the case that the patterns aren't there to examine.
2: Use $AdjustedProbability instead of $Probability. This will favor products that are less likely to occur. For example, if Milk occurs in 99% of all baskets, it will be the #1 item regardless of what is actually in the basket. $AdjustedProbability takes this into account
3: Increase the values of MAXIMUM_INPUT_ATTRIBUTES and MAXIMUM_OUTPUT_ATTRIBUTES to include all the products in your catalog
|||
first:
i can see patterns. but it doesn't predict productid equal 1 to 150 and some other productid. so when i choose productid in (1,....,150) it show same set.
second:
how i increase the values of maximum _ input and output attributes ( i use wizar to create).
