如何在SQL中对从N到M的所有排列的值求和而不重复

Question

我需要编写一个查询，对所有可能的排列的值求和，并只显示一行结果。我还需要提到的是，表存储为一个数组，列表中的项目数量可以大于或小于3，例如，x、y、y、x或o、p、q、r、p、q、r、o等等

我尝试了一个硬编码查询，但似乎有太多不同的可能性。

SELECT 
case 
when replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'a,b'
or replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'b,a' then 'a,b'
when replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'c,d'
or replace(replace(replace(cast(json_format(cast(ARRAY [table_list] as json)) as varchar), '[["', ''), '"]]'), '","',', ') = 'd,c' then 'c,d'
END AS tables_list,
SUM(value) AS value
FROM my_table
GROUP BY 1

我有一张这样的桌子：

table value
a,b,c   10    
b,c,a   21
c,b,a   12
a,c,b   13
b,a,c   16
c,a,b   12
d,e,f   15
e,f,d   12
f,e,d   13
d,f,e   16
e,d,f   11
f,d,e   20
...

我想得到的是对所有可能的排列求和，并将其存储在一个表下，即对于a，b，c(10+21+12+13+16+12(=84:

table value
a,b,c   84
d,e,f   87

Answer 1

如果我正确理解你的问题，我就有部分答案。观察排序后所有排列都是相同的。如果你有一个对字符串进行排序的函数，比如

string_sort('c,b,a')

产生

a,b,c

然后SQL自己写：

select T, sum(value)
from (select string_sort(table) as T, value
from tables_list ) as A
group by T

我再具体不过了，因为我不知道您的DBMS的功能集。

Answer 2

您可以使用数组和字符串函数对字符串进行排序和分组：

-- sample data
WITH dataset("table", value) AS (
values ('a,b,c', 10),
('b,c,a', 21),
('c,b,a', 12),
('a,c,b', 13),
('b,a,c', 16),
('c,a,b', 12),
('d,e,f', 15),
('e,f,d', 12),
('f,e,d', 13),
('d,f,e', 16),
('e,d,f', 11),
('f,d,e', 20)
),
-- query parts
ordered as (
select array_join(
array_sort(
split("table", ',')), 
','
) t
, value
from dataset
)
select t, sum(value)
from ordered
group by t;

输出：

t	_col1
d，e，f	87
a，b，c	84