Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nephari
Starting Member
3 Posts |
 Posted - 2007-07-20 : 14:02:12
|
| Hello! I'm pretty new to t-sql, so I have a kind of basic question (maybe?).I have 2 tables: T1 and T2. T1 has an id, query_id, search_engine, position, and relevancy. There are multiple search engines per query and multiple positions per search engine. T2 has an id, category, search_engine, and score. There are multiple search engines per category and one score per search engine.I want to create a value for score in T2 based off of the relevancy and position columns in T1. I had a few ideas, but none of them worked, so I'm thinking that there must be some way to do this that I don't know about. Does anyone have any suggestions?Thank you! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-20 : 14:31:16
|
| Possible to post some sample data in both tables and the result you like to see? |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-20 : 14:36:03
|
| Post some sample data from each of these tables and expected output formatted like this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86556Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
nephari
Starting Member
3 Posts |
Posted - 2007-07-20 : 14:54:28
|
| It would be something like this (the spacing got a little messed up, but hopefully you can still understand it):Current Tables:T1:id query_id search_engine position relevancy-------- -------------- --------------- --------------- ---------69 2 name1 1 True70 2 name1 2 True72 2 name1 3 FalseT2:id category search_engine score------- --------------- --------------- -----36 Home name1 NULL37 Home name2 NULL38 Home name3 NULLT3:id keyword category------- --------------- ---------------2 homeword12 Home3 newsword1 NewsExpected Output:T2:id category search_engine score------- --------------- --------------- -----36 Home name1 .7237 Home name2 .4638 Home name3 .95(I didn't mention T3 before, but it's just a table of all the queries, which category they belong to, and a unique id) |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-20 : 15:02:19
|
| Please also explain this: >>>I want to create a value for score in T2 based off of the relevancy and position columns in T1.Also help us understand how you arrived at these numbers in column score:T2:id category search_engine score------- --------------- --------------- -----36 Home name1 .7237 Home name2 .4638 Home name3 .95Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
nephari
Starting Member
3 Posts |
Posted - 2007-07-20 : 15:27:58
|
| I just made those numbers up for the purpose of showing data, but the formula I want to use is:[#931; 10, i=1]Ni/10 * r/10 * c-the sum of i=1 to 10 of Ni where N is 1/i if the query in the i-th position is relevant and set to zero otherwise-divided by 10-multiplied by the total number of relevant documents (out of 10) per search engine per query divided and by 10-multiplied by a constant...if that makes sense. I'm not really sure how to show formulas in this format. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-20 : 16:58:11
|
| I am unable to (1) understand the formula + (2) apply the given data into it. Perhaps someone here will jump in too.. Meanwhile, can you explain with an example. Take the search_engine value of "name1" and explain how you arrived at .72? If the value of .72 is hypothetical, walk through the formula and show how the value should be calculated. Bulleted list is fine.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
|
|
Updating T2 with a calculated value from T1