代码之家 › 专栏 › 技术社区 › dark horse

红移-除以零误差

divide-by-zero amazon-redshift sql

dark horse · 技术社区 · 6 年前

我有以下问题,一直抛出“除以零”的错误

下面是我正在使用的查询:

select cust_name,
sum(case WHEN sale_date > CURRENT_TIMESTAMP-14 AND sale_date < CURRENT_TIMESTAMP then 1 else 0  END) AS "0_2_sales_count",
sum(case WHEN sale_date > CURRENT_TIMESTAMP-28 AND sale_date < CURRENT_TIMESTAMP-14  then 1 else 0  END) AS "2_4_sales_count",
("0_2_sales_count") - ("2_4_sales_count") as "load_volume_diff_0_2_2_4",
(("0_2_sales_count" - "2_4_weeks_load_volume")/"2_4_sales_count"::float) * 100  as "load_volume_diff_percent_0_2_2_4"
from sales 
group by cust_name;

我在“0~2~u销售计数”和“2~4~u销售计数”栏中为不同的客户设置了0。有人能告诉我如何处理上述错误吗。我试过当column=0时使用case,但仍然会出现同样的问题。

我正在使用亚马逊红移数据库。

谢谢

3 回复 | 直到 6 年前

ScaisEdge 6 年前

您可以使用案例检查“2~4~u销售计数”中的0,并为每种情况使用正确的代码

select cust_name,
      sum(case WHEN sale_date > CURRENT_TIMESTAMP-14 
          AND sale_date < CURRENT_TIMESTAMP then 1 else 0  END) AS "0_2_sales_count",
        sum(case WHEN sale_date > CURRENT_TIMESTAMP-28 
          AND sale_date < CURRENT_TIMESTAMP-14  then 1 else 0  END) AS "2_4_sales_count",
      ("0_2_sales_count") - ("2_4_sales_count") as "load_volume_diff_0_2_2_4",
      CASE WHEN "2_4_sales_count"::float = 0
          THEN 0 ELSE (("0_2_sales_count" - "2_4_weeks_load_volume")/"2_4_sales_count"::float) * 100  END as "load_volume_diff_percent_0_2_2_4"
from sales 
group by cust_name;

Fahmi 6 年前

当“2~4~u销售计数”时,使用case when like case:float=0,然后1结束

select cust_name,
sum(case WHEN sale_date > CURRENT_TIMESTAMP-14 AND sale_date < CURRENT_TIMESTAMP then 1 else 0  END) AS "0_2_sales_count",
sum(case WHEN sale_date > CURRENT_TIMESTAMP-28 AND sale_date < CURRENT_TIMESTAMP-14  then 1 else 0  END) AS "2_4_sales_count",
("0_2_sales_count") - ("2_4_sales_count") as "load_volume_diff_0_2_2_4",
(("0_2_sales_count" - "2_4_weeks_load_volume")/case when "2_4_sales_count"::float =0 then 1 end) * 100  as "load_volume_diff_percent_0_2_2_4"
from sales 
group by cust_name;

Gordon Linoff 6 年前

(("0_2_sales_count" -"2_4_weeks_load_volume") / "2_4_sales_count"::float) * 100  as "load_volume_diff_percent_0_2_2_4"

(("0_2_sales_count" - "2_4_weeks_load_volume") * 100.0 / nullif("2_4_sales_count", 0.0) as "load_volume_diff_percent_0_2_2_4"

除以零的最简单解是 nullif() .