多次拆分并分配 order_id [英] Multiple split and assign order_id

问题描述

我有一些地址字符串需要为每个组件进行分析.

I have some address string that needs to be analyzed for each component.

SQL Fiddle 演示

CREATE TABLE Table1 ("ID" int, "address" varchar(41));

INSERT INTO Table1 ("ID", "address")
VALUES
    (1, 'calle 15, lomas del avila'),
    (2, 'calle av. solano edif:Apalache apt-15');

所以我需要用不同的字符来分割，比如 ( ., , , :, ;, <代码><空格>, -)

So I need to split by different characters like ( ., ,, :, ;, <space>, -)

对于一个角色，我知道该怎么做.

For one character I know how to do it.

SELECT ID, s.token
FROM   Table1 t, unnest(string_to_array(t.address, ' ')) s(token);

如何链接多个取消嵌套以及如何分配 OrderID?

| ID |         token | orderID
|----|---------------|--------
|  1 |         calle |    1
|  1 |           15, |    2
|  1 |         lomas |    3
|  1 |           del |    4
|  1 |         avila |    5
|  2 |         calle |    1
|  2 |           av. |    2
|  2 |        solano |    3
|  2 | edif:Apalache |    4
|  2 |        apt-15 |    5

对于此示例，第二行 15, 将拆分为 15 和 null，因此可以丢弃第二个结果并且顺序不会改变.

For this example second row 15, will be split into 15 and null so second result can be discard and order won't change.

但是在最后 2 行 edif:Apalache 和 apt-15 中，第二个分割将产生 edif, Apalache、apt 和 15 所以 orderID 将从 4 变为 7:

But in the last 2 rows edif:Apalache and apt-15 the second split will produce edif, Apalache, apt and 15 so orderID will go from 4 to 7:

|  2 | edif     |    4
|  2 | Apalache |    5
|  2 | apt      |    6
|  2 | 15       |    7

推荐答案

使用 翻译():

SELECT "ID", token, row_number() over (partition by "ID")
FROM (
    SELECT "ID", s.token
    FROM   
        Table1 t, 
        unnest(string_to_array(translate(t.address, '.,:;-', '     '), ' ')) 
            s(token)
    WHERE token <> ''
    ) sub

SqlFiddle.

这篇关于多次拆分并分配 order_id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！