sql - 在 SQL Server 中确定百分比协议(protocol)

Question

我正在尝试弄清楚如何确定表中不同条目的值匹配的时间百分比。例如，使用以下简单表格:

User     Question   Answer
-----------------------------------
Tony      1         Yes
Bill      1         No
Jeff      2         Cat
Tony      2         Dog
Bill      2         Dog
Tony      3         No

我想写一个查询，告诉我 Tony 和 Bill 在他们都回答的两个问题上同意 50% 的时间。

Answer 1

'Tony'为User1参数，

'Bill' 是 User2 参数:

---

SELECT
    COUNT(CASE WHEN a.user = 'Tony' THEN 1 ELSE NULL END) AS U1_Total_Questions,
    COUNT(CASE WHEN a.user = 'Bill' THEN 1 ELSE NULL END) AS U2_Total_Questions,
    COUNT(CASE WHEN b.user IS NOT NULL THEN 1 ELSE NULL END) AS Total_Same_Questions,
    COUNT(CASE WHEN a.answer = b.answer THEN 1 ELSE NULL END) AS Total_Matched_Answers,
    (COUNT(CASE WHEN a.answer = b.answer THEN 1 ELSE NULL END) / COUNT(CASE WHEN b.user IS NOT NULL THEN 1 ELSE NULL END)) * 100 AS Percentage_Agreement
FROM 
    answertbl a
LEFT JOIN
    answertbl b ON a.question = b.question AND a.user = 'Tony' AND b.user = 'Bill'

-

U1_Total_Questions  U2_Total_Questions  Total_Same_Questions    Total_Matched_Answers   Percentage_Agreement
------------------------------------------------------------------------------------------------------------
3                   2                   2                       1                       50.0000