问将单元格值与该组中的最大值进行比较，错误选择失败3707
EN

Stack Overflow用户

提问于 2018-12-24 14:17:25

回答 3查看 37关注 0票数 0

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

内部查询运行良好，它为我提供了患者的列表，他们的适应症以及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

sql

max

case

teradata

teradata-sql-assistant

回答 3

Stack Overflow用户

回答已采纳

发布于 2018-12-24 16: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

票数 0

Stack Overflow用户

发布于 2018-12-24 14: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中，或者找到其他解决方案。

票数 0

Stack Overflow用户

发布于 2018-12-25 16: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
      )