SQL Story摘录(三)————可扩展设计

原创 2002年06月06日 09:45:00

面向集合的结构化设计。这一点很多人都知道,可真正能够活用的就太少了。举一个简单的例子:
例1-3:有一个简单的数据表Orders,存储某商店的订单信息:
CREATE TABLE [dbo].[ORDERS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[OrderDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CU_INX_OrderDate] ON [dbo].[ORDERS]([OrderDate]) WITH FILLFACTOR = 50 ON [PRIMARY]
GO
ALTER TABLE [dbo].[ORDERS] WITH NOCHECK ADD
CONSTRAINT [PK_ORDERS] PRIMARY KEY NONCLUSTERED([ID])
ON [PRIMARY]
GO
表中现在有以下数据:
ID CustomerID OrderDate
----------- ----------- ------------------------------------------------------
1 1 1999-1-4
2 10 1999-3-5
3 22 1999-5-2
4 2 1999-6-7
5 2 2000-3-6
7 101 2001-5-3
8 10 2001-6-5
6 101 2002-4-2
那么,我们如何生成一个1999-2002的年度订单数报表呢(四年只有8个订单?我为了演示方便才这样做的,这并不代表真实的情况:P)?现在,我给出实际报表的数据格式,读者们请先试一下这个语句的写法
CustomerID 1999 2000 2001 2002
-------------- ------ ------ ------ ------
1 1 0 0 0
2 1 1 0 0
10 1 0 1 0
22 1 0 0 0
101 0 0 1 1
最直观的想法,是在前台,用其它语言实现这一功能。不过有一个办法,可以用SQL语言来实现它。而且不一定比你想像的更复杂:
SELECT CustomerID,
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2001 THEN 1 ELSE 0 END) AS "2001",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2002 THEN 1 ELSE 0 END) AS "2002"
FROM ORDERS
GROUP BY CustomerID
我想这时会有朋友提出InterBase不支持Case的问题。不过即使如此,我还是要向大家推荐这种写法。因为它优美、简洁,不仅我们读着好懂,还可以很方便地写出程序来自动生成它。事实上,Case关键字已是SQL标准之一,大势所趋,会有越来越多的数据库系统支持它的。
那么它又是怎么来的呢?我在设计这个语句时是这样的思路:
1、我们需要一个同时在时间和客户两个坐标轴上展开的报表;
2、纵向上,我们要为每一位客户建立一行数据,这个比较好办,我们首先确定了这个语句会有一个基本框架
SELECT CustomerID,
………………
FROM ORDERS
GROUP BY CustomerID
如果不区分年度,已下语句就是我们要的结果
SELECT CustomerID,
COUNT(ID) AS ORDERS_COUNT,
FROM ORDERS
GROUP BY CustomerID
3、设所有订单为一全集,那么这个集合的总数用以下语句来统计:
SELECT COUNT(ID) FROM ORDERS
横向上,我们为每一年度的订单数定义一列,以1999年为例,取年份为1999年的订单子集的元素数为
SELECT SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999"
FROM ORDERS
其它年份依此类推,我们得到每一年的订单数:
SELECT SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2001 THEN 1 ELSE 0 END) AS "2001",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2002 THEN 1 ELSE 0 END) AS "2002"
FROM ORDERS
其返回结果如下:
1999 2000 2001 2002
----------- ----------- ----------- -----------
4 1 2 1

 

(所影响的行数为 1 行)
4、顾及到关系型数据库“诡异”的NULL值问题后,综合2、3步,我们得出最终的语句:
SELECT CustomerID,
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2001 THEN 1 ELSE 0 END) AS "2001",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2002 THEN 1 ELSE 0 END) AS "2002"
FROM ORDERS
GROUP BY CustomerID
现在这个报表结构清晰明白。扩展性极强。比如明年我们需要2003年的统计数据,只要再依葫芦画瓢,来一列
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2003 THEN 1 ELSE 0 END) AS "2003"
加在最后就可以了,它是全集中的2003年数据的子集。还有,用来判断空值的isnull函数不一定所有的数据库都有,没关系,只要在Case的分支里加一行
WHEN OrderDate is NULL THEN 0
就可以了。基于这个思想,我们可以很容易地写出一个存储过程,只要给定起讫年份,就可以生成一个完整的年度报表。由于所有的运算都在服务器端运行,并且是随着数据检索一次就完成了。它的速度快于客户端的报表。而且传输的数据量也少,可以有效减轻网络负载。
在《SQL Server6.5技术内幕》中,有一个类似的例子。不过作者使用的语句结构比我的复杂,他的例子中,From关键字是从一个子查询导出表中选择的数据,这让我百思不得其解。也许6.5版的MS SQL Server还不支持我的写法,也许那样写性能更好。作者并没有说明,我也一直没有机会接触到MS SQL Server6.5。
对于InterBase,我还没有办法用足够优雅的语句生成这个报表。这主要是由于InterBase不支持Case。不过,如果你对语句的性能和美感要求不高的话,下面这个语句可以实现与以上的SQL Server版本相同的功能:
SELECT O.CUSTOMERID,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 1999))
as COUNT_1999,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 2000))
as COUNT_2000,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 2001))
as COUNT_2001,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 2002))
as COUNT_2002
FROM ORDERS O
GROUP BY O.CUSTOMERID
依照SQL Server版本,我们完成了InterBase版的年度报表。不同的是由于使用了相关子查询统计数据,它的效率会差一些(好在你不需要即时更新你的年度报表吧)。不过由于它同样是基于面向集合的设计构架,至少我们保证了它的可扩展性。只是很明显的,当子查询版本中增加一列年度统计,所带来的开销增长会比Case版本多很多。如果你对速度要求较高,还是在客户端另写程序生成吧。
InterBase数据库的用户会在这个示例中遇到很多不满意的地方:不支持自动标识列、没有聚簇索引、没有Case、没有……更可恨的是,这个数据系统的开放源码版本没有附带ODBC或ADO驱动,在得到一个免费的数据库系统后,我们却要为它花几十美元去买一套ODBC驱动?
不过,InterBase正在得到开放源码社区的支持,Borland公司也通过DBExpress和InterClient技术来为InterBase提供开放的接口(目前DBExpress驱动基本上也只存在于Borland昂贵的企业版开发工具中:()。只要每一个InterBase的程序员和用户都为这个属于我们自己的软件做出贡献,它的前途还很光明。

面向集合的设计方法虽然只适用于特定的目标,并不是通用的软件设计方法。但也不是三言两语能说清的,以后的章节中,我们会一直实际这种设计方法,还会有专门的章节讨论这个问题。那时,我们的示例数据库也建设的比较完整了,我也许会给出更实用的年度订单统计报表。现在,我们先简单地总结一下:
1、定义我们要生成的结果集的结构;
2、找出结果集的数据来源,定义全集;
3、定义结果集的取值范围,定义所取的子集;
4、完成操作。

如何写好User Story的分析与设计

项目组使用敏捷开发流程已经有近两年的时间了,除了书本、培训得到的知识外,版本使用敏捷流程后,有一些心得和体会,这里记录下来。...
  • babyblue_963
  • babyblue_963
  • 2014年03月25日 00:04
  • 4848

敏捷开发(agile)中story

agile概念的比较详细的说明见:http://www.cnblogs.com/astar/archive/2012/02/28/Scrum.html 对于敏捷开发来说,User Story是开发的...
  • u013102039
  • u013102039
  • 2014年02月07日 15:24
  • 2843

给自己做个地图故事 —— Trip to Greece

Esri 的 地图故事 很有意思,用句官媒的广告 “每个人都有要讲述的故事,利用地图的强大功能来讲述您的故事” …… 国庆假期将至,放点有趣的东西。如果喜欢分享,又想记录自己的过往,动手试试不错哦。如...
  • kikitaMoon
  • kikitaMoon
  • 2015年09月30日 13:53
  • 4172

敏捷测试(6)--基于story的敏捷基础知识

基于story的敏捷基础知识----需求管理(三) (3)每日站会 站会的目的有三个: (1)周知进度 仅从用户故事和任务的层面周知进度,任务进度只有两种状态:完成或未完成(完成百分比)。 (2)周...
  • kaka1121
  • kaka1121
  • 2014年01月16日 15:01
  • 2942

SQL三表连接查询与集合的并、交、差运算查询

use db_sqlserver2   select 姓名, 工资, 面积, 金额, (工资+金额/1000) as 实发工资 from 职工,仓库, 订购单       where 职工.职工号...
  • u011854789
  • u011854789
  • 2016年08月15日 11:16
  • 8763

敏捷测试(3)--基于story的敏捷基础知识

基于story的敏捷基础知识----story编写 为什么使用Story? 软件行业40年多来,需求分析技术已经很成熟了,但是MRD驱动的过程不堪重负。因为往往MRD编写会占去很多时间,MRD评审又会...
  • kaka1121
  • kaka1121
  • 2014年01月13日 17:03
  • 2524

敏捷开发真正的重点不是 User Story 的拆分, 而是开发人员的能力

谈到敏捷开发, 许多人纠结的第一个问题便是: User Story 如何的划分? 更有不少人, 一遇到在 User Story 上有延迟交付或交付的质量不佳时, 便说是因为 User Story 的拆...
  • u011790275
  • u011790275
  • 2015年09月04日 11:34
  • 2002

[自习室]摘录代码大全:类的设计

ADT abstract data type 抽象数据类型:一些数据以及对这些数据所进行操作的集合;尽可能限制类和成员的可访问性;对于一个类:提供一致的抽象...
  • mangosnow
  • mangosnow
  • 2014年12月18日 23:38
  • 1180

自编小工具:Collect-便利你的阅读摘录收集, 含源码,分享给大家

你是否已经厌烦频繁的切换窗口进行复制粘贴?你是否想要一个工具在不影响阅读连续性的情况下进行摘录? Collect,便利你的摘录,享受你的阅读。 性质: 完全免费,代码公开,无广告,无病毒,绿色...
  • lanbing510
  • lanbing510
  • 2015年03月20日 13:46
  • 1478

Jbehave 学习笔记 (一)

1、什么是JBehave JBehave是一个用java编写的BDD(Behavior-Driven-Design)框架, java界的Cucumber。(注: 1、BDD主要的目的是能够从业务领域...
  • emma_6
  • emma_6
  • 2014年02月25日 10:46
  • 1297
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Story摘录(三)————可扩展设计
举报原因:
原因补充:

(最多只允许输入30个字)