針對N計算P欄相同的機率,P欄重複均不列計,再依機率大小排序
機率=雙方相同筆數X2/總筆數
除用迴圈計算外,想請教更有效益的方法,目前作法如下:
$N = $_GET['Name'];
$res = mysql_query("SELECT P FROM TEST WHERE N = '".$N."' GROUP BY P", $acc) or die(mysql_error());
while( $row = mysql_fetch_array($res) )
{
$NP .= $row[0].',';
}
$res1 = mysql_query("SELECT N, COUNT(DISTINCT P) FROM TEST WHERE P IN (".substr($NP, 0, -1).") GROUP BY N", $acc) or die(mysql_error());
while( $row1 = mysql_fetch_array($res1) )
{
if( $row1[0] == $N ) { $NA = $row1[1]; continue; }
$NC[$row1[0]]['S'] = $row1[1];
$NN .= '\''.$row1[0].'\',';
}
$res2 = mysql_query("SELECT N, COUNT(DISTINCT P) FROM TEST WHERE N IN (".substr($NN, 0, -1).") GROUP BY N", $acc) or die(mysql_error());
while( $row2 = mysql_fetch_array($res2) )
{
$NC[$row2[0]]['A'] = $row2[1];
$NC[$row2[0]]['R'] = $NC[$row2[0]]['S']*2/($NA+$row2[1]);
}
usort($NC, function($a, $b)
{
if( $a['R'] == $b['R'] ) return 0;
return ( $a['R'] > $b['R'] ) ? -1 : 1;
});
我是用一次SQL查詢直接抓出你要的資料
(下面SQL的語法可能還有更好的寫法)
查詢出來的內容是
N | REP | TOTAL | K |
---|---|---|---|
原先的 N | 重複次數 | N的不重複總數 | 是否為被查詢的N |
/**
* 從資料庫取得資料
*
* @param resource pdo 連結到資料庫的pdo物件
* @param string target 要查詢的目標
* @return array|false 查詢結果的關聯陣列或失敗
*/
function getData($pdo, $target)
{
$sql=<<<SQLSTATMENT
SELECT T3.N, REP, COUNT(T3.N) AS TOTAL, IF(T3.N=:target,1,0) AS K
FROM (SELECT DISTINCT N,P FROM TEST) AS T3
INNER JOIN (
SELECT N,COUNT(DISTINCT P) AS REP
FROM TEST AS T1
WHERE EXISTS (
SELECT 1 FROM TEST
WHERE P=T1.P AND N=:target
) GROUP BY N
) AS T2
ON T3.N=T2.N
GROUP BY T3.N
ORDER BY K DESC,T3.N
SQLSTATMENT;
$stat=$pdo->prepare($sql);
$stat->bindParam(':target', $target, PDO::PARAM_STR);
if($stat->execute() === false || $stat->rowCount() === 0)
return false;
return $stat->fetchAll(PDO::FETCH_ASSOC);
}
//以下為測試程式碼
try {
$pdo=new PDO('mysql:host=localhost;dbname=test6','test6','test6');
} catch(PDOException $e) {
exit('error:'.$e);
}
foreach(['A','B','C','D','E'] as $v){
echo "<h3>查詢 $v 的結果</h3>";
$result=getData($pdo, $v);
if($result===false){
echo '<p>無</p>';
continue;
}
foreach($result as $i=>$row) {
if($i==0) {
$baseTotal=(int)$row['TOTAL'];
continue;
}
echo $row['N'].' 的機率為 '.((int)$row['REP']*2).'/'.((int)$row['TOTAL']+$baseTotal).'<br>';
}
}
另外提醒:
"SELECT P FROM TEST WHERE N = '".$N."' GROUP BY P"
這種寫法,而那個 $N
是來自於 $_GET
,這很容易有 SQL INJECTION 的風險,可以改用 PDO 或是 mysqli 都有 prepaer 跟 bindParam 這類的方法。