如何使用sql代码计算患病率 [英] how to calculate prevalence using sql code

问题描述

我正在尝试计算sql中的流行率。
被困在编写代码中。
我想制作自动化代码。

我已经检查出我有1453477的样本量，使用计数的患病人数是851451。
计算患病率的公式是患病人数/样本量的编号。

 从疾病
中选择（COUNT（condition_id）/ COUNT（person_id））作为患病率
，其中condition_id = 12345; 
  

在运行上述代码时，我得到1作为输出，我想得到0.5858。 / p>

有人可以帮帮我吗？
谢谢！

解决方案

我很确定您想要的逻辑是这样的：

 选择avg（（condition_id = 12345）:: int）
来自疾病； 
  

您的版本没有样本量，因为您要过滤掉没有条件的人。

如果数据中有重复的人，则情况会有些复杂。一种方法是：

  select（count（distinct person_id）过滤器（其中condition_id = 12345）:: numeric / 
计数（与疾病不同的person_id 
）
; 
  

I am trying to calculate prevalence in sql. kind of stuck in writing the code. I want to make automative code.

I have check that I have 1453477 of sample size and number of people who has disease is 851451 using count. The formula of calculating prevalence is no.of person who has disease/no.sample size.

select (COUNT(condition_id)/COUNT(person_id)) as prevalence
from disease
where condition_id=12345;

when I run above code, I get 1 as a output where I am suppose to get 0.5858.

Can some one please help me out? Thanks!

解决方案

I am pretty sure that the logic that you want is something like this:

select avg( (condition_id = 12345)::int )
from disease;

Your version doesn't have the sample size, because you are filtering out people without the condition.

If you have duplicate people in the data, then this is a little more complicated. One method is:

select (count(distinct person_id) filter (where condition_id = 12345)::numeric /
        count(distinct person_id
       )
from disease;

这篇关于如何使用sql代码计算患病率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！