SQL Story摘录（八）————数据抽取
2024-07-21 02:09:04
供稿：网友
数据抽取
理论上的关系型数据库，数据是以关系的形式存在。通常我们都可以把它们视为一种集合。这样，数据一般是以无序的形式存在的。这种做法的好处自不用多加讨论了，不过我们也得承认，有时这样也会带给我们一些麻烦。我最近就遇到这么一件。
我在网上遇上我的一个老同学，他提出了这样一个问题。有一个表（假设就叫mytable），表中有三个整型字段（假设就叫a11，a12，a13），其上有一个唯一键约束。现在他想要在这个表的数据中取一些样品。他希望从中a13的各个取值中，各取一条记录。然后我问他，对a11和a12有什么要求吗？他说，没有，怎么取都可以，有没有规律都行。我想当然的说，简单，给我二十分钟。
有一个笑话不知你们听过没有。说很久以前，一个大数学家讲课，有学生问他能不能证明四色定理。他说，那容易，我现在就证给你们……然后他写啊写啊……想啊想啊……一直想到下课也没证出来，然后外面打了一个大大的响雷，他自嘲说，上帝也被我的狂妄激怒了……
什么，你问我刚才的问题怎么样了？我告诉你，二十分种，不不，四十分钟，不不，好像是一个小时……后来到半夜了，外面开始打雷……我对我的同学说，老兄，我错了，让我把这问题拿回去想想先……
以下是一个示例，我随便录了些数据进去
a11 a12 a13
----------- ----------- -----------
0 0 0
0 0 1
0 1 1
1 1 1
1 1 2
1 2 2
2 2 2
2 2 1
2 2 3
2 1 3
2 3 3
3 3 1
3 3 2
3 2 3
3 2 1
3 2 2
3 1 1
3 1 2
3 1 3
一开始，我是想用一个select max(a11), max(a12), a13 from mytable group by a13，后来用脚趾头想了想，发现不可能。因为 max(a11)和max(a12)都只依赖于分组子句 group by a13。显然这样max(a11)和max(a12) 不一定会是同一条记录中的数据。就像下面这样：
a13
----------- ----------- -----------
0 0 0
3 3 1
3 3 2
3 3 3
注意，其实3, 3, 3这一行是不存在的。
然后我想，select max(a11), max(a12), a13 from mytable group by a13, a12行不行？用sql server 试了试，也不对。 因为这样的话，它会先按 a13, a12来分组所返回的结果集，所以会比我们要的数据多的多。就像下面这样
a13
----------- ----------- -----------
0 0 0
0 0 1
3 1 1
3 1 2
3 1 3
3 2 1
3 2 2
3 2 3
3 3 1
3 3 2
2 3 3
诸如此类的傻事我还干了很多，后来我终于想明白我要什么数据了：取a12，a13，使得在所有按a13的值分组后，取每组中最大的a12，然后取整记录，使得a11是a12和a13满足前述条件后的最大的一个值。这样，我写出了一个语句：
select
(select max(i.a11) from mytable i where i.a13 = o.a13 and i.a12 = max(o.a12)) a11
, max(o.a12) a12
, o.a13 a13
from mytable o
group by o.a13
这行语句返回值如下：
a11 a12 a13
----------- ----------- -----------
0 0 0
3 3 1
3 3 2
2 3 3
同样的，我们可以解释它为最两个结果集，一个是 select max(a11), max(a12), a13 from mytable group by a13，一个是 select max(a12), a13 from mytable group by a13, a12，然所按a12和a13进行等值联接，这就是：
select max(l.a11) a11
, max(l.a12) a12
, l.a13 a13
from mytable l
join mytable r
on l.a13 = r.a13
group by l.a13, l.a12
having l.a12 = max(r.a12)
我个觉得这样子比较清爽，不知各位以为如何？
事实上，这一类的数据抽取，不可能用一个简单查询得到，因为其中有一个字段要同时参予分组和统计。只有（事实上是只要）把我们的问题用sql语言的思考方式描述清楚，答案也就得到了，一切就这么简单。
对于一类完全按行随机抽取的问题，除了化为top n问题解决外，还有一个好主意就是在支持物理行号的数据库系统中，用rowid 或标识列之类的技术来解决，这样比较简单，就不多说了。
补充一句，以前我说过interbase不支持自动标识列，但实际上，它也可以间接的实现。在interbase中，我们可以create一种叫 generator的全局变量，然后用一个触发器，结合一个函数叫gen_id的，就可以做到。虽说有点麻烦，但并非不可能。详细情况，大家可以查询 interbase有关技术文档。
欢迎来信和我讨论技术问题：[email protected]。
