Làm sạch chuỗi trong postgresql

Question

Tôi có một cột trong bảng chứa dữ liệu về bất kỳ cập nhật nào liên quan đến những thay đổi về công ty ở định dạng bên dưới -

#=============#==============#================#
| 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           |
+-------------+--------------+----------------+

Như bạn có thể thấy ở trên, cột updateschứa các chuỗi bao gồm các dòng mới và mô tả một hoặc nhiều cập nhật. Trong ví dụ trên, điều này có nghĩa là đối với ID công ty 101, tên đã thay đổi từ ABC thành XYZ và url đã thay đổi từ www.abc.com thành www.xyz.com . Đối với ID công ty 109, chỉ có xếp hạng thay đổi từ 4,5 thành 4,0.

Tuy nhiên, tôi muốn chia cột cập nhật thành 3 cột - một cột phải chứa những gì đã được thay đổi (url, tên, v.v.), cột thứ hai phải có giá trị cũ và cột thứ 3 phải có giá trị mới. Một cái gì đó như thế này -

#============#============#==============#================#
| 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            |
+------------+------------+--------------+----------------+

Tôi đang làm điều này trong Postgres và biết cách trích xuất chuỗi con dựa trên các ký tự nhưng điều này có vẻ hơi phức tạp với tôi vì tôi cần trích xuất nhiều chuỗi con từ cùng một cột cho mỗi hàng. Bất kỳ trợ giúp sẽ được đánh giá cao. Cảm ơn!

Answer 1

Lúc đầu, bạn có thể sử dụng regexp_split_into_tablevà regexp với một cái nhìn tích cực để có được phiên bản bảng của bạn, trong đó mỗi hàng chứa chính xác một bản cập nhật:

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

Thao tác này sẽ chia cột updatesở bất kỳ dòng mới ( \n) nào theo sau bởi một từ đơn và dấu hai chấm ( \y.+:).

#=============#==============#================#
| 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           |
+-------------+--------------+----------------+

Từ đó, bạn có thể dễ dàng xây dựng bảng mong muốn của mình hơn. Để làm điều này, bạn có thể sử dụng ví dụ: split_partđể chia chuỗi cập nhật thành ba phần bạn muốn.

Đặt điều này cùng với phần đầu tiên sẽ giúp bạn có được truy vấn đầy đủ:

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
       )
;

Đây là một ví dụ db <> fiddle :https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Thêm chi tiết / thông tin bổ sung:

• ký tự dòng mới trong chuỗi postgres: https://stackoverflow.com/a/26638775/14015737
• ranh giới từ postgresql regex: https://stackoverflow.com/a/3825705/14015737
• tách chuỗi thành các cột mới: https://stackoverflow.com/a/8612456/14015737