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

【数据集划分】假如你有接近百万条oracle数据库数据（成真版）

• 写在最前面
• 最终代码
• 原理：生成随机索引并打乱顺序
• • 示例
  • 作用
  • 应用场景
• 遇到报错：ORA-01795，通过CTE（Common Table Expressions）和窗口函数解决

写在最前面

前文：【数据集划分】假如你有超百万条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）和窗口函数来一次性更新所有记录，而不是分批次更新。