时间戳

首页 > 解决方案 > 避免在嵌套 SQL 查询中计算空值

postgresql - 避免在嵌套 SQL 查询中计算空值

问题描述

我有一个 PostgreSQL 计算计算roi，其中定义了几个值，select 100 * ((SUM(time_product)/SUM(rt_id_count))*SUM(rental_cost))/SUM(sale_count)并使用 case 语句作为嵌套查询进行计算。但是，如果任何值是空的（例如 ifrental_cost缺少某个项目），它会计算roias zero，并且该零值将滚入计算的平均值中，从而使平均值向下倾斜。如何在总计算中将这些零视为空值？

（我已经取出了一些嵌套查询并[calculation]在问题中替换它们；这主要是因为它是一个很长的查询，我想过滤掉噪音并使完整的结构更加可见）

  (select 100 * ((SUM(time_product)/SUM(rt_id_count))*SUM(rental_cost))/SUM(sale_count)  FROM

  (select p2.id, p2.inventory_type, p2.working_value, COUNT(distinct p2.id),

  ----nested values calculated here
   (case p2.inventory_type 
      when 'set' then 
      [calculation A]
    else [calculation B] end) rental_cost,
    (case p2.inventory_type 
      when 'set' then 
      [caluculation A]
    else [calculation B]  end)   sale_count,
    (case p2.inventory_type 
      when 'set' then count(distinct rt.id)
      else 1 end 
      ) rt_id_count,
    (case p2.inventory_type 
      when 'set' then 
      [calculation ]  end)   time_total,
 [calculation]  time_product

  FROM warehouses w
    LEFT JOIN rfid_tags rt ON w.id = rt.location_id AND rt.location_type = 'Warehouse'
    LEFT JOIN products p2 ON rt.ancestor_product_id = p2.id 
    LEFT JOIN category_assignments ca  ON ca.product_id = p2.id
    LEFT JOIN categories c ON ca.category_id = c.id
    LEFT JOIN product_selections ps ON ps.rfid_tag_id = rt.id 

    WHERE 
      c.id=categories.id AND ca.primary = true  AND w.id=warehouses.id
      AND (select count(ps.id) from product_selections ps where ps.rfid_tag_id=rt.id)>0
      and p2.working_value>0 
    AND rt.location_id=w.id
    group by p2.id, p2.inventory_type, p2.working_value, c.sale_price_percentage, c.rental_price_percentage) Z) roi

标签： postgresqlnestedcase

解决方案

如果两个参数相等，则用于NULLIF返回：NULL

SELECT NULLIF(?, 0);

?你的计算在哪里。

这对您的情况意味着：

select nullif(100 * ((SUM(time_product)/SUM(rt_id_count))*SUM(rental_cost))/SUM(sale_count), 0)
from ...

推荐阅读