sourceindex | source | target | prob
1 | apple | dog | 2/(2+2+1)
2 | dog | cat | 2/(2+2+1)
3 | door | cat | 1/(2+2+1)
2 | dog | apple| 2/(2+2+1)
4 | cat | dog | 2/(2+2+1) -----step 1 table1
sourceindex | source | target | prob
1 | apple | dog | 2/(2+2+1)
2 | dog | cat | 4/(2+2+1)
3 | door | cat | 1/(2+2+1)
4 | cat | dog | 2/(2+2+1) -----step 2 sum the prob group by sourceindex
and output to new table
---------------------------------------------
sourceindex | source | target | prob | result
1 | apple | dog | 2/(2+2+1) | (2/(2+2+1))*(2/(2+2+1))
2 | dog | cat | 2/(2+2+1) | (2/(2+2+1))*(4/(2+2+1))
3 | door | cat | 1/(2+2+1) | (1/(2+2+1))*(1/(2+2+1))
2 | dog | apple| 2/(2+2+1) | (2/(2+2+1))*(4/(2+2+1))
4 | cat | dog | 2/(2+2+1) | (2/(2+2+1))*(2/(2+2+1))
step3 返回与表2相乘的结果值 prob 列依赖于相同的源索引并插入到 table1
这是预期的结果吗?
| SOURCEINDEX | SOURCE | TARGET | T1_PROB | STEP3 |
|-------------|--------|--------|---------|-------|
| 1 | apple | dog | 0.400 | 0.160 |
| 2 | dog | cat | 0.400 | 0.160 |
| 3 | door | cat | 0.200 | 0.040 |
| 2 | dog | apple | 0.400 | 0.160 |
| 4 | cat | dog | 0.400 | 0.160 |
这是基于此查询:
select
t1.sourceindex
, t1.source
, t1.target
, format(t1.prob,3) as t1_prob
, format(t1.prob*t1.prob,3) as step3
from table1 t1
inner join table2 t2 on t1.sourceindex = t2.sourceindex
;
和这个假设的数据,而不是我从字符串减少概率到指示计算的数字结果。
CREATE TABLE Table1
(`sourceindex` int, `source` varchar(5), `target` varchar(5), `prob` decimal(12,3))
;
INSERT INTO Table1
(`sourceindex`, `source`, `target`, `prob`)
VALUES
(1, 'apple', 'dog', 0.400),
(2, 'dog', 'cat', 0.400),
(3, 'door', 'cat', 0.200),
(2, 'dog', 'apple', 0.400),
(4, 'cat', 'dog', 0.400)
;
CREATE TABLE Table2
(`sourceindex` int, `source` varchar(5), `target` varchar(3), `prob` decimal(12,3))
;
INSERT INTO Table2
(`sourceindex`, `source`, `target`, `prob`)
VALUES
(1, 'apple', 'dog', 0.400),
(2, 'dog', 'cat', 0.800),
(3, 'door', 'cat', 0.200),
(4, 'cat', 'dog', 0.400)
;
请参阅:http://sqlfiddle.com/#!9/80d14/1 您可以尝试或修改它的位置。
如果我正确理解了这个问题,则需要执行左连接以考虑来自table_1的所有记录,并将新字段的值计算为两个探测器的乘积:
SELECT t1.sourceindex, (t1.prob * t2.prob) as result from table1 t1 left join table2 t2 on t1.sourceindex=t2.sourceindex