arrays - Postgresql 函数 string_to_array

Question

我有一张 table :

   c1|c2|c3|c4
-----+--+--+----    
    a  b c  10
    a  a b  20
    c  a c  10
    b  b c  10
    c  b c  30

我想编写一个函数，其中输入是 3 个字符串/文本，例如 ('a b c, b d, c')，比较每个元素相互，查找是否存在具有这种组合的行，将第 4 (c4) 列的数字相加。但是如果有b a c或c a b的星座，它就会匹配a b c 10。如果有像 b c c 这样的行，那么它就不会像 c b b 这样的行。每场比赛都是独一无二的。

我认为最好的方法是使用 string_to_array(text, text)。

我整理了一些伪代码，但不知道如何用 SQL 编写它。可能逻辑也不对。

function (x,y,z)
 res = 0
 x_array = string_to_array(x, ' ')
 y_array = string_to_array(y, ' ')
 z_array = string_to_array(z, ' ')

 foreach(x_item in x_array)
  foreach(y_item in y_array)
   foreach(z_item in z_array)
    if  (c1 = (x_item || y_item || z_item ) && c2 = (x_item || y_item || z_item ) && c3 = (x_item || y_item || z_item ))
     res++

编辑

• 首先，示例表中存在错误。有一排 a b c 和 c b a。这不可能。 a b c = c b a !并且每一行都必须是唯一的。
• 示例:三个text 输入a b c | b c | c
• 每个元素 vs 每个元素:a b c , a c c, b b c, b c c, c b c, c c c
• a b c = 10;a c c(与c a c相同)= 10；b b c = 10;b c c(与c b c相同)= 30；c b c = 30;c c c(不匹配)= 0；结果 = 90

Answer 1

我认为这可能是您想要的:

返回列 c4 的总和来自给定的三个标记集与列 (c1, c2, c3) 匹配的所有行.

<罢工>

<罢工>

简单版

contains @> and is contained <@ by operators 更简单:

SELECT sum(c4) AS sum_of_matching_c4
FROM   tbl
WHERE  ARRAY[c1,c2,c3] <@ ARRAY['b', 'a', 'c'] -- strings in arbitrary order
AND    ARRAY[c1,c2,c3] @> ARRAY['b', 'a', 'c'];

<罢工>

抱歉，('b', 'c', 'c') 会失败与 ('c', 'b', 'b') .

缓慢而确定

WITH i(arr) AS (
   SELECT ARRAY(VALUES ('b'), ('c'), ('c') ORDER BY 1)  -- input once
   )                                                    -- in arbitrary order
SELECT sum(c4) AS sum_of_matching_c4
FROM  (
  SELECT c4, array_agg(x ORDER BY x) AS arr
  FROM  (
      SELECT ctid, c4, unnest(ARRAY[c1,c2,c3]) AS x
      FROM   tbl t, i
      WHERE  ARRAY[c1,c2,c3] <@ arr -- optional pre-selection
      AND    ARRAY[c1,c2,c3] @> arr -- for better performance?
      ) a
   GROUP BY ctid, c4
   ) b
JOIN i USING (arr)

-> sqlfiddle demo.

主要的困难是对行内列的值进行排序。

对于您的输入(3 个字符串)，我在 WHERE 中实现了这一点带有 VALUE 的子句CTE 中的表达式我立即订购并将其收集在一个数组中。为了方便起见，我使用了 CTE，因此我们只需在一个地方输入值。

行值更复杂。我将三列放在一个数组中，然后用 unnest() 将其分成几行.由于您没有提供主键，我使用 ctid 作为临时代理主键 - 我需要 GROUP BY填充现在排序的 (c1, c2, c3)成一个数组。

最后我总结了所有c4现在排序的数组完全匹配的行数。

注意:我明确表示不使用string_agg()因为那不会产生不同的结果。考虑:

'abc' 'cde' 'fgh'
'ab' 'ccdef' 'gh'

.. 如果连接起来会产生相同的字符串。

指数/表现

您可能会考虑保存预先订购的数据以加快查询速度。在飞行中做这件事很昂贵。 IE。您可以预先生成排序的数组并将其保存为冗余列，然后您可以使用索引来支持它。以冗余数据存储为代价，应该会快几个数量级。
如果您正在处理长字符串，解决方案类似于我在 related answer on dba.SE 中概述的解决方案可能是最好的行动方案。

或者(首选!)保证(c1, c2, c3)始终按升序存储。您可以使用触发器 BEFORE INSERT OR UPDATE保持行内的值有序。没有冗余存储，您可以简单地创建一个 multi-column index在三列上并一一比较(而不是像我的示例中那样比较数组)。