Ordering results of Association mode prediction by Suppport

Question

Hello,

Below is the query I use to predict associations and I want to order by Support. However the query seems to order by Adjusted Probability.

It seems that the default behaviour of Predict is to order by $probabilty or Adjusted Probabilty.

Can someone suggest how to overcome this(while retaining the node_id<>'' condition)

SELECT

 

FLATTENED

(

SELECT * FROM Predict([Recommendation],INCLUDE_STATISTICS,$Suppport,5,INCLUDE_NODE_ID)

 

WHERE $NODEID<>'' )

 

From [Recommendation_Model]

NATURAL

 

PREDICTION JOIN (SELECT ( Select 'xxxxxx' AS [Table Nm])

 

 

AS [Recommendation]) AS t

 

Please treat this urgent!

Thanks
Kirthika

Answer 1

Would greatly appreciate if someone can help answer this question.

-Kirthika

Answer 2

You can try using TopCount() function and selecting the column to order by. For more details, see the following:
http://msdn.microsoft.com/en-us/library/ms132149.aspx

Hope this helps
Shuvro

---

MSFT, SQL Server Data Mining

Answer 3

Hello Shuvro,

Thank you for your response. But I am not using TopCount as I do not want a fixed number of results. I need results ordered by $Support if they exists (if rules exists)

This is what Jamie explains in http://blogs.msdn.com/jamiemac/archive/2006/04/19/predict-based-on-rules-alone.aspx

further, what I do not understand is as per msdn Predict () is supposed to predict based on $Support or $Probability based on what is specified in the query. But that is not happeninig in the above query. Can you help explain the reason behind this ?

If this is a known issue, can you please suggest an alternative to select based on rules and still order by $Support.

(I am not sure how to order by $support)

Thanks

Kirthika

Answer 4

Hello,

It will be really helpful if someone on the forum can help reply to the above .

Thanks
Kirthika

Answer 5

Are you using SQL Server 2005 or 2008?

I tried on a couple of models and got the expected results, sorted correctly, by $SUPPORT, in descending order.


Could you please post the results returned by the following query?

SELECT FLATTENED Predict([Recommendation], INCLUDE_STATISTICS, $Support, 5, INCLUDE_NODE_ID)
From [Recommendation_Model]
NATURAL PREDICTION JOIN (SELECT ( Select 'xxxxxx' AS [Table Nm]) AS  Recommendation AS T

 

 

 

 

 

---

bogdan crivat [sql server data mining] / http://www.bogdancrivat.net/dm

Answer 6

Hello Bogdan,

 

Thank you for your response. I am using SQL server AS 08.

The query above gives me the following result. Note that ordering by support here says that 3106 is the support of the top item.

XXXXX   3106       0.399557637262555         0.815711212151828         801

YYYYY    15           0.00182149362477231     0.811337767743637         801

ZZZZZ    7              0.000910746812386157  0.8165614747249              801

WWW   2              0.000260213374967473  0.8165614747249              801

UUUU   1              0.000130106687483737  0.8165614747249              801

Now if I use the same query with an ‘n’ value of 30 I am getting different support level infact higher ones which did not appear in the result above. This makes me think that the query is NOT ordering by descending order of support. Am I missing something here ?

AAAA    7454       0.702706219099662         0.791194273501276         801

BBBB     5352       0.500780640124902         0.79057488043516            801

CCCC     4464       0.49687743950039            0.804583927418361         801

DDDD    4434       0.4463960447567              0.796570710182286         801

EEEEE    4385       0.481134530314858         0.803454066489495         801

FFFFF    3448       0.418293000260213         0.811263202473559         801

XXXXX   3106       0.399557637262555         0.815711212151828         801

HHHH    1387       0.103434816549571         0.771172642705494         801

IIIIIIII     1217       0.102654176424668         0.781859160343093         801

JJJJJ        433         0.0416341399947957       0.792820986466476         801

 

 

 

 Thanks
Kirthika

Answer 7

I am not able to repro the behavior you are describing It is also strange that all results have the same NODE_ID value.
Could you share the model?  EXPORT the model without data and send it by email to bogdanc AT microsoft DOT com?

---

bogdan crivat [sql server data mining] / http://www.bogdancrivat.net/dm

Answer 8

Hello Bogdan,


Thanks for looking into this. I will export and send the model to you.

Thanks
Kirthika