自定义查询统计的实现思路与技术难点[备忘]

时间：2010-07-23 来源：emsdog

在上家公司做了一个自定义查询统计的东东，说实话，该项目做的算比较成功，但由于时间紧且缺少较好的设计理念（做该项目的时候采用的是数据库驱动设计），造成项目改造成产品的代价太高，基本上需要重新来过，所以在公司时没有产品化。这里简单描述一下该查询统计项目的产生背景、设计思路与技术难点。

自定义查询统计往往是针对没有计算机基础的用户，但常见的灵活查询往往需要用户有一定的计算机基础（比如，理解and or的区别，动态勾稽表间关联关系等），或者就是统计报表生产过程太过繁琐，这样不仅增加了软件公司的培训成本，也增加了软件的推广难度。

我们需要的是一种简单易用，但不失灵活的查询统计系统，对用户屏蔽使用的复杂性，同时“查询”、“统计”都可以自定义并保存（用户以“所见即所得”方式定义查询或统计），统计结果可以生成基于时间的“快照”。

设计思路：

1、屏蔽复杂性-----采用单数据集（物理表或视图），单表查询相对于连接查询在大数据量时更有优势。

2、常见group by分组统计 + case when方式实现自定义统计口径

3、统计指标务必支持多种分组函数

4、枚举应当支持自定义，以满足用户自定义枚举查询的需求

5，快照实现：统计的关键数据以xml方式保存，需要查看时生成数据视图即可。

6，用户可同时选择多个统计口径、多个统计指标，口径之间的关系可以并列、可以嵌套

并列、嵌套的区别将在技术难点中讲述。

以下是详细的类图

技术难点：以表（视图）my_student_view来说明统计实现过程吧，表内容如下：表的创建语句如下：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[my_student_view]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[my_student_view]
GO

CREATE TABLE [dbo].[my_student_view] (
    [id] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [name] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
    [sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
    [age] [int] NULL ,
    [address] [nchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
    [degree] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
    [grade] [int] NULL
) ON [PRIMARY]
GO

我们可以实现以下的统计，这些统计过程就是统计数据的产生过程。如不清楚请先复习以下数据库中关于group by 与 case when 语句的部分。

select sex,address,degree,count(id) from my_student_view where 1=1 group by sex,address,degree
---sex,address 口径（Caliber）嵌套 id作为count类型的指标（Indecator）

select degree,sex,count(id) from my_student_view where 1=1 group by sex,degree
select degree,address,count(id) from my_student_view where 1=1 group by address,degree
--- sex，address口径并列

SELECT degree,
       CASE
          WHEN grade < 60
             THEN '不及格'
          WHEN grade >= 60 AND grade < 80
             THEN '及格'
          ELSE '优秀'
       END grade,
       count(id)
from my_student_view
where 1=1
group by degree,
CASE
  WHEN grade < 60
     THEN '不及格'
  WHEN grade >= 60 AND grade < 80
     THEN '及格'
  ELSE '优秀'
END
----grade作为自定义口径

SELECT degree,
       CASE
          WHEN name like '刘%'
             THEN '刘姓'
          WHEN grade like '李%'
             THEN '李姓'
          ELSE '其他'
       END name,
       count(id)
from my_student_view
where 1=1
group by degree,
CASE
  WHEN name like '刘%'
     THEN '刘姓'
  WHEN grade like '李%'
     THEN '李姓'
  ELSE '其他'
END