sql - Multiple objects appeared together - highest number of times -


i have query join conditions in normal oracle database log kind of table.

query_id, join_condition 1         schema1.table1.col1=schema2.table2.col1 1         schema1.table1.col2=schema2.table2.col2 1         schema1.table1.col1=schema2.table3.col1 2         schema1.table1.col1=schema2.table2.col1 2         schema1.table1.col1=schema2.table3.col1 2         schema1.table1.col1=schema3.table3.col1 ........... ......... .......

i have blown out excel sheet this

query_id, left_schema, left_table, left_column, right_schema, right_table, right_schema 1         schema1       table1       col1        schema2       table2       col1 1         schema1       table1       col2        schema2       table2       col2 ................ .............. .........

i wanted answer below question these.

all tables combinations used in same query - display in sorted order of number of times appeared together.

i have solved putting union of left table , right table in sorted order format table , grouped it. there randomly combinations of 2 or 3 or 4 or 5 table names based on each query ids number of tables.

now next question is, how find possible permutations , combinations of these tables in sorted order. example in query id 1, have table1 joined on table2, table3. query id falls in 3 tables combination bucket. (table1, table2) , (table1, table3) falls in 2 tables combination bucket. there may 4 tables together, 5 tables buckets. not able solve in sql or excel. have suggestions solving this. graph databases/r statistical methods can of purpose here along visualizations. sorry long question.

desired output should 4 sets of reports. 1)two tables in sorted order of number of occurrences 

two_tables_combination, number_of_queryids schema1.table1-schema2.table2               2 schema1.table1-schema2.table3               2 schema1.table1-schema3.table3               1

2)three tables in sorted order of number of occurrences 

three_tables_combination,                      number_of_occurances schema1.table1-schema2.table2-schema2.table3               2 schema1.table1-schema2.table3-schema3.table3               1

3)four tables in sorted order of number of occurrences together
4)any combination of tables highly used.

thanks.


Comments

Post a Comment

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more

rewrite - Trouble with Wordpress multiple custom querystrings -

i'm working on adding multiple querystring , doesn't seem work. i've found codes seems work others might i'm missing something. i want like: http://mydomain.com/board/?getyear=2013&getsometext2=sometext to http://mydomain.com/board/2013/sometext/ i added code below functions.php file. doesn't seem work should though. this works. echoing querystring gets both variable without problem. http://mydomain.com/board/2013/sometext/ but doesn't. tested querystring vis isset() seems nothing being set/get http://mydomain.com/board/2013/ function add_query_vars($vars) { $vars[] = "getyear"; $vars[] = "getsometext2"; return $vars; } add_filter('query_vars', 'add_query_vars'); function add_rewrite_rules($arules) { $anewrules = array('board/([^/]+)/([^/]+)/?$' => 'index.php?pagename=board&getyear=$matches[1]&getsometext2=$matches[2]'); $arules = $a...
Read more