SQL实现计算每周收益率-优快云博客

本文链接：https://blog.youkuaiyun.com/friendliu/article/details/2281631

该博客分享了如何使用SQL查询从quote表中获取最近一到52周每周的收益率。通过子查询找到每周的第一个和最后一个交易日的收盘价，然后计算收益率。查询涉及对日期的处理，包括提取年份、周数以及找到每周的最高和最低交易日。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

例：某quote表，有tradedate,closeprice的字段数据，现在要取最近一周到52周每周的收益率

用SQL语名实现如下MAX_WEEK找出每周最后一个交易日及其收盘价，同时MIN_WEEK是取每周第一个

交易日的收盘价。

select MAX_WEEK.stkcode, MAX_WEEK.year_week, ((MAX_WEEK.closeprice - MIN_WEEK.closeprice) / MIN_WEEK.closeprice) DRate from (select B.stkcode, B.year_week, A.closeprice from quote A, (select stkcode, Concat(T_year, wkCount) as year_week, max(tradedate) M_tradedate from (select stkcode, tradedate, substr(tradedate, 0, 4) T_year, closeprice, to_char(to_date(tradedate, 'yyyy-mm-dd'), 'iw') as wkCount from quote where stkcode = '816020' order by tradedate desc) C group by T_year, wkCount, stkcode order by T_year desc, wkcount desc) B where a.stkcode = '816020' and A.Stkcode = B.stkcode and A.Tradedate = B.M_tradedate) MAX_WEEK, (select B.stkcode, B.year_week, A.closeprice from quote A, (select stkcode, Concat(T_year, wkCount) as year_week, min(tradedate) M_tradedate from (select stkcode, tradedate, substr(tradedate, 0, 4) T_year, closeprice, to_char(to_date(tradedate, 'yyyy-mm-dd'), 'iw') as wkCount from quote where stkcode = '816020' order by tradedate desc) C group by T_year, wkCount, stkcode order by T_year desc, wkcount desc) B where a.stkcode = '816020' and A.Stkcode = B.stkcode and A.Tradedate = B.M_tradedate) MIN_WEEK where MAX_WEEK.stkcode = MIN_WEEK.stkcode and max_week.year_week = min_week.year_week order by year_week desc

滚动周计算收益率的SQL