1.国内大盘指数功能

1.1需求分析

页面原型

查询A股大盘最新的数据：
code

国内大盘数据包含：大盘代码、大盘名称、开盘点、最新点、前收盘点、交易量、交易金额、涨跌值、涨幅、振幅、当前日期

1.2接口定义

功能说明：

获取最新国内A股大盘信息（仅包含上证和深证大盘数据）;

查询时间点不在正常股票交易时间内，则显示最近时间点的交易信息;

比如:当前查询时间点是周一上午8点整，因为当天尚未开盘，则显示上周五最新的数据，也就是收盘时数据；
请求路径：/api/quot/index/all
请求方式：GET
参数：无
响应数据格式：

{
    "code": 1,
    "data": [
        {
            "code": "sh000001",//大盘编码
            "name": "上证指数",//指数名称
            "openPoint": 3267.81,//开盘点
            "curPoint": 3236.70,//当前点
            "preClosePoint": 3283.43,//前收盘点
            "tradeAmt": 160591,//交易量
            "tradeVol": 1741099,//交易金额
            "upDown": -46.73,//涨跌值
            "rose": -0.01.42,//涨幅
            "amplitude": 0.0164,//振幅
            "curTime": "2022-01-02 01:32"//当前时间
        },
          {......}
    ]
}

A股大盘开盘周期：周一至周五，每天上午9:30到11:30和下午13:00到15:00；

1.3接口实现

封装响应实体类

package com.itheima.pojo.domain;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
@ApiModel("A股大盘信息")
public class InnerMarketDomain {

    @ApiModelProperty(value = "大盘编码")
    private String code ;
    @ApiModelProperty(value = "大盘名称")
    private String name;
    @ApiModelProperty(value = "开盘点")
    private BigDecimal openPoint;
    @ApiModelProperty(value = "当前点")
    private BigDecimal curPoint;
    @ApiModelProperty(value = "前收盘点")
    private BigDecimal preClosePoint;
    @ApiModelProperty(value = "交易量")
    private Long tradeAmt;
    @ApiModelProperty(value = "交易金额")
    private BigDecimal tradeVol;
    @ApiModelProperty(value = "涨跌值")
    private BigDecimal upDown;
    @ApiModelProperty(value = "涨幅")
    private BigDecimal rose;
    @ApiModelProperty(value = "振幅")
    private BigDecimal amplitude;
    @ApiModelProperty(value = "当前时间(数据对应的时间)")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm")
    private Date curTime;

}

股票交易时间工具类封装
项目中经常需要查询股票最近的一次交易时间点，而大盘的开盘时间又分为不同的时间段，这给我们的逻辑判断增加了复杂度，而且项目中股票是每分钟采集一次，时间需要精确到分钟，综上，我们需要在stock_common工程下维护一个公共的时间工具类：

package com.itheima.utils;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

public class DateTimeUtil {
    /**
     * 获取指定日期下股票的上一个有效交易日时间
     * @return
     */
    public static DateTime getPreviousTradingDay(DateTime dateTime){
        //获取指定日期对应的工作日
        int weekNum = dateTime.dayOfWeek().get();
        //判断所属工作日
        DateTime preDateTime=null;
        //周一，那么T-1就是周五
        if (weekNum==1){
            //日期后退3天
          preDateTime=dateTime.minusDays(3);
        }
        //周末，那么T-1就是周五
        else if (weekNum==7){
            preDateTime=dateTime.minusDays(2);
        }
        else {
            preDateTime=dateTime.minusDays(1);
        }
        return getDateTimeWithoutSecond(preDateTime);
    }

    /**
     * 判断是否是工作日
     * @return true：在工作日 false:不在工作日
     */
    public static boolean isWorkDay(DateTime dateTime){
        //获取工作日
        int weekNum = dateTime.dayOfWeek().get();
        return  weekNum>=1 && weekNum<=5;
    }

    /**
     * 获取上一天日期
     * @param dateTime
     * @return
     */
    public static DateTime getPreDateTime(DateTime dateTime){
        return dateTime.minusDays(1);
    }

    /**
     * 日期转String
     * @param dateTime 日期
     * @param pattern 日期正则格式
     * @return
     */
    public static String parseToString(DateTime dateTime,String pattern){
       return  dateTime.toString(DateTimeFormat.forPattern(pattern));
    }

    /**
     * 获取股票日期格式字符串
     * @param dateTime
     * @return
     */
    public static String parseToString4Stock(DateTime dateTime){
        return parseToString(dateTime,"yyyyMMddHHmmss");
    }

    /**
     * 获取指定日期的收盘日期
     * @param dateTime
     * @return
     */
    public static DateTime getCloseDate(DateTime dateTime){
       return dateTime.withHourOfDay(14).withMinuteOfHour(58).withSecondOfMinute(0).withMillisOfSecond(0);
    }

    /**
     * 获取指定日期的开盘日期
     * @param dateTime
     * @return
     */
    public static DateTime getOpenDate(DateTime dateTime){
       return dateTime.withHourOfDay(9).withMinuteOfHour(30).withSecondOfMinute(0).withMillisOfSecond(0);
    }

    /**
     * 获取最近的股票有效时间，精确到分钟
     * @param target
     * @return
     */
    public static String getLastDateString4Stock(DateTime target){
        DateTime dateTime = getLastDate4Stock(target);
        dateTime=getDateTimeWithoutSecond(dateTime);
        return parseToString4Stock(dateTime);
    }
    /**
     * 获取最近的股票有效时间，精确到分钟
     * @param target
     * @return
     */
    public static DateTime getLastDate4Stock(DateTime target){
        //判断是否是工作日
        if (isWorkDay(target)) {
            //当前日期开盘前
            if (target.isBefore(getOpenDate(target))) {
                target=getCloseDate(getPreviousTradingDay(target));
            }else if (isMarketOffTime(target)){
                target=target.withHourOfDay(11).withMinuteOfHour(28).withSecondOfMinute(0).withMillisOfSecond(0);
            }else if (target.isAfter(getCloseDate(target))){
                //当前日期收盘后
                target=getCloseDate(target);
            }
        }else{
            //非工作日
            target=getCloseDate(getPreviousTradingDay(target));
        }
         target = getDateTimeWithoutSecond(target);
        return target;
    }

    /**
     * 判断当前时间是否在大盘的中午休盘时间段
     * @return
     */
    public static boolean isMarketOffTime(DateTime target){
        //上午休盘开始时间
        DateTime start = target.withHourOfDay(11).withMinuteOfHour(28).withSecondOfMinute(0).withMillisOfSecond(0);
        //下午开盘时间
        DateTime end = target.withHourOfDay(13).withMinuteOfHour(0).withSecondOfMinute(0).withMillisOfSecond(0);
        if (target.isAfter(start) && target.isBefore(end)) {
            return true;
        }
        return false;
    }

    /**
     * 将秒时归零
     * @param dateTime 指定日期
     * @return
     */
    public static DateTime getDateTimeWithoutSecond(DateTime dateTime){
        DateTime newDate = dateTime.withSecondOfMinute(0).withMillisOfSecond(0);
        return newDate;
    }

    /**
     * 将秒时归零
     * @param dateTime 指定日期字符串，格式必须是：yyyy-MM-dd HH:mm:ss
     * @return
     */
    public static DateTime getDateTimeWithoutSecond(String dateTime){
        DateTime parse = DateTime.parse(dateTime, DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
        return getDateTimeWithoutSecond(parse);
    }
}

常量数据封装
股票常用的公共参数非常多，我们可以在stock_common下把他们封装到一个Value Object(vo)对象下，并通过Spring为调用方动态赋值；
本小节我们把股票大盘编码信息配置到StockInfoConfig实体类下：

package com.itheima.pojo.vo;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author by itheima
 * @Date 2021/12/30
 * @Description: 定义股票相关的值对象封装
 */
@Data
@ConfigurationProperties(prefix = "stock")
public class StockInfoConfig {
    //A股大盘ID集合
    private List<String> inner;
    //外盘ID集合
    private List<String> outer;
}

在调用方stock_backend工程下定义application-stock.yml文件，并配置A股大盘和外盘的编码数据：

# 配置股票相关的参数
stock:
  inner: # A股
    - sh000001 # 上证ID
    - sz399001 #  深证ID
  outer: # 外盘
    - int_dji # 道琼斯
    - int_nasdaq # 纳斯达克
    - int_hangseng # 恒生
    - int_nikkei # 日经指数
    - b_FSSTI # 新加坡

同时在主配置文件application.yml中激活该配置：

1
2
3

spring:
   profiles:
           active: stock

在公共配置类中加载实体VO对象：

@EnableConfigurationProperties(StockInfoConfig.class)
@Configuration
public class CommonConfig {
    //省略N行
}

SQL语句分析

SELECT 
    market_code AS code,
    market_name AS name,
    open_point AS openPoint,
    cur_point AS curPoint,
    pre_close_point AS preClosePoint,
    trade_amount AS tradeAmt,
    trade_volume AS tradeVol,
    cur_point-pre_close_point AS upDown,
    (cur_point-pre_close_point)/pre_close_point AS rose,
    (max_point-min_point)/pre_close_point AS amplitude,
    cur_time AS curTime
FROM stock_market_index_info 
WHERE market_code IN ('sh000001','sz399001')
AND cur_time ='2021-12-28 09:31:00';

StockController

package com.itheima.controller;

import com.itheima.pojo.domain.InnerMarketDomain;
import com.itheima.service.StockService;
import com.itheima.vo.resq.R;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@Api(tags = "股票相关控制器")
@RequestMapping("/api/quot")
public class StockController {

    @Autowired
    private StockService stockService;
    @ApiOperation("获取国内大盘最新的数据")
    @GetMapping("/index/all")
    public R<List<InnerMarketDomain>> getInnerMarketInfo(){
        return stockService.getInnerMarketInfo();
    }
}

StockService

public interface StockService {
    //获取国内大盘最新的数据
    R<List<InnerMarketDomain>> getInnerMarketInfo();
}

package com.itheima.service.impl;

import com.itheima.mapper.StockMarketIndexInfoMapper;
import com.itheima.pojo.domain.InnerMarketDomain;
import com.itheima.pojo.vo.StockInfoConfig;
import com.itheima.service.StockService;
import com.itheima.utils.DateTimeUtil;
import com.itheima.vo.resq.R;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;

@Service
public class StockServiceImpl implements StockService {

    @Autowired
    private StockInfoConfig stockInfoConfig;
    @Autowired
    private StockMarketIndexInfoMapper stockMarketIndexInfoMapper;

    //获取国内大盘最新的数据
    @Override
    public R<List<InnerMarketDomain>> getInnerMarketInfo() {
        //1.获取股票最新的交易时间点(精确到时分，秒和毫秒设置为0)
        Date curDate = DateTimeUtil.getLastDate4Stock(DateTime.now()).toDate();
        //mock data 等后续完成股票采集job工程，再将代码删除即可
        curDate = DateTime.parse("2021-12-28 09:31:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
        //2.获取大盘编码集合
        List<String> mCodes = stockInfoConfig.getInner();
        //3.调用mapper查询数据
        List<InnerMarketDomain> data = stockMarketIndexInfoMapper.getMarketInfo(curDate,mCodes);
        //4.封装响应
        return R.ok(data);
    }
}

StockMarketIndexInfoMapper

1
2
3

//根据指定时间和大盘编码查询对象的时间
    // (指定时间点，大盘编码集合)
    List<InnerMarketDomain> getMarketInfo(@Param("curDate") Date curDate, @Param("marketCodes") List<String> mCodes);

<select id="getMarketInfo" resultType="com.itheima.pojo.domain.InnerMarketDomain">
    SELECT
        market_code AS code,
        market_name AS name,
        open_point AS openPoint,
        cur_point AS curPoint,
        pre_close_point AS preClosePoint,
        trade_amount AS tradeAmt,
        trade_volume AS tradeVol,
        cur_point-pre_close_point AS upDown,
        (cur_point-pre_close_point)/pre_close_point AS rose,
        (max_point-min_point)/pre_close_point AS amplitude,
        cur_time AS curTime
    FROM stock_market_index_info
    WHERE market_code IN
    <foreach collection="marketCodes" item="mCode"  open="(" separator="," close=")">
        #{mCode}
    </foreach>
      AND cur_time = #{curDate};
</select>

1.4前端端联调

2.板块指数功能实现【作业】

2.1需求分析

页面原型

stock_block_rt_info板块表分析：
code

2.2接口定义

需求说明: 查询沪深两市最新的板块行情数据，并按照交易金额降序排序展示前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//涨幅
        },
        //省略.......   
    ]
}

2.3接口实现

封装StockBlockDomain实体类

package com.itheima.pojo.domain;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import lombok.Data;

import java.math.BigDecimal;

@Data
@ApiModel("国内板块指数")
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 Data curDate;
    //交易总金额
    private BigDecimal tradeVol;
    //涨幅
    private BigDecimal updownRate;
}

国内板块指数SQL分析

SELECT 
    company_num as companyNum,
    trade_amount as tradeAmt,
    label        as code,
    avg_price    as avgPrice,
    block_name   as name,
    cur_time     as curDate,
    trade_volume as tradeVol,
    updown_rate  as updownRate
FROM stock_block_rt_info
where cur_time='2021-12-21 09:30:00'
order by trade_volume DESC
LIMIT 10

StockController

@ApiOperation("获取国内板块指数")
    @GetMapping("/sector/all")
    public R<List<StockBlockDomain>> sectorAll(){
        return stockService.sectorAllLimit();
    }

StockService

1	R<List<StockBlockDomain>> sectorAllLimit();

//获取国内板块指数
   @Override
   public R<List<StockBlockDomain>> sectorAllLimit() {
       //1.获取股票最新的交易时间点(精确到时分，秒和毫秒设置为0)
       Date curDate = DateTimeUtil.getLastDate4Stock(DateTime.now()).toDate();
       //mock data 等后续完成股票采集job工程，再将代码删除即可
       curDate = DateTime.parse("2021-12-21 09:30:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
       //根据当前时间查询数据
       List<StockBlockDomain> data = stockBlockRtInfoMapper.sectorAllLimit(curDate);
       return R.ok(data);
   }

StockBlockRtInfoMapper

1	List<StockBlockDomain> sectorAllLimit(Date curDate);

<select id="sectorAllLimit" resultType="com.itheima.pojo.domain.StockBlockDomain">
    SELECT
        company_num as companyNum,
        trade_amount as tradeAmt,
        label        as code,
        avg_price    as avgPrice,
        block_name   as name,
        cur_time     as curDate,
        trade_volume as tradeVol,
        updown_rate  as updownRate
    FROM stock_block_rt_info
    where cur_time= #{curDate}
    order by trade_volume DESC
        LIMIT 10
</select>

2.4前后端联调

code
code

3.涨幅榜更多数据功能

3.1需求分析

页面原型
stock_rt_info表相关数据
3.2接口定义
功能描述：分页查询最新股票交易时间点下沪深两市个股行情数据，并根据涨幅降序排序展示
服务路径：/api/quot/stock/all
服务方法：GET
请求参数：

参数说明	参数名称	是否必须	数据类型	备注
当前页	page	false	Integer	默认值：1
每页大小	pageSize	false	Integer	默认值：20

响应数据格式：

{
    "code": 1,
    "data": {
        "totalRows": 46750,//总行数
        "totalPages": 4675,//总页数
        "pageNum": 2,//当前页
        "pageSize": 10,//每页大小
        "size": 10,//当前页大小
        "rows": [
            {
                "tradeAmt": 4594802,//交易量
                "preClosePrice": 18.78,//前收盘价
                "amplitude": 0.059638,//振幅
                "code": "000004",//股票编码
                "name": "国华网安",//股票名称
                "curDate": "2021-12-30 10:20",//当前日期
                "tradeVol": 4594802,//交易金额
                "increase": 0.039936,//涨跌
                "upDown": 0.75,//涨幅
                "tradePrice": 19.53//当前价格
            },
           //省略......
        ]
    }
}

说明：
1.项目第一天已配置好PageHelper分页插件；
2.相关参数：
涨跌：当前价-前收盘价
涨幅：（当前价-前收盘价）/ 前收盘价 100%
振幅：（最高成交价-最低成交价）/ 前收盘价 100%

3.3接口实现

封装StockUpdownDomain实体类—-响应rows集合里面的数据

package com.itheima.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 股票涨跌信息
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class StockUpdownDomain {

    private String code;

    private String name;

    private BigDecimal preClosePrice;

    private BigDecimal tradePrice;

    private BigDecimal increase;

    private BigDecimal upDown;

    private BigDecimal amplitude;

    private Long tradeAmt;

    private BigDecimal tradeVol;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm")
    private Date curDate;
}

封装PageResult实体类—-响应实体类

package com.itheima.vo.resq;

import com.github.pagehelper.PageInfo;
import lombok.Data;
import java.io.Serializable;
import java.util.List;

/**
 * 分页工具类
 */
@Data
public class PageResult<T> implements Serializable {
    /**
     * 总记录数
     */
    private Long totalRows;

    /**
     * 总页数
     */
    private Integer totalPages;

    /**
     * 当前第几页
     */
    private Integer pageNum;
    /**
     * 每页记录数
     */
    private Integer pageSize;
    /**
     * 当前页记录数
     */
    private Integer size;
    /**
     * 结果集
     */
    private List<T> rows;

    /**
     * 分页数据组装
     * @param pageInfo
     * @return
     */
    public PageResult(PageInfo<T> pageInfo) {
        totalRows = pageInfo.getTotal();
        totalPages = pageInfo.getPages();
        pageNum = pageInfo.getPageNum();
        pageSize = pageInfo.getPageSize();
        size = pageInfo.getSize();
        rows = pageInfo.getList();
    }
}

涨幅榜更多SQL分析

SELECT 
    trade_amount as tradeAmt,
    pre_close_price as preClosePrice,
    (max_price- min_price)/pre_close_price as amplitude,
    stock_code as code,
    stock_name as name,
    cur_time   as curDate,
    trade_volume as tradeVol,
    (cur_price-pre_close_price) as increase,
    (cur_price- pre_close_price)/pre_close_price as upDown,
    cur_price as tradePrice
FROM stock_rt_info
WHERE cur_time = '2021-12-30 09:32:00'
order by upDown DESC

StockController

@GetMapping("/stock/all")
@ApiOperation("分页查询更多数据涨幅榜")
public R<PageResult<StockUpdownDomain>> getStockPageInfo(@RequestParam(name = "page",required = false,defaultValue = "1") Integer page,
                                                         @RequestParam(name = "pageSize",required = false,defaultValue = "20")Integer pageSize){
    return stockService.getStockPageInfo(page,pageSize);
}

StockService

1 2	//分页查询更多数据涨幅榜 R<PageResult<StockUpdownDomain>> getStockPageInfo(Integer page, Integer pageSize);

//分页查询更多数据涨幅榜
    @Override
    public R<PageResult<StockUpdownDomain>> getStockPageInfo(Integer page, Integer pageSize) {
        //1.获取股票最新的交易时间点(精确到时分，秒和毫秒设置为0)
        Date curDate = DateTimeUtil.getLastDate4Stock(DateTime.now()).toDate();
        //mock data 等后续完成股票采集job工程，再将代码删除即可
        curDate = DateTime.parse("2021-12-30 09:32:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
        //2.设置分页插件
        PageHelper.startPage(page,pageSize);
        //3.调用mapper查询数据
        List<StockUpdownDomain> pageDate = stockRtInfoMapper.getStockPageInfo(curDate);
        //4.组织PageResult对象
        PageInfo<StockUpdownDomain> pageInfo = new PageInfo<>(pageDate);
        PageResult<StockUpdownDomain> data = new PageResult<>(pageInfo);
        //5.响应返回
        return R.ok(data);
    }

StockRtInfoMapper

1 2	//查询指定时间点下的股票数据集合 List<StockUpdownDomain> getStockPageInfo(Date curDate);

<select id="getStockPageInfo" resultType="com.itheima.pojo.domain.StockUpdownDomain"
         parameterType="java.util.Date">
     SELECT
         trade_amount as tradeAmt,
         pre_close_price as preClosePrice,
         (max_price- min_price)/pre_close_price as amplitude,
         stock_code as code,
         stock_name as name,
         cur_time   as curDate,
         trade_volume as tradeVol,
         (cur_price-pre_close_price) as increase,
         (cur_price- pre_close_price)/pre_close_price as upDown,
         cur_price as tradePrice
     FROM stock_rt_info
     WHERE cur_time = #{curDate}
     order by upDown desc
 </select>

3.4前后端联调

4.涨幅榜功能实现(作业)

4.1需求分析

界面原型

stock_rt_info表相关数据
code

4.2接口定义

功能描述：统计沪深两市个股最新交易数据，并按涨幅降序排序查询前4条数据
服务路径：/api/quot/stock/increase
服务方法：GET
前端请求频率：每分钟
请求参数：无
响应数据格式：

{
    "code": 1,
    "data": [
        {
            "code": "000004",//股票编码
            "name": "国华网安",//股票名称
            "preClosePrice": 18.78,//前收盘价
            "tradePrice": 19.53//当前价格
             "tradeAmt": 4594802,//交易量
            "tradeVol": 4594802,//交易金额
            "increase": 0.039936,//涨跌
            "upDown": 0.75,//涨幅
            "amplitude": 0.059638,//振幅
            "curDate": "2021-12-30 10:30",//当前日期
        },
       //省略......
    ]
}

4.3接口实现

这个接口可以使用上面的方法实现，但是会减低效率，所有不用插件实现

StockController

@GetMapping("/stock/increase")
    @ApiOperation("获取涨幅榜的4条数据")
    public R<List<StockUpdownDomain>> getStockInfo(){
        return stockService.getStockInfo();
    }

StockService

1 2	//获取涨幅榜的4条数据 R<List<StockUpdownDomain>> getStockInfo();

//获取涨幅榜的4条数据
@Override
public R<List<StockUpdownDomain>> getStockInfo() {
    //1.获取股票最新的交易时间点(精确到时分，秒和毫秒设置为0)
    Date curDate = DateTimeUtil.getLastDate4Stock(DateTime.now()).toDate();
    //mock data 等后续完成股票采集job工程，再将代码删除即可
    curDate = DateTime.parse("2021-12-30 09:32:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
    //2.根据指定数据查询涨幅榜的4条数据
    List<StockUpdownDomain> data = stockRtInfoMapper.getStockInfoByfour(curDate);
    return R.ok(data);
}

StockRtInfoMapper

1	List<StockUpdownDomain> getStockInfoByfour(Date curDate);

<select id="getStockInfoByfour" resultType="com.itheima.pojo.domain.StockUpdownDomain">
    SELECT
        trade_amount as tradeAmt,
        pre_close_price as preClosePrice,
        (max_price- min_price)/pre_close_price as amplitude,
        stock_code as code,
        stock_name as name,
        cur_time   as curDate,
        trade_volume as tradeVol,
        (cur_price-pre_close_price) as increase,
        (cur_price- pre_close_price)/pre_close_price as upDown,
        cur_price as tradePrice
    FROM stock_rt_info
    WHERE cur_time = #{curDate}
    order by upDown DESC
        LIMIT 4
</select>

4.4前后端联调

5.涨跌停数据统计业务

5.1需求分析

界面原型
code

说明：
A股市场有涨幅±10%限制；
股票是否涨停和跌停并不以我们的统计结果为基准，而是由证券交易所来确定，可能真实情况是涨幅超过10%或者低于-10%；

5.2接口定义

功能描述：统计沪深两市T日(当前股票交易日)每分钟达到涨跌停股票的数据
注意：如果不在股票的交易日内，则统计最近的股票交易日下的数据
服务路径：/api/quot/stock/updown/count
服务方法：GET
前端请求频率：每分钟
请求参数：无
响应数据格式：

{
    "code": 1,
    "data": {
        "upList": [
            {
                "count": 1,//涨停数量
                "time": "202112311412"//当天分时
            },
           {
                "count": 3,//涨停数量
                "time": "202112311413"//当天分时
            },
              //省略......
        ],
        "downList": [
            {
                "count": 2,//跌停数量
                "time": "202112310925"//当天分时
            },
            //省略......
        ]
    }
}

总之，业务要求获取最新交易日下每分钟达到涨跌停数股票的数量；
关于SQL日期函数，详见：今日指数资料\V3\day03-股票数据报表与导出\资料\预习基础知识点\SQL日期函数.md

5.3接口实现

StockController

@GetMapping("/stock/export")
@ApiOperation("涨幅榜数据导出功能")
public void exportStockUpDownInfo(@RequestParam(name = "page",required = false,defaultValue = "1") Integer page,
                        @RequestParam(name = "pageSize",required = false,defaultValue = "20")Integer pageSize,
                        HttpServletResponse response){
    stockService.exportStockUpDownInfo(page,pageSize,response);
}

StockService

1 2	//统计最新股票交易日内股票涨跌停的股票数量 R<Map<String, List>> getStockUpDownCount();

//统计最新股票交易日内股票涨跌停的股票数量
@Override
public R<Map<String, List>> getStockUpDownCount() {
    //1.获取股票最新的交易时间点(截至时间)
    DateTime curDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
    //假数据
    curDateTime = DateTime.parse("2022-01-06 14:25:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
    Date endDate = curDateTime.toDate();
    //2.获取对应的开盘时间
    Date startDate = DateTimeUtil.getOpenDate(curDateTime).toDate();
    //3.统计涨停数据
    List<Map> upList = stockRtInfoMapper.getStockUpDownCount(startDate,endDate,1);
    //4.统计跌停数据
    List<Map> downList = stockRtInfoMapper.getStockUpDownCount(startDate,endDate,0);
    //5.组装数据
    HashMap<String,List> info = new HashMap<>();
    info.put("upList",upList);
    info.put("downList",downList);
    //6.响应数据
    return R.ok(info);
}

sql分析

功能描述：统计沪深两市T日(当前股票交易日)每分钟达到涨跌停股票的数据
注意：如果不在股票的交易日内，则统计最近的股票交易日下的数据

分析业务：

相关的表：stock_rt_info 股票流水表

条件：以查询涨停为例：

         1. 统计指定日期范围内的股票数据，从开盘到最新股票交易时间点 between and
         2. 涨停与涨幅有关，而涨幅与股票的当前价，前收盘价有关， 获取涨幅>=0.1
         3. 根据分钟分组，然后使用count函数统计对应的股票数量

返回字段：time count

实现：

先查询达到涨停的涨幅流水

SELECT
 (cur_price - pre_close_price) / pre_close_price AS ud,
 date_format(cur_time,'%Y%m%d%H%i') AS time
FROM
 stock_rt_info
WHERE cur_time BETWEEN '2022-01-06 09:30:00' and '2022-01-06 14:25:00'
 HAVING ud >= 0.1

将步骤一的结果作为一张表，然后统计每分钟对应的行记录数

1	SELECT tmp.time,COUNT(*) AS count FROM () AS tmp GROUP BY tmp.cur_time;

合并sql

SELECT tmp.time, COUNT(*) AS count
FROM (
SELECT
 (cur_price - pre_close_price) / pre_close_price AS ud, DATE_FORMAT(cur_time,'%Y%m%d%H%i') AS time
FROM
 stock_rt_info
WHERE cur_time BETWEEN '2022-01-06 09:30:00' AND '2022-01-06 14:25:00'
HAVING ud >= 0.1) AS tmp
GROUP BY tmp.time;

跌停

SELECT tmp.time,COUNT(*) AS count FROM (SELECT
 (cur_price - pre_close_price) / pre_close_price AS ud,
 date_format(cur_time,'%Y%m%d%H%i') AS time
FROM
 stock_rt_info
WHERE cur_time BETWEEN '2022-01-06 09:30:00' and '2022-01-06 14:25:00'
 HAVING ud <= -0.1) AS tmp GROUP BY tmp.time;

StockRtInfoMapper

/**
 * 统计最新股票交易日内股票涨跌停的股票数量
 * @param startDate 开始时间
 * @param endDate   结束时间
 * @param flag      约定：1代表统计涨停 0代表统计跌停
 * @return
 */
List<Map> getStockUpDownCount(@Param("startDate") Date startDate, @Param("endDate") Date endDate, @Param("flag") int flag);

<select id="getStockUpDownCount" resultType="map">
    SELECT tmp.time, COUNT(*) AS count
    FROM (
        SELECT
        (cur_price - pre_close_price) / pre_close_price AS ud,
        DATE_FORMAT(cur_time,'%Y%m%d%H%i') AS time
        FROM
        stock_rt_info
        WHERE cur_time BETWEEN #{startDate} AND #{endDate}
        HAVING ud
    <if test="flag == 1">
        >= 0.1
    </if>
    <if test="flag == 0">
        &lt;= -0.1
    </if>
    ) AS tmp
    GROUP BY tmp.time
</select>

5.4前后端联调

code
code

6.EasyExcel使用

传统操作Excel大多都是利用Apach POI进行操作的，但是POI框架并不完善，使用过程非常繁琐且有较多的缺陷：

动态操作Excel非常繁琐,对于新手来说，很难在短时间内上手;
读写时需要占用较大的内存，当数据量大时容易发生内存溢出问题（OOM）;
基于上述原因，阿里开源出一款易于上手，且比较节省内存的Excel框架：EasyExcel

注意：easyExcel底层也是使用POI实现的；
官网地址：https://www.yuque.com/easyexcel/doc/easyexcel

依赖资源：

<!--引入easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.4</version>
</dependency>

6.1EasyExcel导出/导入数据快速入门

构建测试实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class User implements Serializable {
    private String userName;
    private Integer age;
    private String address;
    private Date birthday;
}

数据导出到excel

public class TestEasyExcel {

    public List<User> init(){
        //组装数据
        ArrayList<User> users = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setAddress("上海"+i);
            user.setUserName("张三"+i);
            user.setBirthday(new Date());
            user.setAge(10+i);
            users.add(user);
        }
        return users;
    }
    /**
     * 直接导出后，表头名称默认是实体类中的属性名称
     */
    @Test
    public void test02(){
        List<User> users = init();
        //不做任何注解处理时，表头名称与实体类属性名称一致
        EasyExcel.write("C:\\Users\\46035\\Desktop\\ex\\用户.xls",User.class).sheet("用户信息").doWrite(users);
    }
}

导入数据excel

/**
    * excel数据格式必须与实体类定义一致，否则数据读取不到
    */
   @Test
   public void readExcel(){
       ArrayList<User> users = new ArrayList<>();
       //逐行读取excel内容，并封(读一行，调用一次)
       EasyExcel.read("C:\\Users\\46035\\Desktop\\ex\\用户.xls", User.class, new AnalysisEventListener<User>() {
           @Override
           public void invoke(User o, AnalysisContext analysisContext) {
               System.out.println(o);
               users.add(o);
           }
           @Override
           public void doAfterAllAnalysed(AnalysisContext analysisContext) {
               System.out.println("完成。。。。");
           }
       }).sheet().doRead();
       System.out.println(users);
   }

6.2导出数据的高级设置

自定义表头

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
/**
  * 通过注解自定义表头名称 注解添加排序规则，值越大 越靠近右边
*/
public class User implements Serializable {
    @ExcelProperty(value = {"用户名"},index = 1)
    private String userName;
    @ExcelProperty(value = {"年龄"},index = 2)
    private Integer age;
    @ExcelProperty(value = {"地址"} ,index = 4)
    private String address;
    @ExcelProperty(value = {"生日"},index = 3)
    private Date birthday;
}

code

自定义日期格式

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class User implements Serializable {
    @ExcelProperty(value = {"用户名"},index = 1)
    private String userName;
    @ExcelProperty(value = {"年龄"},index = 2)
    private Integer age;
    @ExcelProperty(value = {"地址"} ,index = 4)
    private String address;
    @ExcelProperty(value = {"生日"},index = 3)
    //注意：日期格式注解由alibaba.excel提供
    @DateTimeFormat("yyyy/MM/dd HH:mm")
    private Date birthday;
}

code

合并表头

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class User implements Serializable {
    @ExcelProperty(value = {"用户基本信息","用户名"},index = 1)
    private String userName;
    @ExcelProperty(value = {"用户基本信息","年龄"},index = 2)
    private Integer age;
    @ExcelProperty(value = {"用户基本信息","地址"} ,index = 4)
    private String address;
    @ExcelProperty(value = {"用户基本信息","生日"},index = 3)
    //注意：日期格式注解由alibaba.excel提供
    @DateTimeFormat("yyyy/MM/dd HH:mm")
    private Date birthday;
}

code

忽略指定表头信息

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class User implements Serializable {
    @ExcelProperty(value = {"用户基本信息","用户名"},index = 1)
    @ExcelIgnore
    private String userName;
    @ExcelProperty(value = {"用户基本信息","年龄"},index = 2)
    private Integer age;
    @ExcelProperty(value = {"用户基本信息","地址"} ,index = 4)
    private String address;
    @ExcelProperty(value = {"用户基本信息","生日"},index = 3)
    //注意：日期格式注解由alibaba.excel提供
    @DateTimeFormat("yyyy/MM/dd HH:mm")
    private Date birthday;
}

code

设置单元格大小

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@HeadRowHeight(value = 35) // 表头行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 50) // 列宽
public class User implements Serializable {
    @ExcelProperty(value = {"用户基本信息","用户名"},index = 1)
    @ExcelIgnore
    private String userName;
    @ExcelProperty(value = {"用户基本信息","年龄"},index = 2)
    private Integer age;
    @ExcelProperty(value = {"用户基本信息","地址"} ,index = 4)
    private String address;
    @ExcelProperty(value = {"用户基本信息","生日"},index = 3)
    //注意：日期格式注解由alibaba.excel提供
    @DateTimeFormat("yyyy/MM/dd HH:mm")
    private Date birthday;
}

code

7.涨幅榜数据导出功能

7.1需求分析

code

7.2接口定义

数据导出接口说明
功能说明：将分页涨幅榜下指定页码的数据导出到excel表格下
请求地址：/api/quot/stock/export
请求方式：GET
请求参数：

参数名称	参数说明	是否必须	数据类型	备注
当前页	page	false	Integer	默认值：1
每页大小	pageSize	false	Integer	默认值：20

响应：excel格式的文件流

7.3接口实现

导入easyExcel依赖

<!--引入easyExcel-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
</dependency>

调整StockUpdownDomain实体类：

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class StockUpdownDomain {
    @ExcelProperty(value = {"股票涨幅信息统计表","股票编码"},index = 0)
    private String code;

    @ExcelProperty(value = {"股票涨幅信息统计表","股票名称"},index = 1)
    private String name;

    @ExcelProperty(value = {"股票涨幅信息统计表","前收盘价格"},index = 2)
    private BigDecimal preClosePrice;

    @ExcelProperty(value = {"股票涨幅信息统计表","当前价格"},index= 3)
    private BigDecimal tradePrice;

    @ExcelProperty(value = {"股票涨幅信息统计表","涨跌"},index= 4)
    private BigDecimal increase;

    @ExcelProperty(value = {"股票涨幅信息统计表","涨幅"},index= 5)
    private BigDecimal upDown;

    @ExcelProperty(value = {"股票涨幅信息统计表","振幅"},index= 6)
    private BigDecimal amplitude;

    @ExcelProperty(value = {"股票涨幅信息统计表","交易总量"},index = 7)
    private Long tradeAmt;

    @ExcelProperty(value = {"股票涨幅信息统计表","交易总金额"},index = 8)
    private BigDecimal tradeVol;

    @ExcelProperty(value = {"股票涨幅信息统计表","日期"},index = 9)
    @DateTimeFormat("yyy-MM-dd HH:mm")//easyExcel的注解-》excel
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm")//springmvc支持的注解-》json格式数据
    private Date curDate;
}

StockController

@GetMapping("/stock/export")
@ApiOperation("涨幅榜数据导出功能")
public void exportStockUpDownInfo(@RequestParam(name = "page",required = false,defaultValue = "1") Integer page,
                        @RequestParam(name = "pageSize",required = false,defaultValue = "20")Integer pageSize,
                        HttpServletResponse response){
    stockService.exportStockUpDownInfo(page,pageSize,response);
}

StockService

1 2	//涨幅榜数据导出功能 void exportStockUpDownInfo(Integer page, Integer pageSize, HttpServletResponse response);

//涨幅榜数据导出功能
    @Override
    public void exportStockUpDownInfo(Integer page, Integer pageSize, HttpServletResponse response) {
        //1.获取分页数据
        R<PageResult<StockUpdownDomain>> r = this.getStockPageInfo(page, pageSize);
        List<StockUpdownDomain> rows = r.getData().getRows();
        //2.将数据导出到excel中
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系(默认名称)
        try {
            String fileName = URLEncoder.encode("股票信息表", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), StockUpdownDomain.class).sheet("股票涨幅信息").doWrite(rows);
        } catch (IOException e) {
            log.error("当前页码:{},每页大小:{},当前数据:{},异常信息:{}",page,pageSize,
                    DateTime.now().toString("yyyy-MM-dd HH:mm:ss"),e.getMessage());
            //通知前端异常，稍后重试
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            R<Object> error = R.error(ResponseCode.ERROR);
            try {
                String jsonData = new ObjectMapper().writeValueAsString(error);
                response.getWriter().write(jsonData);
            } catch (IOException ex) {
                log.error("响应错误信息失败，时间:{}",DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
            }

        }

7.4前后端联调

code

8.股票成交量对比

8.1需求分析

界面原型

8.2接口定义

功能描述：统计A股大盘T日和T-1日成交量对比功能（成交量为沪深两市成交量之和）
服务路径：/api/quot/stock/tradeAmt
服务方法：GET
前端请求频率：每分钟
请求参数：无
返回数据格式：

{
    "code": 1,
    "data": {
        "amtList": [{"count": 3926392,"time": "202112310930"},{"count": 3926392,"time": "202112310931"}，...],//T日每分钟成交量信息
        "yesAmtList":[{"count": 3926392,"time": "202112310930"},...]//T-1日每分钟成交量信息 
    }
}

注意事项：如果当前日期不在股票交易日，则按照前一个有效股票交易日作为T日查询
R>>

8.3接口实现

StockController

@GetMapping("/stock/tradeAmt")
@ApiOperation("统计大盘T日和T-1日每分钟交易量的统计")
public R<Map<String,List>> getCompareStockTradeAmt(){
    return stockService.getCompareStockTradeAmt();
}

StockService

1 2	//统计大盘T日和T-1日每分钟交易量的统计 R<Map<String, List>> getCompareStockTradeAmt();

//统计大盘T日和T-1日每分钟交易量的统计
    @Override
    public R<Map<String, List>> getCompareStockTradeAmt() {
        //1.获取T日(最新股票交易日的日期访问)
        DateTime tEndDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
        //假数据
        tEndDateTime = DateTime.parse("2022-01-03 14:40:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
        Date tEndDate = tEndDateTime.toDate();
        //开盘时间
        Date tStartDate = DateTimeUtil.getOpenDate(tEndDateTime).toDate();
        //2.获取T-1日的时间访问
        DateTime preEndDateTime = DateTimeUtil.getPreviousTradingDay(tEndDateTime);
        //假数据
        preEndDateTime = DateTime.parse("2022-01-02 14:40:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
        Date preEndDate = preEndDateTime.toDate();
        //开盘时间
        Date preStartDate = DateTimeUtil.getOpenDate(preEndDateTime).toDate();
        //3.调用mapper查询
        //3.1统计T日
        List<Map> tDate = stockMarketIndexInfoMapper.getSumAmtInfo(tStartDate,tEndDate,stockInfoConfig.getInner());
        //3.2统计T-1日
        List<Map> preDate = stockMarketIndexInfoMapper.getSumAmtInfo(preStartDate,preEndDate,stockInfoConfig.getInner());
        //4.组装数据
        HashMap<String,List> info = new HashMap<>();
        info.put("amtList",tDate);
        info.put("yesAmtList",preDate);
        //5.响应数据
        return R.ok(info);
    }

sql分析

SELECT
    date_format(cur_time,'%Y%m%d%H%i') as time,
    SUM(trade_amount) AS count
FROM
    stock_market_index_info
WHERE
    cur_time BETWEEN '2022-01-03 09:30:00' and '2022-01-03 14:40:00'
  AND market_code in ('sh000001','sz399001')
GROUP BY TIME

StockMarketIndexInfoMapper

/**
     * 统计指定日期范围内，指定大盘每分钟的成交量流水信息
     * @param openDate  开盘时间
     * @param endDate   截至时间
     * @param marketCodes   大盘编码集合
     * @return
     */
    List<Map> getSumAmtInfo(@Param("openDate") Date openDate,
                            @Param("endDate") Date endDate,
                            @Param("marketCodes") List<String> marketCodes);

<select id="getSumAmtInfo" resultType="map">
    SELECT
        date_format(cur_time,'%Y%m%d%H%i') as time,
        SUM(trade_amount) AS count
    FROM
        stock_market_index_info
    WHERE
        cur_time BETWEEN #{openDate} AND #{endDate}
      AND market_code IN
        <foreach collection="marketCodes" item="mCode"  open="(" separator="," close=")">
        #{mCode}
        </foreach>
    GROUP BY TIME
</select>

8.4前后端联调

9.个股分时涨跌幅度统计功能

9.1需求分析

code

9.2接口定义

功能描述：统计当前时间下（精确到分钟），A股在各个涨跌区间股票的数量；
服务路径：/api/quot/stock/updown
服务方法：GET
前端请求频率：每分钟
请求参数：无

注意事项：如果当前不在股票有效时间内，则以最近最新的一个有效股票交易日作为查询时间点展示；

响应数据格式：

{
    "code": 1,
    "data": {
        "time": "2021-12-31 14:58:00",
        "infos": [
            {
                "count": 17,
                "title": "-3~0%"
            },
            {
                "count": 2,
                "title": "-5~-3%"
            },
            //省略......
        ]
    }
}

9.3接口实现

StockController

@GetMapping("/stock/updown")
@ApiOperation("统计最新交易时间点下股票(A股)在各个涨幅区间的数量")
public R<Map> getIncreseRangeInfo(){
    return stockService.getIncreseRangeInfo();
}

StockService

1 2	//统计最新交易时间点下股票(A股)在各个涨幅区间的数量 R<Map> getIncreseRangeInfo();

//统计最新交易时间点下股票(A股)在各个涨幅区间的数量
    @Override
    public R<Map> getIncreseRangeInfo() {
        //1.获取股票最新的交易时间点(截至时间)
        DateTime curDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
        //假数据
        curDateTime = DateTime.parse("2022-01-06 09:55:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
        Date curDate = curDateTime.toDate();
        //2.调用mapper获取数据
        List<Map> infos = stockRtInfoMapper.getIncreseRangeInfoByDate(curDate);
        //3.组装数据
        HashMap<String,Object> data= new HashMap<>();
        data.put("time",curDateTime.toString("yyyy-MM-dd HH:mm:ss"));
        data.put("infos",infos);
        //4.响应
        return R.ok(data);
    }

sql分析
code

-- 整体思路：先统计当前时间点下每支股票的涨幅和时间集合，然后再将结果子查询将涨幅值转换成涨幅区间名称，
-- 最后再根据涨幅区间分组统计每一组对应的数量即可
-- 步骤1：统计当前时间下，每只股票的涨幅值
SELECT (cur_price - pre_close_price)/pre_close_price AS rate 
FROM stock_rt_info 
WHERE cur_time = '2022-01-06 09:55:00'

-- 步骤2：将步骤1的查询结果中数据转换为区间范围集合
select
        CASE
            WHEN tmp.rate > 0.07 THEN  '>7%'
            WHEN tmp.rate > 0.05  AND tmp.rate <= 0.07 THEN '5~7%'
            WHEN tmp.rate > 0.03  AND tmp.rate <= 0.05 THEN '3~5%'
            WHEN tmp.rate > 0     AND tmp.rate <= 0.03 THEN '0~3%'
            WHEN tmp.rate > -0.03 AND tmp.rate <= 0 THEN '-3~0%'
            WHEN tmp.rate > -0.05 AND tmp.rate <= -0.03 THEN '-5~-3%'
            WHEN tmp.rate > -0.07 AND tmp.rate <= -0.05 THEN '-7~-5%'
            ELSE '<-7%'
        END 'title'
from
    (
        select
            (cur_price-pre_close_price)/pre_close_price as rate
        from stock_rt_info 
        where cur_time='2022-01-06 09:55:00'
    )as tmp

-- 3.根据区间分组，统计各个区间数据量
select
    tmp2.title,
    count(*) as count
from
(select
    CASE
        WHEN tmp.rate > 0.07 THEN  '>7%'
        WHEN tmp.rate > 0.05 AND tmp.rate <= 0.07 THEN '5~7%'
        WHEN tmp.rate > 0.03 AND tmp.rate <= 0.05 THEN '3~5%'
        WHEN tmp.rate > 0 AND tmp.rate <= 0.03 THEN '0~3%'
        WHEN tmp.rate > -0.03 AND tmp.rate <= 0 THEN '-3~0%'
        WHEN tmp.rate > -0.05 AND tmp.rate <= -0.03 THEN '-5~-3%'
        WHEN tmp.rate > -0.07 AND tmp.rate <= -0.05 THEN '-7~-5%'
        ELSE '<-7%'
    END 'title'
from
(select
(cur_price-pre_close_price)/pre_close_price as rate
from stock_rt_info 
where cur_time='2022-01-06 09:55:00')
as tmp)
as tmp2 group by tmp2.title;

StockRtInfoMapper

/**
 * 统计指定时间点下股票在各个涨跌区间的数量
 * @param curDate
 * @return
 */
List<Map> getIncreseRangeInfoByDate(@Param("curDate") Date curDate);

<!--如果在XML中SQL语句遇到大量特殊字符需要转义，比如：< 等，建议使用<![CDATA[ sql 语句 ]]>标记，这样特殊字符就不会被解析器解析，所以最终xml方式-->
<select id="getIncreseRangeInfoByDate" resultType="map">
    <![CDATA[
    select
    tmp2.title,
    count(*) as count
    from
    (select
        CASE
        WHEN tmp.rate > 0.07 THEN  '>7%'
        WHEN tmp.rate > 0.05 AND tmp.rate <= 0.07 THEN '5~7%'
        WHEN tmp.rate > 0.03 AND tmp.rate <= 0.05 THEN '3~5%'
        WHEN tmp.rate > 0 AND tmp.rate <= 0.03 THEN '0~3%'
        WHEN tmp.rate > -0.03 AND tmp.rate <= 0 THEN '-3~0%'
        WHEN tmp.rate > -0.05 AND tmp.rate <= -0.03 THEN '-5~-3%'
        WHEN tmp.rate > -0.07 AND tmp.rate <= -0.05 THEN '-7~-5%'
        ELSE '<-7%'
        END 'title'
    from
    (select
         (cur_price-pre_close_price)/pre_close_price as rate
    from stock_rt_info
    where cur_time='2022-01-06 09:55:00')as tmp)as tmp2
    group by tmp2.title;
    ]]>
</select>

9.4前后端联调

code
code

9.5遇到的问题

前端查询的数据是无序展示的，涨幅区间应该从小到大顺序展示;
当前涨幅区间下如果没有对应的股票，则区间标题不会被展示，我们需要对无数据的区间默认为0给前端显示；

最终效果
实现思路分析

说明：
1.先顺序定义一个包含区间范围标题的有序集合；
2.遍历有序集合，然后从实际查询结果中找出各自的区间值，如果没有则以0补齐；
3.遍历过程形成新的集合，包可以顺序性保证数据有序且完整；

在application-stock.yml中顺序添加股票涨幅区间信息：

# 配置股票相关的参数
stock:
  upDownRange:
    - "<-7%"
    - "-7~-5%"
    - "-5~-3%"
    - "-3~0%"
    - "0~3%"
    - "3~5%"
    - "5~7%"
    - ">7%"

在stock_common工程下为StockInfoConfig类补齐配置：

@Data
@ConfigurationProperties(prefix = "stock")
public class StockInfoConfig {
    //a股大盘ID集合
    private List<String> inner;
    //外盘ID集合
    private List<String> outer;
    //股票区间
    private List<String> upDownRange;
}

完善过滤实现

//统计最新交易时间点下股票(A股)在各个涨幅区间的数量
    @Override
    public R<Map> getIncreseRangeInfo() {
        //1.获取股票最新的交易时间点(截至时间)
        DateTime curDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
        //假数据
        curDateTime = DateTime.parse("2022-01-06 09:55:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
        Date curDate = curDateTime.toDate();
        //2.调用mapper获取数据
        List<Map> infos = stockRtInfoMapper.getIncreseRangeInfoByDate(curDate);
        //2.1 获取有序的标题集合
        List<String> orderSections = stockInfoConfig.getUpDownRange();
       //思路：利用List集合的属性，然后顺序编译，找出每个标题对应的map，然后维护到一个新的List集合下即可
       List<Map> orderMaps =new ArrayList<>();
       for (String title : orderSections) {
           Map map=null;
           for (Map m : infos) {
               if (m.containsValue(title)) {
                   map=m;
                   break;
               }
           }
           if (map==null) {
               map=new HashMap();
               map.put("count",0);
               map.put("title",title);
           }
           orderMaps.add(map);
       }
        //3.组装数据
        HashMap<String,Object> data= new HashMap<>();
        data.put("time",curDateTime.toString("yyyy-MM-dd HH:mm:ss"));
        data.put("infos",orderMaps);
        //4.响应
        return R.ok(data);
    }

//统计最新交易时间点下股票(A股)在各个涨幅区间的数量
    @Override
    public R<Map> getIncreseRangeInfo() {
        //1.获取股票最新的交易时间点(截至时间)
        DateTime curDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
        //假数据
        curDateTime = DateTime.parse("2022-01-06 09:55:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
        Date curDate = curDateTime.toDate();
        //2.调用mapper获取数据
        List<Map> infos = stockRtInfoMapper.getIncreseRangeInfoByDate(curDate);
        //2.1 获取有序的标题集合
        List<String> orderSections = stockInfoConfig.getUpDownRange();
        //方式2：使用lambda表达式指定
        List<Map> orderMaps  =  orderSections.stream().map(title->{
            Map mp=null;
            Optional<Map> op = infos.stream().filter(m -> m.containsValue(title)).findFirst();
            //判断是否存在符合过滤条件的元素
            if (op.isPresent()) {
                mp=op.get();
            }else{
                mp=new HashMap();
                mp.put("count",0);
                mp.put("title",title);
            }
            return mp;
        }).collect(Collectors.toList());
        //3.组装数据
        HashMap<String,Object> data= new HashMap<>();
        data.put("time",curDateTime.toString("yyyy-MM-dd HH:mm:ss"));
        data.put("infos",orderMaps);
        //4.响应
        return R.ok(data);
    }

10.个股分时K线行情功能

10.1需求分析

code
code

10.2接口定义

功能描述：查询个股的分时行情数据，也就是统计指定股票T日每分钟的交易数据；
服务路径：/api/quot/stock/screen/time-sharing
服务方法：GET
前端请求频率：每分钟请求
请求参数：code

参数说明	参数名称	是否必须	数据类型	备注
股票编码	code	true	string	股票编码

返回数据格式：

{
    "code": 1,
    "data": [
        {
            "date": "2021-12-31 09:25",//当前时间，精确到分钟
            "tradeAmt": 63263,//当前交易量
            "code": "000021",//股票编码
            "lowPrice": 15.85,//最低价格
            "preClosePrice": 15.85,//前收盘价格
            "name": "深科技",//股票名称
            "highPrice": 15.85,//最高价格
            "openPrice": 15.85,//开盘价
            "tradeVol": 1002718.55,//交易金额
            "tradePrice": 15.85//当前价格（最新价格）
        },
        //......
          ]
}

10.3接口实现

在stock_common工程下添加实体类：

/**
 * @author by itheima
 * @Date 2022/2/28
 * @Description 个股分时数据封装
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Stock4MinuteDomain {
    /**
     * 日期，eg:202201280809
     */
   @JsonFormat(pattern = "yyyy-MM-dd HH:mm",timezone = "Asia/Shanghai")
   private Date date;
    /**
     * 交易量
     */
   private Long tradeAmt;
    /**
     * 股票编码
     */
   private String code;
    /**
     * 最低价
     */
   private BigDecimal lowPrice;
    /**
     * 前收盘价
     */
   private BigDecimal preClosePrice;
    /**
     * 股票名称
     */
   private String name;
    /**
     * 最高价
     */
   private BigDecimal highPrice;
    /**
     * 开盘价
     */
   private BigDecimal openPrice;

    /**
     * 当前交易总金额
     */
   private BigDecimal tradeVol;
    /**
     * 当前价格
     */
   private BigDecimal tradePrice;
}

StockController

@GetMapping("/stock/screen/time-sharing")
@ApiOperation("获取指定股票T日的分时数据")
public R<List<Stock4MinuteDomain>> getStockScreenTimeSharing(@RequestParam(value = "code",required = true) String stockCode){
    return stockService.getStockScreenTimeSharing(stockCode);
}

StockService

1 2	//获取指定股票T日的分时数据 R<List<Stock4MinuteDomain>> getStockScreenTimeSharing(String stockCode);

//获取指定股票T日的分时数据
@Override
public R<List<Stock4MinuteDomain>> getStockScreenTimeSharing(String stockCode) {
    //1.获取T日最新股票交易时间点 endTime
    DateTime endDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
    //假数据
    endDateTime = DateTime.parse("2021-12-30 14:30:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
    Date endData = endDateTime.toDate();
    Date openDate = DateTimeUtil.getOpenDate(endDateTime).toDate();
    //2.调用mapper
    List<Stock4MinuteDomain> data = stockRtInfoMapper.getStock4MinuteInfo(openDate,endData,stockCode);
    //3.返回数据
    return R.ok(data);
}

sql分析

-- 分析：查询个股分时K线图，说白了就是查询指定股票在当前交易日产生的流水数据报表展示
-- 综合条件：1.股票ID 2.股票开盘时间 3.当前时间点
select
    sri.cur_time     as date,
    sri.trade_amount as tradeAmt,
    sri.stock_code as code,
    sri.min_price lowPrice,
    sri.pre_close_price as preClosePrice,
    sri.stock_name as name,
    sri.max_price as highPrice,
    sri.open_price as openPrice,
    sri.trade_volume as tradeVol,
    sri.cur_price as tradePrice
from stock_rt_info as sri
where    sri.stock_code='600021'
and sri.cur_time between '2021-12-30 09:30:00' and '2021-12-30 14:30:00';

StockRtInfoMapper

/**
 * 根据股票编码查询指定时间范围内的分时数据
 * @param openDate  开盘时间
 * @param endData   截至时间
 * @param stockCode 股票编码
 * @return
 */
List<Stock4MinuteDomain> getStock4MinuteInfo(@Param("openDate") Date openDate, @Param("endData") Date endData, @Param("stockCode") String stockCode);

<select id="getStock4MinuteInfo" resultType="com.itheima.pojo.domain.Stock4MinuteDomain">
    select
        cur_time  as date,
        trade_amount as tradeAmt,
        stock_code as code,
        min_price lowPrice,
        pre_close_price as preClosePrice,
        stock_name as name,
        max_price as highPrice,
        open_price as openPrice,
        trade_volume as tradeVol,
        cur_price as tradePrice
    from stock_rt_info
    where  stock_code= #{stockCode}
      and cur_time between #{openDate} and #{endData};
</select>

10.4前后端联调

11.个股日K线详情功能

11.1需求分析

code

说明：
1.日K线就是将股票交易流水按天分组，然后统计出每天的交易数据，内容包含：日期、股票编码、名称、最高价、最低价、开盘价、收盘价、前收盘价、交易量；
2.需要注意的是这里的收盘价就是指每天最大交易时间点下对应的价格；

11.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//前收盘价
        },
        //......
    ]
}

11.3接口实现

在stock_common工程添加实体类：

/**
 * @author by itheima
 * @Date 2022/2/28
 * @Description 个股日K数据封装
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Stock4EvrDayDomain {
    /**
     * 日期，eg:202201280809
     */
   @JsonFormat(pattern = "yyyy-MM-dd HH:mm",timezone = "Asia/Shanghai")
   private Date date;
    /**
     * 交易量
     */
   private Long tradeAmt;
    /**
     * 股票编码
     */
   private String code;
    /**
     * 最低价
     */
   private BigDecimal lowPrice;
    /**
     * 股票名称
     */
    private String name;
    /**
     * 最高价
     */
    private BigDecimal highPrice;
    /**
     * 开盘价
     */
    private BigDecimal openPrice;
    /**
     * 当前交易总金额
     */
    private BigDecimal tradeVol;
    /**
     * 当前收盘价格指收盘时的价格，如果当天未收盘，则显示最新cur_price）
     */
    private BigDecimal closePrice;
    /**
     * 前收盘价
     */
   private BigDecimal preClosePrice;
}

StockController

@GetMapping("/stock/screen/dkline")
@ApiOperation("统计指定股票的日K线数据")
public R<List<Stock4EvrDayDomain>> getStockScreenDkLine(@RequestParam(value = "code",required = true) String stockCode) {
    return stockService.getStockScreenDkLine(stockCode);
}

StockService

1 2	//统计指定股票的日K线数据 R<List<Stock4EvrDayDomain>> getStockScreenDkLine(String stockCode);

//统计指定股票的日K线数据
@Override
public R<List<Stock4EvrDayDomain>> getStockScreenDkLine(String stockCode) {
    //1.获取统计日K线的数据的数据访问
    //1.1获取截止时间
    DateTime endDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
    //假数据
    endDateTime = DateTime.parse("2022-06-06 14:25:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
    Date endData = endDateTime.toDate();
    //1.2起始时间
    DateTime startDataTime = endDateTime.minusMonths(3);
    startDataTime = DateTime.parse("2022-01-01 09:30:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
    Date startDate = startDataTime.toDate();
    //2.调用mapper,获取指定日期范围内的日K线数据
    List<Stock4EvrDayDomain> dkLineData = stockRtInfoMapper.getStock4DkLine(startDate,endData,stockCode);
    //3.返回
    return R.ok(dkLineData);
}

sql分析

-- 说明：因为在股票流水中，开盘价、最高价、最低价、当前价等信息在每条记录中都会记录，所以我们更加关注的是每天的收盘价格，业务要求如果当前没有收盘，则以最新价格作为收盘价，所以该业务就可以转化成查询每天最大交易时间对应的信息；
-- 步骤1：查询指定股票在指定日期范围内每天的最大时间，说白了就是以天分组，求每天最大时间
select
    max( sri.cur_time ) as closeDate 
from
    stock_rt_info as sri 
where
    sri.stock_code ='600021' 
    and sri.cur_time between '2022-01-01 09:30:00' and '2022-01-06 14:25:00' 
group by
    date_format( sri.cur_time, '%Y%m%d' )
-- 步骤2：以步骤1查询结果作为条件，同统计指定时间点下，股票的数据信息
select
    sri2.cur_time 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 preClosePrice
from
    stock_rt_info as sri2
where sri2.stock_code='600021'  and sri2.cur_time in (
  select
    max( sri.cur_time ) as closeDate 
  from
    stock_rt_info as sri 
  where
    sri.stock_code ='600021' 
    and sri.cur_time between '2022-01-01 09:30:00' and '2022-01-06 14:25:00' 
  group by
    date_format( sri.cur_time, '%Y%m%d' )
  )    
  order by sri2.cur_time;

StockRtInfoMapper

/**
 *根据股票编码查询指定范围内的日K线数据
 * @param startDate  开盘时间
 * @param endData   截至时间
 * @param stockCode 股票编码
 * @return
 */
List<Stock4EvrDayDomain> getStock4DkLine(@Param("startDate") Date startDate, @Param("endData") Date endData, @Param("stockCode") String stockCode);

<select id="getStock4DkLine" resultType="com.itheima.pojo.domain.Stock4EvrDayDomain">
    select
        sri2.cur_time 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 preClosePrice
    from
        stock_rt_info as sri2
    where sri2.stock_code= #{stockCode} and sri2.cur_time in (
        select
        max( sri.cur_time ) as closeDate
        from
        stock_rt_info as sri
        where
        sri.stock_code = #{stockCode}
      and sri.cur_time between #{startDate} and #{endData}
        group by
        date_format( sri.cur_time, '%Y%m%d' )
        )
    order by sri2.cur_time;
</select>

11.4前后端联调

code
code
code

11.5拆分目标(作业)

第一步：查询指定股票在指定日期范围内的每天的最大时间；
第二步：将第一步的结果作为条件查询对应的数据；
- 定义2个Mapper方法。落后逻辑层逐次调用方法，最终获取日K线的数据；

StockService

//统计指定股票的日K线数据(拆分sql)
@Override
public R<List<Stock4EvrDayDomain>> getStockScreenDkLine(String stockCode) {
    //1.获取统计日K线的数据的数据访问
    //1.1获取截止时间
    DateTime endDateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
    //假数据
    endDateTime = DateTime.parse("2022-06-06 14:25:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
    Date endData = endDateTime.toDate();
    //1.2起始时间
    DateTime startDataTime = endDateTime.minusMonths(3);
    startDataTime = DateTime.parse("2022-01-01 09:30:00", DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss"));
    Date startDate = startDataTime.toDate();


    //2.调用mapper,根据给定的时间范围获取股票每天最大的时间点
    List<String> dkLineTime = stockRtInfoMapper.getStockMaxTimeByCode(startDate,endData,stockCode);
    //3.调用mapper，根据dkLineTime集合中的时间查询指定股票编码的数据
    List<Stock4EvrDayDomain> dkLineData = stockRtInfoMapper.getStock4DkLineByTimeList(dkLineTime,stockCode);
    
    
    //3.返回
    return R.ok(dkLineData);
}

StockRtInfoMapper

/**
 * 根据给定的时间范围获取股票每天最大的时间点
 * @param startDate 开盘时间
 * @param endData   截至时间
 * @param stockCode 股票编码
 * @return
 */
List<String> getStockMaxTimeByCode(@Param("startDate") Date startDate, @Param("endData") Date endData, @Param("stockCode") String stockCode);

/**
 * 根据dkLineTime集合中的时间查询指定股票编码的数据
 * @param dkLineTime    指定的时间范围内的每天的最大时间点
 * @param stockCode     股票编码
 * @return
 */
List<Stock4EvrDayDomain> getStock4DkLineByTimeList(@Param("dkLineTime") List<String> dkLineTime, @Param("stockCode") String stockCode);

<select id="getStockMaxTimeByCode" resultType="java.util.Date">
    select
        max( cur_time ) as closeDate
    from
        stock_rt_info
    where
        stock_code = #{stockCode}
      and cur_time between #{startDate} and #{endData}
    group by
        date_format( cur_time, '%Y%m%d' )
</select>

<select id="getStock4DkLineByTimeList" resultType="com.itheima.pojo.domain.Stock4EvrDayDomain">
    select
        cur_time as date,
        trade_amount as tradeAmt,
        stock_code as code,
        min_price as lowPrice,
        stock_name as name,
        max_price as highPrice,
        open_price as openPrice,
        trade_volume as tradeVol,
        cur_price as closePrice,
        pre_close_price as preClosePrice
    from
        stock_rt_info
    where stock_code= #{stockCode}
      and cur_time IN
    <foreach collection="dkLineTime" item="lineTime" open="(" separator="," close=")">
        #{lineTime}
    </foreach>
    order by cur_time;
</select>

1.国内大盘指数功能

1.1需求分析

1.2接口定义

1.3接口实现

1.4前端端联调

2.板块指数功能实现【作业】

2.1需求分析

2.2接口定义

2.3接口实现

2.4前后端联调

3.涨幅榜更多数据功能

3.1需求分析

3.2接口定义

3.3接口实现

3.4前后端联调

4.涨幅榜功能实现(作业)

4.1需求分析

4.2接口定义

4.3接口实现

4.4前后端联调

5.涨跌停数据统计业务

5.1需求分析

5.2接口定义

5.3接口实现

5.4前后端联调

6.EasyExcel使用

6.1EasyExcel导出/导入数据快速入门

6.2导出数据的高级设置

7.涨幅榜数据导出功能

7.1需求分析

7.2接口定义

7.3接口实现

7.4前后端联调

8.股票成交量对比

8.1需求分析

8.2接口定义

8.3接口实现

8.4前后端联调

9.个股分时涨跌幅度统计功能

9.1需求分析

9.2接口定义

9.3接口实现

9.4前后端联调

9.5遇到的问题

10.个股分时K线行情功能

10.1需求分析

10.2接口定义

10.3接口实现

10.4前后端联调

11.个股日K线详情功能

11.1需求分析

11.2接口定义

11.3接口实现

11.4前后端联调

11.5拆分目标(作业)