临时 2x2 列联表 SQL Server 2008 [英] Ad hoc 2x2 contingency tables SQL Server 2008

问题描述

我正在研究在 SQL Server 2008 中创建列联表的方法.他们没有'不一定必须出现在 2x2 典型矩阵中.我只想知道是否有人能看到比我更好的解决方案.

I'm looking at ways of creating contingency tables in SQL Server 2008. They don't necessarily have to appear in the 2x2 typical matrix. I'd just like to know if anyone out there can see a better solution than mine.

为清楚起见，请参阅图片.为简单起见，红色字母是方块的名称.标签 X+ 表示 X 存在于该单元格中，反之亦然.

Please refer to the picture for clarity. The red letters are names of the squares for simplicity's sake. The label X+ means X is present in that cell and the opposite is true as well.

我将用它们代表的表格中的方框字母标记我的查询

I will label my queries with the letter of box in the table that they represent

A

       select count(*) from 
    (

    select distinct p.patientid
        from Patient as p
        inner join icdpatient as picd on picd.patientid = p.patientid
        and picd.admissiondate = p.admissiondate
        and picd.dischargedate = p.dischargedate
        inner join tblicd as t on t.icd_id = picd.icd_id
        where t.icdText like '%x%' 
    ) as t
    inner join 
    (
    select distinct p.patientid
        from Patient as p
        inner join icdpatient as picd on picd.patientid = p.patientid
        and picd.admissiondate = p.admissiondate
        and picd.dischargedate = p.dischargedate
        inner join tblicd as t on t.icd_id = picd.icd_id
        where t.icdText like '%y%'
    ) as s on s.patientid=t.patientid

<小时>

B
select count(*) from 
(

select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%x%' 
) as t
left join 
(
select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%y%'
) as s on s.patientid=t.patientid
where s.patientid is null

<小时>

C
select * from 
(

select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%x%' 
) as t
right join 
(
select distinct p.patientid
    from Patient as p
    inner join icdpatient as picd on picd.patientid = p.patientid
    and picd.admissiondate = p.admissiondate
    and picd.dischargedate = p.dischargedate
    inner join tblicd as t on t.icd_id = picd.icd_id
    where t.icdText like '%y%'
) as s on s.patientid=t.patientid
where t.patientid is null

D这个我有点不确定，但我想我会做类似的事情

D This one I'm a little iffy about but I think I'm going to do something like

declare @d int
set @d = (select count(distinct p.patientid) from Patient as p) - b -c

这旨在找到整个人口并仅用 X 和仅用 y 减去那些

This aims to find the entire population and subtracting those ONLY with X and ONLY with y

推荐答案

是的！有更简单的方法.假设您的加入不会产生重复的患者:

Yes! There are easier ways. Assuming your join produces no duplicate patients:

select (case when t.icdText like '%x%' then 'X' else 'NO-X' end) as X,
       (case when t.icdText like '%y%' then 'Y' else 'NO-Y' end) as Y,
       count(*) as cnt
from Patient p inner join
     icdpatient picd
     on picd.patientid = p.patientid and
        picd.admissiondate = p.admissiondate and
        picd.dischargedate = p.dischargedate inner join
     tblicd t
     on t.icd_id = picd.icd_id
group by  (case when t.icdText like '%x%' then 'X' else 'NO-X' end),
           (case when t.icdText like '%y%' then 'Y' else 'NO-Y' end)

否则将 count(*) 替换为:

Otherwise replace the count(*) with:

count(distinct patientid)

这应该会为您提供列联表所需的信息.

This should give you the information you need for the contingency table.

这篇关于临时 2x2 列联表 SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！