db2 带有Case语句的Group By不计算零

sg3maiej 于 2022-11-07 发布在 DB2

关注(0)|答案(2)|浏览(213)

我有以下SQL：

WITH MYFILTER AS (
SELECT
CASE WHEN STATE IN ('A', 'B') then
case
   when (NUM/DENOM - 1.0) < -0.3 then 'Less than -30%'
           when (NUM/DENOM - 1.0) >= -0.3
           and (NUM/DENOM - 1.0) < -0.2  then '-30% to -20%'
           when (NUM/DENOM - 1.0) >= -0.2
           and (NUM/DENOM - 1.0) < -0.1  then '-20% to -10%'
           when (NUM/DENOM - 1.0) >= -0.1
           and (NUM/DENOM - 1.0) < 0.0   then '-10% to 0%'
           when (NUM/DENOM - 1.0) >= 0.0
           and (NUM/DENOM - 1.0) < 0.1    then '0% to 10%'
           when (NUM/DENOM - 1.0) >= 0.1
           and (NUM/DENOM - 1.0) < 0.2    then '10% to 20%'
           when (NUM/DENOM - 1.0) >= 0.2
           and (NUM/DENOM - 1.0) < 0.3    then '20% to 30%'
           when (NUM/DENOM - 1.0) >= 0.3  THEN 'At least 30%'
           end
ELSE case
   when (NUM/DENOM < -0.3 then 'Less than -30%'
           when (NUM/DENOM >= -0.3
           and (NUM/DENOM < -0.2  then '-30% to -20%'
           when (NUM/DENOM >= -0.2
           and (NUM/DENOM < -0.1  then '-20% to -10%'
           when (NUM/DENOM >= -0.1
           and (NUM/DENOM < 0.0   then '-10% to 0%'
           when (NUM/DENOM >= 0.0
           and (NUM/DENOM < 0.1    then '0% to 10%'
           when (NUM/DENOM >= 0.1
           and (NUM/DENOM < 0.2    then '10% to 20%'
           when (NUM/DENOM >= 0.2
           and (NUM/DENOM < 0.3    then '20% to 30%'
           when (NUM/DENOM >= 0.3  THEN 'At least 30%'
           end
END AS indrange
FROM MYTABLE
WHERE DENOM <> 0 AND
YEAR = 2020 AND
MONTH = 11
)
SELECT
indrange,
count (*) AS total
FROM FILTER
GROUP BY indrange

只要比率不在其中一个范围内（比如说，我的表中没有NUM/DENOM - 1〉0.3的行），那么我最终得到的结果就不会包含值为0的行“至少30%”。该行是根本不存在的。我该如何更改代码以使它仍然包含“至少30%”行以及相应的0值呢？换句话说，我得到这个：

INDRANGE        TOTAL
Less than -30%  285
-30% to -20%    1,608
-20% to -10%    7,409
-10% to 0%      164,212
0% to 10%       169,665
10% to 20%      1

但我想要这个：

INDRANGE        TOTAL
Less than -30%  285
-30% to -20%    1,608
-20% to -10%    7,409
-10% to 0%      164,212
0% to 10%       169,665
10% to 20%      1
20% to 30%      0
At Least 30%    0

我该怎么做呢？这是DB2。

db2

来源：https://stackoverflow.com/questions/70307848/group-by-with-case-statement-doesnt-count-zeros

2条答案

按热度按时间

nukf8bse1#

您可以在范围和现有CTE之间使用LEFT JOIN。例如：

with myranges (indrange) as (
  select 'Less than -30%'         from sysibm.sysdummy1
  union all select '-30% to -20%' from sysibm.sysdummy1
  union all select '-20% to -10%' from sysibm.sysdummy1
  union all select '-10% to 0%'   from sysibm.sysdummy1
  union all select '0% to 10%'    from sysibm.sysdummy1
  union all select '10% to 20%'   from sysibm.sysdummy1
  union all select '20% to 30%'   from sysibm.sysdummy1
  union all select 'At Least 30%' from sysibm.sysdummy1
),
myfilter as (
  -- here add you existing CTE
)
select r.indrange, count(f.indrange) AS total
from myranges r
left join myfilter f on f.indrange = r.indrange
group by r.indrange

赞(0）回复(0）举报 2022-11-07

carvr3hs2#

未经测试：但基于我之前的评论，类似这样的东西。

添加MyRanges cte，列出每个范围1次，从而确保每个范围都包含在最终查询中。
在最终查询中使用Union all将其与MyFilter的结果组合
从count（*）结果中减去1，因为我们从myRanges并集中将结果增加了1

。

WITH  MYRANGES AS(
  SELECT 'Less than -30%' as INDRANGE FROM DUAL UNION ALL
  SELECT '-30% to -20%'    as INDRANGE FROM DUAL UNION ALL
  SELECT '-20% to -10%'    as INDRANGE FROM DUAL UNION ALL
  SELECT '-10% to 0%'      as INDRANGE FROM DUAL UNION ALL
  SELECT '0% to 10%'       as INDRANGE FROM DUAL UNION ALL
  SELECT '10% to 20%'      as INDRANGE FROM DUAL UNION ALL
  SELECT '20% to 30%'      as INDRANGE FROM DUAL UNION ALL
  SELECT 'At Least 30%'    as INDRANGE FROM DUAL),
MYFILTER AS (SELECT
  CASE WHEN STATE IN ('A', 'B') then
  case
   when (NUM/DENOM - 1.0) < -0.3 then 'Less than -30%'
           when (NUM/DENOM - 1.0) >= -0.3
           and (NUM/DENOM - 1.0) < -0.2  then '-30% to -20%'
           when (NUM/DENOM - 1.0) >= -0.2
           and (NUM/DENOM - 1.0) < -0.1  then '-20% to -10%'
           when (NUM/DENOM - 1.0) >= -0.1
           and (NUM/DENOM - 1.0) < 0.0   then '-10% to 0%'
           when (NUM/DENOM - 1.0) >= 0.0
           and (NUM/DENOM - 1.0) < 0.1    then '0% to 10%'
           when (NUM/DENOM - 1.0) >= 0.1
           and (NUM/DENOM - 1.0) < 0.2    then '10% to 20%'
           when (NUM/DENOM - 1.0) >= 0.2
           and (NUM/DENOM - 1.0) < 0.3    then '20% to 30%'
           when (NUM/DENOM - 1.0) >= 0.3  THEN 'At least 30%'
           end
ELSE case
   when (NUM/DENOM < -0.3 then 'Less than -30%'
           when (NUM/DENOM >= -0.3
           and (NUM/DENOM < -0.2  then '-30% to -20%'
           when (NUM/DENOM >= -0.2
           and (NUM/DENOM < -0.1  then '-20% to -10%'
           when (NUM/DENOM >= -0.1
           and (NUM/DENOM < 0.0   then '-10% to 0%'
           when (NUM/DENOM >= 0.0
           and (NUM/DENOM < 0.1    then '0% to 10%'
           when (NUM/DENOM >= 0.1
           and (NUM/DENOM < 0.2    then '10% to 20%'
           when (NUM/DENOM >= 0.2
           and (NUM/DENOM < 0.3    then '20% to 30%'
           when (NUM/DENOM >= 0.3  THEN 'At least 30%'
           end
END AS indrange
FROM MYTABLE
WHERE DENOM <> 0 AND
  YEAR = 2020 AND
  MONTH = 11
),

SELECT indrange, count (*)-1 AS total
FROM   (SELECT * FROM MyFilter UNION ALL
        SELECT * FROM MyRanges) as MyFilterandAllRanges
GROUP BY indrange

好吧，现在我已经做了一些事情，我已经考虑过了，我可以做得更好...
因此，将最后的查询修改为......这样我们就不必在数字和数学上做手脚了。我们基本上采用了一个范围的“主列表”，并将其外部连接到我们的总计中，这样所有的范围都被包括在内，只有那些匹配的范围才显示总计。我们使用合并来处理一些可能不存在的事实，并替换所需的0。

SELECT MyRanges.indrange, coalesce(total,0) as total
FROM   MyRanges
LEFT JOIN (SELECT count(*) total, Indrange 
           FROM MyFilter
           GROUP BY indrange) as sub
     on MyRanges.indrange = sub.indrange

赞(0）回复(0）举报 2022-11-07

我来回答

db2 带有Case语句的Group By不计算零

2条答案

相关问题

热门标签

最新问答