sql - 整个表中行中值组合的计数

Question

在表格中使用以下数据:

|   name |    d1 |     d2 |     d3 |     d4 |     d5 |     d6 |     d7 |     d8 |
|--------|-------|--------|--------|--------|--------|--------|--------|--------|
|  matty | 116.7 |  17.88 |   16.1 |  9.731 | (null) | (null) | (null) | (null) |
|   jana | 17.88 |  116.7 |  65.45 |   72.1 | (null) | (null) | (null) | (null) |
|  chris |  72.1 | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| khaled | 9.731 |  116.7 |  17.88 |   53.1 |      2 |   85.2 | (null) | (null) |
|    "   |   "   |   "    |   "    |   "    |   "    |   "    |   "    |   "    |
|    n   |   "   |   "    |   "    |   "    |   "    |   "    |   "    |   "    |

如何确定在 SQL 中所有行中出现值组合的次数？

The following is the output sample required:

116.7, 17.88 (3)

116.7, 17.88, 9.731 (2)

72.1 (2)

16.1 (1)

65.45 (1)

53.1 (1)

2 (1)

85.2 (1)

如果用 SQL 做不到，有什么替代方法可以做到吗？

Answer 1

PostgreSQL 中没有内置组合计算的东西，但是你可以为它写一个函数，f.ex.:

create or replace function combinations(variadic anyarray)
  returns setof anyarray
  language sql
  immutable
  called on null input
as $func$
  with recursive e as (
      select *
      from   unnest($1) with ordinality u(e, o)
      where  e is not null
  ),
  r as (
      select distinct on (e) array[e] ea, array[o] oa
      from   e
    union all
      select distinct on (oea) oea, oa || o
      from   r, e, lateral (select array_agg(u order by u) oea from unnest(ea || e) u) l
      where  o <> all(oa)
  )
  select ea
  from   r
$func$;

使用此函数，您可以编写如下查询:

select     combinations, count(*)
from       table_name
cross join combinations(d1, d2, d3, d4, d5, d6, d7, d8)
group by   1

但是，样本输入中的组合将比样本输出中包含的组合多得多。 (也许您只是为了节省空间而将它们排除在外？)

http://rextester.com/NNVK84197

注释:

• 上面的函数使用可变数量的参数，这些参数被转换为 native PostgreSQL 数组(因为 variadic )。
• 它接受任何类型的输入，只要它们都是同一类型(因为 anyarray )。这被称为政策主义。另外，因为 returns setof anyarray ，它将返回相同数组类型的完整结果集(多行)。
• language sql只是简化了函数体:它不会包含任何高级过程语言结构，比如 IF或 LOOP无论如何(language plpgsql 可以包含这些)。
• CTE与 e alias 从输入数组展开数据，但保留 o 中的排序/索引信息字段(参见 with ordinality )。这在以后是必不可少的，因为我们不能使用这些值本身来删除重复(即 (2, 2) 应该是一个有效的组合，正如您之前评论的那样)。 NULL s 在这里被丢弃。
• r 的递归 CTE别名(因此 recursive 之后的关键字 with )将累积每个组合。它从每一个值开始。然后在每个步骤中，它附加一个元素，该元素具有来自原始集合的 另一个 序数(索引)(参见 where o <> all(oa) )。因为组合中的元素顺序无关紧要(正如您评论的那样)，所以我在子查询中对元素进行了排序。此外，两个递归查询部分都使用 distinct on (<combination>)删除任何可能的重复，当多个元素具有相同的值时可能会发生这种情况。
• 解决方案查询使用隐式 LATERAL join 计算每一行的每一个组合。此步骤会将表格的原始行乘以与它们存在的组合一样多的次数。然后，我们只需要使用 GROUP BY combinations & COUNT(*)他们每个人。