将单元格值与该组中的最大值进行比较，错误选择失败3707
将单元格值与该组中的最大值进行比较，错误选择失败3707
提问于 2018-12-24 22:17:25
我试图从视图中将单元格值与该组中的最大值进行比较，但它抛出了一个错误。
内部查询运行良好，它为我提供了患者的列表，他们的适应症以及2017年和3年索赔的计数。在外部查询中，我只需要保留patient，并且只保留2017年索赔>0和3年内最大索赔的适应症。
这里是代码抛出错误的地方
SELECT patient_id,
CASE
WHEN Count(patient_id) = 1 THEN diagnosis_grouping
WHEN claim_2017 > 0
AND claims_3yr = Max(claims_3yr) THEN diagnosis_grouping
ELSE NULL
END AS INDICATION
FROM (SELECT patient_id,
diagnosis_grouping,
Sum (CASE
WHEN LEFT(service_date, 4) = '2017' THEN 1
ELSE 0
END) AS CLAIM_2017,
Count(DISTINCT claim_id) AS CLAIMS_3YR,
Max(Cast(service_date AS DATE)) AS LATEST_CLAIM
FROM l01_dx_claims A
INNER JOIN diagnosis_code_imm B
ON A.diagnosis_code = B.diagnosis_code
GROUP BY 1,
2
HAVING Count (DISTINCT claim_id) > 1
AND Sum(CASE
WHEN Cast(LEFT(service_date, 4) AS INT) = 2017 THEN 1
ELSE 0
END) > 0
ORDER BY Cast(patient_id AS INT),
diagnosis_grouping)
GROUP BY patient_id 回答 3
Stack Overflow用户
回答已采纳
发布于 2018-12-25 00:29:55
考虑两个级别的聚合的多个CTE：
WITH agg1 AS
(SELECT patient_id,
diagnosis_grouping,
Sum (CASE
WHEN LEFT(service_date, 4) = '2017'
THEN 1
ELSE 0
END) AS CLAIM_2017,
Count(DISTINCT claim_id) AS CLAIMS_3YR,
Max(Cast(service_date AS DATE)) AS LATEST_CLAIM
FROM l01_dx_claims A
INNER JOIN diagnosis_code_imm B
ON A.diagnosis_code = B.diagnosis_code
GROUP BY 1,
2
HAVING Count (DISTINCT claim_id) > 1
AND Sum(CASE
WHEN Cast(LEFT(service_date, 4) AS INT) = 2017
THEN 1
ELSE 0
END) > 0
),
agg2 AS
(SELECT patient_id,
COUNT(patient_id) AS patient_count,
MAX(CLAIMS_3YR) AS max_claims_3yr
FROM agg1
GROUP BY patient_id)
SELECT a1.*
FROM agg1 a1
INNER JOIN agg2 a2
ON a1.patient_id = a2.patient_id
AND a1.CLAIMS_3YR = a2.max_claims_3yr
ORDER BY CAST(a1.patient_id AS INT),
a1.diagnosis_grouping Stack Overflow用户
发布于 2018-12-24 22:25:43
问题出现在这里：
WHEN Count(patient_id) = 1 THEN diagnosis_grouping
WHEN claim_2017 > 0
AND claims_3yr = Max(claims_3yr) THEN diagnosis_grouping
ELSE NULL claim_2017 (Patent_id)是聚合的，而as Count不是。
因此，group by语句对此查询的效率很低。您需要将claim_2017包含到您的group by中，或者找到其他解决方案。
Stack Overflow用户
发布于 2018-12-26 00:50:56
似乎你想比较不同级别的聚合，这就是我对你的逻辑的理解：
SELECT patient_id,
diagnosis_grouping,
Sum (CASE
WHEN Left(service_date, 4) = '2017' THEN 1
ELSE 0
END) AS CLAIM_2017,
Count(DISTINCT claim_id) AS CLAIMS_3YR,
Max(Cast(service_date AS DATE)) AS LATEST_CLAIM
FROM l01_dx_claims A
INNER JOIN diagnosis_code_imm B
ON A.diagnosis_code = B.diagnosis_code
GROUP BY 1,
2
HAVING -- Teradata allows using a Select alias in any place, simplified conditions
CLAIMS_3YR > 1
AND CLAIM_2017 > 0
QUALIFY -- using Windowed Aggregates to filter the correct result
Count(*) -- only a single diagnosis_grouping
Over (PARTITION BY patient_id) = 1
OR ( -- there was a claim in 2017
Max(claim_2017)
Over (PARTITION BY patient_id) > 0
AND -- diagnosis_grouping with the higthest count
Row_Number()
Over(PARTITION BY patient_id
ORDER BY claims_3yr DESC ) = 1
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53914561
复制相关文章
相似问题
腾讯云开发者
