将该组中的Cell值与Max进行比较，得到错误SELECT Failed 3707

Question

我试图从视图中将单元格值与该组中的最大值进行比较，但这给我带来了一个错误。

内部查询运行良好，它给了我一份患者名单、他们的适应症以及2017年和3年索赔的计数。在外部查询中，我需要只保留患者，只保留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 

Answer 1

考虑两个聚合级别的多个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 

Answer 2

问题发生在这里：

WHEN Count(patient_id) = 1 THEN diagnosis_grouping 
WHEN claim_2017 > 0 
AND claims_3yr = Max(claims_3yr) THEN diagnosis_grouping 
ELSE NULL 

Count(patent_id)是聚合，而as claim_2017不是。

因此，您的groupby语句对此查询效率低下。您需要通过将claim_2017包含到您的小组中，或者找到其他解决方案。

Answer 3

似乎你想比较不同级别的聚合，这就是我理解你的逻辑的方式：

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
)