SQL 常用函数及逻辑片段

TERADATA
1.TERADATA 中行转列合并
– 一个客户有多个客户名称，客户名称拼接为一条数据

select cust_id
,trim(TRAILING ',' from (XMLAGG(cast(cust_name as varchar(100)) || ',')(varchar(100))))  as cust_names
from tmpdb.temp_cust_info t
group by cust_id
;

oracle
1.oracle中多行转一列合并

select cust_id
       ,listagg(cust_name,'$') within group (order by cust_dt) as cust_name_list
from tmpdb.temp_cust_info to
group by cust_id

postgresql
1.postgresql 中多行转一列合并
1）string_agg

select cust_id
,string_agg(cust_name,',' order by cust_id)  as cust_names
from tmpdb.temp_cust_info t
group by cust_id
;

2）array_agg

select cust_id, array_agg(distinct course ORDER BY course ) from tmpdb.temp_cust_info
group by cust_id;

3.一行转多列

select * from (
select T.cust_num, F.*
from (
      select cust_id  -- 客户号
             ,base_acc_flg  -- 是否基本户
      	     ,bill_flg        -- 是否票据客户
      	     ,subsaly_flg     -- 是否代发工资
      	     ,fun_flg         -- 是否购买基金
      from tmpdb.temp_cust_info
      ) T
CROSS JOIN LATERAL(
VALUES
(base_acc_flg,'base_acc_flg'),-- 是否基本户
(bill_flg,'bill_flg'),        -- 是否票据客户
(subsaly_flg,'subsaly_flg'),  -- 是否代发工资
(fun_flg,'fun_flg')           -- 是否购买基金
) AS F(FLAG, TITLE)
) AS G where G.FLAG = '1'
;

4.字符串分割函数
1).分隔符取特定位置值

select split_part('1,2,3,4,5,6,7',',',2)

2).把字符分割成数据集(可用于字符串数据列转行)

select regexp_split_to_table('1,2,3,4,5,6,7',',')

3).把字符分割成数组

select regexp_split_to_array('1,2,3,4,5,6,7',',')

5.拼接函数
1).concat是纯粹的拼接函数，可以忽略null值直接拼接，拼接成的值没有分割符

select concat('1',null,'2','3')

2).concat_ws 拼接可以指定分隔符

select concat_ws(',','1',null,'2','3')

6.窗口函数

--数值相等时 序号递增
row_number() over([partition by xxx] [order by xxx1 desc|asc])
-- 数据相等时 序号一致 后面序号不连续 例如 1 1 1 4
rank() over([partition by xxx] [order by xxx1 desc|asc])
-- 数据相等时 序号一致 后面序号连续   例如 1 2 2 2
dense_rank() over([partition by xxx] [order by xxx1 desc|asc])