时间戳

首页 > 解决方案 > 将 CASE 重写为 COALESCE

sql - 将 CASE 重写为 COALESCE

问题描述

我的查询中有部分代码有 CASE，我想将其重写为 COALESCE

这是代码的原始部分

CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END -
                            CASE WHEN LAGClientCharges IS NULL THEN 0
                            ELSE LAGClientCharges
                            END AS my_Delta,

    CASE WHEN (CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAGClientCharges IS NULL THEN 0
                            ELSE LAGClientCharges END) > 0 THEN 'Gain'
         WHEN CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAGClientCharges IS NULL THEN 0
                            ELSE LAGClientCharges END < 0 THEN 'Loss'
                            ELSE NULL END AS GainsLosses,
    CASE WHEN u.ClientCharges >=0 AND LAGClientCharges IS NULL AND t.MonthBilled <> '2015-01-01' THEN 'New' ELSE 'Existing' END AS ClientType,

    CASE WHEN u.ClientCharges IS NULL THEN 'InActive' ELSE 'Active' END AS ActiveStatus,

    CASE WHEN LAGClientCharges IS NOT NULL AND t.MonthBilled <> '2015-01-01' THEN 1 ELSE NULL END AS ActiveLastMonth,

    CASE WHEN u.ClientCharges IS NULL AND (LAGClientCharges <> 0 AND LAGClientCharges IS NOT NULL) AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS OneMonthChurn,

    u2.FeatureKeyCount,

    CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter

这是部分，我重写了一些 CASE 语句

    COALESCE(u.ClientCharges, 0) - COALESCE(LAGClientCharges, 0) AS my_Delta,

    CASE WHEN (COALESCE(u.ClientCharges , 0) - COALESCE(LAGClientCharges , 0)) > 0 THEN 'Gain'
         WHEN COALESCE (u.ClientCharges , 0) - COALESCE(LAGClientCharges , 0) < 0 THEN 'Loss'
                            ELSE NULL END AS GainsLosses,
    CASE WHEN u.ClientCharges >=0 AND LAGClientCharges IS NULL AND t.MonthBilled <> '2015-01-01' THEN 'New' ELSE 'Existing' END AS ClientType,

   COALESCE (u.ClientCharges, 'InActive') AS ActiveStatus,

    CASE WHEN LAGClientCharges IS NOT NULL AND t.MonthBilled <> '2015-01-01' THEN 1 ELSE NULL END AS ActiveLastMonth,

    CASE WHEN u.ClientCharges IS NULL AND (LAGClientCharges <> 0 AND LAGClientCharges IS NOT NULL) AND t.MonthBilled <> '2015-01-01' THEN 1 ELSE NULL END AS OneMonthChurn,

    u2.FeatureKeyCount,

    CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter

但是我如何用 AND 重写行，或者它有一些更好的变体，如何重写它？

标签： sqlamazon-redshift

解决方案

问题似乎是您正在努力为涉及的案例重新编写逻辑AND

CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter

对于读者来说是否更清楚尚有争议，但您要求遵循以下逻辑：

CASE WHEN coalesce(u.ClientCharges,0) > 0 THEN 1 ELSE 0 END AS OrgCounter

这在 中的子句AND使用相同字段时有效，但对于要检查多个不同字段的子句，您需要保持原样。

在这段代码中对数据进行了大量解释——实际上业务逻辑被嵌入到 SQL 中——您可能希望重新考虑这一点。

推荐阅读