如何在DB2中找到表中记录的百分比

Question

我只有一个表-驱动程序我想知道与所有活跃的司机相比，本月司机被解雇的比例是多少。我想我已经把它弄得很复杂了，虽然它确实返回了一个结果，但它只是一个0。我试着使用小数形式的转换，但这对我来说也不起作用，因为计算结果仍然是0。

WITH X AS
( 
SELECT
CAST(COUNT(*) AS DECIMAL (5,2)) TERMINATED,
0 ACTIVE
FROM DRIVER WHERE 
MONTH(TERMINATION_DATE) = MONTH(CURRENT TIMESTAMP) AND YEAR(TERMINATION_DATE) = YEAR(CURRENT TIMESTAMP)
UNION ALL
SELECT 
0 TERMINATED,
CAST(COUNT(*) AS FLOAT) ACTIVE
FROM DRIVER WHERE
ACTIVE_IN_DISP = 'True'
)
SELECT
CAST(SUM(TERMINATED)/(SUM(ACTIVE) + SUM(TERMINATED)*100) AS DECIMAL (10,2))
FROM X

Answer 1

括号有点偏离，而不是

SUM(TERMINATED)/(SUM(ACTIVE) + SUM(TERMINATED)*100)

它应该读取

SUM(TERMINATED)/(SUM(ACTIVE) + SUM(TERMINATED))*100

你的数字太小了，这就是为什么它不能放进小数点后两位的原因。

我会这样重写你的查询：

WITH begin_of_month(begin_of_month) AS (VALUES CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS),
X AS (
SELECT COUNT(*) AS terminated, 0 AS active
FROM driver
WHERE termination_date >= begin_of_month
AND termination_date <  begin_of_month + 1 MONTH --index, if present, can be used!
UNION ALL
SELECT 0 AS terminated, COUNT(*) AS active
FROM driver
WHERE active_in_disp = 'True'
)
SELECT CAST(FLOAT(SUM(TERMINATED))/(SUM(ACTIVE) + SUM(TERMINATED))*100 AS DECIMAL (10,2)) --CAST only if You wish to display with 2 decimal places
FROM X