在使用 awk 脚本；数组是一大利器；在很多场景是用数组能处理。
在 python 中，数据类型list；相当于array类型。
在 Oracle 中，对 array 不够友好，感觉像是鸡肋。但是在 PostgreSQL 中，对array有很多支持，很多场景可以应用到。下面慢慢说
1、any(array) 替换 in（table）
-- 案例1-- 创建表A；插入1000条记录；并每条记录重复4次postgres=# create table A (id int, info text);CREATE TABLEpostgres=# postgres=# insert into A select generate_series(1,1000), 'lottu';INSERT 0 1000postgres=# postgres=# insert into A select generate_series(1,1000), 'lottu';INSERT 0 1000postgres=# insert into A select * from A;INSERT 0 2000-- 用in的方式去处理重复数据postgres=# begin;BEGINpostgres=# explain (analyze, costs, timing) delete from A where ctid not in (select min(ctid) from A group by id, info); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Delete on a (cost=74.38..131.31 rows=1397 width=6) (actual time=12.619..12.619 rows=0 loops=1) -> Seq Scan on a (cost=74.38..131.31 rows=1397 width=6) (actual time=5.146..7.129 rows=3000 loops=1) Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 1000 SubPlan 1 -> HashAggregate (cost=70.89..73.69 rows=279 width=42) (actual time=3.762..4.155 rows=1000 loops=1) Group Key: a_1.id, a_1.info -> Seq Scan on a a_1 (cost=0.00..49.94 rows=2794 width=42) (actual time=0.017..1.158 rows=4000 loops=1) Planning Time: 1.923 ms Execution Time: 44.130 ms(10 rows)-- 用any(array)的方式处理postgres=# explain (analyze, costs, timing) delete from Apostgres-# where ctid = any(array (select ctidpostgres(# from (select "row_number"() over(partition by id, info) as rn,postgres(# ctidpostgres(# from A) as adpostgres(# where ad.rn > 1)); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Delete on a (cost=300.69..340.79 rows=10 width=6) (actual time=17.686..17.686 rows=0 loops=1) InitPlan 1 (returns $0) -> Subquery Scan on ad (cost=209.87..300.68 rows=931 width=6) (actual time=3.995..9.503 rows=3000 loops=1) Filter: (ad.rn > 1) Rows Removed by Filter: 1000 -> WindowAgg (cost=209.87..265.75 rows=2794 width=50) (actual time=3.986..8.570 rows=4000 loops=1) -> Sort (cost=209.87..216.86 rows=2794 width=42) (actual time=3.974..4.577 rows=4000 loops=1) Sort Key: a_1.id, a_1.info Sort Method: quicksort Memory: 284kB -> Seq Scan on a a_1 (cost=0.00..49.94 rows=2794 width=42) (actual time=0.015..1.486 rows=4000 loops=1) -> Tid Scan on a (cost=0.01..40.11 rows=10 width=6) (actual time=11.130..12.945 rows=3000 loops=1) TID Cond: (ctid = ANY ($0)) Planning Time: 0.619 ms Execution Time: 17.808 ms(14 rows)结论：1、效率大大提升；数据量越大提升效果越好；any（array） 的效果 >= in2、判断 array 所含元素的方法,有 any / some (any) 还有 all两种方法2、array 相关函数
-- string 转换 array-- 函数 string_to_arrayselect array_to_string(array[1, 2, 3], '~^~'); array_to_string ----------------- 1~^~2~^~3-- 函数 string_to_arrayselect string_to_array('1~^~2~^~3','~^~'); string_to_array ----------------- {1,2,3}-- 函数 regexp_split_to_array；跟string_to_array有点类似select regexp_split_to_array('1~^~2~^~3','\~\^\~'); regexp_split_to_array ----------------------- {1,2,3}-- 函数 unnestselect unnest(array['a', 'b', 'c']); unnest -------- a b c-- 还可以结合with ordinality；添加行号select * from unnest(array['a', 'b', 'c']) with ordinality; unnest | ordinality --------+------------ a | 1 b | 2 c | 3原文转载：http://www.shaoqun.com/a/477645.html
环球华网：https://www.ikjzd.com/w/1063
巴克莱：https://www.ikjzd.com/w/2775
贸发局：https://www.ikjzd.com/w/1621
在使用awk脚本；数组是一大利器；在很多场景是用数组能处理。在python中，数据类型list；相当于array类型。在Oracle中，对array不够友好，感觉像是鸡肋。但是在PostgreSQL中，对array有很多支持，很多场景可以应用到。下面慢慢说1、any(array)替换in（table）--案例1--创建表A；插入1000条记录；并每条记录重复4次postgres=#createta
锦桥纺织网：https://www.ikjzd.com/w/2469
慧聪集团：https://www.ikjzd.com/w/1836
免费22天JS官方注册资料：https://www.ikjzd.com/tl/107615
独立站怎么做？ 跨境电商运营品牌独立站的秘籍分享！：https://www.ikjzd.com/home/107612
跨境电商最容易爆单的3C电子品类，现在最大的难题是什么？：https://www.ikjzd.com/home/102676
No comments:
Post a Comment