pg使用小技巧

mysql使用小技巧
pg使用小技巧
ck使用小技巧

一些常用的

1
2
3
4
5
6
7
-- 2021-10-17 10:56:35
-- 查询版本号
select version();

-- 查看事务状态
SHOW default_transaction_isolation;
SHOW transaction_isolation;

json写入错误

1
2
3
-- 问题:json 类型插入报错 column "**" is of type json but expression is of type character varying
-- 解决方案:url添加后缀 &stringtype=unspecified
-- 参考: jdbc:postgresql://10.11.12.232:5432/patrol?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&stringtype=unspecified

修改连接数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 2021-10-18 18:21:08
-- 2023-03-03 17:37:13 更新
-- 1、查看最大连接数
show max_connections;

-- 2、查看当前连接数
select count(*) from pg_stat_activity;

-- 3、查看系统保留的用户数(此参数为数据库为超级用户预留的连接数,默认值为3。)
-- 当数据库的连接数达到(max_connections - superuser_reserved_connections)时,只有超级用户才能建立新的数据库连接,
-- 普通用户连接时将会返回错误信息“FATAL: sorry, too many clients already.”
-- 或者“FATAL: remaining connection slots are reserved for non-replication superuser connections”,登录数据库查询。
show superuser_reserved_connections;

-- 4、查看按用户分组统计连接数
select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

-- 5、优化建议
-- postgresql的最大连接数合适值used_connections/max_connections ≈ 85%
-- 通过postgresql.conf文件修改,需重启服务
-- 修改最大连接数 alter system set max_connections= 数量

-- 6、Docker 修改最大连接数
-- docker inspect {容器id} | grep Mounts -A 20
-- vim postgresql.conf ===> 搜索 max_connections 修改后重启容器

四舍五入

1
2
3
4
5
6
-- 2021-11-04 11:54:33
-- 测试 PostgreSQL 列计算
select 1/4 AS value;-- 0
select 11/4 AS value;-- 2
select ROUND(1::numeric/4::numeric,2) AS value;-- 0.25 => 1/4 保留两位小数
-- 例如(金额 money:元 -> 万 保留一位小数):SELECT ROUND(money::numeric/10000::numeric,1) feature_value FROM table ...

日期函数

1
2
3
4
5
6
7
-- 2023-03-07 14:24:28
<if test="dbType == 'PostgreSQL'">
to_char(date_created,'yyyy-mm-dd') as cover_day
</if>
<if test="dbType == 'MySQL'">
date_format(date_created,'%Y-%m-%d') as cover_day
</if>

count多条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 2023-03-07 09:18:24
-- 方式1 (不兼容mysql)
WITH tmp AS (...)

SELECT
COUNT(1) FILTER(WHERE rel_type='CATEGORY_TASK') score_count,-- 打分项
COUNT(1) FILTER(WHERE rel_type='CATEGORY_TASK' AND score!=max_score) loss_score_count,-- 失分项
COUNT(1) FILTER(WHERE rel_type='DIAGNOSIS_TASK') diagnosis_count,-- 异常跟进
(SELECT ROUND(SUM(score)/SUM(max_score)*100, 0) FROM tmp WHERE rel_type='CATEGORY_TASK') patrol_score-- 巡检检查分
FROM tmp

-- 2023-03-17 18:03:08
-- 方式2 (兼容mysql)
WITH tmp AS (SELECT COUNT(1) FROM patrol_config WHERE tenant_id=$tenant_id$ AND config_type='CATEGORY')

SELECT
COUNT(CASE WHEN rel_type='SIGNED' THEN 1 END) signed,
COUNT(CASE WHEN rel_type='CATEGORY_TASK' THEN 1 END) category_task,
(SELECT * FROM tmp) category_task_all,
COUNT(CASE WHEN rel_type='DIAGNOSIS_TASK' THEN 1 END) diagnosis_task,
COUNT(CASE WHEN rel_type='PATROL_SUMMARY' THEN 1 END) patrol_summary
FROM patrol_rel_detail WHERE record_id=$record_id$

rank函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
2023-01-28...11.47.49 pg rank函数.txt

之前竟然没记录,今天邹俊问我,我才记录

select
region_id,
score,
-- 默认别名 rank
rank() over(partition by null order by score desc)
FROM t_score_card_report
WHERE tenant_id='xtep'
and score_card_type ='DAY_STORE_SC'
and calc_begin_time ='2023-01-27'


region_id|score|rank|
---------+-----+----+
3012| 4.10| 1|
2204| 4.10| 1|
2200| 4.10| 1|
2936| 4.00| 4|
3036| 4.00| 4|
3035| 4.00| 4|
2642| 4.00| 4|
3154| 4.00| 4|
2217| 3.90| 9|
2802| 3.90| 9|
2214| 3.80| 11|
2957| 3.80| 11|
2266| 3.80| 11|
3046| 3.80| 11|
2974| 3.80| 11|
3068| 3.80| 11|
3123| 3.80| 11|

查询默认值返回

1
2
3
-- 2021-12-27 17:19:04
-- 默认值(查询 end_date = null 的时候,使用默认值 '2021-12-31')
SELECT id,start_date,COALESCE(end_date,'2021-12-31') FROM t_store_log WHERE id=323

序列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 2021-10-21 10:09:43
-- 查看下一个序列号
-- '%s_id_seq' => %s 表名
SELECT nextval('t_cloak_user_id_seq');
SELECT nextval('t_task_center_log_id_seq');
--nextval(regclass)
--currval(regclass)
--lastval()
--setval(regclass, bigint)
--setval(regclass, bigint, boolean)

-- 一个库所有表恢复最大
SELECT CONCAT(E'SELECT setval(\'', relname, E'\', MAX(id)) FROM ', split_part(relname, '_id_seq', 1), ';') sql FROM pg_class WHERE relkind = 'S'

-- 2023-12-15 14:57:13 使用序列插入 ===> 序列名称使用上面的方式查询【一个库所有表恢复最大】
SELECT setval('patrol_config_id_seq', MAX(id)) FROM patrol_config;
insert into public.patrol_config (id, tenant_id, config_type, category) values (nextval('patrol_config_id_seq'), 'amoros', 'DIAGNOSIS', '鲜货销售占比');
insert into public.patrol_config (id, tenant_id, config_type, category) values (nextval('patrol_config_id_seq'), 'amoros', 'DIAGNOSIS', '零售销售额');
insert into public.patrol_config (id, tenant_id, config_type, category) values (nextval('patrol_config_id_seq'), 'amoros', 'DIAGNOSIS', '件牌价');
SELECT setval('patrol_config_id_seq', MAX(id)) FROM patrol_config;

值替换

1
2
3
4
5
6
7
8
9
10
-- 2021-12-16 16:45:46
-- 值替换
-- [1] string 类型(把表 table_01 中varchar类型字段 rule_json_data 的值 aaa 全部替换为 bbb)
UPDATE table_01 SET rule_json_data=replace(rule_json_data,'aaa','bbb')
-- [2] json 类型(把表 table_01 中json类型字段 rule_json_data 的值 aaa 全部替换为 bbb。先把字段::text转为字符串,替换后的结果::json转为json类型赋值给rule_json_data字段)
UPDATE table_01SET rule_json_data=replace(rule_json_data::text,'aaa','bbb')::json
-- [3] 转义符。在要替换的字符串前面加大写的 E,在内容中'单引号前面加 \ 反斜杠
-- 把 scr.region_type='STORE' 修改为 scr.region_type='BRANCH'
UPDATE t_sc_feature_template_rule
SET rule_json_data=replace(rule_json_data::text,E'scr.region_type=\'STORE\'',E'scr.region_type=\'BRANCH\'')::json;

json数据删除元素

1
2
3
4
5
6
7
8
9
10
-- 2023-12-01 15:45:03
UPDATE public.t_sc_feature_month_target_info
SET fetch_target_json = (
SELECT json_agg(element)
FROM (
SELECT json_array_elements(fetch_target_json) AS element
) AS sub
WHERE element->>'featureCode' != 'sales'
)
WHERE id = 9444;

json解析/行转列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- 取key,转numeric,求sum          SUM((sc.json_config ->> 'maxScore')::numeric)
WITH tmp AS(
WITH r AS(...)
SELECT
cc.category,
-- rd.score,
-- CAST(sc.json_config ->> 'maxScore' AS NUMERIC) max_score
-- (sc.json_config ->> 'maxScore')::numeric max_score

SUM(rd.score) score_sum,
SUM((sc.json_config ->> 'maxScore')::numeric) max_score_sum
FROM r
LEFT JOIN patrol_rel_detail rd ON r.id=rd.record_id
LEFT JOIN patrol_category_config cc ON rd.rel_id=cc.id
LEFT JOIN patrol_score_config sc ON cc.score_config_id=sc.id
WHERE rd.rel_type='SCORE_TASK'
GROUP BY cc.category
)

SELECT
category,-- 大类
ROUND(score_sum/max_score_sum*100, 0) score -- 检查项平均分 大类中每一项总分/最高总分 * 100
FROM tmp
ORDER BY score DESC
LIMIT 5


-- PG case when 行转列
-- PG JSON 类型解析
-- ->> 双箭头获取字符串值 sales
-- -> 单箭头获取字符串 "sales"
select
sum(case when feature_code = 'sales' then target_value else 0 end ) sales,
sum(case when feature_code = 'retailCount' then target_value else 0 end ) retail_count,
sum(case when feature_code = 'atv' then target_value else 0 end) atv,
sum(case when feature_code = 'storeCustomerCnt' then target_value else 0 end) store_customer_cnt,
sum(case when feature_code = 'upt' then target_value else 0 end) upt,
sum(case when feature_code = 'cr' then target_value else 0 end) cr,
sum(case when feature_code = 'md' then target_value else 0 end) md
from (
select
sub.code feature_code,
sum(sub.value::numeric) target_value
from
(
select
json_array_elements(fetch_target_json) ->> 'featureCode' code,
json_array_elements(fetch_target_json) ->> 'targetValue' value
from
t_sc_feature_daily_target_info
where
tenant_id = $tenant_id$
and rel_type = 'STORE'
and rel_id in ($storeIds$)
and stats_date between $startDate$ and $endDate$) sub
group by sub.code order by sub.code
) sub2

一行转多行

1
2
3
4
5
6
7
8
-- 2023-02-14 16:25:49
WITH s AS (SELECT store_id, org_structure_id FROM t_sys_org_structure_store WHERE tenant_id=$tenant_id$)
SELECT
s.store_id,
-- o.path -- 一行变多行
unnest(string_to_array(o.path, ',')) org_id
FROM s left join v_sys_org_structure o on s.org_structure_id=o.id
-- WHERE s.store_id=8

字符串合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 2023-04-17 09:49:09

-- -- --
-- PG --
-- -- --

-- concat 所有元素直接拼接(忽略NULL)
SELECT concat('a', 'b', 'c') -- abc
SELECT concat('a', NULL, 'c') -- ac

-- concat_ws 第一个为拼接字符,忽略NULL
SELECT concat_ws('.', 'a', 'b', NULL, 'c') -- a.b.c

-- 字段合并一行,支持排序
SELECT tenant_id, string_agg(id::text, ',') FROM hawkeye_tenant_config GROUP BY tenant_id
SELECT tenant_id, string_agg(id::text, ',' ORDER BY id DESC) FROM hawkeye_tenant_config GROUP BY tenant_id

SELECT tenant_id, array_agg(id) FROM hawkeye_tenant_config GROUP BY tenant_id-- 数组类型
SELECT tenant_id, array_agg(DISTINCT id) FROM hawkeye_tenant_config GROUP BY tenant_id-- 数组类型 去重
SELECT tenant_id, array_agg(DISTINCT id ORDER BY id DESC) FROM hawkeye_tenant_config GROUP BY tenant_id-- 数组类型 去重 排序
SELECT tenant_id, array_to_string(array_agg(DISTINCT id ORDER BY id DESC), ',') FROM hawkeye_tenant_config GROUP BY tenant_id-- 字符串类型

建表 SQL

1
-- 手动 Navicat 建,DBeaver 导出 DDL(只勾选 “Show comments”)。

修改字段

1
2
-- 修改字段类型
ALTER TABLE hawkeye_job_log ALTER COLUMN rel_id TYPE varchar(255);

索引

1
2
3
4
5
6
7
8
9
-- 2023-03-16 11:42:51   创建
CREATE INDEX hawkeye_job_log_date_created_job_id_idx ON hawkeye_job_log (date_created, job_id);

-- 2023-12-05 16:08:38 查询
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'message';

-- 2024-02-22 13:19:51 删除
drop index 索引名;
drop index hawkeye_job_log_date_created_job_id_idx;