SQL Story摘录（四）————信息开掘初步
日期：2013-10-06 浏览次数：20473 次
写这本书的最后想法，来自于在第一个公司任务时，与同事的交流和学习。不过发布这本书的导火索，却在于一次在CSDN上读到一篇关于最新信息的报表问题的贴子。贴子中的问题可以用子查询和联接两种方式完成。由于条件所限，我不能详细解答，由此发贴的朋友不能理解我的本意，让我心生遗憾。所以决定将写书的想法付诸举动，并将这本书贴在CSDN上，与大家一同交流，共同进步。今天正好又见到类似于当日的问题，心生感触，决定在这里把它详细讨论一下。
在实际任务中，我们有时会需求建立数据表来存储变动的数据，并由这些数据统计出我们所需的信息。其中有一类问题的特点在于最终结果的过滤条件来自分组统计后的数据。这类使用常见于网站数据库、财会系统、实时系统、数据仓库与数据挖掘等。理想上，这种命题本身曾经包括了数据挖掘。如今，我们看下面的例子
例4-4-1：最新报价
网友kikilyq问：
我有一个table:COMPUTER_PRICE，格式如下：
goods price dates
--------------------------------
HP电脑 20000 5.21
HP电脑 20050 5.23
NEC电脑 31200 5.3
NEC电脑 32000 5.5
查询结果要求：要查出每种电脑的最新价格；
上面表的结果为：
goods price dates
---------------------------------
HP电脑 20050 5.23
NEC电脑 32000 5.5
帮帮忙，搞定这个问题？
依据问题，我们先建立数据表，经分析，表中数据应由货物名和日期标示，所以设这两个字段为主关键字：
SQL Server版脚本如下
CREATE TABLE [dbo].[GOODS] (
[GOODS] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PRICE] [money] NOT NULL ,
[DATE_TIME] [datetime] NOT NULL ,
PRIMARY KEY (GOODS, DATE_TIME)
) ON [PRIMARY]
InterBase版脚本如下
CREATE TABLE GOODS
(
GOODS CHAR(10) NOT NULL,
PRICE NUMERIC(15, 4) NOT NULL,
DATE_TIME TIMESTAMP NOT NULL,
PRIMARY KEY (GOODS, DATE_TIME)
)
建立表后，请读者自即将数据插入。
这个问题中，最终报表中的电脑的价格取决于其后一次报价，也就是报价日期最新的那一行数据。典型地属于前面提到的类型。直觉来讲，我先试着选出每种电脑的最新报价日期，这个比较简单：
SELECT GOODS, MAX(DATE_TIME)
FROM GOODS
GROUP BY GOODS
前往结果
GOODS
---------- ------------------------------------------------------
HP 2002-05-23 00:00:00.000
NEC 2002-05-05 00:00:00.000
显然，只需把各品牌电脑在以上日期的报价显示出来，就是我们所要的结果了。那么直接这么写如何？
SELECT GOODS, PRICE, MAX(DATE_TIME)
FROM GOODS
GROUP BY GOODS
我想这个语句就不用试了，稍有经验的程序员会发现，PRICE列不在统计函数中，也不在GROUP BY 之列，数据库系统无法执行这样的语句。这样的列一定要从另一个数据集中取出，所以我首先想到的是自联接。不过置信大多数朋友会先想到子查询。如今我们先看看子查询如何做，毕竟这样比较直观。最偷懒的办法是直接把PRICE表达为一个插入的标量子查询：
SELECT L.GOODS,
(SELECT R.PRICE FROM GOODS R WHERE R.GOODS = L.GOODS AND R.DATE_TIME = MAX(L.DATE_TIME)) AS PRICE,
MAX(L.DATE_TIME) AS CURRENT_DATE_TIME
FROM GOODS L
GROUP BY L.GOODS
不过很遗憾，这个语句只能在SQL Server中执行，InterBase的提示是invalid column reference（无效的列援用）。不过换一个思路就可以写一个通用版。如今，我们对系统说，我要从表中取出部分行，每个品牌的电脑一行，其日期是这个牌子的最新报价日期：
SELECT L.GOODS, L.PRICE, L.DATE_TIME
FROM GOODS L
WHERE L.DATE_TIME =
(SELECT
MAX(R.DATE_TIME)
FROM GOODS R
WHERE R.GOODS = L.GOODS
GROUP BY R.GOODS)
这个
我想应该还可以写出几个不同的子查询变种，不过大同小异，就不逐一尝试了。这个版本看来有些不可靠，由于主查询的WHERE条件中只要DATE_TIME，似乎不能精确地区分出每一行数据。不过放心，这里有一个“诡异”的互相援用，主查询的记录要满足日期等于子查询的前往值，而子查询的货物名（GOODS列）依赖于主查询的货物名（GOODS列）。这样，子查询会针对当前的品牌前往其正确日期，这是相关子查询的绝技，也是形成它在很多场合效率较差的缘由。我对子查询的兴味到此为止了，前面的文章中我说过，联接查询是一种很好的技术，那么这个查询有没有可能用联接来实现呢？前面对子查询的分析在这里会有助于我们的思考。如今我们如果有两个数据集，一个有最大日期，一个有价格，把它们一联接，不就可以了吗？这两个结果集就在上面的子查询中，如今的问题是我们如何把它们联接起来，显然，有一个联接条件是R.GOODS = L.GOODS ，这同时也确定了最终结果集的独一标识之一——GOODS列，而日期列的过滤条件照搬L.DATE_TIME = MAX(R.DATE_TIME) ，加上GROUP BY结果集中的列，于是就有了：
SELECT L.GOODS, L.PRICE, L.DATE_TIME
FROM GOODS L
JOIN GOODS R
ON L.GOODS = R.GOODS
GROUP BY L.PRICE, L.GOODS, L.DATE_TIME
HAVING L.DATE_TIME = MAX(R.DATE_TIME)
有些朋友可能不明白这个GROUP BY 为何而来，简单说一下。我们做了一个GOODS表的自联接（两个数据集分别是L和R）后，会生成一个迪卡尔积。有些人称之为数据爆炸。不过把矿石炸开之后，倒方便我们在其中找金子了。用JOIN条件一联接，L数据集中的每一种品牌在R数据集中都对应上了本人的所有报价日期。如今我们要对R数据集分组，选出每一品牌的最新报价日期，以其过滤L数据集就应该用
GROUP BY R.DATE_TIME
HAVING L.DATE_TIME = MAX(R.DATE_TIME)
由于我们要取出L数据集中的三列，所以要把它们也列在GROUP BY 中，由于曾经有了L.GOODS = R.GOODS 这个联接条件，R.GOODS 倒是可以不写在GROUP BY 中了。由于我在前面的文章中提到过的种种理由，我团体比较喜欢用联接。当然从效率方面讲，当表中每个分组的数据很多时，联接查询的效率会因巨大的迪卡尔积而被抵消，这时使用子查询（如果这是一个记录生产线即时任务情况的表，统计其最新的生产情况，通常就是这样）；而表中每个分组的数据很少，但数据组很多（如大型网上书店，用户可能是一个天文数字，绝对来说每个用
在实际任务中，我们有时会需求建立数据表来存储变动的数据，并由这些数据统计出我们所需的信息。其中有一类问题的特点在于最终结果的过滤条件来自分组统计后的数据。这类使用常见于网站数据库、财会系统、实时系统、数据仓库与数据挖掘等。理想上，这种命题本身曾经包括了数据挖掘。如今，我们看下面的例子
例4-4-1：最新报价
网友kikilyq问：
我有一个table:COMPUTER_PRICE，格式如下：
goods price dates
--------------------------------
HP电脑 20000 5.21
HP电脑 20050 5.23
NEC电脑 31200 5.3
NEC电脑 32000 5.5
查询结果要求：要查出每种电脑的最新价格；
上面表的结果为：
goods price dates
---------------------------------
HP电脑 20050 5.23
NEC电脑 32000 5.5
帮帮忙，搞定这个问题？
依据问题，我们先建立数据表，经分析，表中数据应由货物名和日期标示，所以设这两个字段为主关键字：
SQL Server版脚本如下
CREATE TABLE [dbo].[GOODS] (
[GOODS] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PRICE] [money] NOT NULL ,
[DATE_TIME] [datetime] NOT NULL ,
PRIMARY KEY (GOODS, DATE_TIME)
) ON [PRIMARY]
InterBase版脚本如下
CREATE TABLE GOODS
(
GOODS CHAR(10) NOT NULL,
PRICE NUMERIC(15, 4) NOT NULL,
DATE_TIME TIMESTAMP NOT NULL,
PRIMARY KEY (GOODS, DATE_TIME)
)
建立表后，请读者自即将数据插入。
这个问题中，最终报表中的电脑的价格取决于其后一次报价，也就是报价日期最新的那一行数据。典型地属于前面提到的类型。直觉来讲，我先试着选出每种电脑的最新报价日期，这个比较简单：
SELECT GOODS, MAX(DATE_TIME)
FROM GOODS
GROUP BY GOODS
前往结果
GOODS
---------- ------------------------------------------------------
HP 2002-05-23 00:00:00.000
NEC 2002-05-05 00:00:00.000
显然，只需把各品牌电脑在以上日期的报价显示出来，就是我们所要的结果了。那么直接这么写如何？
SELECT GOODS, PRICE, MAX(DATE_TIME)
FROM GOODS
GROUP BY GOODS
我想这个语句就不用试了，稍有经验的程序员会发现，PRICE列不在统计函数中，也不在GROUP BY 之列，数据库系统无法执行这样的语句。这样的列一定要从另一个数据集中取出，所以我首先想到的是自联接。不过置信大多数朋友会先想到子查询。如今我们先看看子查询如何做，毕竟这样比较直观。最偷懒的办法是直接把PRICE表达为一个插入的标量子查询：
SELECT L.GOODS,
(SELECT R.PRICE FROM GOODS R WHERE R.GOODS = L.GOODS AND R.DATE_TIME = MAX(L.DATE_TIME)) AS PRICE,
MAX(L.DATE_TIME) AS CURRENT_DATE_TIME
FROM GOODS L
GROUP BY L.GOODS
不过很遗憾，这个语句只能在SQL Server中执行，InterBase的提示是invalid column reference（无效的列援用）。不过换一个思路就可以写一个通用版。如今，我们对系统说，我要从表中取出部分行，每个品牌的电脑一行，其日期是这个牌子的最新报价日期：
SELECT L.GOODS, L.PRICE, L.DATE_TIME
FROM GOODS L
WHERE L.DATE_TIME =
(SELECT
MAX(R.DATE_TIME)
FROM GOODS R
WHERE R.GOODS = L.GOODS
GROUP BY R.GOODS)
这个
我想应该还可以写出几个不同的子查询变种，不过大同小异，就不逐一尝试了。这个版本看来有些不可靠，由于主查询的WHERE条件中只要DATE_TIME，似乎不能精确地区分出每一行数据。不过放心，这里有一个“诡异”的互相援用，主查询的记录要满足日期等于子查询的前往值，而子查询的货物名（GOODS列）依赖于主查询的货物名（GOODS列）。这样，子查询会针对当前的品牌前往其正确日期，这是相关子查询的绝技，也是形成它在很多场合效率较差的缘由。我对子查询的兴味到此为止了，前面的文章中我说过，联接查询是一种很好的技术，那么这个查询有没有可能用联接来实现呢？前面对子查询的分析在这里会有助于我们的思考。如今我们如果有两个数据集，一个有最大日期，一个有价格，把它们一联接，不就可以了吗？这两个结果集就在上面的子查询中，如今的问题是我们如何把它们联接起来，显然，有一个联接条件是R.GOODS = L.GOODS ，这同时也确定了最终结果集的独一标识之一——GOODS列，而日期列的过滤条件照搬L.DATE_TIME = MAX(R.DATE_TIME) ，加上GROUP BY结果集中的列，于是就有了：
SELECT L.GOODS, L.PRICE, L.DATE_TIME
FROM GOODS L
JOIN GOODS R
ON L.GOODS = R.GOODS
GROUP BY L.PRICE, L.GOODS, L.DATE_TIME
HAVING L.DATE_TIME = MAX(R.DATE_TIME)
有些朋友可能不明白这个GROUP BY 为何而来，简单说一下。我们做了一个GOODS表的自联接（两个数据集分别是L和R）后，会生成一个迪卡尔积。有些人称之为数据爆炸。不过把矿石炸开之后，倒方便我们在其中找金子了。用JOIN条件一联接，L数据集中的每一种品牌在R数据集中都对应上了本人的所有报价日期。如今我们要对R数据集分组，选出每一品牌的最新报价日期，以其过滤L数据集就应该用
GROUP BY R.DATE_TIME
HAVING L.DATE_TIME = MAX(R.DATE_TIME)
由于我们要取出L数据集中的三列，所以要把它们也列在GROUP BY 中，由于曾经有了L.GOODS = R.GOODS 这个联接条件，R.GOODS 倒是可以不写在GROUP BY 中了。由于我在前面的文章中提到过的种种理由，我团体比较喜欢用联接。当然从效率方面讲，当表中每个分组的数据很多时，联接查询的效率会因巨大的迪卡尔积而被抵消，这时使用子查询（如果这是一个记录生产线即时任务情况的表，统计其最新的生产情况，通常就是这样）；而表中每个分组的数据很少，但数据组很多（如大型网上书店，用户可能是一个天文数字，绝对来说每个用
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
