提取每月最后一个交易日的收盘价

select b.s_info_windcode,b.trade_dt,b.s_dq_close
from aindexindustrieseodcitics b
inner join 
(select max(trade_days) as lastday from 
(select trade_days, lpad(trade_days,6) as yue from asharecalendar where trade_days between 20180101 and 20191231 and s_info_exchmarket= 'SHN' order by trade_days asc)
group by yue order by yue) a
on b.trade_dt = a.lastday order by s_info_windcode,trade_dt

image.png

select  b.s_info_windcode,b.trade_dt,b.s_dq_close
from aindexindustrieseodcitics b，
(select max(trade_days) as lastday from 
(select trade_days, lpad(trade_days,6) as yue from asharecalendar where trade_days between 20180101 and 20191231 and s_info_exchmarket= 'SHN' order by trade_days asc)
group by yue order by yue) a
where b.trade_dt = a.lastday order by b.s_info_windcode,b.trade_dt

image.png

提取各个指数，每个月总的交易量

select s_info_windcode,yue,sum(s_dq_volume) as totvolume from
(select a.*,b.s_info_windcode,b.s_dq_volume from
(select trade_days, lpad(trade_days,6) as yue from asharecalendar where trade_days between 20180101 and 20191231 and s_info_exchmarket= 'SHN' order by trade_days asc) a,
AIndexIndustriesEODCITICS b
where b.trade_dt=a.trade_days order by s_info_windcode,trade_days) c group by s_info_windcode,yue order by s_info_windcode,yue

image.png