I have two tables like so
CREATE TABLE c (c_no INTEGER PRIMARY KEY, c_name TEXT);
CREATE TABLE t (t_no INTEGER PRIMARY KEY, t_name TEXT);
CREATE TABLE c_t (c_no INTEGER, t_no INTEGER);
Every row in `c` has one or more `t`. I want, in a single SQL, the following
source_c, target_c, similarity
where `similarity` is a measure of overlap of `t` between each pair of `c` and
is calculated as the number of `t` common between a given pair divided by the
total number of unique `t` in the pair. For example, given the following records
c t
--- ---
1 1
1 2
1 3
2 1
2 5
I want the result to be
source_c target_c similarity
-------- -------- ----------
1 2 0.2 (= 1 common `t` between the 2 `c` / by total 4 `t`)
Would appreciate any nudge toward a solution.
--
Puneet Kishor
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
