国内板块指数业务分析
功能原型效果
板块表数据分析
stock_block_rt_info板块表分析:
说明:板块表涵盖了业务所需的所有字段数据;
国内板块接口说明
需求说明: 查询沪深两市最新的板块行情数据,并按照交易金额降序排序展示前10条记录 请求URL: /api/quot/sector/all 请求方式: GET 请求参数: 无
接口响应数据格式:
{
"code": 1,
"data": [
{
"companyNum": 247,//公司数量
"tradeAmt": 5065110316,//交易量
"code": "new_dzxx",//板块编码
"avgPrice": 14.571,//平均价格
"name": "电子信息",//板块名称
"curDate": "2021-12-30 09:50:10",//当前日期
"tradeVol": 60511659145,//交易总金额
"updownRate": 0.196//涨幅
},
//省略.......
]
}
查询实体封装
实体类维护到stock_common工程下:
package com.itheima.stock.pojo.domain;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
import java.util.Date;
/**
* @author by itheima
* @Date 2022/2/28
* @Description 股票板块domain
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class StockBlockDomain {
/**
* 公司数量
*/
private Integer companyNum;
/**
* 交易量
*/
private Long tradeAmt;
/**
* 板块编码
*/
private String code;
/**
* 平均价
*/
private BigDecimal avgPrice;
/**
* 板块名称
*/
private String name;
/**
* 当前日期
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm")
private Date curDate;
/**
*交易金额
*/
private BigDecimal tradeVol;
/**
* 涨跌率
*/
private BigDecimal updownRate;
}
国内板块指数SQL分析
核心功能要求:查询最新板块数据信息,按交易金额降序排序取前10;
方案1:
-- 思考:将板块表根据日期和交易金额降序排序,取前10
select
sbr.company_num as companyNum,
sbr.trade_amount as tradeAmt,
sbr.label as code,
sbr.avg_price as avgPrice,
sbr.block_name as name,
sbr.cur_time as curDate,
sbr.trade_volume as tradeVol,
sbr.updown_rate as updownRate
from stock_block_rt_info as sbr
order by sbr.cur_time desc,sbr.trade_volume desc
limit 10
弊端:每次查询都会==全表==排序,然后再limit返回指定数据,开销非常大;
方案2-推荐:
-- 思路:业务要求是查询最新的数据,也就以为这只需查询最新交易时间点对应的数据,然后根据交易金额降序排序取前10即可【日期字段构建索引,提高查询效率,通过索引获取少量数据,然后再排序,这样cpu和磁盘io的开销得以降低】
select
sbr.company_num as companyNum,
sbr.trade_amount as tradeAmt,
sbr.label as code,
sbr.avg_price as avgPrice,
sbr.block_name as name,
sbr.cur_time as curDate,
sbr.trade_volume as tradeVol,
sbr.updown_rate as updownRate
from stock_block_rt_info as sbr
where sbr.cur_time='2021-12-21 14:30:00'
order by sbr.trade_volume desc
limit 10
国内板块指数功能实现
定义板块web访问接口方法
/**
*需求说明: 获取沪深两市板块最新数据,以交易总金额降序查询,取前10条数据
* @return
*/
@GetMapping("/sector/all")
public R<List<StockBlockDomain>> sectorAll(){
return stockService.sectorAllLimit();
}
定义服务方法和实现
服务接口方法:
/**
* 需求说明: 获取沪深两市板块最新数据,以交易总金额降序查询,取前10条数据
* @return
*/
R<List<StockBlockDomain>> sectorAllLimit();
方法实现:
@Autowired
private StockBlockRtInfoMapper stockBlockRtInfoMapper;
/**
*需求说明: 沪深两市板块分时行情数据查询,以交易时间和交易总金额降序查询,取前10条数据
* @return
*/
@Override
public R<List<StockBlockDomain>> sectorAllLimit() {
//获取股票最新交易时间点
Date lastDate = DateTimeUtil.getLastDate4Stock(DateTime.now()).toDate();
//TODO mock数据,后续删除
lastDate=DateTime.parse("2021-12-21 14:30:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
//1.调用mapper接口获取数据
List<StockBlockDomain> infos=stockBlockRtInfoMapper.sectorAllLimit(lastDate);
//2.组装数据
if (CollectionUtils.isEmpty(infos)) {
return R.error(ResponseCode.NO_RESPONSE_DATA.getMessage());
}
return R.ok(infos);
}
定义mapper方法与xml
mapper接口方法:
/**
* 沪深两市板块分时行情数据查询,以交易时间和交易总金额降序查询,取前10条数据
* @param timePoint 指定时间点
* @return
*/
List<StockBlockDomain> sectorAllLimit(@Param("timePoint") Date timePoint);
定义mapper接口xml:
<select id="sectorAllLimit" resultType="com.itheima.stock.pojo.domain.StockBlockDomain">
select
sbr.company_num as companyNum,
sbr.trade_amount as tradeAmt,
sbr.label as code,
sbr.avg_price as avgPrice,
sbr.block_name as name,
sbr.cur_time as curDate,
sbr.trade_volume as tradeVol,
sbr.updown_rate as updownRate
from stock_block_rt_info as sbr
where sbr.cur_time=#{timePoint}
order by sbr.trade_volume desc
limit 10
</select>
web接口测试
-
前端页面效果: