postgresql 將逗號(hào)分隔的字符串轉(zhuǎn)為多行的實(shí)例

-- 將逗號(hào)分隔的字符串轉(zhuǎn)為多行

SELECT unnest(string_to_array('4513,4564,555',',')) as a1;

-- array轉(zhuǎn)為行

SELECT unnest(ARRAY[1,2]);
SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);

補(bǔ)充：PostgreSQL 行轉(zhuǎn)列、列轉(zhuǎn)行字符串函數(shù)、字符串分割函數(shù)

本文主要介紹PostgreSQL 的行轉(zhuǎn)列、列轉(zhuǎn)行以及字符串切割函數(shù)，實(shí)際業(yè)務(wù)中對(duì)前兩個(gè)均有使用，并配有實(shí)際例子參考。

1、字符串列轉(zhuǎn)行

string_agg，某些地方也稱為字符串聚合操作。

如果需要按照一個(gè)字符串按照某個(gè)分割符拼接起來(lái)。

例如：

按照id把字符串按照指定分隔符拼接起來(lái)。實(shí)際業(yè)務(wù)中有什么需要這種字符串函數(shù)的呢？

-----------------------update 2020年1月16日17:05:59-----------------------

這里學(xué)習(xí)一個(gè)和系統(tǒng)表有關(guān)的查詢，用到了stragg ，用法之妙，自己體會(huì)。

 SELECT string_agg(att.attname,',' order by attrnums) as distribution
  FROM gp_distribution_policy a,pg_attribute att
  WHERE a.localoid ='sor.wpp_adefect_f_n'::regclass
  and a.localoid = att.attrelid
  and att.attnum = any(a.attrnums);

select attname,attnum from pg_attribute where attrelid='26625' order by attnum asc;

可以看到每個(gè)表上除了可見(jiàn)的列之外還有attrnums 1的列。這幾個(gè)列用來(lái)實(shí)現(xiàn)mvcc、表明數(shù)據(jù)的物理位置、數(shù)據(jù)所處segID...

select string_agg(attname,',' order by attnum) from pg_attribute where attrelid='26625' and attnum >0;

將所有可見(jiàn)列查詢出來(lái)拼接sql，屢試不爽。

2、字符串行轉(zhuǎn)列

regexp_split_to_table(string, pattern [, flags ])

regexp_split_to_table(string, pattern [, flags ])。如果沒(méi)有與pattern的匹配，該函數(shù)返回string。

如果有至少有一個(gè)匹配，對(duì)每一個(gè)匹配它都返回從上一個(gè)匹配的末尾（或者串的開(kāi)頭）到這次匹配開(kāi)頭之間的文本。當(dāng)沒(méi)有更多匹配時(shí)，它返回從上一次匹配的末尾到串末尾之間的文本。

flags參數(shù)是一個(gè)可選的文本串，它包含零個(gè)或更多單字母標(biāo)志，這些標(biāo)識(shí)可以改變?cè)摵瘮?shù)的行為。

這個(gè)標(biāo)識(shí)有很多，具體可查看http://postgres.cn/docs/9.6/functions-matching.html。不過(guò)該參數(shù)是可以省略的，我看到很少用這個(gè)flg的。

E是Posix樣式轉(zhuǎn)義字符串的前綴?，F(xiàn)代Postgres通常不需要這個(gè),此處的E可有可無(wú)。

--\\s+ 可匹配至少一個(gè)空白字符。

--\\s 表示空白字符。包括，空格，制表符等

--\s*

--*是貪婪模式,會(huì)盡可能匹配更多的字符

--而*?是非貪婪模式 會(huì)盡量匹配少的字符

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s') AS foo;
SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*?') AS foo;

關(guān)于正則表達(dá)式計(jì)劃以專題來(lái)學(xué)習(xí)

幾個(gè)元字符是需要記住的：

-----------------------update 2019年10月23日11:37:32-----------------------

imagePath字符串切割處理

如果想將如上的長(zhǎng)列轉(zhuǎn)換為多行，可通過(guò)regexp_split_to_table()來(lái)處理。處理的結(jié)果如下。

sql形如:

select regexp_split_to_table(image_path,E'\\ ') pattern from (select distinct panel_id, 'Y:\\' || prod_id || '\\' || substring( glass_id, 0, 6 )|| '\\' || substring( glass_id, 0, 9 )|| '\\' || panel_id || '\\' || 'big' || ' '||
 'Y:\\' || prod_id || '\\' || substring( glass_id, 0, 6 )|| '\\' || substring( glass_id, 0, 9 )|| '\\' || panel_id || '\\' || 'small'|| ' '||
 'Y:\\' || prod_id || '\\' || substring( glass_id, 0, 6 )|| '\\' || substring( glass_id, 0, 9 )|| '\\' || panel_id || '\\' || 'IMAGE' as image_path 
from tabelName
where 1 = 1
and time>= '2019-09-09 00:00:00'
and time= '2019-09-10 08:00:00'
)A

3、字符串分割符

split_part(str,',',1)
select split_part('A3332-22222222','-',1);  - > A3332 
select split_part('A3332-22222222','-',2);  - > 22222222

這個(gè)比較簡(jiǎn)單一看便知道了。