mysql - 在sql中构建2x2列联表

Question

我正在尝试构建一个 2x2 列联表，如下面的链接所述:

Ad hoc 2x2 contingency tables SQL Server 2008(试图理解代码，但无法理解它)

进行循环以构造 C1,C1 C1,C2 C2,C1 C2,C2 中的对。 (笛卡尔积)

这些对作为 SQL 代码的参数给出。对于这个例子，我已经给了 sql 代码一对 --> C1,C1

当为不同的对构建它时，它们是正确的，如 C1,C2 C2,C1 (经过一些修改后解释如下)。当配对 C1,C1 或 C2,C2 时，它会构建错误的列联表。

例如(表名称为 alpha_occurrence):

id   concept_uri   document_uri

1       C1      D1

2       C2      D1

C1,C1 对的 2x2 列联表应从上面给定的表中给出:

       C1     not C1
    C1  1     0
not C1  0     -

而是给出(经过一些修改后):

       C1    not C1
    C1  0    1
not C1  1    -

注意，我已经为不是 C1 的值添加了 -，不是 C1。因为要计算需要使用其他方法。

此 SQL 代码用于检索值:

SELECT count(*) AS total FROM  
(SELECT document_uri,count(DISTINCT concept_uri) AS count_conc FROM mydb.alpha_occurence 
WHERE concept_uri IN ('C1','C1') 
GROUP BY document_uri 
HAVING count_conc >=2 ) 
AS amount_of_concept_co_occurence #value of both X and Y

UNION ALL 

SELECT count(*) AS total FROM 
(SELECT concept_uri,document_uri FROM mydb.alpha_occurence
WHERE concept_uri IN ('C1'))
AS only_concept_A #value of Only X not Y

UNION ALL 

SELECT count(*) AS total FROM
(SELECT concept_uri,document_uri FROM mydb.alpha_occurence 
WHERE concept_uri IN ('C1'))
AS only_concept_B #value of Not X only Y

检索到这些值后，会运行一个小脚本来对这些值进行更正。完成如下:

To get Only X and not Y            = only_concept_A - amount_of_concept_co_occurence 
To get Not X and Only Y            = Only_concept_B - amount_of_concept_co_occurence
To get the value of neither X or Y = total # of documents (which is not given here as the sample data only has data of which concept occurce in which document) - (amount_of_concept_co_occurence + Only X and not Y + Not X and Only Y) 

Answer 1

我使用了这个脚本

select concept_uri, document_uri, count(*) as count 
from table
group by concept_uri, document_uri

他们已经准备好了..