在DB2中，如何计算表中记录的百分比？

w7t8yxp5 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(191)

我有一个单一的表-驱动程序我想知道多少百分比的驱动程序已被终止本月相比，所有活动的驱动程序。我认为我已经使它非常复杂，虽然它确实返回一个结果，它只是一个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

db2

来源：https://stackoverflow.com/questions/70583397/how-can-i-find-the-percentage-of-records-in-a-table-in-db2

1条答案

按热度按时间

7ivaypg91#

你的括号有点乱，而不是

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

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

我来回答

在DB2中，如何计算表中记录的百分比？

1条答案

相关问题

热门标签

最新问答