sql - SQL:重复项计数和编号-优化相关子查询

Question

在一个SQLite数据库中，我有一个表，我需要在其中对某些列（即3个特定列相同的行）中的重复项进行计数，然后还要对每种情况进行编号（即，如果出现2次特定重复项，则它们需要编号为1和2）。我发现用语言解释起来有点困难，因此我将在下面使用一个简化的示例。

我拥有的数据类似于以下内容（第一行是标题行，表在下文中称为“ idcountdata”）：

id  match1  match2  match3  data
1   AbCde   BC      0       data01
2   AbCde   BC      0       data02
3   AbCde   BC      1       data03
4   AbCde   AB      0       data04
5   FGhiJ   BC      0       data05
6   FGhiJ   AB      0       data06
7   FGhiJ   BC      1       data07
8   FGhiJ   BC      1       data08
9   FGhiJ   BC      2       data09
10  HkLMop  BC      1       data10
11  HkLMop  BC      1       data11
12  HkLMop  BC      1       data12
13  HkLMop  DE      1       data13
14  HkLMop  DE      2       data14
15  HkLMop  DE      2       data15
16  HkLMop  DE      2       data16
17  HkLMop  DE      2       data17

我需要为上述生成的输出将是：

id  match1  match2  match3  data    matchid  matchcount
1   AbCde   BC      0       data01  1        2
2   AbCde   BC      0       data02  2        2
3   AbCde   BC      1       data03  1        1
4   AbCde   AB      0       data04  1        1
5   FGhiJ   BC      0       data05  1        1
6   FGhiJ   AB      0       data06  1        1
7   FGhiJ   BC      1       data07  1        2
8   FGhiJ   BC      1       data08  2        2
9   FGhiJ   BC      2       data09  1        1
10  HkLMop  BC      1       data10  1        3
11  HkLMop  BC      1       data11  2        3
12  HkLMop  BC      1       data12  3        3
13  HkLMop  DE      1       data13  1        1
14  HkLMop  DE      2       data14  1        4
15  HkLMop  DE      2       data15  2        4
16  HkLMop  DE      2       data16  3        4
17  HkLMop  DE      2       data17  4        4

以前，我使用几个相关的子查询来实现此目的，如下所示：

SELECT id, match1, match2, match3, data,
  (SELECT count(*) FROM idcountdata d2 
    WHERE d1.match1=d2.match1 AND d1.match2=d2.match2 AND d1.match3=d2.match3
      AND d2.id<=d1.id)
  AS matchid,
  (SELECT count(*) FROM idcountdata d2 
    WHERE d1.match1=d2.match1 AND d1.match2=d2.match2 AND d1.match3=d2.match3)
  AS matchcount
FROM idcountdata d1;

但是该表具有超过200,000行（并且数据的长度/内容可以更改），因此需要花费数小时才能运行。 （奇怪的是，当我在2013年中后期首次对相同数据使用相同的查询时，它花了几分钟而不是几小时，但这已经不重要了-即使是那时，我也认为这是低效率的。）

我已经将上面的“ matchcount”的相关子查询转换为带有JOIN的不相关子查询，如下所示：

SELECT d1.id, d1.match1, d1.match2, d1.match3, d1.data,
  matchcount
FROM idcountdata d1
JOIN
  (SELECT id,match1,match2,match3,count(*) matchcount 
    FROM idcountdata
    GROUP BY match1,match2,match3) d2
  ON (d1.match1=d2.match1 and d1.match2=d2.match2 and d1.match3=d2.match3);

因此，我只希望对“ matchid”的子查询进行优化。
简而言之，以下查询对于较大的数据集运行太慢：

SELECT id, match1, match2, match3, data,
  (SELECT count(*) FROM idcountdata d2 
    WHERE d1.match1=d2.match1 AND d1.match2=d2.match2 AND d1.match3=d2.match3
      AND d2.id<=d1.id)
  matchid
FROM idcountdata d1;

如何提高上述查询的性能？
它不必在几秒钟内运行，但它需要几分钟而不是几小时（大约200,000行）。

Answer 1

自联接可能比相关子查询快

SELECT d1.id, d1.match1, d1.match2, d1.match3, d1.data, count(*) matchid
FROM idcountdata d1
JOIN idcountdata d2 on d1.match1 = d2.match1 
  and d1.match2 = d2.match2 
  and d1.match3 = d2.match3
  and d1.id >= d2.id
GROUP BY d1.id, d1.match1, d1.match2, d1.match3, d1.data

该查询可以利用 (match1,match2,match3,id)上的复合索引