`
datamachine
  • 浏览: 163392 次
社区版块
存档分类
最新评论

集算器协助SQL实现非等值分组

    博客分类:
  • DB
阅读更多

SQL通常只能按源表字段进行分组,如果分组依据来自另一张表、外部参数、条件列表,用SQL就很繁琐。有时分组依据需要动态生成,这些往往要借助高级语言实现。有时分组依据和源表不完全对应(或区间没有交集),差异部分有时要补齐在分组结果中,有时要排除在外,用SQL处理起来难度很大。

         集算器支持对位分组、枚举分组、有序计算,可轻松实现上述非等值分组。集算器还支持独立使用、控制台执行、报表调用、JAVA代码调用,参考【集算器辅助SQL编写的应用结构】。

下面举例说明SQL开发中常见的非分等值分组问题,以及集算器的解法。

 

基本固定分组

sales存储着订单记录,其中CLIENT列是客户名,AMOUNT列是订单金额,请将sales按照“潜力客户列表”进行分组,并对各组的AMOUNT列汇总求和,其中潜力客户列表是外部参数,有些项目可能不在sales表的Client列(汇总结果应为0),比如:TAS,BON,CHO,ZTOZ。部分数据如下:

OrderID

Client

SellerId

Amount

OrderDate

26

TAS

1

2142.4

2009/8/5

33

DSGC

1

613.2

2009/8/14

84

GC

1

88.5

2009/10/16

133

HU

1

1419.8

2010/12/12

32

JFS

3

468

2009/8/13

39

NR

3

3016

2010/8/21

43

KT

3

2169

2009/8/27

         分组依据是定值且项目较少时,SQL可用union/decode配合进行分组,但本案例的分组依据是外部参数,经常发生变化,SQL就只能创建临时表,将参数解析后一条条插入临时表,再进行后续的计算。用集算器实现本案例不必建立临时表,代码如下:


 

         函数align可按外部参数列表arg1分组,选项@a表示取出分组中的所有记录,无该选项时只取每组第一条。函数pjoin可按顺序横向拼接记录,结果如下:

动态分段合计

按订单金额对表sales进行分段,统计每一段的订单总额,分段条件是参数列表,每次都可能不同,比如分为4个区间:0-10001000-20002000-4000

 

如果条件已知,那就可以将这些条件写死在SQL里,如果条件是动态的外部参数,则经常需要用JAVA等高级语言拼凑SQL,过程非常复杂。集算器支持动态表达式,代码更加简单:




其中byFac是参数,比如 ["?<=1000" ,"?>1000 && ?<=2000","?>2000 && ?<=4000","?>4000"]Enum函数可按条件对数据分组。结果如下:



 

         上述条件分组中,条件恰好没有发生重叠,但实际上发生重叠的情况很常见,比如将订单金额按照如下规则分组:

         10004000:常规订单r14

         2000以下:非重点订单r2

         3000以上:重点订单r3

上述条件中r2r1发生了条件重叠,在这种情况下有时我们希望结果不重叠(即按r1统计完后,从剩下的记录中统计r2),有时也会要求重叠(每次都按全部数据重新统计)。想实现此类需求,SQL要使用大量withunionexceptminus语句,代码非常繁琐。集算器的函数enum默认不重叠,使用@r选项时允许重叠。

 

动态分段累计

         performance存储每位员工的绩效分和绩效奖金,现在要从0开始,以10分为一个分数段,将绩效分为多个分数段,然后从低到高累计每个分数段的奖金总数,即当前段的累计值要包含之前所有段的奖金总数。源数据如下:

id

score

bonus

e01

9

800

e02

21

2300

e03

25

2800

e04

33

4100

e05

46

5800

e06

52

6099

 

         绩效分数不定,因此区间数量不定,Oracle/MSSQL可以建立辅助区间表结合窗口函数来实现,但代码相当繁琐,MySQL等不支持窗口函数的数据库将更加困难。此类非等值分组用集算器实现较简单,代码如下:



 

         A2动态生成区间范围,其中m表示按序号取成员,m(-1)表示取倒数第1条,“\”表示除后取整数部分。A3根据A2建立新二维表,通过查询A1对应的记录来累计奖金,结果如下:



 

补齐固定分组

         building存储产品完工记录,其中year为完工时间,字符型,格式为“年份 上半年\下半年”。现在要指定起止年份,统计出每类产品每半年的完工数量。源数据如下:

id

type

year

1

33

2014 last half

2

33

2014 last half

3

33

2013 first half

4

34

2013 first half

         源表中的年份不连续,SQL要先拼凑连续的年份表再left join,难度较大。集算器代码相对简单:



 

         A2创建新二维表,A3按照起止时间参数(argb,arge)创建年份表,A4building表按type分组,并循环处理每组数据。每次循环(B4)向A2插入与年份表数量相等的记录。A2的最终结果如下:



 

补齐固定分组并转置

         onBusiness存储员工的出差记录,主要字段是Date,id_user。表user存储用户信息,主要字段是id,name,现在要指定时间段,按顺序列出每周每个员工是否出差,特殊之处在于每个员工要占一列。onBusiness部分数据如下:

 

Date

id_user

2015-06-22

2

2015-06-01

1

2015-06-03

1

2015-06-19

1

2015-06-02

2

如果当起止日期是2015-05-312015-06-28,则期望结果应当如下:

week

user1

user2

1

yes

yes

2

No

No

3

yes

No

4

No

yes

 

集算器代码如下:



 

先用简单关联语句查询数据,再按区间造二维表A3,每周占1行(间断时自动补齐数据),每个员工占一列,初值为“No”。然后循环A2,将A3对应的位置修改为“Yes”。

按月份查询分组

       work存储着某岗位的任职信息,People字段是用户名,Date表示到岗日期,Deleted表示离岗日期,现在要统计出3月份到7月份每个月有多少人在岗。源数据如下:

People

Date

Deleted

Amanda

2015-03-01

Null

Ray

2015-03-01

Null

Moe

2015-04-01

Null

Yan

2015-05-01

Null

Bee

2015-05-05

2015-06-12

Lee

2015-06-06

Null

jason

2015-05-01

2015-07-03

       本案例难点在于源表中月份不一定连续,但计算结果要求连续,因此要先生成连续月份,再将源数据按连续月份对齐分组。此外,如果某人离岗,则当月的在岗人员中包含此人,下月不包含,因此要进行跨列计算和组间累计才能算出正确的在岗人数。

  集算器代码:



 

函数to可生成连续序列,函数new可根据序列(或二维表)生成新二维表,~表示源序列中当前成员,最终结果如下:



 

动态区间定位

         Transaction记录每个客户的交易时间,表Discount存储某个时间点之后的折扣,折扣信息有多条,形成了一系列动态时间段。现在要计算每位客户每项交易的折扣。

    Transaction部分数据

TransID

Tuser

Date

t1

Andrew

2015-06-16 13:13:00

t2

Andrew

2015-06-16 13:15:00

t3

Andrew

2015-06-16 13:17:00

t4

Andrew

2015-06-16 14:15:00

t5

Andrew

2015-06-16 14:18:00

t6

Andrew

2015-06-16 14:25:00

t7

Andrew

2015-06-16 14:35:00

t8

Andrew

2015-06-16 14:55:00

t9

tylor

2015-06-16 13:13:00

t10

tylor

2015-06-16 14:15:00

t11

tylor

2015-06-16 14:55:00

    Discount部分数据:

DiscountID

Date

Discount

d1

2015-06-16 13:00:00

30

d2

2015-06-16 14:00:00

25

d3

2015-06-16 14:30:00

20

 SQL要先跨行算出时间段,再进行join运算,代码很难写。集算器可将TransactionDisccount等值分组,代码相对简单



 

         函数pseq可算出某条数据所属的区间号,A2(…)可按序号取记录。计算结果如下:



 

         上述例子中所有的客户共享折扣信息,如果每个客户的折扣都不同(discount表增加DUser字段),则代码应当写成这样:



 

         结果如下:



 

 

 

 

 

  • 大小: 16.9 KB
  • 大小: 10 KB
  • 大小: 14.7 KB
  • 大小: 18.5 KB
  • 大小: 20.4 KB
  • 大小: 10.6 KB
  • 大小: 30.9 KB
  • 大小: 36.8 KB
  • 大小: 43.4 KB
  • 大小: 17.9 KB
  • 大小: 6.8 KB
  • 大小: 25.6 KB
  • 大小: 29.1 KB
  • 大小: 36.4 KB
  • 大小: 48.1 KB
0
0
分享到:
评论

相关推荐

    SQL Server分组排序取数据的实现

    本文将深入探讨如何在SQL Server中实现分组排序并获取每组的特定行数据。 首先,我们要理解分组的概念。在SQL中,`GROUP BY`语句用于将数据根据一个或多个列进行分类,例如在示例代码中,我们根据`A.DocNo`, `A1....

    sqlserver 分组合并 分组统计

    sqlserver 分组合并字符串 分组统计数量

    pb通过sql语句实现分组小计统计

    #### SQL分组小计 在SQL中,实现分组小计主要依赖于`GROUP BY`子句与聚合函数(如`SUM()`, `AVG()`等)。这些功能组合起来可以对数据进行多维度的分析与汇总。 例如,为了计算每个`STOCK_ID`下按`TYPE_CD`分类的...

    Teradata SQL 根据分组抽样

    标题 "Teradata SQL 根据分组抽样" 指的是在Teradata数据库系统中,使用SQL查询语句对数据进行分组后的抽样操作。这种操作在数据分析、统计建模或者测试查询性能时非常常见,因为它允许我们从大量数据中选取代表性...

    sql server report server分组示例

    标题“SQL Server Report Server分组示例”指向的是一个关于如何在SQL Server 2005的报表服务器中实现数据分组的实际应用。分组可以帮助用户按特定字段对数据进行分类,例如按地区、时间或产品类型等。分组不仅可以...

    分组汇总的sql

    用于分组汇总的sql语句,可根据某一列分组统计

    SuperMap iObjects Java SQL查询中的分组查询

    这个范例主要是实现SQL查询中的分组查询,可以查询在一定的空间范围内各个地物的数目,这个在实践中很用的较广,比如查询某一区域内A类建筑、B类建筑、C类建筑的数目,有利于后续的小区的建设等等,但是这个查询只能...

    SQLserver 实现分组统计查询(按月、小时分组)

    这两种方法都利用了SQL的分组函数(如`SUM`)和条件语句(如`CASE`)来实现分组统计。这样的查询方式能够帮助我们快速获取按月或按小时的访问统计信息,即使在某些时间段没有数据的情况下也能正确展示。 请注意,...

    用SQL实现统计报表中的小计与合计的方法详解

    本文将详细介绍如何利用SQL实现这一功能,主要关注"利用临时表"这一方法,因为这种方法具有逻辑清晰且执行效率高的特点,尽管在大数据量时可能会占用较多内存。 首先,我们需要创建一个临时表#TB,它是原始数据的...

    部分普通sql查询在hive中的实现方式

    ### 部分普通SQL查询在Hive中的实现方式 Hive是一款基于Hadoop的数据仓库工具,能够对存储在Hadoop文件系统中的数据集进行数据提取、转换、加载(ETL),这是一种可以简化MapReduce编程的工具。由于Hive的设计初衷...

    java 实现两张表的等值连接

    在数据库领域,等值连接(Equi-Join)是一种常见的操作,它用于将两个或多个表的数据基于一个或多个共享...然而,对于学习和理解等值连接的原理,以及在无法使用SQL的情况下的解决方案,上述Java实现是一个很好的起点。

    实现recordset分组汇总

    在Visual Basic Scripting Edition (VBScript) 或 Visual Basic for Applications (VBA) 环境下,实现RecordSet的分组汇总功能并不直观,因为这些语言本身并未直接提供类似SQL中的GROUP BY这样的功能。因此,我们...

    SQL2000企业管理器绿色版

    这个"SQL2000企业管理器绿色版"是精简版或者便携式版本,无需安装即可使用,方便在不同环境或非固定工作台上快速启动数据库管理工作。 1. SQL Server 2000简介:SQL Server 2000是微软发布的一款关系型数据库管理...

    Sqlch11_2having条件分组查询.sql

    Sqlch11_2having条件分组查询.sql,Sqlch11_2having条件分组查询.sql Sqlch11_2having条件分组查询.sql,Sqlch11_2having条件分组查询.sql

    基于视图机制和分组统计实现SQL中全称量词的转换.pdf

    本文主要探讨了如何利用视图机制和分组统计来实现SQL语言中全称量词的转换,使查询涉及“全部”语义时能更直观易懂。SQL(Structured Query Language,结构化查询语言)是关系数据库中一种标准的查询语言,虽然它...

    SQL实现汉字转拼音(非拼音首字母)

    SQL实现汉字转拼音(非拼音首字母) 希望大家支持

    SQL SERVER 分组求和sql语句

    本篇将详细解释如何使用SQL Server的`GROUP BY`语句来实现分组求和。 首先,`GROUP BY`语句是SQL中的一个关键组件,它的主要功能是对数据进行分类,将相同值的记录归为一类。当与聚合函数(如`SUM`, `COUNT`, `AVG`...

    SQL语句生成器.rar

    在易语言SQL语句生成器源码中,我们可以深入理解如何利用易语言实现SQL语句的构建和解析功能。 1. SQL基础知识: - SELECT:用于从表中选取数据,是最常见的SQL命令,包括选择列、过滤行、排序和分组等操作。 - ...

    SQL数据排序与分组

    SQL数据排序与分组是数据库查询中的核心概念,它们对于数据的分析和处理至关重要。首先,数据分组的目的是为了将具有相同值的记录合并在一起,这样可以方便地对这些记录进行统计分析。例如,如果我们有一个销售记录...

Global site tag (gtag.js) - Google Analytics