Vertica高级分析.pdf

《Vertica高级分析.pdf》由会员分享，可在线阅读，更多相关《Vertica高级分析.pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 HP Confidential SQL Analytics+-高级分析 部分特性 Time series gap filing and interpolation（时序填补插值）Event-based window functions and sessionization（事件窗口功能及会话流分析）Pattern matching（模式匹配）Event series join（事件序列关联）Statistical functions（统计分析）Geospatial functions（地理分析）优势 高性能(保持数据靠近CPU)低成本(基于行业标准硬件搭建)易用(自动+高可用)适用场景 股

2、票价格分析，CDR/VOD数据分析 点击流会话流分析，反欺诈 客户行为模式分析、市场营销 定位分析、位置距离测算 Monte Carlo模拟法 社交分析 传感器数据分析 智能电网 2 HP Confidential 范例：时序填插值 以两秒为间隔取股票价格：tsts symbolsymbol bidbid 3:00:00 PM FOO 10.0 3:00:05 PM FOO 10.5 tsts symbolsymbol bidbid 3:00:00 PM FOO 10.0 3:00:02 PM FOO 10.010.0 3:00:04 PM FOO 10.010.0 SELECT TIME_S

3、LICE(ts,2 second)ts,symbol FIRST_VALUE(bid)OVER (PARTITION BY symbol,TIME_SLICE(ts,2,second)ORDER BY ts)bid FROM Tickstore;3 HP Confidential 范例：时序聚合 计算三秒间隔的最小股票价格：tsts symbolsymbol bidbid 3:00:00 PM FOO 10.0 3:00:01 PM FOO 10.1 3:00:04 PM FOO 10.3 3:00:05 PM FOO 10.5 tsts symbolsymbol bidbid 3:00:00

4、 PM FOO 10.0 3:00:03 PM FOO 10.1 SELECT slice_time,ts_min(bid,const)bid FROM Tickstore TIMESERIES slice_time AS 3 seconds OVER(PARTITION BY symbol ORDER BY ts);4 HP Confidential 范例：时序关联 不同来源的股票交易价格对比：SELECT*FROM hTicks h FULL OUTER JOIN aTicks a ON(h.time INTERPOLATE PREVIOUS VALUE a.time);5 HP Conf

5、idential 范例：点击流（Clickstreams）分析 user_iduser_id timestamptimestamp URLURL session_idsession_id U0 15:00:00 http:/ 0 U0 15:00:25 http:/ 0 U0 15:00:45 http:/ U0 15:01:45 http:/ SELECT user_id,timestamp,URL CONDITIONAL_TRUE_EVENT(timestamp LAG(timestamp)30 seconds)OVER(PARTITION BY user_id ORDER BY time

6、stamp)AS session_id FROM clickstream;易于获取跨站点、跨终端的点击流 简单、快速、可扩展 6 HP Confidential 范例：模式匹配与网站内容优化 4 种类型事件:主页,注册,目标,以及其他 目的:分析用户如何到达目标界面。分析案例：Q Q:对以上类型网页进行PMD(模式匹配发现)查询 A A:非常用模式:Home page-Register-Goal Q Q:另一PMD查询：Home Page-?-Register-Goal A A:80%的时间是通过Anna所写的白皮书所在网页！行动:让Anna给我们写更多的白皮书 SELECTSELECT ui

7、d,sid,ts,refurl,pageurl,action,event_name(),pattern_id(),match_id()FROMFROM clickstream_log MATCHMATCH(PARTITION BY uid,sid ORDER BY ts DEFINEDEFINE EntryEntry AS RefURL NOT ILIKE%some_%AND PageURL ILIKE%some_%,OnsiteOnsite AS PageURL ILIKE%some_%AND Action=V,PurchasePurchase AS PageURL ILIKE%some_%

8、AND Action=P PATTERNPATTERN P AS(Entry Onsite*PurchaseEntry Onsite*Purchase)RESULTS ALL ROWS);7 HP Confidential 范例：查找附近的酒吧 select name,address from POI_TABLEselect name,address from POI_TABLE wherewhere dwithindwithin(lat,(lat,lonlon,42.5439,42.5439,-71.2791,2.0)71.2791,2.0)and type=bar;and type=bar;Here is Mickees!