浅谈db2在线分析处理函数

关键词：

最近碰到一个测试需求，使用到了在线分析处理（OLAP），现总结记录一下，也希望能帮到有相关问题的朋友。

1. 测试环境是DB2，通过ETL（数据抽取，数据转换，数据加载）技术将数据源数据加载到目标数据仓库。

2. 需求大概意思：验证能否将包含制定商品编码的订单从数据源加载到目标数据库。

3. 目标数据来源于7个不同的应用数据库，每个应用数据库都存储商品编码组相应的订单，其中的50个商品编码相关的订单需要加载到目标仓库。

分析需求后我们得到测试的方法：

1. 在源数据库查询样本订单数据，取得其中所有包含在50个制定商品编码的订单，每个商品编码2条数据用来验证。

2. 通过常规的SQL，我们对订单按照商品编码分类，可以得到Max函数得到最新创建的订单，但这个方法每个商品编码只能得到一条数据，如果需要得到2条，10条数据呢？SQL就感到很吃力了。现在我们可以引入OLAP函数,简单高效的达到业务目标。

 1 SELECT * FROM
 2 (SELECT 
 3 DISTINCT RTRIM(A.RECORD_ID),RTRIM(A.PO_ID),RTRIM(A.ANT_ID),B.CAT_ID,B.EXTRACT_DT,ROW_NUMBER()OVER(PARTITION BY B.CAT_ID ORDER BY B.EXTRACT_DT DESC)RN
 4 
 5 FROM 
 6 --retreve 100 POs for each CAT_ID for last year from BDW
 7 (SELECT RECORD_ID,PO_ID,CAT_ID,EXTRACT_DT,ANT_ID FROM TEAME.PO_ITEM
 8 WHERE CAT_ID IN (‘4Q6‘, ‘4W8‘, ‘S86‘, ‘S89‘, ‘QU39‘, ‘U4Q0‘, ‘UQ41‘, ‘UQ43‘, ‘U89‘, ‘W24‘, ‘YQ44‘, ‘QY45‘, ‘QY50‘, ‘Y5Q1‘, ‘E0W4‘, 
 9 ‘W72‘, ‘8Q3‘, ‘0W3‘,‘Q75‘,‘73‘, ‘P74‘, ‘75‘, ‘P76‘, ‘77E‘, ‘P78‘,‘E03‘, ‘E05‘, ‘E06‘, ‘E07‘, ‘ED8‘
10 , ‘WW9‘, ‘E37‘, ‘WW0‘, ‘DD3‘, ‘DS3‘, ‘E65‘, ‘7S4‘, ‘45‘, ‘CA1‘, ‘0QS4‘, ‘W31‘, ‘64‘, ‘9A4‘, ‘Y95‘, ‘QY96‘)
11 --AND DATE(EXTRACT_DT) >= DATE(CURRENT_DATE - 365 DAYS) AND DATE(EXTRACT_DT) <= DATE(CURRENT_DATE)
12 )B,
13 IP.COM C, 
14 TEAME.PO_IA POIA,
15 TEAME.PO A
16 
17 LEFT OUTER JOIN TEAME.P_G_M D 
18 ON 
19 D.RECORD_ID = A.RECORD_ID AND
20 D.PRCHORG_ID = A.PRCHORG_ID AND
21 D.PRCHGRP_ID = A.PRCHGRP_ID AND
22 D.PRCHMEM_UNIQ_ID = A.PRCHMEM_UNIQ_ID 
23 
24 WHERE 
25 A.RECORD_ID = B.RECORD_ID AND
26 A.PO_ID = B.PO_ID AND
27 A.ANT_ID = B.ANT_ID AND
28 A.RECORD_ID = POIA.RECORD_ID AND
29 
30 A.PO_ID = POIA.PO_ID AND
31 B.CAT_ID = C.CORPCOMMCODE AND
32 (COMGROUP IN (‘J‘, ‘D‘) 
33     OR POIA.LEDGACCT_MINOR_NUM IN (‘123‘,‘422‘,‘1‘,‘21‘,‘324‘,‘123‘,‘442‘,‘123‘,‘FDF‘,‘FD‘))
34 AND A.RECORD_ID > ‘   ‘  
35 AND DATE(A.EXTRACT_DT) >= DATE(CURRENT_DATE - 365 DAYS) AND DATE(A.EXTRACT_DT) <= DATE(CURRENT_DATE))RN
36 WHERE RN=1
37 WITH UR;

接下我们主要来看这一句：ROW_NUMBER()OVER(PARTITION BY B.CAT_ID ORDER BY B.EXTRACT_DT DESC)RN

ROW_NUMBER()这个函数是用来给查询结果集编号，

OVER是一个表达式，它的作用是定义一个作用域（或者可以说是结果集），OVER前面的函数只对OVER定义的结果集起作用，

PARTITION BY用来给结果集分组， 和group by一样。

ORDER BY对分组后子组按某列排序。

最后用条件WHERE RN=2得到买个分组后的每个小组的前两行。

除了这个函数，我们可以扩展一下，了解下其他常用函数：

rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。

相比之下row_number是没有重复值的，每组内部排序后的顺序编号（组内连续的唯一的）。

FIRST_VALUE，用来求OVER 定义集合的最小值

LAST_VALUE，用来求OVER 定义集合的最大值。值得注意的是这两个函数有个参数，‘IGNORE NULLS‘ 或 ‘RESPECT NULLS‘，用来忽略NULL值和考虑NULL值

OLAP所有其他函数：

ROW_NUMBER  
RANK  
DENSE_RANK  
FIRST_VALUE  
LAST_VALUE  
LAG  
LEAD  
COUNT  
MIN  
MAX  
AVG  
SUM 
ROW_NUMBER
RANK
DENSE_RANK
FIRST_VALUE
LAST_VALUE
LAG
LEAD
COUNT
MIN
MAX
AVG
SUM

浅谈javascript中的异步处理

在JavaScript的世界中，所有代码都是单线程执行的由于这个“缺陷”，导致JavaScript的所有网络操作，浏览器事件，都必须是异步执行。异步执行可以用回调函数实现异步操作会在将来的某个时间点触发一个函数调用主流的异步处... 查看详情

浅谈javascript词法分析步骤

转载来自:https://www.cnblogs.com/zingp/p/6102561.htmlJavaScript代码运行前有一个类似编译的过程即词法分析，词法分析主要有三个步骤：分析参数再分析变量的声明分析函数声明具体步骤如下：函数在运行的瞬间，生成一个... 查看详情

浅谈sass与less区别优缺点

Sass是一种动态样式语言，Sass语法的缩排语法，比Css比多出很多功能，如变量，嵌套，运算，继承，颜色处理，函数等，易于阅读。Cass的安装需要安装Ruby环境，是服务器端处理的，Less是需要引入Less.js来处理代码输出css到浏览器... 查看详情

浅谈js中关于事件处理函数执行顺序的问题

  Js给dom元素绑定事件的处理函数总的来说有两种方式：在html文档中绑定，在js代码中绑定。然而，并不推荐在html标签上绑定事件。在js代码中也可以分两种方式绑定事件：　　1：通过dom元素的onclick等属性，直接绑定；... 查看详情

『这是一篇干货blog』

...过来。各文章版权归原作者所有，仅做搬运工作，侵删。浅谈C++IO优化——读优输优方法集锦浅谈斜率优化思维导图好助手——开心食用XmindTypora---一款简洁的Markdown编辑器在线生成树图csacademy在线平面直角坐标系和函数解析desmos... 查看详情

什么是在线交易处理或在线分析处理中的“在线”

】什么是在线交易处理或在线分析处理中的“在线”【英文标题】：Whatisthe"Online"inOnlineTransactionProcessingorOnlineAnalyticalProcessing【发布时间】：2017-05-2607:45:28【问题描述】：在网上我可以找到很多关于OLAP和OLTP的详细信息和... 查看详情

多元线性回归分析浅谈

 　　回归分析方法说白了就是处理多个变量相互依赖关系的一种数理统计方法(之前并没学过数理统计，恶补了一下，挺爽的~)。这篇随笔中主要运用了线性代数和数理统计知识，欢迎各方大佬指正,错误之处，不胜感激。 ... 查看详情

浅谈javascript的事件（事件处理程序）

　　事件就是用户或者浏览器自身执行的某种动作。诸如click、load和mouseover，都是事件的名字。而响应某个事件的函数就叫事件处理程序。事件处理程序的名字以“on”开头，比如click事件的事件处理程序是onclick。为事件指定事... 查看详情

浅谈泰勒展开

我们学习泰勒展开，本质上就是为了在某个点附近，用多项式函数取近似其他函数。可能有些童鞋就要问了，既然有一个函数了，为什么还需要用多项式函数取进行近似，理由就是多项式函数具有非常多优良的性质。比如说，多... 查看详情

hive基础(代码片段)

Hive基础1、介绍Hive是OLAP(onlineanalyzeprocess，在线分析处理)。通常称为数据仓库，简称数仓。内置很多分析函数，可进行海量数据的在线分析处理。hive构建在hadoop之上，使用hdfs作为进行存储，计算过程采用的是Mapreduce完成，本质... 查看详情

第55课：在线广告点击流处理代码的分析和实现

重点内容 查看详情

浅谈javascript的事件（事件委托）

　　事件处理程序为Web程序提供了系统交互，但是如果页面中的事件处理程序太多，则会影响页面的性能。每个函数都是对象，都会占用内存，内存中对象越多，性能越差。需要事先为DOM对象指定事件处理程序，导致访问DOM的次... 查看详情

浅谈getaddrinfo函数的超时处理机制

在sockproxy上发现，getaddrinfo解析域名相比ping对域名的解析，慢很多。我觉得ping用了gethostbyname解析域名。问题变为getaddrinfo解析域名，是否比gethostbyname慢。写测试程序，分别用getaddrinfo和gethostbyname解析，发现getaddrinfo确实慢。strac... 查看详情

浅谈awk命令

简介awk是一个强大的文本分析工具，相对于grep、sed命令，awk在其对数据分析并生成报告时，显得尤为强大。简单来说awk就是把文件逐行的读入，然后将每行切片，再对切开的部分进行处理。awk有3个不同版本:awk、nawk和gawk，未作... 查看详情

hive基础1(代码片段)

Hive基础1、介绍Hive是OLAP(onlineanalyzeprocess，在线分析处理)。通常称为数据仓库，简称数仓。内置很多分析函数，可进行海量数据的在线分析处理。hive构建在hadoop之上，使用hdfs作为进行存储，计算过程采用的是Mapreduce完成，本质... 查看详情

数据分析浅谈

1.数据分析基础知识 2.数据分析之numpy模块  查看详情

DB2 UDF 异常处理

】DB2UDF异常处理【英文标题】：DB2UDFExceptionHandling【发布时间】：2017-12-1413:22:06【问题描述】：我正在努力处理DB2中用户定义函数(UDF)的异常处理。我的问题的出发点是，我想选择具有特定格式的varchar-timestamps的表并将它们转换... 查看详情

浅谈文本分析分词及关系图

参考技术A在文本分析中，我们需要对其文本进行分词，并对这些分词统计分析，基于python，jieba是很受欢迎的一种分词库，而后对分词之间的关系，当然PythonMatplotlib基于networkx画关系网络图也是可以的，但是这里我们将借助Gephi... 查看详情