今日指数项目实现个股日K线详情功能
个股日K线详情功能
一. 什么是个股日K线
1.日K线就是将股票交易流水按天分组，然后统计出每天的交易数据，内容包含：日期、股票编码、名称、最高价、最低价、开盘价、收盘价、前收盘价、交易量；
2.需要注意的是这里的收盘价就是指每天最大交易时间点下对应的价格；
二. 接口说明
功能描述：查询指定股票每天产生的数据，组装成日K线数据；
如果当大盘尚未收盘，则以最新的交易价格作为当天的收盘价格；
服务路径：/api/quot/stock/screen/dkline
服务方法：GET
前端请求频率：每分钟
请求参数：code
| 参数说明 | 参数名称 | 是否必须 | 数据类型 | 备注 |
|---|---|---|---|---|
| 股票编码 | code | true | string | 股票编码 |
响应数据结构：
{"code": 1,"data": [{"date": "2021-12-20 10:20",//日期"tradeAmt": 28284252,//交易量(指收盘时的交易量，如果当天未收盘，则显示最新数据)"code": "000021",//股票编码"lowPrice": 16,//最低价格（指收盘时记录的最低价，如果当天未收盘，则显示最新数据）"name": "深科技",//名称"highPrice": 16.83,//最高价（指收盘时记录的最高价，如果当天未收盘，则显示最新数据）"openPrice": 16.8,//开盘价"tradeVol": 459088567.58,//交易金额（指收盘时记录交易量，如果当天未收盘，则显示最新数据）"closePrice": 16.81//当前收盘价（指收盘时的价格，如果当天未收盘，则显示最新cur_price）"preClosePrice": 16.81//前收盘价},//......]
}
三. SQL分析
SQL查询要划定一个默认的日期范围，这样可避免大数据量下全表查询而导致慢查询的问题；
在指定的日期范围内以天分组统计出每天日期的最大值（收盘时间);
根据获取的最大日期组，使用in进行条件查询，进而获取日K线相关的数据；
1. 查询每天对应的收盘时间
select max(cur_time)
from stock_rt_info
where stock_code = '600021'and cur_time between '2022-01-01 09:30:00' and '2022-06-06 14:25:00'
group by date_format(cur_time, '%Y%m%d');
2. 获取在该时间段内所需要的数据
select sri.cur_time as date,sri.trade_amount as tradeAmt,sri.stock_code as code,sri.min_price as lowPrice,sri.stock_name as name,sri.max_price as highPrice,sri.open_price as openPrice,sri.trade_volume as tradeVol,sri.cur_price as closePrice,sri.pre_close_price as preClosePrice
from stock_rt_info as sri
where cur_time in (select max(cur_time)from stock_rt_infowhere stock_code = '600021'and cur_time between '2022-01-01 09:30:00' and '2022-06-06 14:25:00'group by date_format(cur_time, '%Y%m%d'))
这里同样也可以将查询时间与获取数据分开为两个Mapper接口, 这样更加方便查询
四. 代码实现
1. 表现层
/*** 个股日K图*/@ApiOperation(value = "个股日K图", notes = "个股日K图", httpMethod = "GET")@GetMapping("/stock/screen/dkline")public R<List<Stock4EvrDayDomain>> getStockScreenDkline(@RequestParam(value = "code" , required = true) String code){return service.getStockScreenDkline(code);}
2. 服务层
接口
R<List<Stock4EvrDayDomain>> getStockScreenDkline(String code);
实现类
/*** 个股日K图*/@Overridepublic R<List<Stock4EvrDayDomain>> getStockScreenDkline(String code) {// 获取截止时间DateTime curTime = DateTimeUtil.getLastDate4Stock(DateTime.now());curTime = DateTime.parse("2022-06-07 15:00:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));Date nowDate = curTime.toDate();// 获取起始时间Date startDate = curTime.minusMonths(3).toDate();startDate = DateTime.parse("2022-01-01 09:30:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();// 获取最新数据列表List<String> latestChangeTime = stockRtInfoMapper.getLatestChangeTime(startDate, nowDate, code);// 查询数据List<Stock4EvrDayDomain> data = stockRtInfoMapper.getStockScreenDkline(startDate,nowDate,code,latestChangeTime);return R.ok(data);}
3.Dao层
mapper接口
// 实现数据查询
List<Stock4EvrDayDomain> getStockScreenDkline(@Param("startDate") Date startDate, @Param("nowDate") Date nowDate, @Param("code") String code , @Param("latestChangeTime") List<String> latestChangeTime);// 实现查询每天对应的收盘时间
List<String> getLatestChangeTime(@Param("startDay") Date startDay , @Param("latestTime") Date latestTime , @Param("code") String code);
xml代码实现
<select id="getStockScreenDkline" resultType="com.jixu.stock.pojo.domain.Stock4EvrDayDomain">selectdate_format(sri2.cur_time,'%Y%m%d') as date,sri2.trade_amount as tradeAmt,sri2.stock_code as code,sri2.min_price as lowPrice,sri2.stock_name as name,sri2.max_price as highPrice,sri2.open_price as openPrice,sri2.trade_volume as tradeVol,sri2.cur_price as closePrice,sri2.pre_close_price as preClosePricefrom stock_rt_info as sri2where sri2.cur_time in<foreach collection="latestChangeTime" item="time" open="(" separator="," close=")">#{time}</foreach>and sri2.stock_code=#{code}order by sri2.cur_time</select><select id="getLatestChangeTime" resultType="java.lang.String">select max(cur_time) as max_timefrom stock_rt_infowhere stock_code = #{code}and cur_time between #{startDay} and #{latestTime}group by date_format(cur_time, '%Y%m%d')</select>