SQLite的选择具有重复分组的行多个列的查询 [英] SQLite SELECT query across multiple columns with duplicate grouped rows

问题描述

我不完全知道我怎么可以把一个单一的SQLite查询来实现以下。我能得到的点点滴滴的工作，但似乎无法把它全部融合到一个单一的之一。

I'm not entirely sure how I can put together a single SQLite query to achieve the following. I can get bits and pieces to work, but can't seem to meld it all into one single one.

我有一个表，看起来像这样（真实数据集是几G​​B）：

I have a table that looks like this (the real data set is several GB):

| ID | ColumnA | ColumnB | ColumnC | ColumnD |
| 1  |    21   |    34   |   10    | 0.12654 |
| 2  |    21   |    34   |   20    | 0.25478 |
| 3  |    21   |    46   |   10    | 0.43564 |
| 4  |    21   |    46   |   20    | 1.02487 |
| 5  |    34   |    21   |   10    | 0.01476 |
| 6  |    34   |    21   |   20    | 0.87265 |
| 7  |    34   |    46   |   10    | 0.46478 |
| 8  |    34   |    46   |   20    | 0.13665 |
| 9  |    46   |    21   |   10    | 0.04189 |
| 10 |    46   |    21   |   20    | 0.91754 |
| 11 |    46   |    34   |   10    | 0.73688 |
| 12 |    46   |    34   |   20    | 0.24299 |

此数据是由一个嵌套的DO循环对于一些仿真我执行产生的。

This data is generated by a nested do-loop for some simulations I'm carrying out.

从这个表，我基本上是需要提取表看起来像以下，这样我就可以绘制彩色/热图。

From this table, I essentially need to extract a table that looks like the following, so that I can plot a color/heatmap.

| ID | ColumnA | ColumnB | ColumnC | ColumnD |
| 1  |    21   |    34   |   10    | 0.12654 |
| 3  |    21   |    46   |   10    | 0.43564 |
| 5  |    34   |    21   |   10    | 0.01476 |
| 8  |    34   |    46   |   20    | 0.13665 |
| 9  |    46   |    21   |   10    | 0.04189 |
| 12 |    46   |    34   |   20    | 0.24299 |

因此​​，这将使我能够基于使用2D阵列ColumnD值的颜色表（对角线应当设置为零，作为ColumnA和ColumnB的值是从未等于一个给定行;因此数据的对角线不是$数据库p $ psent）：

So this will enable me to make a colormap based on ColumnD values using a 2D-array (the diagonal should be set to zero, as the values of ColumnA and ColumnB are never equal for a given row; hence data for the diagonal is not present in the database):

   | 21 | 34 | 46
------------------
21 | 0  |    |
------------------
34 |    | 0  |
------------------
46 |    |    | 0

我的问题是基本上我怎么可以设置单个查询聚合所有进入二维数组生成颜色表中的数据。

My question is basically how I can set up a single query to aggregate all the data that goes into the 2D array to generate the colormap.

要注意的是ColumnA和ColumnB基本上都是由同一个整数集居住是很重要的。我能得到使用DISTINCT整数值的唯一列表。我发现有关选择多个列DISTINCT一些SO线程，但是，没有一个例子说明如何使用聚合挑中的其他列中的值。在这种情况下，我想使用min（）来选择ColumnD的最低值，对于每对ColumnA和ColumnB的ID。单个列上的选择DISTINCT不起作用，因为它的坐标对（ColumnA，ColumnB）这是不同的。

It's important to note that ColumnA and ColumnB are basically populated by the same set of integers. I can obtain the unique list of integer values using DISTINCT. I've found some SO threads about selecting DISTINCT across multiple columns, however none of the examples show how to use an aggregator to pick values in the other columns. In this case, I want to use min() to select the lowest value in ColumnD, for each pair of ids in ColumnA and ColumnB. Selecting DISTINCT on a single column doesn't work, because it's the coordinate pair (ColumnA,ColumnB) that's distinct.

任何帮助将大大AP preciated！

Any help would be greatly appreciated!

推荐答案

对我来说，它看起来像你想 Col​​umnD 为对<$ C $的最小值C> Col​​umnA 和 Col​​umnB 。如果你不关心 ID 或 Col​​umnC ，通过一个简单的组就足够了：

To me, it looks like you want the minimum value of ColumnD for pairs of ColumnA and ColumnB. If you don't care about the id or ColumnC, a simple group by is sufficient:

select ColumnA, ColumnB, min(ColumnD)
from table t
group by ColumnA, ColumnB;

如果你确实需要的行中的所有值，可以加入回让他们：

If you do need all the values in the row, you can join back to get them:

select t.*
from table t join
     (select ColumnA, ColumnB, min(ColumnD) as ColumnD
      from table t
      group by ColumnA, ColumnB
     ) tt
     on t.ColumnA = tt.ColumnA and t.ColumnB = tt.ColumnB and
        t.ColumnD = tt.ColumnD;

这假定 Col​​umnD 永远不会重复的值 Col​​umnA 和 Col​​umnB 。

This assumes that ColumnD is never duplicated for values in ColumnA and ColumnB.

这篇关于SQLite的选择具有重复分组的行多个列的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！