oracle 在执行5年和10年移动平均线时遇到问题？

MonthName Calendar_Year Total Number    Moving Average 5 Year   Moving Average 10 Year
April   2000    135     
April   2001    76      
April   2002    33      
April   2003    31      
April   2004    228     
April   2005    200 101 
April   2006    350 114 
April   2007    126 168 
April   2008    102 187 
April   2009    126 201 
April   2010    333 181 141
April   2011    137 207 161
April   2012    121 165 167
April   2013    36  164 175
April   2014    79  151 176
April   2015    272 141 161
April   2016    282 129 168
April   2017    96  158 161
April   2018    93  153 158
April   2019    181 164 158
April   2020    64  185 163
April   2021    144 143 136
April   2022    126 116 137
April   2023    236 122 137
August  2000    66      
August  2001    83      
August  2002    118     
August  2003    236     
August  2004    117     
August  2005    84  124 
August  2006    151 128 
August  2007    157 141 
August  2008    221 149 
August  2009    178 146 
August  2010    171 158 141
August  2011    154 176 152
August  2012    267 176 159
August  2013    164 198 174
August  2014    249 187 166
August  2015    149 201 180
August  2016    122 197 186
August  2017    247 190 183
August  2018    160 186 192
August  2019    73  185 186
August  2020    176 150 176
August  2021    164 156 176
August  2022    275 164 177
August  2023    52  170 178

上表是从excel中获得的，如何在sql中执行5年和10年移动平均值，例如2005年4月的5年移动平均值是（2000年4月至2004年4月）和2006年4月的平均数为（2001年4月至2005年），10年移动平均值相同，其中2010年8月为平均值（2000年8月- 2000年）我想在SQL查询中复制上述格式，对于没有以前5年或10年数据的数据，则为空白？

WITH MovingAverages AS (
  SELECT
    MonthName,
    Calendar_Year,
    TotalNumber,
    AVG(TotalNumber) OVER (
      PARTITION BY MonthName
      ORDER BY Calendar_Year
      ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS MovingAverage5Year,
    AVG(TotalNumber) OVER (
      PARTITION BY MonthName
      ORDER BY Calendar_Year
      ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS MovingAverage10Year
  FROM MOVINGAVERAGES
)
SELECT
  MonthName,
  Calendar_Year,
  TotalNumber,
  CASE
    WHEN MovingAverage5Year IS NOT NULL THEN ROUND(MovingAverage5Year, 2)
    ELSE NULL
  END AS "Moving Average 5 Year",
  CASE
    WHEN MovingAverage10Year IS NOT NULL THEN ROUND(MovingAverage10Year, 2)
    ELSE NULL
  END AS "Moving Average 10 Year"
FROM MovingAverages;

我得到下面的错误，你能帮我解决这个问题吗
ORA-32039：递归WITH子句必须具有列别名列表32039。00000 -“递归WITH子句必须有列别名列表”* 原因：WITH子句查询引用自身（递归），但没有为其指定列别名列表。* 操作：为WITH子句查询名称添加列别名列表。行错误：16柱：8

这里的主要语法问题似乎是您使用与CTE中的表相同的名称来命名CTE。这是在欺骗MySQL，让它认为您正在尝试进行递归CTE，但这似乎不是您的意图。给予CTE一个唯一的名称以解决此问题。

WITH cte AS (
    SELECT MonthName, Calendar_Year, TotalNumber,
           AVG(TotalNumber) OVER (
               PARTITION BY MonthName ORDER BY Calendar_Year
               ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
           ) AS MovingAverage5Year,
           AVG(TotalNumber) OVER (PARTITION BY MonthName ORDER BY Calendar_Year
               ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
           ) AS MovingAverage10Year
    FROM MOVINGAVERAGES
)

SELECT MonthName, Calendar_Year, TotalNumber,
       CASE WHEN MovingAverage5Year IS NOT NULL
            THEN ROUND(MovingAverage5Year, 2) END AS "Moving Average 5 Year",
       CASE WHEN MovingAverage10Year IS NOT NULL
            THEN ROUND(MovingAverage10Year, 2) END AS "Moving Average 10 Year"
FROM cte;

oracle 在执行5年和10年移动平均线时遇到问题？

1条答案

相关问题

热门标签

最新问答