时间戳

首页 > 解决方案 > SQL：用连续索引替换不同的（数字）值

sql - SQL：用连续索引替换不同的（数字）值

问题描述

我有一个非常大的表，需要将其导出到文本文件中，以便将其读入不同的程序 (R)。为了省去编写/读取大型文本文件的麻烦，我想通过用连续索引替换所有不同的数值来转换其中一列（有问题的列是一个恰好是数字的 ID。它可能是不同环境中的角色）。所以我想用一个连续的索引替换这个 ID 的所有不同值（n用数字替换不同的值1到n）。

我的数据如下所示：

ID        SOMECHAR    SOMECOUNT
---------------------------------
1534561   FISH        2
7537281   CAT         14
4738920   CAT         3
7537281   BIRD        7
7537281   FISH        6
1534561   CAT         10
...       ...         ...

我想写一个这样的表：

ID        SOMECHAR    SOMECOUNT
---------------------------------
1         FISH        2
2         CAT         14
3         CAT         3
2         BIRD        7
2         FISH        6
1         CAT         10
...       ...         ...

1) 我可以期望节省大量空间吗？我有大约 1 亿行和 1000 万个不同的 ID，原始 ID 的长度为 10 位。

2) 如何在 SQL 或 PL/SQL 中做到这一点？该REPLACE命令用于字符串...我是否需要创建第二个表作为每个不同 ID 和连续索引的查找？如果是，我如何获得索引？

标签： sqloracleplsql

解决方案

您可以DENSE_RANK按以下方式使用：

这是演示：

WITH DATAA(ID) AS
(
SELECT 123 FROM DUAL UNION ALL
SELECT 121 FROM DUAL UNION ALL
SELECT 123 FROM DUAL UNION ALL
SELECT 121 FROM DUAL UNION ALL
SELECT 124 FROM DUAL 
)
SELECT ID, DENSE_RANK() OVER (ORDER BY ID) FROM DATAA;

输出：

        ID DENSE_RANK()OVER(ORDERBYID)
---------- ---------------------------
       121                           1
       121                           1
       123                           2
       123                           2
       124                           3

干杯！！

- 更新 -

更新值的示例：

-- Sample data preparation 
CREATE TABLE MY_TABLE (
    ID          NUMBER,
    SOMECHAR    VARCHAR2(100),
    SOMECOUNT   NUMBER
);

INSERT INTO MY_TABLE 
SELECT 1534561   ,'FISH',        2  FROM DUAL UNION ALL
SELECT 7537281   ,'CAT',         14 FROM DUAL UNION ALL
SELECT 4738920   ,'CAT',         3  FROM DUAL UNION ALL
SELECT 7537281   ,'BIRD',        7  FROM DUAL UNION ALL
SELECT 7537281   ,'FISH',        6  FROM DUAL UNION ALL
SELECT 1534561   ,'CAT',         10 FROM DUAL ;

-- 之前的输出 --

SELECT * FROM MY_TABLE;

        ID SOMECHAR    SOMECOUNT
---------- ---------- ----------
   1534561 FISH                2
   7537281 CAT                14
   4738920 CAT                 3
   7537281 BIRD                7
   7537281 FISH                6
   1534561 CAT                10

-- 查询修改ID --

MERGE INTO MY_TABLE M
USING 
(SELECT
    ROWID,
    DENSE_RANK() OVER(
        ORDER BY
            ID
    ) MODIFIED_ID
FROM
    MY_TABLE) MODIFIED
ON (M.ROWID = MODIFIED.ROWID)
WHEN MATCHED THEN 
UPDATE SET M.ID = MODIFIED.MODIFIED_ID;

-- 之后输出 --

SELECT * FROM MY_TABLE;


        ID SOMECHAR    SOMECOUNT
---------- ---------- ----------
         1 FISH                2
         3 CAT                14
         2 CAT                 3
         3 BIRD                7
         3 FISH                6
         1 CAT                10

干杯！！

推荐阅读