mysql - 第一个 SUM 为零的 2 个日期之间 SUM 增加/减少的百分比

Question

我有一个像这样的 mysql 查询 -

SELECT location, 
SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0)) AS previous, 
SUM(IF(action_dt_tm BETWEEN '2013-07-01' AND '2013-12-31',1,0)) AS current, 
SUM(IF(action_dt_tm BETWEEN '2013-07-01' AND '2013-12-31',1,0))- 
SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0)) as diff, ( 
SUM(IF(action_dt_tm BETWEEN '2013-07-01' AND '2013-12-31',1,0))- 
SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0)))/ 
SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0))*100 AS percent 
FROM table group by location order by percent DESC

我基于之前在 stackoverflow 上找到的答案。

这个想法是返回一个位置、之前的日期范围计数、当前计数(这些范围发生变化)、之前和当前之间的数字差异以及增加或减少的百分比。

类似 -

LOCATION 1     38    27    -11    -28.94%
LOCATION 2     6     18     12    200.00%

等等

上面的代码虽然可能很笨重，但似乎可以工作，除非发现 PREVIOUS 计数为零，然后百分比返回为 NULL，而不是增加/减少。

即

LOCATION 3     0     32      32     (NULL)

任何人都可以建议如何更改查询来解决此问题。

Answer 1

如果您之前的值等于 0，则会导致百分比列除以零。

By default, division by zero produces a result of NULL and no warning.

https://dev.mysql.com/doc/refman/5.0/en/precision-math-expressions.html

由于您除以零，我不确定将 NULL 值替换为任何其他值是否有意义，但您应该能够通过在进行除法之前检查先前计数的零来替换它。

示例(用零替换):

SELECT location, 
SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0)) AS previous, 
SUM(IF(action_dt_tm BETWEEN '2013-07-01' AND '2013-12-31',1,0)) AS current, 
SUM(IF(action_dt_tm BETWEEN '2013-07-01' AND '2013-12-31',1,0))- 
SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0)) as diff, ( 
case 
  when (SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0))) then
    0
  else
    SUM(IF(action_dt_tm BETWEEN '2013-07-01' AND '2013-12-31',1,0))- 
    SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0)))/ 
    SUM(IF(action_dt_tm BETWEEN '2013-01-01' AND '2013-06-30',1,0))*100
end
 AS percent 
FROM table group by location order by percent DESC