• 世卫将“游戏障碍”列为新疾病 哪些症状可被确诊? 2019-05-07
  • 多部门三令五申禁网售彩票,世界杯竞猜APP上仍热卖 2019-05-07
  • 退牧还草 为藏羚羊迁徙让路 2019-05-05
  • 候选企业:苏宁控股集团有限公司 2019-05-05
  • 这是全国精气最为集中的地方 被称为中国丹田所在 ——凤凰网房产 2019-05-04
  • 状态差被郎平退货?丁霞归队仍是国内最好二传 2019-05-01
  • 天津市环境保护突出问题边督边改公开信息 2019-05-01
  • 董明珠代表:建立保护企业人才机制 遏制“恶意挖人”现象 2019-04-30
  • 7月1日起铁路将实施新列车运行图 北京再增12.5对复兴号 2019-04-24
  • 国际国内--山西频道--人民网 2019-04-23
  • 奥迪在华“三十已立” 坐拥天时,指向人和 2019-04-23
  • 中考期间 太原27个公共自行车服务点有人值守 2019-04-10
  • 有人被逼作弊,有人收获爱情,和高考有关的60个瞬间 2019-04-10
  • 深圳2018年将开行3趟援疆旅游扶贫专列 2019-04-05
  • 警惕套路贷 放贷是幌子 建议增设非法放贷罪 2019-03-25
  • 山东十一选五专家杀号 Chinaunix首页 | 论坛 | 山东十一选五专家杀号

    -

    • 博客访问: 87551
    • 博文数量: 43
    • 博客积分: 0
    • 博客等级: 民兵
    • 技术积分: 527
    • 用 户 组: 普通用户
    • 注册时间: 2018-12-20 14:57
    文章分类

    全部博文(43)

    文章存档

    2019年(38)

    2018年(4)

    我的朋友

    分类: 敏捷开发

    2019-02-11 10:05:38

     

    一、行转列

    1、数据库的 pivot

            pivot 并不是从一开始就存在的功能,只有主流大数据库厂商较新版本产品,例如 Oralce 11g 以上或 SqlServer2005 以上,才支持这个功能。

            从名称中可以猜到,这个功能是实现行与列的转换,也就是将行中的值作为列名。但是,数据库的行、列,与普通的表格不一样,不能直接将 X 轴与 Y 轴相互对掉就算大功告成。究其原因,数据库的列是有唯一性的(也就是列名是不能重复的),而行中存储的是动态的数据,如果不作为主键,就是可以重复的。所以,pivot 的实际应用,基本都要跟随在分组聚合运算之后,通过分组把用于转置的列(通常都是维度)中每一行数据都处理成不重复的值后,再将各行的值作为列名来展开。

            从具体应用的来看,pivot 的作用,其实就是将某一列的聚合结果,细分为多个更具体的列的聚合结果,以达到更直观的视觉效果。

            光说概念是不是比较枯燥,不容易理解?下面我们就以一个具体事例说明,比如 Oracle 数据库中有一个学生成绩表:

            

            如果想统计每个班的各科最高分,传统的做法是:

            select 班级, 科目, max(成绩) 最高分 from 学生成绩表 group by 班级, 科目

            

            上面的结果可以说观感非常不好:首先,在“班级”一列里,一班、二班重复出现,很容易就让人看错行;其次,在“科目”一列里,语文、数学和英语三个科目都放在一起,然而实际上这三个科目的最高分并没有什么比较的意义。

            事实上,我们应该更希望看到以下这样的结果:

            

            这个结果中,把科目这一列中的三个科目,各自分离出来单独作为一列,既减少了无用的重复,又明确了各科目最高分之间的相互独立性,看上去清晰明了了很多。

            可以说,pivot 就是为了这个目的而诞生的,为了实现上面的结果,现在的查询写法如下:

            select * from (select 班级, 科目, 成绩 from 学生成绩表) pivot (max( 成绩) for 科目 in ('数学' as 数学最高分, '英语' as 英语最高分, '语文' as 语文最高分))

    2、集算器的 pivot

            有的同学可能会问,既然数据库中已经有了 pivot,那为什么我还需要集算器的 pivot 呢?

            答案是:首先,不是所有的数据库都提供 pivot;其次,就算所有的数据库都提供 pivot,但如果是汇总了多个数据库的数据后还想再来个 pivot?那还是要用到集算器的 pivot。

            下面我们来看集算器的 pivot 如何使用

     

    A

    1

    =connect("orcl")

    2

    =A1.query("select   班级, 科目,max( 成绩) 最高分 from 学生成绩表   group by 班级, 科目")

    3

    =A2.pivot(班级; 科目, 最高分; "数学":"数学最高分", "英语":"英语最高分", "语文":"语文最高分")

            代码说明:

            A1:第一步连接数据库

            A2:第二步提取数据做预处理 (这一步可进一步扩展为做汇总或聚合等复杂的计算,具体方法请参考相关文章)

            A3:第三步即实现 pivot 的列转行功能并呈现出来,其效果与 Oracle 的 pivot 是完全一样的。

            

    3、pivot 的其他意义

            除了数据呈现需求,将行转为列后,还可以使用列间的计算方法。因为列与行的属性不同,有些列间的计算要在行间实现会比较繁琐。比如学校对班级成绩的某种考核评比,数、外、语三科的权重分别是:0.6、0.3 和 0.1,用两个班的三科平均分来计算评比指标:

     

    A

    1

    =connect("orcl")

    2

    =A1.query("select   班级, 科目,avg( 成绩) 平均分 from 学生成绩表   group by 班级, 科目")

    3

    =A2.pivot(班级; 科目, 平均分; "数学",   "英语", "语文")

    4

    =A3.new(班级, ( 数学 *0.6+ 英语 *0.3+ 语文 *0.1): 考核 )

            计算结果:

            

            上面的计算,假如要在行间实现,则会麻烦许多,有兴趣的同学可以自己试一下。

    二、 列转行

    1、数据库的 unpivot

            有行转列,自然就有列转行?;故且?Oracle 为例,它提供的列转行函数是 unpivot。

            列传行的功能在业务上又有什么意义呢?我们来看这样一份个人成绩表:

            

            如果想知道的是每个人最擅长哪个科目(也就是每个人的哪一科得分最高),行间计算时用 max 函数会很方便,而使用列间计算则相对比较繁琐。这时 unpivot 函数就派上用场了:

            with T1 as (select * from 个人成绩表 unpivot (成绩 for 科目 in ( 数学, 英语, 语文))), T2 as (select 姓名 姓名, max( 成绩) 最好成绩 from T1 group by 姓名 ) select T1. 姓名 姓名, T1. 科目 擅长科目, T2. 最好成绩 该科成绩 from T1 join T2 on T1. 姓名 = T2. 姓名 and T1. 成绩 =T2. 最好成绩

    2、集算器的 [email protected]

            那么,如果使用的数据库不是 Oracle 怎么办?还需要研究新数据库的转置语法细节么?如果数据库不支持转置语句又怎么办?需要用 case when 或是子查询之类的来间接实现类似功能么?

            不必如此烦恼!因为我们有集算器:

     

    A

    1

    =connect("orcl")

    2

    =A1.query("select   * from 个人成绩表")

    3

    =A2.piv[email protected](姓名; 科目, 成绩; 数学:"数学", 英语:"英语", 语文:"语文")

    4

    =A3.group(姓名).([email protected](-1; 成绩))

    5

    =A4.new(姓名, 科目: 擅长科目, 成绩: 该科成绩)

            计算结果,二者是一样的(在排序上可能略有差异):

            

            另外,还需要注意一点:数据库的 unpivot 并不完全是 pivot 的逆运算,因为 pivot 语句中往往包含了聚合函数,而聚合计算本身是不可逆的,也就是说 unpivot 并不能将 pivot 聚合后的结果再还原回原先的详细数据。但是集算器的 pivot 因为并不参与聚合计算(聚合计算在 pivot 执行之前已经单独执行了),所以集算器的 [email protected] 可以说是集算器的 pivot 运算的逆运算。

    三、 双向转置

            有时需要一些更复杂的转置操作,比如有这样一个学生成绩表

            

            而我们想要得到类似下面结构的学生成绩表(含义是查看某个学生某科目的成绩变化趋势):

    学生

    科目

    学期一

    学期二

    张三

    数学

    99

    87

            这里,首先要将数学、语文等列合并成科目列,需要列转行的操作;而要将学期列拆分成学期一、学期二等列,需要行转列的操作。

            考虑到数据表的结构一般是行数远大于列数,所以我们可以先进行列转行,再进行行转列。由于本表的原始数据在行列转换后数据与转换前的表中数据可以一一对应(不需要计算聚合),因此使用集算器的 [email protected] 和 pivot 函数显然会更方便。

     

    A

    1

    =connect("orcl")

    2

    =A1.query("select   * from 学生成绩表")

    3

    [email protected](学生, 学期; 科目, 成绩)

    4

    =A3.pivot(学生, 科目; 学期, 成绩)

            运行结果

            

    四、 动态列转置

            上面举的例子都属于静态转置,要求处理的表格和数据都是“规规矩矩”的。但实际业务中却总有不那么守规矩的异类存在,而且相信数量还不少,这时用 SQL 不管是 pivot/unpivot、还是 case when,还是别的啥,都有点力不从心……那该怎么办?这时集算器的优势就体现出来了:

            比如有下面一个记录收入情况的个人收入表

            

            但我们想得到一个类似下面结构的表

            姓名 收入来源 1 收入金额 1 收入来源 2 收入金额 2 ……

    山东十一选五专家杀号 www.pqwa.net         张三 工资 8000 炒股 6000 ……

            我们不确定行转列后,列的数量,甚至连列名也不能完全确定。这时就不能使用只适用于静态转置的 pivot 函数了,而需要使用动态转置的方法。而集算器的 SPL 语言在动态编程方面,要远比 SQL 语言灵活得多:

     

    A

    B

    1

    =connect("orcl")

     

    2

    =A1.query("select   * from 个人收入表").group(姓名)

    3

    =A2.max(~.len())

    4

    =create(姓名, ${A3.("收入来源"+string(~)+", 收入金额"+string(~))[email protected]()})

    5

    for A2

    =A5. 姓名 |A5.conj([收入来源, 收入金额])

    6

     

    >A4.record(B5)

            结果如下:

            

     

    五、 转置同时列间计算

            假设我有一张关于蔬菜的一周价格清单

            

            而我想由此计算得出关于各种蔬菜的一周价格走势,其中走势又包含四种状态:上涨、下降、平稳和初始(周一的值)。

            设计出来的表结构大体如下

            蔬菜 周一 周二 周三 周四 ……

            茄子 空值 上涨 下降 上涨 ……

            黄瓜 空值 上涨 上涨 ……

            ……

            虽然需要使用的转置属于静态类型,但在转置时需要实现列间的计算,这种计算对于 SQL 来说,处理起来非常麻烦。但若使用灵活性更强的集算器的 SPL 语言,则会轻松许多:

     

    A

    B

    1

    =connect("orcl")

    =["周一","周二","周三","周四","周五","周六","周日"]

    2

    =A1.query("select   * from 蔬菜价格表")

    3

    =create(蔬菜, 周一, 周二, 周三, 周四, 周五, 周六, 周日)

    4

    for A2.group(蔬菜)

    =A4.align(B1, 星期)

    5

     

    =B4.(if(#==1:"初始", 价格 > 价格 [-1]:"上升", 价格

    6

     

    >A3.record(A4. 蔬菜 |B5)

            获得“蔬菜的一周价格走势”表如下

            

     

    六、 总结

            相比于 SQL 提供的 pivot 和 unpivot,集算器 SPL 语言所提供的转置功能要更加灵活,适应性也更加广泛,可以满足各种复杂的转置需求。

     

    阅读(13796) | 评论(0) | 转发(0) |
    给主人留下些什么吧!~~
    评论热议
    请登录后评论。

    登录 注册

  • 世卫将“游戏障碍”列为新疾病 哪些症状可被确诊? 2019-05-07
  • 多部门三令五申禁网售彩票,世界杯竞猜APP上仍热卖 2019-05-07
  • 退牧还草 为藏羚羊迁徙让路 2019-05-05
  • 候选企业:苏宁控股集团有限公司 2019-05-05
  • 这是全国精气最为集中的地方 被称为中国丹田所在 ——凤凰网房产 2019-05-04
  • 状态差被郎平退货?丁霞归队仍是国内最好二传 2019-05-01
  • 天津市环境保护突出问题边督边改公开信息 2019-05-01
  • 董明珠代表:建立保护企业人才机制 遏制“恶意挖人”现象 2019-04-30
  • 7月1日起铁路将实施新列车运行图 北京再增12.5对复兴号 2019-04-24
  • 国际国内--山西频道--人民网 2019-04-23
  • 奥迪在华“三十已立” 坐拥天时,指向人和 2019-04-23
  • 中考期间 太原27个公共自行车服务点有人值守 2019-04-10
  • 有人被逼作弊,有人收获爱情,和高考有关的60个瞬间 2019-04-10
  • 深圳2018年将开行3趟援疆旅游扶贫专列 2019-04-05
  • 警惕套路贷 放贷是幌子 建议增设非法放贷罪 2019-03-25
  • 中国足彩网官网 快乐10分 京东彩票怎么提款 体育彩票22选5 福彩3d出号走势图 推广北京pk10平台 北京赛车pk10应用计划 竞彩足球比分直播500新浪 双色球012路蓝球分布图 18141期足彩6场半全场 西双版纳彩票大奖 百家乐开户 重庆时时彩真的假的 大乐透大奖2.2亿新闻 贵州生肖时时彩玩法 中彩网开奖视频直播