我很难相信任何脚本没有任何事先知道的数据(不同于预先加载了这种信息的MySql),它将比SQL方法更快.
除了解析输入的时间外,脚本还需要通过数组等“继续”排序.
以下是第一个猜测,在SQL中,如果在表的aa,bb,cc列上按索引(*)顺序,那么应该在SQL中工作得很快. (可能的替代方案是“aa,bb DESC,cc”索引
(*)此索引可以聚集或不聚集,不影响以下查询.选择聚类与否,需要“aa,bb,cc”的独立索引取决于用例,表中行数的大小等.
SELECT T1.aa, T1.bb, T1.cc , COUNT(*)
FROM tblAbc T1
LEFT OUTER JOIN tblAbc T2 ON T1.aa = T2.aa AND
(T1.bb < T2.bb OR(T1.bb = T2.bb AND T1.cc < T2.cc))
GROUP BY T1.aa, T1.bb, T1.cc
HAVING COUNT(*) < 5 -- trick, remember COUNT(*) goes 1,1,2,3,...
ORDER BY T1.aa, T1.bb, T1.cc, COUNT(*) DESC
这个想法是在一个给定的aa值小于self的情况下得到多少条记录.然而,有一个小小的技巧:我们需要使用LEFT OUTER加入,以免丢弃具有最大bb值或最后一个的记录(可能恰好是前5名之一).作为左连接的结果,COUNT(*)值计数为1,1,2,3,4等,因此HAVING测试为“<5”以有效地选择前5名. 为了模拟OP的样本输出,ORDER BY在COUNT()上使用DESC,可以删除它,以获得更传统的前5种列表.此外,如果需要,可以删除选择列表中的COUNT(),这不会影响查询的逻辑和正确排序的能力. 还要注意,这个查询在处理关系方面是确定性的,即e,当给定的一组记录对于bb(在aa组内)具有相同的值时;我认为,当输入数据的顺序发生变化时,Python程序可能会提供稍微不同的输出,这是因为它偶尔会截断排序字典. 实际解决方案:基于SQL的程序方法 上述自连接方法演示了如何使用声明性语句来表达OP的要求.然而,这种方法是天真的,因为它的表现大致与每个“类别”中记录计数的平方和的总和相关. (不是O(n ^ 2),而是大致O((n / a)^ 2)其中a是aa列的不同值的数量)换句话说,它对数据执行得很好,使得平均记录数量相关给定的aa值不会超过几十.如果数据是aa列不是选择性的,以下方法是非常多的 – 更适合.它利用SQL的有效的排序框架,同时实现一个简单的算法,很难以声明的方式表达.通过在光标中向前看(并且有时返回…),通过引入对下一个aa值的简单二进制搜索,可以进一步改进具有特别大量记录数/大多数“类别”的数据集.对于相对于tblAbc中的整体行数的aa’category’数量较少的情况,请参考下一个方法.
DECLARE @aa AS VARCHAR(10), @bb AS INT, @cc AS VARCHAR(10)
DECLARE @curAa AS VARCHAR(10)
DECLARE @Ctr AS INT
DROP TABLE tblResults;
CREATE TABLE tblResults
( aa VARCHAR(10),
bb INT,
cc VARCHAR(10)
);
DECLARE abcCursor CURSOR
FOR SELECT aa, bb, cc
FROM tblABC
ORDER BY aa, bb DESC, cc
FOR READ ONLY;
OPEN abcCursor;
SET @curAa = ''
FETCH NEXT FROM abcCursor INTO @aa, @bb, @cc;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @curAa <> @aa
BEGIN
SET @Ctr = 0
SET @curAa = @aa
END
IF @Ctr < 5
BEGIN
SET @Ctr = @Ctr + 1;
INSERT tblResults VALUES(@aa, @bb, @cc);
END
FETCH NEXT FROM AbcCursor INTO @aa, @bb, @cc;
END;
CLOSE abcCursor;
DEALLOCATE abcCursor;
SELECT * from tblResults
ORDER BY aa, bb, cc -- OR .. bb DESC ... for a more traditional order.
对于aa非常不选择的情况,可以替代上述情况.换句话说,当我们有相对较少的“类别”.这个想法是通过不同类别的列表,并为每个这些值运行一个“LIMIT”(MySql)“TOP”(MSSQL)查询.
为了参考,以下是在63秒内运行的tblAbc的61百万条记录,分为45个aa值,在MSSQL 8.0上,在较老/弱的主机上.
DECLARE @aa AS VARCHAR(10)
DECLARE @aaCount INT
DROP TABLE tblResults;
CREATE TABLE tblResults
( aa VARCHAR(10),
bb INT,
cc VARCHAR(10)
);
DECLARE aaCountCursor CURSOR
FOR SELECT aa, COUNT(*)
FROM tblABC
GROUP BY aa
ORDER BY aa
FOR READ ONLY;
OPEN aaCountCursor;
FETCH NEXT FROM aaCountCursor INTO @aa, @aaCount
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT tblResults
SELECT TOP 5 aa, bb, cc
FROM tblproh
WHERE aa = @aa
ORDER BY aa, bb DESC, cc
FETCH NEXT FROM aaCountCursor INTO @aa, @aaCount;
END;
CLOSE aaCountCursor
DEALLOCATE aaCountCursor
SELECT * from tblResults
ORDER BY aa, bb, cc -- OR .. bb DESC ... for a more traditional order.
关于需要索引的问题. (参考OP的评论)
当仅运行“SELECT * FROM myTable”时,表扫描实际上是最快的appraoch,不需要麻烦索引.然而,SQL通常更适合这种事情的主要原因(除了首先存储数据的存储库之外,而任何外部解决方案需要考虑到导出相关数据的时间)它可以依靠索引来避免扫描.许多通用语言更适合处理原始处理,但是他们正在与SQL进行不公平的对抗,因为他们需要重新构建SQL在数据收集/导入阶段收集的数据的任何先前知识.由于排序通常是一个时间,有时候是耗时的任务,因此SQL和其相对较慢的处理能力通常会超前于替代解决方案.
另外,即使没有预先构建的索引,现代查询优化器也可以决定涉及创建临时索引的计划.而且,因为排序是DDMS的一个内在部分,所以SQL服务器在这方面一般都是高效的.
那么SQL是否更好?
这就是说,如果我们试图比较纯ETL作业的SQL和其他语言,即处理堆(unindexed表)作为其输入来执行各种转换和过滤,可能是多线程的实用程序C,并利用高效的排序库,可能会更快.决定SQL与非SQL方法的决定性问题是数据所在的位置以及它最终将驻留在哪里.如果我们只是将一个文件转换为“链”,外部程序就更适合.如果我们在SQL服务器中需要或需要数据,那么只有少数情况才能使外部值得出口和处理.