1 / 55

分析 ！ SQL 的分析能力超乎您的想象

分析 ！ SQL 的分析能力超乎您的想象. Mick Xu Oracle 大学首席讲师. 以下内容旨在概述产品的总体发展方向。 该内容仅供参考，不可纳入任何合同。 其内容不构成提供任何材料、代码或功能的承诺，并且不应该作为制定购买决策的依据。 此处所述有关 Oracle 产品的任何特性或功能的开发、发布以及相应的日程安排均由 Oracle 自行决定。. SQL 发展历程 数据库中的分析 SQL. 模式匹配 前 N 个子句 水平视图，APPLY 标识列 列默认属性 第三代数据挖掘技术. 第二代数据挖掘技术 SQL Pivot 递归 WITH

gay-moses
Download Presentation

分析 ！ SQL 的分析能力超乎您的想象

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 分析！ SQL 的分析能力超乎您的想象 Mick XuOracle 大学首席讲师

  2. 以下内容旨在概述产品的总体发展方向。 该内容仅供参考，不可纳入任何合同。 其内容不构成提供任何材料、代码或功能的承诺，并且不应该作为制定购买决策的依据。 此处所述有关 Oracle 产品的任何特性或功能的开发、发布以及相应的日程安排均由 Oracle 自行决定。

  3. SQL 发展历程数据库中的分析 SQL • 模式匹配 • 前 N 个子句 • 水平视图，APPLY • 标识列 • 列默认属性 • 第三代数据挖掘技术 • 第二代数据挖掘技术 • SQL Pivot • 递归 WITH • ListAgg，第 N 个值窗口 • 统计函数 • Sql 模型子句 • 分区外连接 • 第一代数据挖掘技术 • 增强型窗口函数（百分比等） • Rollup、groupingsets、cube • 窗口函数介绍 2012 1998 2001 2002 2004 2005 2007 2009

  4. SQL 模式匹配

  5. 行序列中的模式识别 挑战 • “找出满足以下条件的人，从国家 X 飞往国家 Y，并呆上 2 天，然后飞往国家 Z，又呆上 30 天，并与 A 进行了联系，然后提了 1 万美元” • SQL 中当前的模式识别比较困难 • 使用多个自连接（不适用于 *） • T1.person = T2.person AND T1.country=‘X’ AND T2.country=‘Y’ & T2.time BETWEEN T1.time and T1.time+2…. • 对于 * 使用递归查询（WITH 子句，CONNECT BY） • 使用窗口函数（可能要用到多个查询块）

  6. 行序列中的模式识别 “找出在 1 分钟时间间隔内一个或多个事件 A 之后出现一个事件 B，然后出现一个或多个事件 C 的情形” 提供原生 SQL 语言结构 与熟知的正则表达式声明 (PERL) 保持一致 跨行应用表达式 快速达到 ANSI SQL 标准 目标 > 1 min A+ B C — perl

  7. 行序列中的模式识别 “SQL 模式匹配” — 概念 • 在事件序列中使用 SQL 识别模式 • 序列是一连串的行 • 事件对应流中的一个行 • 新的 SQL 结构 MATCH_RECOGNIZE • 逻辑划分和排列数据 • ORDER BY 命令（可选 PARTITION BY） • 使用正则表达式和变量定义模式 • 正则表达式与行序列相匹配 • 使用行和聚合上的条件定义每个模式变量

  8. SQL 模式匹配 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) 9 13 1 天数 19

  9. SQL 模式匹配 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) 9 13 1 天数 19

  10. SQL 模式匹配 X 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) 天数

  11. SQL 模式匹配 X Y 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) Y AS (price > PREV(price)) 天数

  12. SQL 模式匹配 X Y W Z 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) Y AS (price > PREV(price)) W AS (price < PREV(price)) Z AS (price > PREV(price)) 天数

  13. SQL 模式匹配 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price))) 9 13 1 天数 19

  14. SQL 模式匹配 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price) AND z.time - FIRST(x.time) <= 7 )) 9 13 1 天数 19

  15. 可以参考前面的变量 SQL 模式匹配 X Z 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格涨幅 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price) AND z.time - FIRST(x.time) <= 7 )) 9 13 1 天数 19

  16. 平均股价： $52.00 SQL 模式匹配 股价 找出双底 (W) 模式和报告： 模式的起始日期和结束日期 第二梯度的平均价格 修改搜索以查找持续时间小于一周的模式 例如： 找出双底 (W) SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z, AVG(z.price) AS avg_price ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price) AND z.time - FIRST(x.time) <= 7 )) 9 13 1 天数 19

  17. SQL 模式匹配 语法 <table_expression> := <table_expression> MATCH_RECOGNIZE ( [ PARTITION BY <cols> ] [ ORDER BY <cols> ] [ MEASURES <cols> ] [ ONE ROW PER MATCH | ALL ROWS PER MATCH ] [ SKIP_TO_option ] PATTERN ( <row pattern> ) [ SUBSET <subset list> ] DEFINE <definition list> )

  18. SQL 模式匹配 “声明式”模式匹配 • 在有序数据分区内匹配 MATCH_RECOGNIZE (PARTITION BY stock_name ORDER BY time MEASURES … • 使用 Perl 正则表达式框架（条款是行上的条件） PATTERN (X+ Y+ W+ Z+) • 利用行上的布尔条件定义匹配 DEFINE X AS (price > 15)…

  19. SQL 模式匹配 “声明式”模式匹配（续） • 命名并参考条件中先前的变量（即行） • DEFINE X AS (price < PREV(price,1)),Y AS (price > PREV(price,1)),W AS (price < PREV(price,1)),Z AS (price > PREV(price,1) AND Z.price > X.price) • 新聚合： FIRST, LAST • DEFINE X AS (price < PREV(price)),Y AS (price > PREV(price)),W AS (price < PREV(price)),Z AS (price > PREV(price) AND Z.time < FIRST(X.time)+10)

  20. SQL 模式匹配 “声明式”模式匹配（续） • 在当前定义的变量上的条件中运行聚合： • DEFINE X AS (price < PREV(price) AND AVG(num_of_shares) < 10 ),Y AS (price > PREV(price) AND count(Y.price) < 10 ), W AS (price < PREV(price)),Z AS (price > PREV(price) AND Z.price > Y.price ) • 仅在先前定义的变量上的条件中最终化聚合 • DEFINE X AS (price < PREV(price)),Y AS (price > PREV(price)), W AS (price < PREV(price) AND count(Y.price) > 10 ) ,Z AS (price > PREV(price) AND Z.price > LAST(Y.price) )

  21. SQL 模式匹配 “声明式”模式匹配（续） • 匹配后 SKIP 选项： • SKIP PAST LAST ROW • SKIP TO NEXT ROW • SKIP TO <VARIABLE> • SKIP TO FIRST(<VARIABLE>) • SKIP TO LAST (<VARIABLE>) • 返回哪些行 • ONE ROW PER MATCH • ALL ROWS PER MATCH • ALL ROWS PER MATCH WITH UNMATCHED ROWS

  22. SQL 模式匹配 构建正则表达式 • 串联： 无运算符 • 限定符： • * 0 个或更多的匹配 • + 1 个或更多的匹配 • ? 0 或 1 个匹配 • {n} n 个确切匹配 • {n,} n 个或更多的匹配 • {n, m} n 到 m 个（含m）匹配 • {, m} 0 到 m 个（含m）匹配 • 勉强模式的限定符 (Reluctant quantifier) — 外加？

  23. SQL 模式匹配 构建正则表达式 • 交替： | • A | B • 分组： () • (A | B)+ • 排列： Permute() — 交替所有排列 • PERMUTE (A B C) -> A B C | A C B | B A C | B C A | C A B | C B A • ^: 表示分区的开头 • $: $: 表示分区的结尾

  24. SQL 模式匹配 “声明式”模式匹配 • 可以子集化变量名称 • SELECT first_x, avg_xyFROM ticker MATCH_RECOGNIZE (PARTITION BY name ORDER BY time ONE ROW PER MATCH MEASURES FIRST(x.time)first_x, AVG(T.price) avg_xyPATTERN (X+ Y+ W+ Z+) SUBSET T = (X, Y)DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price) AND Z.price > T.price ) );

  25. SQL 模式匹配 SELECT name, rev_time, time, clas FROM event_log MATCH_RECOGNIZE (PARTITION BY name ORDER BY time PATTERN (X Y* Z) MEASURES x.time rev_time, classifier() clas ALL ROWS PER MATCH DEFINE X AS (event = ‘revoke’), Y AS (event NOT IN (‘login’, ‘grant’)), Z AS (event = ‘login’ ) ) 在撤销用户的优先权后检测所有登录事件。 针对第一次不当登录尝试（事件）生成一行 ALL ROWS PER MATCH 选项

  26. SQL 模式匹配 SELECT name, rev_time, first_log FROM event_log MATCH_RECOGNIZE (PARTITION BY name ORDER BY time PATTERN (X Y* Z Z W+) MEASURES FIRST(x.time) first_log ONE ROW PER MATCH DEFINE X AS (event = ‘revoke’), Y AS (event NOT IN (‘login’, ‘grant’)), Z AS (event = ‘login’), W AS (event = ‘login’ AND W.time - FIRST(z.time) <= 60) ) 在撤销优先权之后每 3 次或更多连续登录尝试（事件）检测一次 登录尝试必须均在 1 分钟内发生 ONE ROW PER MATCH 选项

  27. SQL 模式匹配用例示例

  28. SQL 模式匹配 用户日志的会话处理示例 • 将一个会话定义为一个或多个具有相同分区键（其中时间戳间距小于指定阈值）的事件序列 • “用户日志分析”示例 • 分区键： 用户ID，时间戳间差距： 10（秒） • 检测会话 • 为每个会话分配一个分区内（按用户）代理Session_ID • 使用相应的Session_ID为每个输入元组添加注释

  29. SQL 模式匹配 用户日志的会话处理示例： ALL ROWS PER MATCH SELECT time, user_id, session_id FROM Events MATCH_RECOGNIZE (PARTITION BY User_ID ORDER BY time MEASURES match_number() as session_id ALL ROWS PER MATCH PATTERN (b s*) DEFINE s as (s.time - prev(s.time) <= 10) );

  30. SQL 模式匹配 用户日志的会话处理示例 每个用户的会话数 识别会话

  31. SQL 模式匹配 会话处理示例 – 会话化数据聚合 • 原始会话处理只是分析的基础 • 逻辑识别相关事件并对事件进行分组的命令 • 第一次数据洞察的聚合 • 单个会话内发生了多少“事件”？ • 单个会话的总时长是多少？

  32. SQL 模式匹配 会话处理示例 – 聚合： ONE ROW PER MATCH SELECT user_id, session_id, start_time, no_of_events, duration FROM Events MATCH_RECOGNIZE ( PARTITION BY User_ID ORDER BY time ONE ROW PER MATCH MEASURES match_number() session_id, count(*) as no_of_events, first(time) start_time, last(time) - first(time) duration PATTERN (b s*) DEFINE s as (s.time - prev(time) <= 10) ) ORDER BY user_id, session_id;

  33. SQL 模式匹配 会话处理示例 – 会话化数据聚合

  34. SQL 模式匹配 呼叫详情记录分析示例 • 情景： • 可以中断（或丢弃）相同的呼叫。 • 呼叫者会在中断的几秒钟时间内呼叫被呼叫者。 仍然算作一次会话 • 需要知道我们中断呼叫的频率以及有效呼叫时长 • 准备会话化现象的特点是 • Start_Time，End_Time • Caller_ID, Callee_ID

  35. SQL 模式匹配 使用 SQL 模式匹配的呼叫详情记录分析示例 SELECT Caller, Callee, Start_Time, Effective_Call_Duration, (End_Time - Start_Time) - Effective_Call_Duration AS Total_Interruption_Duration, No_Of_Restarts, Session_ID FROM call_details MATCH_RECOGNIZE ( PARTITION BY Caller, Callee ORDER BY Start_Time MEASURES A.Start_Time AS Start_Time, B.End_Time AS End_Time, SUM(B.End_Time – A.Start_Time) as Effective_Call_Duration, COUNT(B.*) as No_Of_Restarts, MATCH_NUMBER() as Session_ID PATTERN (A B*) DEFINE B as B.Start_Time - prev(B.end_Time) < 60) ;

  36. SQL 模式匹配 Oracle Database 12c 之前的呼叫详情记录分析示例 With Sessionized_Call_Details as (select Caller, Callee, Start_Time, End_Time, Sum(case when Inter_Call_Intrvl < 60 then 0 else 1 end) over(partition by Caller, Callee order by Start_Time) Session_ID from (select Caller, Callee, Start_Time, End_Time, (Start_Time - Lag(End_Time) over(partition by Caller, Callee order by Start_Time)) Inter_Call_Intrvl from Call_Details)), Inter_Subcall_Intrvls as (select Caller, Callee, Start_Time, End_Time, Start_Time - Lag(End_Time) over(partition by Caller, Callee, Session_ID order by Start_Time) Inter_Subcall_Intrvl, Session_ID from Sessionized_Call_Details) Select Caller, Callee, Min(Start_Time) Start_Time, Sum(End_Time - Start_Time) Effective_Call_Duration, Nvl(Sum(Inter_Subcall_Intrvl), 0) Total_Interuption_Duration, (Count(*) - 1) No_Of_Restarts, Session_ID from Inter_Subcall_Intrvls group by Caller, Callee, Session_ID;

  37. SQL 模式匹配 可疑转账示例 • 检测一个账户的可疑转账模式 在 30 天内有三笔或以上小额 (<2K) 转账 最后一笔小额转账之后的 10 天内出现大额转账 (>=1M) • 报告账户、第一笔小额转账的日期和最后一笔大额转账的日期 30 天内三笔小额转账 最后一笔小额转账的 10 天内出现大额转账

  38. 彼此 30 天内 三笔或以上小额转账 最后一笔小额转账 10 天内 随后出现一笔大额转账 SQL 模式匹配 可疑转账示例 SELECT userid, first_t, last_t, amount FROM (SELECT * FROM event_log WHERE event = 'transfer') MATCH_RECOGNIZE ( PARTITION BY userid ORDER BY time MEASURES FIRST(x.time) first_t, y.time last_t, y.amount amount PATTERN ( x{3,} Y ) DEFINE X as (event='transfer' AND amount < 2000), Y as (event='transfer' AND amount >= 1000000 AND last(X.time) - first(X.time) < 30 AND Y.time - last(X.time) < 10 ))

  39. SQL 模式匹配 可疑转账示例 — 改进版 • 检测账户间的可疑转账模式 • 在 30 天内有三笔或以上小额 (<2K) 转账 • 转账至不同账户（小额转账总金额 (20K)） • 最后一笔小额转账之后的 10 天内出现大额转账 (>=1M) • 报告账户、第一笔小额转账的日期和最后一笔大额转账的日期 30 天内三笔小额转账 转至不同账户并且总金额 < 20K 最后一笔小额转账的 10 天内出现大额转账

  40. 第一笔小额转账 接下来的两笔或以上转至不同账户的小额转账 小额转账总额小于 20000 SQL 模式匹配 可疑转账示例 — 改进版 SELECT userid, first_t, last_t, amount FROM (SELECT * FROM event_log WHERE event = 'transfer') MATCH_RECOGNIZE ( PARTITION BY userid ORDER BY time MEASURES z.time first_t, y.time last_t, y.amount amount PATTERN ( z x{2,} y ) DEFINE z as (event='transfer' and amount < 2000), x as (event='transfer' and amount < 2000 AND prev(x.transfer_to) <> x.transfer_to ), y as (event='transfer' and amount >= 1000000 AND last(x.time) – z.time < 30 AND y.time – last(x.time) < 10 AND SUM(x.amount) + z.amount < 20000 ) )

  41. 原生前 N 个支持

  42. 针对前 N 个查询的原生支持 “我的企业中最会赚钱的 5 个人是谁？” SELECT empno, ename, deptnoFROM emp ORDER BY sal, comm FETCH FIRST 5 ROWS; 本地识别 SQL 中的前 N 个 显著简化代码开发 ANSI SQL:2008 与 SELECT empno, ename, deptnoFROM (SELECT empno, ename, deptno, sal, comm, row_number() OVER (ORDER BY sal,comm) rn FROM emp )WHERE rn <=5ORDER BY sal, comm;

  43. 针对前 N 个查询的原生支持 新的 offset 和 fetch_first 子句 ANSI 2008/2011 兼容一些附加扩展 指定偏移和返回行的数量或百分比 使用与最后一行相同的排序键返回其他行的规定（WITH TIES 选项） 语法： OFFSET <offset> [ROW | ROWS] FETCH [FIRST | NEXT] [<rowcount> | <percent> PERCENT] [ROW | ROWS] [ONLY | WITH TIES]

  44. 针对前 N 个查询的原生支持 内部处理 找出薪资最低的 5% 员工 SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 percent ROWS ONLY;

  45. 针对前 N 个查询的原生支持 内部处理（续） 找出薪资最低的 5% 员工 SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 percent ROWS ONLY; • 在内部，使用窗口函数将此查询转换为同等查询 • SELECT employee_id, last_name, salary • FROM (SELECT employee_id, last_name, salary, • row_number() over (order by salary) rn, • count(*) over () total • FROM employee) • WHERE rn <= CEIL(total * 5/100); • 其他前 N 优化： • SELECT 列表可能包含占用资源比较多的 PL/SQL 函数或成本很高的表达式 • 对 SELECT 列表表达式的评估限于最终结果集的行

  46. SQL 发展历程数据库中的分析 SQL • 模式匹配 • 前 N 个子句 • 水平视图，APPLY • 标识列 • 列默认属性 • 第三代数据挖掘技术 • 第二代数据挖掘技术 • SQL Pivot • 递归 WITH • ListAgg，第 N 个值窗口 • 统计函数 • Sql 模型子句 • 分区外连接 • 第一代数据挖掘技术 • 增强型窗口函数（百分比等） • Rollup、groupingsets、cube • 窗口函数介绍 2012 1998 2001 2002 2004 2005 2007 2009

  47. “随着 Oracle 不断提供更多的工具和应用程序，Oracle 大学始终坚持为个人学习者提供培训服务。” 2013 IT 培训领导者根据行业分析公司 IDC 的报告 “在被评估的所有供应商中，Oracle 最擅长在技术部署生命周期的各个环节展现培训的价值。” “[对课程开发的]这种分散化控制使其提供的培训能够最大程度地融入对最终用户来说最为有用的内容要素。”

  48. 世界最大的培训机构之一 全球培训教室： 200+，遍布 89 个国家 培训课程： 2000+ 支持的语言： 28 每周开课: 600+ 课程，90+ 虚拟课程 点播课程： 100 个，并且在不断增加 每年培训的学员：400,000+ 全球教育合作伙伴：500+ Oracle 认证专家： 160 万以上

  49. 涵盖整个体系的培训和认证 基于角色的培训 管理员 开发人员 业务用户 架构师 实施者 在您需要时以您期望的方法提供培训 课堂 实时虚拟课堂 点播培训 私人培训 经过行业公认认证的验证 Oracle 认证专家 Oracle 认证专项技术专家 Oracle 认证专员 Oracle 认证大师

More Related