使用SQL对抓取的股票数据进行清洗

使用SQL对抓取的股票数据进行清洗

背景介绍

从网络上抓取了A股股票的历史数据,经过初步的清洗后入库(MySQL),但是在使用过程中发现有数据缺失的问题,在使用时需要对数据再次进行清洗,比如补全空行,对数据进行排序.

目标

通过SQL查询某一时间段,所有A股股票的闭市价.并且按照时间/股票代码进行排序,并且要求缺失的数据用null补齐.

结论

目标可以用SQL实现,数据分析师一般对SQL的应用不是很熟练,一般这个任务的难点在于对缺失数据的补齐.

其实并不需要对数据进行补齐,只需要对股票代码和时间进行一个笛卡尔积操作,然后join数据表取出数据即可.

下面是我代码,使用时只需要换掉表名和列名即可.

SELECT
  td.date,
  tc.code,
  tt.close
FROM (
    (
      SELECT td.date
      FROM test td
      GROUP BY td.date) td,
    (
      SELECT tc.code
      FROM test tc
      GROUP BY tc.code) tc
  ) LEFT JOIN test tt ON tt.code = tc.code AND td.date = tt.date
WHERE td.DATE BETWEEN :start_date AND :stop_date
ORDER BY td.date, tc.code;

思路

在这里介绍一下解决思路

建表并插入测试数据.

CREATE TABLE IF NOT EXISTS test
(
  id    INT AUTO_INCREMENT
    PRIMARY KEY,
  # 股票代码
  code  VARCHAR(5) NULL,
  # 日期
  date  INT        NULL,
  # 闭市价
  close INT        NULL,
  # 开市价
  open  INT        NULL,
  # 最低价
  low   INT        NULL,
  # 最高价
  high  INT        NULL
);

插入测试数据

NSERT INTO test_export.test (CODE, DATE, CLOSE, OPEN, high, low) VALUES ('s1', 1, 2, 3,