mysql - SQL- Selecting the most similar product -

-

alright, have relation stores 2 keys, product id , attribute id. want figure out product similar given product. (attributes numbers makes example more confusing have been changed letters simplify visual representation.)

prod_att 

product | attributes      1   |            1   |    b      1   |    c      2   |         2   |    b      2   |    d      3   |         3   |    e      4   |

initially seems simple, select attributes product has , count number of attributes per product shared. result of compared number of attributes product has , can see how similar 2 products are. works products large number of attributes relative compared products, issues arise when products have few attributes. example product 3 have tie every other product (as common).

select product, count(attributes)   prod_att   attributes in   (select attributes   prod_att   product = 1)   group product ;

any suggestions on how fix or improvements current query?
thanks!

*edit: product 4 return count() =1 products. show product 3 more similar has fewer differing attributes.

try this

select    a_product_id,    coalesce( b_product_id, 'no_matchs_found' ) closest_product_match (   select      *,       @row_num := if(@prev_value=a_product_id,@row_num+1,1) row_num,     @prev_value := a_product_id        (select @prev_value := 0) r     join (         select           a.product_id a_product_id,          b.product_id b_product_id,          count( distinct b.attributes ),          count( distinct b2.attributes ) total_products                   products           left join products b on ( a.attributes = b.attributes , a.product_id <> b.product_id )           left join products b2 on ( b2.product_id = b.product_id )        /*where */          /*  a.product_id = 3 */         group          a.product_id,          b.product_id         order            1, 3 desc, 4   ) t ) t2     row_num = 1

the above query gets closest matches products, can include product_id in innermost query, results particular product_id, have used left join if product has no matches, displayed

sqlfiddle

hope helps


Comments

Post a Comment

Popular posts from this blog

java - Jmockit String final length method mocking Issue -

-
using jmockit 1.2 jar, trying mock string's length method getting unexpected invocation exception: failed: test java.lang.illegalstateexception: missing invocation mocked type @ point; please make sure such invocations appear after declaration of suitable mock field or parameter @ stringdemo.testa$1.<init>(testa.java:17) @ stringdemo.testa.test(testa.java:13) i using testng: @test public void test() throws exception { new expectations() { @mocked("length") string astring; { astring.length(); result = 2; } }; system.out.println(a.showa("test")); } } actual class a: public class { public static int showa(string str){ int a= str.length(); return a; } } this wrong way of recording expected results. shouldn't mock , record string's length() method, record showa() instead. here solution @suppresswarnings("unused...
Read more

What is the difference between data design and data model(ERD) -

-
i have google question , find answer in day. know, data design many many table have list column,and mark pk , fk. why er-diagram have find this? data design process of designing database. main output of data design detailed logical data model of database. some people data design includes of needed logical , physical design choices , physical storage parameters needed generate design in data definition language. technically, database analysis, database analysts (dbas) trained do. while person can both data design , database analysis, these 2 different tasks. data design models data. database analysis takes model , applies 1 or more database engines (relational, hierarchical, nosql). a logical data model 1 of main outputs of data design. data model represented entity relationship diagram, or er diagram.
Read more

ios - Can NSManagedObject conform to NSCoding -

-
i need transfer single object across device. right converting nsmanagedobject dictionary , archiving , sending nsdata. upon receiving unarchiving it. transfer nsmanagedobject archiving , unarchiving instead of creating intermediate data object. @interface test : nsmanagedobject<nscoding> @property (nonatomic, retain) nsstring * title; @end @implementation test @dynamic title; - (id)initwithcoder:(nscoder *)coder { self = [super init]; if (self) { self.title = [coder decodeobjectforkey:@"title"]; //<crash } return self; } - (void)encodewithcoder:(nscoder *)coder { [coder encodeobject:self.title forkey:@"title"]; } @end nsdata *archivedobjects = [nskeyedarchiver archiveddatawithrootobject:testobj]; nsdata *objectsdata = archivedobjects; if ([objectsdata length] > 0) { nsarray *objects = [nskeyedunarchiver unarchiveobjectwithdata:objectsdata]; } the problem above code is. crashes @ self.title in initwithcoder sa...
Read more