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

SQL Story 专栏收录该内容
15 篇文章 0 订阅

面向集合的结构化设计。这一点很多人都知道,可真正能够活用的就太少了。举一个简单的例子:
例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、完成操作。

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
<p style="color:#666666;"> <span style="font-size:14px;">本门课程重实战,将基础知识拆解到项目里,让你在项目情境里学知识。</span> </p> <p style="color:#666666;"> <span style="font-size:14px;">这样的学习方式能让你保持兴趣、充满动力,时刻知道学的东西能用在哪、能怎么用。</span> </p> <p style="color:#666666;"> <span style="font-size:14px;">平时不明白的知识点,放在项目里去理解就恍然大悟了。</span> </p> <p style="color:#666666;"> <span></span> </p> <p style="color:#666666;"> <span style="font-size:14px;"> </span> </p> <p style="color:#666666;"> <span style="color:#FF0000;font-size:14px;"><strong>一、融汇贯通</strong></span> </p> <p style="color:#666666;"> <span style="font-size:14px;">本视频采用了前后端分离的开发模式,前端使用Vue.js+Element UI实现了Web页面的呈现,后端使用Python 的Django框架实现了数据访问的接口,前端通过Axios访问后端接口获得数据。在学习完本章节后,真正理解前后端的各自承担的工作。</span> </p> <p style="color:#666666;"> <span style="font-size:14px;"> </span> </p> <p style="color:#666666;"> <span style="color:#FF0000;font-size:14px;"><strong>二、贴近实战</strong></span> </p> <p style="color:#666666;"> <span style="font-size:14px;">本系列课程为练手项目实战:学生管理系统v4.0的开发,项目包含了如下几个内容:项目的总体介绍、基本功能的演示、Vuejs的初始化、Element UI的使用、在Django中实现针对数据的增删改查的接口、在Vuejs中实现前端增删改查的调用、实现文件的上传、实现表格的分页、实现导出数据到Excel、实现通过Excel导入数据、实现针对表格的批量化操作等等,所有的功能都通过演示完成、贴近了实战</span> </p> <p style="color:#666666;"> <span style="font-size:14px;"> </span> </p> <p style="color:#666666;"> <span style="color:#FF0000;font-size:14px;"><strong>、课程亮点</strong></span> </p> <p style="color:#666666;"> <span style="font-size:14px;">在本案例中,最大的亮点在于前后端做了分离,真正理解前后端的各自承担的工作。前端如何和后端交互</span> </p> <p style="color:#666666;"> <span style="font-size:14px;"> </span> </p> <p style="color:#666666;"> <span style="color:#FF0000;font-size:14px;"><strong>适合人群:</strong></span> </p> <p style="color:#666666;"> <span style="font-size:14px;">1、有Python语言基础、web前端基础,想要深入学习Python Web框架的朋友;</span> </p> <p style="color:#666666;"> <span style="font-size:14px;">2、有Django基础,但是想学习企业级项目实战的朋友;</span> </p> <p style="color:#666666;"> <span style="font-size:14px;">3、有MySQL数据库基础的朋友</span> </p> <p style="color:#666666;"> <span style="font-size:14px;"> </span> </p> <p style="color:#666666;"> <span style="font-size:14px;"><img alt="" src="https://img-bss.csdnimg.cn/202009070752197496.png" /><br /> </span> </p> <p style="color:#666666;"> <span style="font-size:14px;"><br /> </span> </p>
<div style="color:rgba(0,0,0,.75);"> <span style="color:#4d4d4d;"> </span> <div style="color:rgba(0,0,0,.75);"> <span style="color:#4d4d4d;"> </span> <div style="color:rgba(0,0,0,.75);"> <div style="color:rgba(0,0,0,.75);"> <span style="color:#4d4d4d;">当前课程中商城项目的实战源码是我发布在 GitHub 上的开源项目 newbee-mall 新蜂商城,目前已有 6300 多个 star,</span><span style="color:#4d4d4d;">本课程是一个 Spring Boot 技术栈的实战类课程,课程共分为 3 大部分,前面两个部分为基础环境准备和相关概念介绍,第个部分是 Spring Boot 商城项目功能的讲解,让大家实际操作并实践上手一个大型的线上商城项目,并学习到一定的开发经验以及其中的开发技巧。<br /> 商城项目所涉及的功能结构图整理如下:<br /> </span> </div> <div style="color:rgba(0,0,0,.75);">   </div> <div style="color:rgba(0,0,0,.75);"> <p style="color:#4d4d4d;"> <img alt="modules" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3N0b3JlL25ld2JlZS1tYWxsLXMucG5n?x-oss-process=image/format,png" /> </p> </div> <p style="color:rgba(0,0,0,.75);"> <strong><span style="color:#e53333;">课程特色</span></strong> </p> <p style="color:rgba(0,0,0,.75);">   </p> <div style="color:rgba(0,0,0,.75);">   </div> <div style="color:rgba(0,0,0,.75);"> <ul> <li> 对新手开发者十分友好,无需复杂的操作步骤,仅需 2 秒就以启动这个完整的商城项目 </li> <li> 最终的实战项目是一个企业级别的 Spring Boot 大型项目,对于各个阶段的 Java 开发者都是极佳的选择 </li> <li> 实践项目页面美观且实用,交互效果完美 </li> <li> 教程详细开发教程详细完整、文档资源齐全 </li> <li> 代码+讲解+演示网站全方位保证,向 Hello World 教程说拜拜 </li> <li> 技术栈新颖且知识点丰富,学习后以提升大家对于知识的理解和掌握,以进一步提升你的市场竞争力 </li> </ul> </div> <p style="color:rgba(0,0,0,.75);">   </p> <p style="color:rgba(0,0,0,.75);"> <span style="color:#e53333;">课程预览</span> </p> <p style="color:rgba(0,0,0,.75);">   </p> <div style="color:rgba(0,0,0,.75);">   </div> <div style="color:rgba(0,0,0,.75);"> <p style="color:#4d4d4d;"> 以下为商城项目的页面和功能展示,分别为: </p> </div> <div style="color:rgba(0,0,0,.75);"> <ul> <li> 商城首页 1<br /> <img alt="" src="https://img-bss.csdnimg.cn/202103050347585499.gif" /> </li> <li> 商城首页 2<br /> <img alt="" src="https://img-bss.csdn.net/202005181054413605.png" /> </li> <li>   </li> <li> 购物车<br /> <img alt="cart" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3QvY2FydC5wbmc?x-oss-process=image/format,png" /> </li> <li> 订单结算<br /> <img alt="settle" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3Qvc2V0dGxlLnBuZw?x-oss-process=image/format,png" /> </li> <li> 订单列表<br /> <img alt="orders" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3Qvb3JkZXJzLnBuZw?x-oss-process=image/format,png" /> </li> <li> 支付页面<br /> <img alt="" src="https://img-bss.csdn.net/201909280301493716.jpg" /> </li> <li> 后台管理系统登录页<br /> <img alt="login" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3QvbWFuYWdlLWxvZ2luLnBuZw?x-oss-process=image/format,png" /> </li> <li> 商品管理<br /> <img alt="goods" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9uZXdiZWUtbWFsbC5vc3MtY24tYmVpamluZy5hbGl5dW5jcy5jb20vcG9zdGVyL3Byb2R1Y3QvbWFuYWdlLWdvb2RzLnBuZw?x-oss-process=image/format,png" /> </li> <li> 商品编辑<br /> <img alt="" src="https://img-bss.csdnimg.cn/202103050348242799.png" /> </li> </ul> </div> </div> </div> </div>
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值