SELECT
A.SecuCode,
B.NetProfit,
B.EndDate,
TradingDay = B.InfoPublDate
FROM
SecuMain A,
LC_IncomeStatementAll B
WHERE
A.CompanyCode = B.CompanyCode
AND B.EndDate >= '2010-01-25'
AND B.EndDate <= '2016-02-05'
AND CONVERT (VARCHAR, B.EndDate, 120) LIKE '%-12-31%'
ORDER BY
SecuCode,
EndDate DESC
对于以上结果,我想按照对每个SecuCode去最近五年的NetProfit求平均(表中有的SecuCode对应五年的数据,有的是六年的),请问sql语句怎么修改好~
下面是按时间顺序取五笔数据,然后取平均值
SELECT SecuCode,AVG(NetProfit) FROM (
SELECT
A.SecuCode,
B.NetProfit,
B.EndDate,
TradingDay = B.InfoPublDate,
ROW_NUMBER()OVER(PARTITION BY A.SecuCode ORDER BY B.EndDat DESC) AS rn
FROM
SecuMain A,
LC_IncomeStatementAll B
WHERE
A.CompanyCode = B.CompanyCode
AND B.EndDate >= '2010-01-25'
AND B.EndDate <= '2016-02-05'
AND CONVERT (VARCHAR, B.EndDate, 120) LIKE '%-12-31%'
--ORDER BY
-- SecuCode,
-- EndDate DESC
) t WHERE t.rn<=5
GROUP BY SecuCode
SELECT
A.SecuCode,
AVG(B.NetProfit)
FROM
SecuMain A,
LC_IncomeStatementAll B
WHERE
A.CompanyCode = B.CompanyCode
AND B.EndDate >= '2010-01-25'
AND B.EndDate <= '2016-02-05'
AND CONVERT (VARCHAR, B.EndDate, 120) LIKE '%-12-31%'
GROUP BY
A.SecuCode
我觉得用Group By 能实现
先按 A.SecuCode分组
再对每个组内的 B.NetProfit求平均值
用row_number()over () 能搞定