Bereinigen Sie die Zeichenfolge in postgresql

Question

Ich habe eine Spalte in einer Tabelle, die Daten zu Aktualisierungen im Zusammenhang mit Änderungen an einem Unternehmen im folgenden Format enthält.

#=============#==============#================#
| Company ID  |  updated_at  |   updates      |
#=============#==============#================#
| 101         | 2020-11-01   | name:          |
|             |              | -ABC           |
|             |              | -XYZ           |
|             |              | url:           |
|             |              | -www.abc.com   |
|             |              | -www.xyz.com   |
+-------------+--------------+----------------+
| 109         | 2020-10-20   | rating:        |
|             |              | -4.5           |
|             |              | -4.0           |
+-------------+--------------+----------------+

Wie Sie oben sehen können, updatesenthält die Spalte Zeichenfolgen, die Zeilenumbrüche enthalten und eine oder mehrere Aktualisierungen beschreiben. Im obigen Beispiel bedeutet dies, dass für die Firmen-ID 101 der Name von ABC in XYZ und die URL von www.abc.com in www.xyz.com geändert wurde . Für die Firmen-ID 109 wurde nur die Bewertung von 4,5 auf 4,0 geändert.

Ich möchte jedoch die Aktualisierungsspalte in 3 Spalten unterteilen - eine sollte enthalten, was geändert wurde (URL, Name usw.), die zweite sollte den alten Wert haben und die dritte Spalte sollte den neuen Wert haben. Etwas wie das -

#============#============#==============#================#
| Company ID |   Field    |  Old Value   |   New Value    |
#============#============#==============#================#
| 101        |   name     | ABC          | XYZ            |
+------------+------------+--------------+----------------+
| 101        |   url      | www.abc.com  | www.xyz.com    |
+------------+------------+--------------+----------------+
| 109        |   rating   | 4.5          | 4.0            |
+------------+------------+--------------+----------------+

Ich mache das in Postgres und weiß, wie man Teilzeichenfolgen basierend auf Zeichen extrahiert, aber das erscheint mir etwas kompliziert, da ich für jede Zeile mehrere Teilzeichenfolgen aus derselben Spalte extrahieren muss. Jede Hilfe wäre dankbar. Vielen Dank!

Answer 1

Zuerst können Sie regexp_split_into_tableeinen regulären Ausdruck mit einem positiven Lookahead verwenden, um eine Version Ihrer Tabelle zu erhalten, in der jede der Zeilen genau ein Update enthält:

select companyID, 
       updated_at, 
       regexp_split_to_table(updates, '\n(?=\y.+:)') as updates 
  from old;

Dadurch wird die Spalte updatesan jeder neuen Zeile ( \n) aufgeteilt, auf die ein einzelnes Wort und ein Doppelpunkt ( \y.+:) folgen .

#=============#==============#================#
| companyID   |  updated_at  |   updates      |
#=============#==============#================#
| 101         | 2020-11-01   | name:          |
|             |              | -ABC           |
|             |              | -XYZ           |
+-------------+--------------+----------------+
| 101         | 2020-11-01   | url:           |
|             |              | -www.abc.com   |
|             |              | -www.xyz.com   |
+-------------+--------------+----------------+
| 109         | 2020-10-20   | rating:        |
|             |              | -4.5           |
|             |              | -4.0           |
+-------------+--------------+----------------+

Daraus können Sie einfacher Ihre gewünschte Tabelle erstellen. Dazu können Sie zB split_partdie Update-Zeichenfolge in die drei gewünschten Teile aufteilen.

Wenn Sie dies mit dem ersten Teil zusammenfügen, erhalten Sie die vollständige Abfrage:

select companyID, 
       updated_at, 
       split_part(updates, E':', 1) as field, 
       split_part(updates, E'\n-', 2) as old_value, 
       split_part(updates, E'\n-', 3) as new_value  
  from (select companyID, 
               updated_at, 
               regexp_split_to_table(updates, '\n(?=\y.+:)') as updates 
          from old
       )
;

Hier ist ein Beispiel für eine db <> Geige :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Weitere Details / zusätzliche Infos:

• Zeilenumbruchzeichen in Postgres-Zeichenfolgen: https://stackoverflow.com/a/26638775/14015737
• postgresql Regex Wortgrenzen: https://stackoverflow.com/a/3825705/14015737
• Aufteilen von Zeichenfolgen in neue Spalten: https://stackoverflow.com/a/8612456/14015737