主页 / dba / 问题 / 260668
Accepted
samosql
Asked: 2020-02-27 11:36:50 +0800 CST

SQL Server：帮助编写查询以获得表的 3 个滚动月平均值

  • 772

查询到目前为止：

select month
     , year
     , vol
     --, sum(vol) over (order by month,year  ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
     , avg(vol) over ( order by  month,year  
                       ROWS BETWEEN 2 PRECEDING 
                                AND current row) '3monthra'
     , name
     , cnt
     , costx  
from (
    SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
         , year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
         , sum(SumOfNetAmount) as vol
         , count(*) as cnt
         , costx
         , name 
    FROM [ngl_OTB] 
    GROUP BY costx
           , MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
           , YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
           , name
           , SumOfNetAmount
) s 
where costx = '1000.002' and 
 name = 'ngl-raw' 
order by month,year

我需要有关如何递归设置 [costx] 和 [name] 列以允许每个 [costx] 和 [name] 正确滚动 3 个月的帮助。所有尝试都提供了错误的计算，例如 3 个月的滚动平均值正在渗入其他成本和名称。我的目标是按 costx 和名称以及按年份和月份的顺序对所有数据进行分组，然后使用该 [costx] 的 [vol] 的值对每个 costx 执行 3 个月的滚动平均

任何帮助将不胜感激

sql-server sql-server-2016

1 个回答

  1. Best Answer
    select month
     , year
     , vol
   --, sum(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
     , avg(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) '3monthra'
     , name
     , cnt
     , costx                                                                       
from (
SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
     , year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
     , sum(SumOfNetAmount) as vol
     , count(*) as cnt
     , costx
     , name 
FROM [ngl_OTB] 
GROUP BY costx
       , MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
       , YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
       , name
       , SumOfNetAmount)s 
where costx = '1000.002' and name = 'ngl-raw' order by month,year
    • 0

相关问题