PostgreSQl常用查询操作

1、分组统计

WITH T AS (
	SELECT 
		a.qc_date, a.task_type, b.rd_code, a.qc_flag, a.task_status, COUNT ( * ) AS C 
	FROM
		cg_aoi_qc a, rd_code b 
	WHERE 
		b.citycode = a.city_code and a.qc_date = 20201027 and a.task_type = 4 
	GROUP BY
		a.qc_date, a.task_type, b.rd_code, a.qc_flag, a.task_status
	) SELECT 
	T.qc_date, T.task_type, T.rd_code,
	SUM ( C ) all_c,
	SUM ( CASE WHEN qc_flag&1 > 0 THEN C ELSE 0 END ) ok_c,
	SUM ( CASE WHEN qc_flag&4 > 0 THEN C ELSE 0 END ) ng_c,
	SUM ( CASE WHEN task_status = 2 THEN C ELSE 0 END ) all_c_2,
	SUM ( CASE WHEN qc_flag&1 > 0 AND task_status = 2 THEN C ELSE 0 END ) ok_c_2,
	SUM ( CASE WHEN qc_flag&4 > 0 AND task_status = 2 THEN C ELSE 0 END ) ng_c_2
FROM
T 
WHERE
	T.rd_code IS NOT NULL AND T.rd_code != '' 
GROUP BY
	T.qc_date, T.task_type, T.rd_code
	
	
	
WITH T AS (
		SELECT 
			src, COUNT ( * ) AS C 
		FROM
			bld_ningbo_330200
		GROUP BY src 
		) SELECT 
		SUM ( C ) all_cnt,
		SUM ( CASE WHEN T.src = '71' THEN C ELSE 0 END ) yx_cnt
		FROM
		T 

2、删除重复数据

--  方法1删除重复的数据
delete from qb_bj.cg_aoi_task where ctid not in (select min(ctid) from qb_bj.cg_aoi_task group by guid);

-- 方法2 快速
delete from qb_bj.cg_aoi_task a where a.ctid = any(array (select ctid from (select row_number() over (partition by guid), ctid from qb_bj.cg_aoi_task where ctid not in (select min(ctid) from qb_bj.cg_aoi_task group by guid)
) t where t.row_number > 1));

3、查询重复数据的最新记录

获取最新batch_id的数据,按id去重
SELECT batch_id, guid FROM (
SELECT a.batch_id, a.id AS guid, a.city_code, (row_number() over (PARTITION by a.id ORDER BY a.batch_id )) as num
FROM qb_bj.cg_aoi_result a
WHERE a.batch_id>='20201009_4' and a.task_type = 4
) AS T WHERE T.num = 1

4、字符串分隔

SELECT split_part('123456.12123,44.762342343', ',', 1) as x,split_part('123456.12123,44.762342343', ',', 2) as y

5、获取UUID

PG11：select UPPER(replace(uuid_generate_v4()::varchar, '-', ''))
PG9：SELECT UPPER(replace(gen_random_uuid()::varchar, '-', ''))

6、重复数据排序后编号，查询重复次数

• row_number() over (PARTITION by name_chn,adcode) as znxh,count(1) over (partition by name_chn,adcode) as znsl 

7、联表删除与更新

delete from db_lx.t_aj_ajjbxx ajjb 
USING db_lx.t_aj_ajfbxx ajfb,db_lx.t_xt_dwxx dw where ajjb.c_bh = ajfb.c_ajbh and ajfb.c_ssdw=dw.c_bh and dw.c_sfbh='65';

8、更新时，更新自增序列

insert into test
(test, float)
VALUES
		('2', 10)
ON CONFLICT(test) DO UPDATE
SET float = EXCLUDED.float
, sys_id = nextval('"qb_bj".seq_test_fid'::regclass)

或者创建触发器
CREATE OR REPLACE FUNCTION upd_test_sys_id()
RETURNS TRIGGER AS $$
BEGIN
    NEW.sys_id = nextval('seq_test_fid'::regclass);
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER "updated_test_sys_id" BEFORE UPDATE ON "test"
FOR EACH ROW
EXECUTE PROCEDURE upd_test_sys_id();

9、JSON 操作

UPDATE aoi_check_result 
SET key_words = CONCAT('{"POI":', key_words::jsonb->'POI', '}')
WHERE task_src = 6 and key_words LIKE '%POI%';

10、group_concat

string_agg(字段,',')

11、查询回车符，换行符以及替换

char(10) 是换行NL  char(13)是回车CR
查询：field like '%'||chr(13)||'%'  or field  like '%'||chr(10)||'%';
替换:  regexp_replace(field,  E'[\\n\\r]+', ' ', 'g' )

12、字符串转为结果集，转换成数组

regexp_split_to_table 函数 转为结果集
regexp_split_to_array 函数 转换成数组
单个：‘1’ = any(regexp_split_to_array(‘11,21,32,33’, ‘,’))

多个：regexp_split_to_array(‘11,21,32,33’, ‘,’) && array[‘11’,‘1’]

13、字符串用不等于时，未得到效果

如： WHERE remark <> 'test'  一个结果也没有
原因：remark存在null  当为null时，不参与比较
修改：WHERE remark is null or remark <> 'test'