Postgresql'de temiz dize

Question

Tabloda, bir şirket hakkındaki değişikliklerle ilgili güncellemeler hakkında aşağıdaki formatta veri içeren bir sütunum var -

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

Yukarıda görebileceğiniz gibi, sütun updatesyeni satırlar içeren ve bir veya birden çok güncellemeyi açıklayan dizeler içerir. Yukarıdaki örnekte bu, şirket kimliği 101 için adın ABC'den XYZ'ye ve url'nin www.abc.com'dan www.xyz.com'a değiştiği anlamına gelir . Şirket kimliği 109 için yalnızca derecelendirme 4,5'ten 4,0'a değiştirildi.

Bununla birlikte, güncellemeler sütununu 3 sütuna bölmek istiyorum - biri neyin değiştirildiğini (url, isim vb.) İçermeli, ikincisi eski değere sahip olmalı ve 3. sütun yeni değere sahip olmalıdır. Bunun gibi bir şey -

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

Bunu Postgres'te yapıyorum ve karakterlere dayalı olarak alt dizeleri nasıl çıkaracağımı biliyorum, ancak bu, her satır için aynı sütundan birden çok alt dizeyi çıkarmam gerektiğinden bana biraz karmaşık görünüyor. Herhangi bir yardım memnuniyetle karşılanacaktır. Teşekkürler!

Answer 1

İlk başta, regexp_split_into_tabletablonuzun her bir satırın tam olarak bir güncelleme içerdiği bir sürümünü almak için ve pozitif bir ön okumaya sahip bir regexp kullanabilirsiniz :

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

Bu, sütunu updatesherhangi bir satırsonu ( \n) ve ardından tek bir kelime ve iki nokta üst üste ( \y.+:) ile böler .

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

Bundan istediğiniz masayı daha kolay oluşturabilirsiniz. Bunu yapmak için, örneğin split_partgüncelleme dizisini istediğiniz üç kısma ayırmak için kullanabilirsiniz .

Bunu ilk bölümle bir araya getirmek size tam sorguyu getirir:

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

İşte bir db <> keman örneği:https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d

Daha fazla ayrıntı / ek bilgi:

• postgres dizelerinde yeni satır karakteri: https://stackoverflow.com/a/26638775/14015737
• postgresql regex kelime sınırları: https://stackoverflow.com/a/3825705/14015737
• dizeleri yeni sütunlara bölme: https://stackoverflow.com/a/8612456/14015737