Oracle SQL -计算性能指标

Question

我想知道是否有人能帮我/启发我--为这件长时间的事道歉。

我有一张表，如下例所示：

ID  | PersonID  | Year  | Status    | Status_A
----------------------------------------
1   |   1       | 2015  |   C       |   SP1
2   |   2       | 2015  |   B       |   SP1
3   |   3       | 2016  |   C       |   SP2

我需要在这张桌子上做一些分析，这样我就可以得到(例如)：

• 总人数
• 多年来拥有C状态的总人数所占百分比
• 占持有C级一年的总人数的百分比
• 多年来患有Status_A SP1的总人数的百分比
• 占患有Status2 SP1一年的总人数的百分比
• 多年来拥有Status2 SP1和C状态的总人数的百分比
• 多年来拥有Status2 SP1、C和B状态的总人数所占百分比
• 等。

到目前为止，我使用的是子查询，然后在select中执行所有的计算。每一项都是绩效指标，如果超过目标，则必须在其旁边加上“X”：

(例如)

(需要连接HTML)

SELECT
  '<tr>
<th>Performance Indicator 1</th>
<td>Performance Indicator Text</td>
<td>Year 2015: '
  || (STATUS1+STATUS2)
  || ' ('
  || NVL(ROUND(100*((STATUS1+STATUS2)/NULLIF(TOTAL2,0)),2),0)
  || '%)'
  ||
  CASE
    WHEN ROUND(100*((STATUS1+STATUS2)/NULLIF(TOTAL2,0)),2)>= 15
    THEN ' X'
  END
  || '</td><td> Year 2016: '
  || (STATUS1+STATUS2)
  || ' ('
  || NVL(ROUND(100*((STATUS1+STATUS2)/NULLIF(TOTAL3,0)),2),0)
  || '%) '
  ||
  CASE
    WHEN ROUND(100*((STATUS1+STATUS2)/NULLIF(TOTAL3,0)),2)>= 15
    THEN ' X'
  END
  || '</td>
<td></td>
</tr>'
FROM
  (
    SELECT
      COUNT(*) AS TOTAL2
    FROM
      TABLE
    WHERE
      YEAR = '2015'
  )
  ,
  (
    SELECT
      COUNT(*) AS TOTAL3
    FROM
      TABLE
    WHERE
      YEAR = '2016'
  )
  ,
  (
    SELECT
      COUNT(*) AS STATUS1
    FROM
      TABLE
    WHERE
      Status = 'C'
  )
  ,
  (
    SELECT
      COUNT(*) AS STATUS2
    FROM
      TABLE
    WHERE
      Status = 'B'
  )
  etc.

如果在所有所需的性能指标上都这样做，这将变得极其低效和笨重。我确信有一种更好/更简单的方法。有人能帮忙吗？

谢谢

Answer 1

我建议您使用以下使用with语句的技巧：

select 
  NVL(ROUND(100*((STATUS1+STATUS2)/NULLIF(TOTAL2,0)),2),0) as kpi1,
  ... ,
  ... ,
  ... ,
from (
    with data as (
      select * from table
      where year between 2015 and 2016
         or status in ('B','C')
    )
    select
      count(case when year=2015 then 1 else NULL end) as total2,
      count(case when year=2016 then 1 else NULL end) as total3,
      count(case when status='C' then 1 else NULL end) as status1,
      count(case when status='B' then 1 else NULL end) as status2
    from data
);

除此之外，您可能希望检查sqlplus的特性SET MARKUP HTML ON (如果您在报告中使用sqlplus)