sql - SQL(Teradata):删除由GroupBy/CASE语句引起的NULL

我在结果中显示NULL时遇到问题。这是因为我如何将Group By＆CASE语句与“ItemDamagedStatus”一起使用。一种解决方案可能是分解这些CASE语句项，然后对同一张表进行JOIN操作。但是，当我这样做时，一些数据丢失了。

下面的查询实际上是给我正确的数字。我只希望它基于以下内容汇总成一行:产品/市场/组1。

有什么想法吗？有什么问题吗

SELECT   t1.Product
        , t1.Market
        , t1.Group1
        , COUNT(DISTINCT t1.ItemID ||'-'||t1.Date1) AS StoredMth
        , CASE WHEN t1.ItemDamagedStatus = 'C' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS CompleteDmgMth
        , CASE WHEN t1.ItemDamagedStatus = 'P' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS PartialDmgMth
        , CASE WHEN t1.ItemDamagedStatus = 'N' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS NotDmgMth
        , CASE WHEN t1.ItemRepairStatus = 'Y' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS RepairMth
FROM  MainDatabase.Items t1
WHERE  t1.Date1 BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY      t1.Product
            , t1.Market
            , t1.Group1
            , t1.ItemDamagedStatus
            , t1.ItemRepairStatus

结果我得到:

Product Market Group1 StoredMth CompleteDmgMth PartialDmgMth NotDmgMth  RepairMth
Car     North  Y      950       50             NULL          NULL       75
Car     North  Y      NULL      NULL           100           NULL       NULL
Car     North  Y      NULL      NULL           NULL          800        NULL
Car     North  N      165       NULL           75            NULL       10
Car     North  N      NULL      NULL           NULL          90         NULL
Car     South  Y      1400      500            NULL          NULL       800
Car     South  Y      NULL      NULL           NULL          900        NULL

我想要的结果:

Product Market Group1 StoredMth CompleteDmgMth PartialDmgMth NotDmgMth  RepairMth
Car     North  Y      950       50             100           800        75
Car     North  N      165       NULL           75            90         10
Car     South  Y      1400      500            NULL          900        800

(只是进行后续操作，以防万一，否则任何人都会被抛弃，或者他们尝试合并一些值...是的:CompleteDmgMth + PartialDmgMth + NotDmgMth = StoredMth，但是在我们的数据中并非始终如此，因此我们使用两种不同的方法。)

抱歉，如果有些东西看起来很奇怪或构架不正确，这是我第一次在这里发布。

最佳答案

使用聚合，但不要在所有列上使用。您可以将CASE表达式嵌套在COUNT(DISTINCT)中:

SELECT t1.Product, t1.Market, t1.Group1,
        COUNT(DISTINCT t1.ItemID || '-' || t1.Date1) AS StoredMth
        COUNT(DISTINCT CASE WHEN t1.ItemDamagedStatus = 'C' THEN t1.ItemID || '' || t1.Date1) END) AS CompleteDmgMth
        COUNT(DISTINCT CASE WHEN t1.ItemDamagedStatus = 'P' THEN t1.ItemID || '' || t1.Date1 END) AS PartialDmgMth
        COUNT(DISTINCT CASE WHEN t1.ItemDamagedStatus = 'N' THEN t1.ItemID || '-' || t1.Date1 END) AS NotDmgMth
        COUNT(DISTINCT CASE WHEN t1.ItemRepairStatus = 'Y' THEN t1.ItemID || '-' || t1.Date1 END) AS RepairMth
FROM  MainDatabase.Items t1
WHERE  t1.Date1 BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY t1.Product, t1.Market, t1.Group1;

关于sql - SQL(Teradata):删除由GroupBy/CASE语句引起的NULL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48547182/