主页 / dba / 问题 / 242888
Accepted
nali
Asked: 2019-07-16 05:26:11 +0800 CST

从现有字符串列创建整数 id 列（整数编码？）

  • 772

我有一个 PostgreSQL 服务器，其现有表具有两个固定宽度非唯一字符串（可变大小）列，例如：

| ID_STRING_A | ID_STRING_B |
|   'AAAA'   |    'BBBB'    |   
|   'BBBB'   |    'CCCC'    | 
|   'AAAA'   |    'DDDD'    |

现在我想计算两个列元素的整数表示并将它们存储到其他列中。结果应如下所示：

| ID_STRING_A | ID_STRING_B | ID_INT_A | ID_INT_B |
|   'AAAA'    |   'BBBB'    |     1    |     2    |
|   'BBBB'    |   'CCCC'    |     2    |     3    |
|   'AAAA'    |   'DDDD'    |     1    |     4    |

我基于答案的第一个方法是：

不幸的是，尽管 ID_STRING_A/B 上有索引，但我的更新部分似乎效率很低。虽然查询本身在几分钟内完成，但更新部分似乎并未结束。这是代码：

ALTER TABLE mytable ADD COLUMN ID_INT_B integer;
ALTER TABLE mytable ADD COLUMN ID_INT_A integer;

UPDATE mytable SET ID_INT_A = g.ID_INT_A , ID_INT_B = g.ID_INT_B FROM
(
    WITH T( n , s ) AS 
    ( 
        SELECT ROW_NUMBER() OVER ( ORDER BY s ) , s
        FROM 
        ( 
            SELECT ID_STRING_A FROM mytable
            UNION 
            SELECT ID_STRING_B FROM mytable
        ) AS X( s )
    )
    SELECT m.ctid AS id_ , m.ID_STRING_A AS ID_STRING_A , m.ID_STRING_B AS ID_STRING_B , T1.n AS ID_INT_A , T2.n AS ID_INT_B FROM mytable AS m
    JOIN T AS T1 ON m.ID_STRING_A = T1.s
    JOIN T AS T2 ON m.ID_STRING_B = T2.s
) AS g
WHERE mytable.ctid = g.id_
postgresql computed-column

2 个回答

  1. Best Answer

    我想你可以使用 ASCII 函数：

    SELECT ID_STRING_A,ID_STRING_B
     , ASCII(ID_INT_A) - 64 AS ID_INT_A
     , ASCII(ID_INT_B) - 64 AS ID_INT_B
FROM ...

    也许使用以下意图更清楚：

         , ASCII(ID_INT_A) - ASCII('A') + 1 AS ID_INT_A

    编辑，因为改变了这样的问题是可能的：

    WITH T (n, s) as ( 
    SELECT row_number() over (order by s), s
    FROM ( 
        SELECT ID_STRING_A FROM mytable
        UNION 
        SELECT ID_STRING_B FROM mytable
    ) as X (s)
)
SELECT m.ID_STRING_A, m.ID_STRING_B, T1.n, T2.n
FROM mytable as m
JOIN T as T1
    ON m.ID_STRING_A = T1.s
JOIN T as T2
    ON m.ID_STRING_B = T2.s

    编辑，更新表

    我有一种直觉，这可以以更简单的方式完成，但我将 cte 与自身交叉连接并使用 WHERE 过滤以同时更新两列：

    ALTER TABLE mytable
    ADD ID_INT_A INT;

ALTER TABLE mytable
    ADD ID_INT_B INT;

WITH cte (n, s) as ( 
    SELECT row_number() over (order by s), s
    FROM ( 
        SELECT ID_STRING_A FROM mytable
        UNION 
        SELECT ID_STRING_B FROM mytable
    ) as X (s)
), cte2 (n1,s1,n2,s2) as (
    SELECT c1.n, c1.s, c2.n, c2.s
    FROM cte c1
    CROSS JOIN cte c2
)
UPDATE mytable
    SET ID_INT_A = cte2.n1
      , ID_INT_B = cte2.n2
FROM cte2
WHERE mytable.ID_STRING_A = cte2.s1
  AND mytable.ID_STRING_B = cte2.s2
;

    需要注意的是，这是一次操作。如果以后决定加AABB，枚举就会出错

    • 2 
  2. CREATE TEMP TABLE map (
   id serial PRIMARY KEY,
   str text NOT NULL
);

INSERT INTO map (str)
SELECT DISTINCT id_string_a
FROM mytab;

ALTER TABLE mytab ADD id_int_a integer;

UPDATE mytab
SET id_int_a = map.id
FROM map
WHERE mytab.id_string_a = map.str;

DROP TABLE map;

    id_string_b留给读者作为练习。

    • 0

相关问题