我目前正在使用这种方法在PostgreSQL中按字母顺序对字符串中的字母进行排序。是否有其他有效的方法？
select string_agg(c, '') as s
from (select unnest(regexp_split_to_array('ijsAafhareDbv', '')) as c
order by c) as t;
s
--------------
ADaabefhijrsv
在PostgreSQL中按字母顺序对字符串中的字母进行排序
3
- Kaushik Nayak
3
3个回答
4
我创建了3个函数，一个使用我的查询语句，另一个使用Laurenz的查询语句，还有一个：我创建了一个用于排序的Python（plpythonu）函数。最后，我创建了一个包含100,000行的表格（目前是从我的Mac笔记本电脑上创建的），每行都包含一个使用此链接中的random_string函数生成的随机15字符字符串。
create table t as select random_string(15) as s FROM generate_series(1,100000);
这里有三个函数。
CREATE or REPLACE FUNCTION sort1(x TEXT) RETURNS TEXT AS $$
select string_agg(c, '') as s
from (select unnest(regexp_split_to_array($1, '')) as c
order by c) as t;
$$ LANGUAGE SQL IMMUTABLE;
CREATE or REPLACE FUNCTION sort2(x TEXT) RETURNS TEXT AS $$
WITH t(s) AS (VALUES ($1))
SELECT string_agg(substr(t.s, g.g, 1), ''
ORDER BY substr(t.s, g.g, 1)
)
FROM t
CROSS JOIN LATERAL generate_series(1, length(t.s)) g;
$$ LANGUAGE SQL IMMUTABLE;
create language plpythonu;
CREATE or REPLACE FUNCTION pysort(x text)
RETURNS text
AS $$
return ''.join(sorted(x))
$$ LANGUAGE plpythonu IMMUTABLE;
这是三个查询的
EXPLAIN ANALYSE 结果。knayak=# EXPLAIN ANALYSE select sort1(s) FROM t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..26541.00 rows=100000 width=32) (actual time=0.266..7097.740 rows=100000 loops=1)
Planning time: 0.119 ms
Execution time: 7106.871 ms
(3 rows)
knayak=# EXPLAIN ANALYSE select sort2(s) FROM t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..26541.00 rows=100000 width=32) (actual time=0.418..7012.935 rows=100000 loops=1)
Planning time: 0.270 ms
Execution time: 7021.587 ms
(3 rows)
knayak=# EXPLAIN ANALYSE select pysort(s) FROM t;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..26541.00 rows=100000 width=32) (actual time=0.060..389.729 rows=100000 loops=1)
Planning time: 0.048 ms
Execution time: 395.760 ms
(3 rows)
从这个分析来看，结果表明-Python排序最快，前两种方法之间没有显著差异。不过需要在我们系统中实时检查大型表的性能。
- Kaushik Nayak
1
1本地函数 - 实现在C语言中，如Python库中的
sorted方法必须是最快的代码。 - Pavel Stehule3
如果您想使用没有正则表达式的解决方案，您可以使用以下方法：
我将进行基准测试，以确定哪个解决方案更快。
WITH t(s) AS (VALUES ('amfjwzeils'))
SELECT string_agg(substr(t.s, g.g, 1), ''
ORDER BY substr(t.s, g.g, 1)
)
FROM t
CROSS JOIN LATERAL generate_series(1, length(t.s)) g;
string_agg
------------
aefijlmswz
(1 row)
我将进行基准测试，以确定哪个解决方案更快。
- Laurenz Albe
1
谢谢Laurenz。我自己进行了（某种程度的）基准测试并发布了一个答案。如果我漏掉了什么或需要修改任何内容以使其更好地比较，请告诉我。 - Kaushik Nayak
2
用
但是，
当前手册（摘自 pg 10）：
C 实现的函数比我们用 LANGUAGE sql 或 plpgsql 实现的函数要快得多。因此，您用 plpythonu 实现的函数 获胜了。但是，
plpythonu 是一种不受信任的过程化语言。它不是默认安装的，只有超级用户才能创建使用不受信任的语言的函数。您需要注意安全性问题。大多数云服务上根本没有不受信任的语言可用。当前手册（摘自 pg 10）：
您测试的 SQL 函数未经过良好优化。有一千种方法可以提高性能，但是：PL/Python 只能作为“不受信任”的语言使用，这意味着它没有任何限制用户在其中执行什么操作的方式，因此将其命名为
plpythonu。如果 Python 中开发出了安全执行机制，未来可能会推出可信的变体plpython。编写不受信任的 PL/Python 函数的作者必须确保该函数不能用于执行任何不需要的操作，因为它可以执行任何数据库管理员登录后可以执行的操作。只有超级用户可以创建使用不受信任的语言（例如plpythonu）的函数。
Demo
-- func to create random strings
CREATE OR REPLACE FUNCTION f_random_string(int)
RETURNS text AS
$func$
SELECT array_to_string(ARRAY(
SELECT substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', (ceil(random()*62))::int, 1)
FROM generate_series(1, $1)
), '')
$func$ LANGUAGE sql VOLATILE;
-- test tbl with 100K rows
CREATE TABLE tbl(str text);
INSERT INTO tbl
SELECT f_random_string(15)
FROM generate_series(1, 100000) g;
VACUUM ANALYZE tbl;
-- 1: your test function 1 (inefficient)
CREATE OR REPLACE FUNCTION sort1(text) RETURNS text AS
$func$ -- your test function 1 (very inefficient)
SELECT string_agg(c, '')
FROM (SELECT unnest(regexp_split_to_array($1, '')) AS c ORDER BY c) t;
$func$ LANGUAGE sql IMMUTABLE;
-- 2: your test function 2 ( inefficient)
CREATE OR REPLACE FUNCTION sort2(text) RETURNS text AS
$func$
WITH t(s) AS (VALUES ($1))
SELECT string_agg(substr(t.s, g.g, 1), '' ORDER BY substr(t.s, g.g, 1))
FROM t
CROSS JOIN LATERAL generate_series(1, length(t.s)) g;
$func$ LANGUAGE sql IMMUTABLE;
-- 3: remove pointless CTE from sort2
CREATE OR REPLACE FUNCTION sort3(text) RETURNS text AS
$func$
SELECT string_agg(substr($1, g, 1), '' ORDER BY substr($1, g, 1))
FROM generate_series(1, length($1)) g;
$func$ LANGUAGE sql IMMUTABLE;
-- 4: use unnest instead of calling substr N times
CREATE OR REPLACE FUNCTION sort4(text) RETURNS text AS
$func$
SELECT string_agg(c, '' ORDER BY c)
FROM unnest(string_to_array($1, NULL)) c
$func$ LANGUAGE sql IMMUTABLE;
-- 5: ORDER BY in subquery
CREATE OR REPLACE FUNCTION sort5(text) RETURNS text AS
$func$
SELECT string_agg(c, '')
FROM (
SELECT c
FROM unnest(string_to_array($1, NULL)) c
ORDER BY c
) sub
$func$ LANGUAGE sql IMMUTABLE;
-- 6: SRF in SELECT list
CREATE OR REPLACE FUNCTION sort6(text) RETURNS text AS
$func$
SELECT string_agg(c, '')
FROM (SELECT unnest(string_to_array($1, NULL)) c ORDER BY 1) sub
$func$ LANGUAGE sql IMMUTABLE;
-- 7: ARRAY constructor instead of aggregate func
CREATE OR REPLACE FUNCTION sort7(text) RETURNS text AS
$func$
SELECT array_to_string(ARRAY(SELECT unnest(string_to_array($1, NULL)) c ORDER BY c), '')
$func$ LANGUAGE sql IMMUTABLE;
-- 8: The same with COLLATE "C"
CREATE OR REPLACE FUNCTION sort8(text) RETURNS text AS
$func$
SELECT array_to_string(ARRAY(SELECT unnest(string_to_array($1 COLLATE "C", NULL)) c ORDER BY c), '')
$func$ LANGUAGE sql IMMUTABLE;
SELECT str, sort1(str), sort2(str), sort3(str), sort4(str), sort5(str), sort6(str), sort7(str), sort8(str) FROM tbl LIMIT 1; -- result samplestr | sort1 | sort2 | sort3 | sort4 | sort5 | sort6 | sort7 | sort8 :-------------- | :-------------- | :-------------- | :-------------- | :-------------- | :-------------- | :-------------- | :-------------- | :-------------- tUkmori4D1rHhI1 | 114DhHiIkmorrtU | 114DhHiIkmorrtU | 114DhHiIkmorrtU | 114DhHiIkmorrtU | 114DhHiIkmorrtU | 114DhHiIkmorrtU | 114DhHiIkmorrtU | 114DHIUhikmorrt
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort1(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.053 ms | | Execution time: 2742.904 ms |
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort2(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.105 ms | | Execution time: 2579.397 ms |
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort3(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.079 ms | | Execution time: 2191.228 ms |
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort4(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.075 ms | | Execution time: 2194.780 ms |
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort5(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.083 ms | | Execution time: 1902.829 ms |
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort6(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.075 ms | | Execution time: 1866.407 ms |
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort7(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.067 ms | | Execution time: 1863.713 ms |
EXPLAIN (ANALYZE, TIMING OFF) SELECT sort8(str) FROM tbl;| QUERY PLAN | | :--------------------------------------------------------------------------------------- | | Seq Scan on tbl (cost=0.00..26541.00 rows=100000 width=32) (actual rows=100000 loops=1) | | Planning time: 0.074 ms | | Execution time: 1569.376 ms |
db<>fiddle 在这里
最后一个排序不使用COLLATION规则，而是严格按照字符的字节值进行排序，这样会更加高效。但是，您可能需要根据不同的语言环境来排序。
- Erwin Brandstetter
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
pl/perl或pl/python或其他第三方过程语言，对于这样的任务来说它可能会更快。 - Abelisto