【数据集划分】oracle数据集划分（总结版）

写在最前面

前文：【数据集划分】假如你有超百万条oracle数据库数据（成真版）
大模型，何所谓大？先从大数据开始。

假如你有超百万条oracle数据库数据，那么一直使用的代码：train_df, temp_df = train_test_split(df, test_size=0.3, random_state=42)，很可能1h还没划分完数据。

最终解决方案：生成一列随机数，然后随机打乱。取前70%的样本划分为训练集，70%到90%之间的样本划分为测试集，剩余的样本划分为验证集。

最终代码

1. 连接Oracle数据库：使用jaydebeapi连接Oracle数据库，确保提供正确的JDBC驱动路径和数据库连接信息。
2. 添加新列：在deal_ct_report表中添加一个新列dataset来保存数据集标签。如果列已经存在，会捕捉到异常并继续执行后续操作。
3. 获取总行数：查询表的总行数，用于生成随机索引。
4. 生成随机索引并打乱顺序：生成从1到总行数的索引列表，并打乱顺序。
5. 计算各数据集的分界点：计算训练集、测试集和验证集的分界点。
6. 创建临时表：将原表的rowid和ROWNUM保存到临时表中。
7. 更新数据集标签列：使用CTE和窗口函数一次性更新所有记录，避免分批次更新的效率问题。
   • 使用dbms_random.value生成随机数进行排序。
   • 使用ROW_NUMBER()窗口函数为每条记录分配一个随机序号。
   • 根据随机序号进行数据集划分并更新dataset列。
8. 删除临时表：删除临时表以清理临时数据。
9. 提交事务：将所有更改提交到数据库。
10. 关闭连接：关闭数据库连接。

通过将参数直接插入到SQL语句中，避免了参数传递中的问题。这种方法可以高效地实现数据集的随机划分和更新操作。

import jaydebeapi
import random

# 连接Oracle数据库
conn = jaydebeapi.connect(
    'oracle.jdbc.driver.OracleDriver',
    'jdbc:oracle:thin:@hostname:port:service_name',
    ['username', 'password'],
    'path/to/ojdbc8.jar'
)
cursor = conn.cursor()

# 添加新列dataset
try:
    cursor.execute("ALTER TABLE deal_ct_report ADD dataset VARCHAR2(10)")
except jaydebeapi.DatabaseError as e:
    print("Column 'dataset' may already exist. Proceeding with data split...")

# 获取表的行数
cursor.execute("SELECT COUNT(*) FROM deal_ct_report")
total_rows = cursor.fetchone()[0]

# 生成随机索引并打乱顺序
indices = list(range(1, total_rows + 1))
random.shuffle(indices)

# 计算各数据集的分界点
train_limit = int(0.7 * total_rows)
test_limit = int(0.9 * total_rows)

# 创建一个临时表来存储带有索引的数据
cursor.execute("CREATE TABLE deal_ct_report_temp AS SELECT rowid AS rid, ROWNUM AS rnum FROM deal_ct_report")

# 更新数据集标签列
update_sql = f"""
    MERGE INTO deal_ct_report d
    USING (
        WITH temp_data AS (
            SELECT rid, rnum,
                   CASE
                       WHEN rnum <= {train_limit} THEN 'train'
                       WHEN rnum <= {test_limit} THEN 'test'
                       ELSE 'validate'
                   END AS dataset
            FROM (
                SELECT rid, ROW_NUMBER() OVER (ORDER BY dbms_random.value) AS rnum
                FROM deal_ct_report_temp
            )
        )
        SELECT rid, dataset
        FROM temp_data
    ) t
    ON (d.rowid = t.rid)
    WHEN MATCHED THEN
    UPDATE SET d.dataset = t.dataset
"""

cursor.execute(update_sql)

# 删除临时表
cursor.execute("DROP TABLE deal_ct_report_temp")

# 提交事务
conn.commit()

# 关闭数据库连接
cursor.close()
conn.close()

原理：生成随机索引并打乱顺序

生成随机索引并打乱顺序的原理是将数据集进行随机化处理，以确保数据集的随机划分，使训练集、测试集和验证集的样本分布尽可能地均匀和独立。这种方法有助于消除因数据顺序带来的偏差，从而使模型训练和评估更加准确。

具体步骤如下：

1. 生成索引列表：创建一个从1到总行数的索引列表，这些索引表示数据集中每一条记录的序号。
2. 打乱索引顺序：使用random.shuffle函数将索引列表随机打乱。这样可以确保索引的顺序是随机的，而不是按原始顺序排列。

示例

假设数据集中有10条记录，生成的索引列表为：[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]。打乱后可能变为：[3, 8, 1, 6, 7, 2, 5, 9, 4, 10]。

import random

# 获取表的行数
total_rows = 10  # 这里假设总行数为10

# 生成索引列表
indices = list(range(1, total_rows + 1))

# 打乱索引顺序
random.shuffle(indices)

print(indices)

打乱后的索引列表是随机的。例如，输出可能是：[7, 2, 9, 1, 5, 3, 10, 6, 4, 8]。

作用

1. 随机化数据顺序：确保数据集的样本顺序是随机的，这样可以防止某些样本因顺序而集中在同一个子集。
2. 均匀分布：在随后的数据集划分中（如7:2:1），可以保证训练集、测试集和验证集中的样本更加均匀和独立。
3. 减少偏差：通过随机化处理，可以减少因数据顺序带来的潜在偏差，从而提高模型的泛化能力。

应用场景

这种方法特别适用于需要将大数据集随机划分为多个子集的场景，如机器学习中的数据集划分（训练集、测试集、验证集）。在这种情况下，确保每个子集的样本分布尽可能均匀和独立是至关重要的。

通过这种方式，可以在后续的模型训练和评估过程中，尽量避免因数据顺序或分布不均而导致的模型偏差，从而提高模型的性能和可靠性。

遇到报错：ORA-01795，通过CTE（Common Table Expressions）和窗口函数解决

DatabaseError: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

ORA-01795错误表示Oracle数据库限制了在IN子句中最多只能包含1000个表达式。

为了克服这一限制，我们可以：

1. （还是很慢，pass）将大的更新分成多个批次，每个批次最多包含1000个表达式。
2. 可以使用CTE（Common Table Expressions）和窗口函数来一次性更新所有记录，而不是分批次更新。