用DBeaver连接本地数据库进行数据筛选

最新推荐文章于 2024-11-01 21:49:58 发布

輕栀

最新推荐文章于 2024-11-01 21:49:58 发布

阅读量4.5k

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/mnwl12_0/article/details/132509380

版权

目标：依次找到2023-01-03到2023-08-23之间的、不同标准期限的、债券类别为国债的、当天成交笔数最大的债券相关信息（剩余期限、债券简称、债券代码、成交笔数）。

表格信息如下：

下面将演示全过程：

首先打开DBeaver软件，连接本地数据库mysql,（其他数据库要用到公司局域网，连不上去，只能自己创建一个本地数据库），点击左上角任务栏数据库（D），连接MYSQL：

前面几篇文章讲过怎么连接了，这里不再赘述，

，下面将演示原始数据：

首先需要对原始数据进行处理，把不同表格数据进行拼接在一个文件里面，最重要的是要把文件格式改成'utf-8'格式，不然在DB里面无法读取数据，具体步骤如下：

import pandas as pd
df1=pd.read_excel(r'C:\Users\59980\Desktop\peixun\zong\债券区间日行情-1.1~3.31.xlsx')
df2=pd.read_excel(r'C:\Users\59980\Desktop\peixun\zong\债券区间日行情-4.1~6.30.xlsx')
df3=pd.read_excel(r'C:\Users\59980\Desktop\peixun\zong\债券区间日行情-7.1~8.23.xlsx')
result = pd.concat([df1,df2,df3],axis=0,ignore_index=True)
result = result.sort_values(by='TRADE_DATE')
result.to_csv(r'C:\Users\59980\Desktop\peixun\zong\债券区间日行情_zong.csv',encoding='utf-8', index=False)

先读取3个文档，然后按行拼接数据，再按日期进行排序，原始数据是很乱的，有可能倒序，最后输出成csv格式，输出后的格式为：25000多行，

重要提示：最好是在把数据文档爬取下来后就对列名进行修改，改成对应中文的英文，一眼就能看懂得那种，后续在数据库创建表的时候会用到此处创建的列名，也就是说，数据导入到数据库的时候表名和本地数据表格 "债券区间日行情_zong.csv"的列名要相同，否则导入时会生成新的列，很麻烦。eg:

对数据处理完毕后，现在在DB的本地数据库mysql里面你创建的数据库 datebases 里面创建新表：我的是mysql下面lite:

点击左上角的SQL——新建脚本会生成脚本,在脚本里面输入：

CREATE TABLE lite.bond_transaction_daily_1 (
    ID INT,
    TRADE_DATE DATE,
    TRADE_CODE VARCHAR(50),
    BOND_NAME VARCHAR(50),
    ISSUER VARCHAR(50),
    REMAINING_TERM DECIMAL(10,2),
    TRADE_SOURCE VARCHAR(50),
    WEIGHTED_YTM DECIMAL(10,2),
    WEIGHTED_YTE DECIMAL(10,2),
    WEIGHTED_YIELD DECIMAL(10,2),
    WEIGHTED_PRICE DECIMAL(10,2),
    TRADE_COUNT INT,
    TRADE_VOLUME INT,
    TRADE_AMOUNT DECIMAL(10,2),
    CLOSING_YTM DECIMAL(10,2),
    CLOSING_YTE DECIMAL(10,2),
    CLOSING_YIELD_WIND DECIMAL(10,2),
    CLOSING_DEVIATION DECIMAL(10,2),
    CLOSING_MARKET_PRICE DECIMAL(10,2),
    DURATION DECIMAL(10,2),
    CONVEXITY DECIMAL(10,2),
    MAX_YIELD DECIMAL(10,2),
    MIN_YIELD DECIMAL(10,2),
    WEIGHTED_AVERAGE_YIELD DECIMAL(10,2),
    OPENING_YIELD DECIMAL(10,2),
    CLOSING_YIELD DECIMAL(10,2),
    MAX_CLEAN_PRICE DECIMAL(10,2),
    MIN_CLEAN_PRICE DECIMAL(10,2),
    WEIGHTED_AVERAGE_CLEAN_PRICE DECIMAL(10,2),
    OPENING_CLEAN_PRICE DECIMAL(10,2),
    CLOSING_CLEAN_PRICE DECIMAL(10,2),
    MAX_FULL_PRICE DECIMAL(10,2),
    MIN_FULL_PRICE DECIMAL(10,2),
    WEIGHTED_AVERAGE_FULL_PRICE DECIMAL(10,2),
    OPENING_FULL_PRICE DECIMAL(10,2),
    CLOSING_FULL_PRICE DECIMAL(10,2),
    ISSUE_COUPON_RATE DECIMAL(10,2),
    INTEREST_RATE_TYPE VARCHAR(50),
    BOND_RATING VARCHAR(50),
    ISSUER_RATING VARCHAR(50),
    SPECIAL_TERMS VARCHAR(50),
    BOND_TYPE VARCHAR(50),
    MARKET VARCHAR(50),
    ISSUE_DATE DATE
);

创建一个名称为bond_transaction_daily_1的表，表的列名和表格的列名一致，点一下lite右键点击 refresh 会看到lite——表下面出现bond_transaction_daily_1的表，同时表为空：

现在点击bond_transaction_daily_1——右键——导入数据：

在新窗口一直点next即可，选中刚才要导入的数据 _zong.csv：

最后点proceed，加载数据，导入完成后表的数据如图：

如果表的列名和表格的列名不一致会发生（如列名ID不一致，表的列名为ID，表格列名为：“代码”），ID列为NULL，且在表的最后一列会生成“代码”一列，后面做运算极其麻烦，要规避这种情况，到这里数据导入部分结束了。

下面就要对数据进行处理了，首先需要创建目标表，表的形式如下，用于存储债券相关信息：

CREATE TABLE info_table (
    TRADE_DATE DATE,
    standard_term VARCHAR(50),
    start_remaining_term DECIMAL(10,4),
    end_remaining_term DECIMAL(10,4),
    BOND_TYPE VARCHAR(50),
    REMAINING_TERM DECIMAL(10,2),
    TRADE_CODE VARCHAR(50),
    BOND_NAME VARCHAR(50),
    TRADE_COUNT DECIMAL(10,2)

);

select * from info_table;

下面就要对数据进行处理了：

相关的其他SQL语句会经常用到：

drop PROCEDURE insert_table;--删除存储过程

drop table info_table ;--删除信息表


select * from info_table;--查看信息表


SHOW PROCEDURE STATUS;--展示存储过程状态

对数据处理的逻辑如下：

首先我要筛选日期，把当天的日期提取出来，然后我要筛选国债，其他债券不要，接着要筛选剩余期限在对应期限内的债券，最后要对成交笔数做排序，仅提取成交笔数最大的那个债券：

查询符合 REMAINING_TERM 范围的债券相关信息，并插入到 info_table 表中：

CREATE PROCEDURE insert_table()-- 创建存储过程
begin-- 声明变量
    DECLARE v_current_date DATE DEFAULT '2023-01-03';-- 开始时间
    DECLARE v_end_date DATE DEFAULT '2023-08-23';-- 结束时间
    DECLARE i INT DEFAULT 1;-- 遍历的辅助变量
    declare v_standard_term VARCHAR(50);-- 标准期限的辅助变量
    DECLARE v_remaining_term_min DECIMAL(10, 4);-- 剩余期限的上限
    DECLARE v_remaining_term_max DECIMAL(10, 4);-- 剩余期限的下限
-- 外循环，遍历所有要找的日期
    WHILE v_current_date <= v_end_date DO

        SET i = 1;-- 初始化辅助变量，标准期限的辅助变量

        WHILE i <= 13 DO

            CASE i-- 每种情况遍历，每种情况都要给剩余期限的上界、下界、期限作赋值
-- 一开始也没想到标准期限也加进来，后来想想工作要做到完美只有不断完善代码才能不用后面再到外部的表格进行修改，防止二次工作
                WHEN 1 THEN
                    SET v_remaining_term_min = 0;
                    SET v_remaining_term_max = 0.167;
                    set v_standard_term='1M';
                WHEN 2 THEN
                    SET v_remaining_term_min = 0.167;
                    SET v_remaining_term_max = 0.375;
                    set v_standard_term='3M';
                WHEN 3 THEN
                    SET v_remaining_term_min = 0.375;
                    SET v_remaining_term_max = 0.625;
                    set v_standard_term='6M';
                WHEN 4 THEN
                    SET v_remaining_term_min = 0.625;
                    SET v_remaining_term_max = 0.875;
                    set v_standard_term='9M';
                WHEN 5 THEN
                    SET v_remaining_term_min = 0.875;
                    SET v_remaining_term_max = 1.5;
                    set v_standard_term='1Y';
                WHEN 6 THEN
                    SET v_remaining_term_min = 1.5;
                    SET v_remaining_term_max = 2.5;
                    set v_standard_term='2Y';
                WHEN 7 THEN
                    SET v_remaining_term_min = 2.5;
                    SET v_remaining_term_max = 3.5;
                    set v_standard_term='3Y';
                WHEN 8 THEN
                    SET v_remaining_term_min = 3.5;
                    SET v_remaining_term_max = 4.5;
                    set v_standard_term='4Y';
                WHEN 9 THEN
                    SET v_remaining_term_min = 4.5;
                    SET v_remaining_term_max = 5.5;
                    set v_standard_term='5Y';
                WHEN 10 THEN
                    SET v_remaining_term_min = 5.5;
                    SET v_remaining_term_max = 6.5;
                    set v_standard_term='6Y';
                WHEN 11 THEN
                    SET v_remaining_term_min = 6.5;
                    SET v_remaining_term_max = 8;
                    set v_standard_term='7Y';
                WHEN 12 THEN
                    SET v_remaining_term_min = 8;
                    SET v_remaining_term_max = 12.5;
                    set v_standard_term='10Y';
                WHEN 13 THEN
                    SET v_remaining_term_min = 12.5;
                    SET v_remaining_term_max = 50;
                    set v_standard_term='30Y';
            END CASE;

            -- 查询符合 REMAINING_TERM 范围的债券相关信息，并插入到 info_table 表中
            -- 这里没什么特别的地方，都是常规的SQL语句
            INSERT INTO info_table (TRADE_DATE, BOND_TYPE, REMAINING_TERM, TRADE_CODE, BOND_NAME, TRADE_COUNT)-- 插入信息
            SELECT TRADE_DATE, BOND_TYPE, REMAINING_TERM, TRADE_CODE, BOND_NAME, TRADE_COUNT-- 挑选信息
            FROM bond_transaction_daily_1-- 从对应的表中找
            WHERE TRADE_DATE = v_current_date
                AND REMAINING_TERM > v_remaining_term_min AND REMAINING_TERM <= v_remaining_term_max
                AND BOND_TYPE = '国债'-- 3个限定条件
            ORDER BY TRADE_COUNT DESC-- 对成交笔数排序
            LIMIT 1;-- 取成交笔数最大的债券

            -- 更新standard_term 、 start_remaining_term 和 end_remaining_term 列
            UPDATE info_table
            SET start_remaining_term = v_remaining_term_min,
                end_remaining_term = v_remaining_term_max,
                standard_term = v_standard_term
            WHERE TRADE_DATE = v_current_date
                AND REMAINING_TERM > v_remaining_term_min AND REMAINING_TERM <= v_remaining_term_max
                AND BOND_TYPE = '国债'
                AND start_remaining_term IS NULL; -- 仅更新未被更新过的记录

            SET i = i + 1;-- 对每种标准期限进行遍历，
        END WHILE;

        SET v_current_date = DATE_ADD(v_current_date, INTERVAL 1 DAY);-- 对日期进行处理，外部循环结束
    END WHILE;
END

运行好后如下图：