sql - 逆笛卡尔积

Question

给定以下数据集:

 a  |  b  |  c  |  d
 1  |  3  |  7  |  11
 1  |  5  |  7  |  11
 1  |  3  |  8  |  11
 1  |  5  |  8  |  11
 1  |  6  |  8  |  11

执行逆笛卡尔积得到:

 a  |  b  |  c  |  d
 1  | 3,5 | 7,8 |  11
 1  |  6  |  8  |  11

我目前正在使用 scala，我的输入/输出数据类型目前是:

ListBuffer[Array[Array[Int]]]

我想出了一个解决方案(见下文)，但感觉它可以优化。我对优化我的方法和全新的方法持开放态度。首选 scala 和 c# 解决方案。

我也很好奇这是否可以在 MS SQL 中完成。

我目前的解决方案:

def main(args: Array[String]): Unit = {

    // Input
    val data = ListBuffer(Array(Array(1), Array(3), Array(7), Array(11)),
                          Array(Array(1), Array(5), Array(7), Array(11)),
                          Array(Array(1), Array(3), Array(8), Array(11)),
                          Array(Array(1), Array(5), Array(8), Array(11)),
                          Array(Array(1), Array(6), Array(8), Array(11)))

    reverseCartesianProduct(data)
}

def reverseCartesianProduct(input: ListBuffer[Array[Array[Int]]]): ListBuffer[Array[Array[Int]]] = {
    val startIndex = input(0).size - 1

    var results:ListBuffer[Array[Array[Int]]] = input

    for (i <- startIndex to 0 by -1) {
      results = groupForward(results, i, startIndex)
    }

    results
}

def groupForward(input: ListBuffer[Array[Array[Int]]], groupingIndex: Int, startIndex: Int): ListBuffer[Array[Array[Int]]] = {

    if (startIndex < 0) {
      val reduced = input.reduce((a, b) => {
        mergeRows(a, b)
      })

      return ListBuffer(reduced)
    }

    val grouped = if (startIndex == groupingIndex) {
      Map(0 -> input)
    }
    else {
      groupOnIndex(input, startIndex)
    }

    val results = grouped.flatMap{
      case (index, values: ListBuffer[Array[Array[Int]]]) =>
        groupForward(values, groupingIndex, startIndex - 1)
    }

    results.to[ListBuffer]
  }

  def groupOnIndex(list: ListBuffer[Array[Array[Int]]], index: Int): Map[Int, ListBuffer[Array[Array[Int]]]] = {

    var results = Map[Int, ListBuffer[Array[Array[Int]]]]()

    list.foreach(a => {
      val key = a(index).toList.hashCode()

      if (!results.contains(key)) {
        results += (key -> ListBuffer[Array[Array[Int]]]())
      }

      results(key) += a
    })

    results
  }

  def mergeRows(a: Array[Array[Int]], b: Array[Array[Int]]): Array[Array[Int]] = {

    val zipped = a.zip(b)

    val merged = zipped.map{ case (array1: Array[Int], array2: Array[Int]) =>
      val m = array1 ++ array2

      quickSort(m)

      m.distinct
        .array
    }

    merged
  }

它的工作方式是:

1. 从右到左遍历各列(groupingIndex 指定要在哪一列上运行。该列是唯一不需要具有彼此相等的值即可合并行的列。)
2. 递归地对所有其他列(不是 groupingIndex)上的数据进行分组。
3. 在对所有列进行分组后，假设每组中的数据在除分组列之外的每一列中都具有相等的值。
4. 将行与匹配的列合并。为每一列取不同的值并对每一列进行排序。

如果其中的某些内容没有意义，我深表歉意，我的大脑今天无法正常工作。

Answer 1

这是我的看法。代码使用 Java，但可以轻松转换为 Scala 或 C#。

我对 n-1 的所有组合运行 groupingBy 并选择计数最少的那个，这意味着最大的合并深度，所以这是一种贪婪的方法。但是，不能保证您会找到最佳解决方案，这意味着最小化 k 的数量，这是 np-hard 要做的，请参阅链接 here寻求解释，但您会找到一个有效的解决方案，而且速度相当快。

完整示例在这里:https://github.com/jbilander/ReverseCartesianProduct/tree/master/src

Main.java

    import java.util.*;
    import java.util.stream.Collectors;

    public class Main {

        public static void main(String[] args) {

            List<List<Integer>> data = List.of(List.of(1, 3, 7, 11), List.of(1, 5, 7, 11), List.of(1, 3, 8, 11), List.of(1, 5, 8, 11), List.of(1, 6, 8, 11));
            boolean done = false;
            int rowLength = data.get(0).size(); //4
            List<Table> tables = new ArrayList<>();

            // load data into table
            for (List<Integer> integerList : data) {

                Table table = new Table(rowLength);
                tables.add(table);

                for (int i = 0; i < integerList.size(); i++) {
                    table.getMap().get(i + 1).add(integerList.get(i));
                }
            }

            // keep track of count, needed so we know when to stop iterating
            int numberOfRecords = tables.size();

            // start algorithm
            while (!done) {

                Collection<List<Table>> result = getMinimumGroupByResult(tables, rowLength);

                if (result.size() < numberOfRecords) {

                    tables.clear();

                    for (List<Table> tableList : result) {

                        Table t = new Table(rowLength);
                        tables.add(t);

                        for (Table table : tableList) {
                            for (int i = 1; i <= rowLength; i++) {
                                t.getMap().get(i).addAll(table.getMap().get(i));
                            }
                        }
                    }
                    numberOfRecords = tables.size();
                } else {
                    done = true;
                }
            }

            tables.forEach(System.out::println);
        }

        private static Collection<List<Table>> getMinimumGroupByResult(List<Table> tables, int rowLength) {

            Collection<List<Table>> result = null;
            int min = Integer.MAX_VALUE;

            for (List<Integer> keyCombination : getKeyCombinations(rowLength)) {

                switch (rowLength) {

                    case 4: {
                        Map<Tuple3<TreeSet<Integer>, TreeSet<Integer>, TreeSet<Integer>>, List<Table>> map =
                                tables.stream().collect(Collectors.groupingBy(t -> new Tuple3<>(
                                        t.getMap().get(keyCombination.get(0)),
                                        t.getMap().get(keyCombination.get(1)),
                                        t.getMap().get(keyCombination.get(2))
                                )));
                        if (map.size() < min) {
                            min = map.size();
                            result = map.values();
                        }
                    }
                    break;
                    case 5: {
                        //TODO: Handle n = 5
                    }
                    break;
                    case 6: {
                        //TODO: Handle n = 6
                    }
                    break;
                }
            }

            return result;
        }

        private static List<List<Integer>> getKeyCombinations(int rowLength) {

            switch (rowLength) {
                case 4:
                    return List.of(List.of(1, 2, 3), List.of(1, 2, 4), List.of(2, 3, 4), List.of(1, 3, 4));

                //TODO: handle n = 5, n = 6, etc...
            }

            return List.of(List.of());
        }
    }

tables.forEach(System.out::println)的输出

    Table{1=[1], 2=[3, 5, 6], 3=[8], 4=[11]}
    Table{1=[1], 2=[3, 5], 3=[7], 4=[11]}

或为了可读性重写:

     a |   b   | c | d
     --|-------|---|---
     1 | 3,5,6 | 8 | 11
     1 |  3,5  | 7 | 11

如果您要在 sql (mysql) 中执行所有这些操作，您可能会使用 group_concat()，我认为 MS SQL 在这里有类似的东西:simulating-group-concat或 STRING_AGG 如果是 SQL Server 2017，但我认为您必须使用在这种情况下有点讨厌的文本列:

例如

    create table my_table (A varchar(50) not null, B varchar(50) not null, 
                           C varchar(50) not null, D varchar(50) not null);

    insert into my_table values ('1','3,5','4,15','11'), ('1','3,5','3,10','11');

    select A, B, group_concat(C order by C) as C, D from my_table group by A, B, D;

将给出以下结果，因此您必须解析、排序和更新逗号分隔的结果，以使任何下一次合并迭代(分组依据)正确。

    ['1', '3,5', '3,10,4,15', '11']