电脑技术学习

group by分组的应用

dn001

第五节 group by分组的应用

首先先说说分组是怎样工作的。举个例子:

表:temp1有以下记录:

bh	sl

aaa	1

aaa	4

ccc	2

bbb	5

aaa	9

bbb	7

现在要统计一下temp1有中多少种编号,各种编号的总数量又是多少。很明显,这是使用分组。

sele bh,sum(sl) as total_sl ;

  from temp1 ;

  grou by bh ;

  orde by bh ;

  into curs temp2

命令的运行过程就象投票选举中的点票过程一样。它中间到底是采用什么样的技术来储存中间结果我不知道,但不这重要,我现在假设它使用临时表(实际上我想不大可能),这样对结果没影响,但又容易理解。

1、是逐个扫描记录。每遇到一个新的编号,就为这个编号建立一个临时表(再次重申,使用临时表只是我的假设布局,真正的处理方法我不知道),然后把这个记录的内容放进对应的临时表中去。如果不是新编号,就直接把记录内容放进对应的临时表中去。

2、全部记录扫描完了,各种编号对应的临时表也产生了,现在就是对每一个临时表采用sum()进行统计了。比如编号为"aaa"的临时表是cursor1,那它的内容就是:

Aaa	1

Aaa	4

Aaa	9

现在进行sum()统计了,它的效果就相当于sele bh,sum(sl) as total_sl from cursor1。每一个编号的临时表都要进行这样的sum()统计,然后把每个编号的统计结果联合起来,就想使用union一样,最后得出的结果就是:

aaa	14

ccc	2

bbb	12

3、然后再给bh排序,结果就是:

aaa	14

bbb	12

ccc	2

看了上面那个例子,再看下面这条命令,想必也可以理解了吧:

sele cpk.cpbh,cpk.cpmc,sum(iif(isnull(thdmx.thsl),0,thdmx.thsl) as total_thsl ;

  from cpk left join thdmx ;

    on cpk.cpbh=thdmx.cpbh ;

  grou by cpk.cpbh ;

  orde by cpk.cpbh ;

  into curs temp1 

产品表左联接提货明细表,这样没有提过的产品,在联接后的临时表中,对应的提货数量就是个null值。到了分组,第一步完成后,生成的临时表中也仍然有null值,所以在第二步进行sum命令时,就要使用iif(isnull())来过滤了。过滤的结果就是把null值改为0,其它的不变,然后再由sum进行汇总。最后第三步再排序,结果就出来了。

以上是分组的最基本用法。现在说说分组的一些古怪用法。

一、使用recn()。例子一:

有种情况,一大段本来是应该连续的号码,但中间地漏掉了一些号码,现在要把这些漏了的号码找出来。恐怕很多人,一来就是使用scan endscan,再加上一些中间变量以作为判断依据,最后写成的代码有一大段,运行起来慢得要死。但如果采用以下这种方法,我想即使它不是最好,但也是很好的了。数据如下:

1、6、3、5、2、7、9、10、15、16

在这些数据当中,要找出漏掉了的4、8、11、12、13、14。

1、首先排序

sele bh from temp1 orde by bh into curs temp1

结果是:

1、2、3、5、6、7、9、10、15、16

如果源表中的物理记录顺序和号码的顺序一样就不用做这一步了。

2、取temp1中各个记录的recn(),即记录号,然后把编号减去记录号

sele bh,val(bh-recn()) as aa from temp1 into curs temp2

结果就是:

Bh	aa

1	0

2	0

3	0

5	1

6	1

7	1

9	2

10	2

15	6

16	6

3、对aa使用分组,取每组中最小的bh和最大的bh

sele min(bh) as minbh,max(bh) as maxbh from temp2 grou by aa into curs temp3

结果如下:

minbh	maxbh

1	3

5	7

9	10

15	16

4、现在结果很明显了,某个记录的minbh跟它上面那个记录的maxbh中间相差的,就是漏掉的号码。现在才使用scan endscan就容易了。

以上方法适用于数据量大、第三步的结果比源表少很多记录的情况下才会发挥效果。因为scan的速度不能和Select - SQL相比,何况它还要作条件判断。虽然这种方法使用了二条全遍历的SQL命令,但这是没过滤条件的SQL命令,速度是很快的,只是分组要多点时间而已。同时只适用于编号没有重复的情况。如果编号有重复,那在第一步的时候,就要使用:

sele bh from temp1 goru by bh orde by bh into curs temp1

把多余的编号去掉。如果想了解那些编号是重复的,就可以使用:

sele bh,coun(bh) as aa from temp1 grou by bh orde by bh havi aa>1 into curs temp1

结果就是重复的编号,对应的aa字段就是重复的次数。

在第二步,好象可以不用二条命令就可以得出结果了,只是暂时还没想到,不知各位对些有什么看法。

上面这种方法有个缺点:就是使用了sum()函数,而它是只能统计数值型的数据,其它字符型、日期型等是不能统计的。所以如果想把一个表中所有记录的产品名称都串起来,变成一个字符串,那是不能的。但对于日期型,变通一下,有时还是可以使用的。

二、日期相减的结果是数值,从而可以使用sum()函数。例子二

编号	名称	入库日期

1	网卡	2000.09.02

1	网卡	2000.09.03

1	网卡	2000.09.05

2	vfp	2000.09.05

2	vfp	2000.09.06

说明:把编号和名称相同,入库日期相间为1的记录合并为一条,并加入另一个表里,如结果:

编号     名称   入库日期        (表2和表1结构一样)

1	网卡	2000.09.03

1	网卡	2000.09.05

2	vfp	2000.09.06

要完成上面的要求,用两个步骤:

1、sele * from 表1 orde by 名称,入库日期 into curs temp1

2、sele 编号,名称,max(入库日期), 入库日期-recn() as dd;

   from temp1 ;

   grou by 名称,dd ;

   into curs temp2

问题的关键是那个"入库日期-recn() as dd",因为已经按日期按好了顺序,所以9月2号减1和9月3号减2的结果都是相同的,但9月5号减3就是另外一个数了,所以这样就把这些日期分开了,就可以用分组了。假设表内容如下:(xm是字符类型,其中的1代表上午上班,2上午下班,3、4如此类推)

这个例子,到在处理第二步的时候,其实就跟第一个例子一样了,都是把一个字段减去记录号,然后根据结果进行分组。

例子三:某员工某月的打卡记录temp1如下:

打卡时间SJ		项目XM	状态ZT

2000/09/1308:01:00AM	1	On time

2000/09/1312:00:00AM	2	On time

2000/09/1302:00:00PM	3	On time

2000/09/1305:59:00PM	4	Early 

2000/09/1208:00:00AM	1	On time

2000/09/1212:00:00AM	2	On time

2000/09/1202:00:00PM	3	On time

2000/09/1206:00:00PM	4	On time

2000/09/1408:00:00AM	1	On time

2000/09/1412:00:00AM	2	On time

2000/09/1402:01:00PM	3	Later 

2000/09/1406:00:00PM	4	On time

2000/09/1508:00:00AM	1	On time

2000/09/1512:00:00AM	2	On time

2000/09/1502:00:00PM	3	On time

2000/09/1506:00:00PM	4	On time

xm中的1、2、3、4分别代表早上上班、早上下班、下午上班、下午下班。zt中on time表示准时,early表示早退,later表示迟到。在以上数据,把每天的上下班状态用以下的格式列出来:

日期	上午上班时间	上午下班时间	下午上班时间	下午下班时间	上午上班状态	下午状态

很明显,这是根据打时间进行分组。如果使用:

ele day(sj) as dd ,

     iif(xm='1',sj,{}) as 上午上班时间, ;

     iif(xm='2',sj,{}) as 上午下班时间 ;  

 from temp1 ;

  grou by dd 

这个方法不行,原因我详细说一下,可能会有点罗嗦。先说说13号这天的数据。在第一个记录,在第一个iif(),xm=1,上午上班时间就是早上八点。到了第二个记录,xm=2,所以第一个iif()的就是{},即空白日期。到了第三、四个记录,上午上班时间都是空白日期,所以到最后的结果就是13号这天,上午上班时间是空白日期而不是早上八点!其他日期、除了下午下班时间之外其他时间都是如此。按前面说的分组过程,是先对每天的记录进行分组,然后按顺序一个个记录的计算iif()的结果,每计算一次iif(),都更新上午上班时间的值,这样在每天四个上下班时间中,前面三个记录都不起作用,只有第四个才起作用了。

那是不是就不能用分组了呢?那又不是,我们有sum()函数,在sum()函数里,它可以累计前面那三个记录的值,但要是数值型的字段才行。而现在却是日期型,所以我们要转换一下。命令如下:

先定义一个全程变量:initsj={^1900-01-01,00:00:00},即1900年1月1日零时,用它来做基准时间。

SELECT DAY(sj) AS dd,;

  initsj+SUM(IIF(xm="1",sj-initsj,0)) AS sj1,;

  initsj+SUM(IIF(xm="2",sj-initsj,0)) AS sj2,;

  initsj+SUM(IIF(xm="3",sj-initsj,0)) AS sj3,;

  initsj+SUM(IIF(xm="4",sj-initsj,0)) AS sj4,;

  IIF(SUM(IIF(xm="1".AND.zt="on time",1,0))=1,"on time","NO time"),;

  IIF(SUM(IIF(xm="3".AND.zt="on time",1,0))=1,"on time","NO time");

FROM 数据1!temp5;

GROUP BY 1

结果如下:(为了看得更清楚,我省略了两个字段sj2,sj3,DD是表示日期)

DD	SJ1			SB3			Exp_6	Exp_7

12	2000/09/12 08:00:00 AM	2000/09/12 02:00:00 PM	On time	On time

13	2000/09/13 08:01:00 AM	2000/09/13 02:00:00 PM	NO time	On time

14	2000/09/14 08:00:00 AM	2000/09/14 02:01:00 PM	On time	NO time

15	2000/09/15 08:00:00 AM	2000/09/15 02:00:00 PM	On time	On time

和第一条命令相比,它不同的地方是:在分组之后,在13号这天的分组里,第一个记录的xm=1,所以iif()的结果是sj-initsj,即这天的上午上班时间跟基准时间的差(是一个数值型的)。到了第二个记录xm=2,iif()的结果是0,第三、四个记录的iif()都是0,最后sum()把sj-initsj、0、0、0这四个数值中起来,结果还是sj-initsj,也就是第一个记录跟基准时间的差,然后再加上基准时间,也就是这天的上班时间了。

这条命令的奥妙就在于日期可以相减,结果是一个数值,然后用sum()进行累加;而日期加一数值,结果还是日期。在求早上状态时(xm='1'),如果不是早上上班的时间,就累加零,否则就累加上班时间与基准时间的差。最后把结果再加上基准时间又得回原来的上班时间。

而字符那里,如果直接使用第一条命令那种做法,也是不行的。原因也一样,前面三个记录的结果都让第四个记录的值给覆盖了。因本例特殊点,具有唯一性,所以还可以sum()+iif()的方法。但sum()不可以处理字符串,所以要用iif()转换为数值型。

工作原理跟刚才计算时间那样,用内部的iif()把xm="1".AND.zt="on time"即上午准时上班的记录设为1,而其它的时间或上午不是准时上班的就是0,然后用sum()累加,最后又用外面的iif()对sum()的结果进行判断,如果结果是1,就表示上午是准时上班。因为只有一个上午上班记录(xm='1'),而只有zt='on time'才表示准时上班。

在决定是否用分组前,应先确定以哪些定段作为分组依据。分组依据确定后,就要检查一下,你认为某些记录是应该在同一个分组内的,但如果直接就去分组的话,这些记录又不是同一个分组内(看看第一和第二个例子),就要想办法找出这些记录有什么共同点,然后根据这些共同点转换一下,得出一个相同的中间值(第一个例子就是减去各自的记录号从而找出共同点)。然后才根据这个共同点进行分组。

这天的数据。在第一个记录,在第一个iif(),xm=1,上午上班时间就是早上八点。到了第二个记录,xm=2,所以第一个iif()的就是{},即空白日期。到了第三、四个记录,上午上班时间都是空白日期,所以到最后的结果就是13号这天,上午上班时间是空白日期而不是早上八点!其他日期、除了下午下班时间之外其他时间都是如此。按前面说的分组过程,是先对每天的记录进行分组,然后按顺序一个个记录的计算iif()的结果,每计算一次iif(),都更新上午上班时间的值,这样在每天四个上下班时间中,前面三个记录都不起作用,只有第四个才起作用了。

那是不是就不能用分组了呢?那又不是,我们有sum()函数,在sum()函数里,它可以累计前面那三个记录的值,但要是数值型的字段才行。而现在却是日期型,所以我们要转换一下。命令如下:

先定义一个全程变量:initsj={^1900-01-01,00:00:00},即1900年1月1日零时,用它来做基准时间。

SELECT DAY(sj) AS dd,;

  initsj+SUM(IIF(xm="1",sj-initsj,0)) AS sj1,;

  initsj+SUM(IIF(xm="2",sj-initsj,0)) AS sj2,;

  initsj+SUM(IIF(xm="3",sj-initsj,0)) AS sj3,;

  initsj+SUM(IIF(xm="4",sj-initsj,0)) AS sj4,;

  IIF(SUM(IIF(xm="1".AND.zt="on time",1,0))=1,"on time","NO time"),;

  IIF(SUM(IIF(xm="3".AND.zt="on time",1,0))=1,"on time","NO time");

FROM 数据1!temp5;

GROUP BY 1

结果如下:(为了看得更清楚,我省略了两个字段sj2,sj3,DD是表示日期)

DD	SJ1			SB3			Exp_6	Exp_7

12	2000/09/12 08:00:00 AM	2000/09/12 02:00:00 PM	On time	On time

13	2000/09/13 08:01:00 AM	2000/09/13 02:00:00 PM	NO time	On time

14	2000/09/14 08:00:00 AM	2000/09/14 02:01:00 PM	On time	NO time

15	2000/09/15 08:00:00 AM	2000/09/15 02:00:00 PM	On time	On time

和第一条命令相比,它不同的地方是:在分组之后,在13号这天的分组里,第一个记录的xm=1,所以iif()的结果是sj-initsj,即这天的上午上班时间跟基准时间的差(是一个数值型的)。到了第二个记录xm=2,iif()的结果是0,第三、四个记录的iif()都是0,最后sum()把sj-initsj、0、0、0这四个数值中起来,结果还是sj-initsj,也就是第一个记录跟基准时间的差,然后再加上基准时间,也就是这天的上班时间了。

这条命令的奥妙就在于日期可以相减,结果是一个数值,然后用sum()进行累加;而日期加一数值,结果还是日期。在求早上状态时(xm='1'),如果不是早上上班的时间,就累加零,否则就累加上班时间与基准时间的差。最后把结果再加上基准时间又得回原来的上班时间。

而字符那里,如果直接使用第一条命令那种做法,也是不行的。原因也一样,前面三个记录的结果都让第四个记录的值给覆盖了。因本例特殊点,具有唯一性,所以还可以sum()+iif()的方法。但sum()不可以处理字符串,所以要用iif()转换为数值型。

工作原理跟刚才计算时间那样,用内部的iif()把xm="1".AND.zt="on time"即上午准时上班的记录设为1,而其

标签: