sql - 进行内部查询以删除重复的代码

Question

我有 redshift 脚本，其中我有重复的代码

这是脚本

    SELECT  
       CASE
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN '60 - 90 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >30 THEN '30 - 60 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) IS NULL THEN '90 + Days'
              ELSE '0 - 30 Days'
       END AS OpenedAging,
       be.ClientId,
       cc.FullName AS ClientName,
       SUM(CASE
              WHEN (bes.UseAgreedView) = 1 THEN a.ChargeTotalAgreed
              ELSE a.ChargeTotal
       END - a.PaymentAdjustment - a.PaymentAmount) AS Owed
FROM    public.billing_entries be
LEFT JOIN
       (
       SELECT  bed.BillingEntryId,
               bed.PaymentCount,
               bed.PaymentRegular,
               bed.Copay,
               bed.ChargeTotal,
               bed.ChargeTotalAgreed,
               bed.PaymentAdjustment,
               bed.PaymentRegular + bed.PaymentCopay - bed.PaymentAdjustment AS PaymentAmount
       FROM    public.billing_entry_dimensions bed
       ) a
ON      a.BillingEntryId = be.Id
LEFT JOIN
       public.billing_settings bes
ON     bes.OrganizationId = be.OrganizationId
LEFT JOIN
       public.contact_insurances cic
ON     cic.Id = be.insuranceId
LEFT JOIN
       public.insurance ic
ON     ic.Id = cic.InsuranceCompanyId
LEFT JOIN
       public.contact_addresses ca
ON     ca.Id = be.ClientLocationId

LEFT JOIN       
       public.contacts cc
ON     cc.Id = be.ClientId

WHERE   be.OrganizationId = 65277
       AND be.IsVoid IS NULL
       AND be.IsDeleted IS NULL

GROUP BY        
       CASE
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days'
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 Days'
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN '60 - 90 Days'
       WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >30 THEN '30 - 60 Days'
              WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) IS NULL THEN '90 + Days'
              ELSE '0 - 30 Days'
       END,
       be.ClientId,
       cc.FullName

如您所见，我在 SELECT 和 GROUP BY 中复制了此代码

CASE WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days' WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 天 WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN 60 - 90 天 WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) > 30 THEN '30 - 60 Days' WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) 为 NULL THEN '90 + Days' ELSE '0 - 30 Days' END

如何在 SELECT 和 GROUP BY 中正确地进行内部查询和别名？

标签： sqlamazon-redshift

Answer 1

不幸的是，子句中定义的别名对同一级别SELECT的子句不可见。GROUP BY想到的唯一选择是将当前查询包装在 CTE 中，然后使用聚合对其进行子查询，在CASE表达式中重用别名：

WITH cte AS (
    SELECT  
        CASE
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >120 THEN '120 + Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >90 THEN '90 - 120 Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >60 THEN '60 - 90 Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) >30 THEN '30 - 60 Days'
            WHEN DATEDIFF(Day,CONVERT(DATE,be.timeWorkedFrom),GETDATE()) IS NULL THEN '90 + Days'
            ELSE '0 - 30 Days'
        END AS OpenedAging,
        be.ClientId,
        cc.FullName AS ClientName,
        bes.UseAgreedView,
        a.ChargeTotalAgreed,
        a.ChargeTotal,
        a.PaymentAdjustment,
        a.PaymentAmount
    FROM public.billing_entries be
    LEFT JOIN
    (
        SELECT
            bed.BillingEntryId,
            bed.PaymentCount,
            bed.PaymentRegular,
            bed.Copay,
            bed.ChargeTotal,
            bed.ChargeTotalAgreed,
            bed.PaymentAdjustment,
            bed.PaymentRegular + bed.PaymentCopay - bed.PaymentAdjustment AS PaymentAmount
        FROM public.billing_entry_dimensions bed
    ) a
        ON a.BillingEntryId = be.Id
    LEFT JOIN public.billing_settings bes
        ON bes.OrganizationId = be.OrganizationId
    LEFT JOIN public.contact_insurances cic
        ON cic.Id = be.insuranceId
    LEFT JOIN public.insurance ic
        ON ic.Id = cic.InsuranceCompanyId
    LEFT JOIN public.contact_addresses ca
        ON ca.Id = be.ClientLocationId
    LEFT JOIN public.contacts cc
        ON cc.Id = be.ClientId
    WHERE
        be.OrganizationId = 65277 AND
        be.IsVoid IS NULL         AND
        be.IsDeleted IS NULL
)

这是使用上述 CTE 的实际查询：

SELECT  
    OpenedAging,
    ClientId,
    ClientName,
    SUM(CASE WHEN UseAgreedView = 1
             THEN ChargeTotalAgreed
             ELSE a.ChargeTotal END - PaymentAdjustment - PaymentAmount) AS Owed
FROM cte
GROUP BY
    OpenedAging,
    ClientId,
    ClientName;