与上一年相比，记录的AVG增加

Question

AVG基于上一年的记录增加

  SELECT TO_CHAR (Application_Date, 'yyyy'), 
         COUNT (*)
    FROM Services
GROUP BY TO_CHAR (Application_Date, 'yyyy')
ORDER BY 1 DESC;

AVG根据上一年的记录增加

示例：

2018 ----- 3000 records - 50% increase
2017 ----- 2000 records - 100% increase
2016 ----- 1000 records - 0% increase

Answer 1

假设对平均值的引用是红色的，而您的35％示例是一个错字...

您想比较两个行的值，因此您可以使用lag()函数查看上一年的数据，并使用：

LAG(COUNT(*)) OVER (ORDER BY TO_CHAR (Application_Date, 'yyyy'))

然后使用当年的计数和上一年的计数来计算正常的百分比变化：

SELECT TO_CHAR (Application_Date, 'yyyy') AS year,
  COUNT(*) AS year_count,
  100 * (COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY TO_CHAR (Application_Date, 'yyyy')))
    / LAG(COUNT(*)) OVER (ORDER BY TO_CHAR (Application_Date, 'yyyy')) AS percentage_change
FROM Services
GROUP BY TO_CHAR (Application_Date, 'yyyy')
ORDER BY 1 DESC;
YEAR YEAR_COUNT PERCENTAGE_CHANGE
---- ---------- -----------------
2018       5000                25
2017       4000                 0
2016       4000                  

您可以格式化计算值的格式(不清楚您是否要％符号作为结果的一部分，例如将其作为字符串(，并且可以使用cocece替换第一年的零值如果需要，请零。

您也可以使用CTE或内联视图仅执行一次滞后：

SELECT year,
  year_count,
  100 * (year_count - prev_year_count) / prev_year_count AS percentage_change
FROM (
  SELECT TO_CHAR (Application_Date, 'yyyy') AS year,
    COUNT(*) AS year_count,
    LAG(COUNT(*)) OVER (ORDER BY TO_CHAR (Application_Date, 'yyyy')) AS prev_year_count
  FROM Services
  GROUP BY TO_CHAR (Application_Date, 'yyyy')
)
ORDER BY 1 DESC;

亲自使用年度值时，我倾向于将它们视为数字而不是字符串，因此我会使用摘录：

select year,
  year_count,
  100 * (year_count - prev_year_count) / prev_year_count as percentage_change
from (
  select extract(year from application_date) as year,
    count(*) as year_count,
    lag(count(*)) over (order by extract(year from application_date)) as prev_year_count
  from services
  group by extract(year from application_date)
)
order by 1 desc;
      YEAR YEAR_COUNT PERCENTAGE_CHANGE
---------- ---------- -----------------
      2018       5000                25
      2017       4000                 0
      2016       4000                  

db＆lt;>小提琴