如何获得价值与数量不同的人的加权总和

Question

我有一个没有给出预期结果的SQL查询：

CREATE TEMP TABLE tmp_cond_num as
          SELECT DISTINCT
          a.age
          ,a.gender
          ,b.concept_id
          ,count(distinct a.person_id) as numpat
          ,round(sum(a.sample_weight)) as w_numpat
          FROM tmp_pat a
          JOIN analytics.aia_tools.dh_t20_medical_histroy_w_hrc b ON a.person_id = b.person_id
          AND a.grp = 1
          GROUP BY
          a.age
          ,a.gender
          ,concept_id

我在这里计算独特的person_id。我需要计算那些独特的人_id的样本权重。现在，W_NUMPAT也正在总结重复的Person_ID。我如何获得独特的person_id的总和？

Answer 1

需要创建包含不同人结果的子查询。

CREATE TEMP TABLE tmp_cond_num as
          SELECT DISTINCT
          a.age
          ,a.gender
          ,b.concept_id
          ,count(a.person_id) as numpat
          ,sum(a.sample_weight) as w_numpat
          FROM tmp_pat a
          JOIN (SELECT distinct person_id, concept_id 
          FROM analytics.aia_tools.dh_t20_medical_histroy_w_hrc where first_inpatient_visit_date IS NOT NULL) b 
          ON a.person_id = b.person_id
          AND a.grp = 1
          GROUP BY
          a.age
          ,a.gender
          ,concept_id;