Categories
日常应用

无知的比较:R和Teradata SQL(附赠TD经验几枚)

今年夏天的时候,刚刚开始被SQL虐,写了一篇很无知且更多是吐槽意味的blog post: 关于R的若干SQL等价问题。当时被若干朋友批评,我还浑然不觉个中精要。现在用Teradata也有半年多的时间了,越来越习惯了SQL的表述方式,也越来越体会到Teradata作为一个强大的数据仓库系统,是有多么的伟大...这感觉,就是只玩过几个G数据的乡下人进城,猛然看到各路英雄都是动辄几个T的数据,只能暂时以原来落后的思维方式、勉强挥舞着新型工具...好在个性不是特别愚钝,终究还是可以慢慢地领悟到T级数据的奥妙之处,终究用着新武器也越来越顺手了。

这一段时间,也充分证明了我是master in economics而绝对不是 in cs。数据库系统的原理终究学的不深——我哪儿知道MySQL的SQL和Teradata的SQL差了那么多呀...后来慢慢的去听同事传授TD使用经验,慢慢的去看老板传过来的代码,慢慢的一次次处理掉 no more spool space的错误和一次次接到SQL语句效率低强制退出的警告信之后,才逐渐地越来越了解TD的原理和脾气。工欲善其事,必先利其器,这些都是沉重的学费。

所以各位如果没有看过那篇「无知者无畏」状post的,就不要看了。直接接受我诚挚的道歉然后看下文吧。Teradata下简称TD。绝非专业知识,只是个人有限的了解,不对之处请及时批评。

有次跟同事聊,问他们为什么不在本机上装个TD测试用...然后被狠狠鄙视了一番——TD没有单机版!天生就是架在云上的。这东西还真是个原生的分布式数据仓库。

TD和oracle的关系也比较简单:一个是数据仓库,一个是数据库,功能设计什么的压根就不一样。这么说吧,oracle支撑的是ebay的网站运行,所以必然涉及大量的查询、插入、删除等请求。更麻烦的是,以ebay的访问量,这些请求都是同时过来的,这就要求系统并发性要好一点(专业人士可以绕道了,我只是浅薄的知道一点东西...)。体验过12306买火车票排队的大家,想必都知道这个系统并发起来的厉害。ebay若是也来个排队,消费者还不疯掉...

为了应对这样的任务,oracle的数据库设计自然是要按那「三大范式」来。这个就不多说了,再说就暴露了...

TD则是把oracle的数据定期地导出来存着,所以除了简单的复制数据之外,还要对数据进行一定程度的清理和整理,并不完全是最最原始的数据。然后到了食物链上端数据分析师手里,面对的数据很多都是已经弄的很整齐的了。说是食物链上端,只是因为这大概是分工中需要用到原始数据的最后一拨人,且这拨人用到的最多的就是查询(甚至是整表查询)和计算,所以我们写SQL的时候更多是考虑到这些需求,利用TD在这方面的性能优势——我已经很少在SAS或者R里面进行数据整理的工作了,性能跟TD完全不是一个量级的。

下面是TD使用的若干经验,不过这东西只有自己碰壁了才知道个中真滋味,我就是缩短一下解决问题的进程,不用太折腾到处搜来搜去。

No more spool space。当你的SQL没有语法错误,那么最常见的运行不下去的情况就是 no more spool space了,这大概是每个用TD的不管新鸟老鸟都会经历的痛苦历程。这个错误就像R里面报"cannot allocate a vector of size ***",或者你玩游戏正high的时候系统告诉你内存不足。解决的思路就是"空间换时间",就是看你具体怎么换了。

1. 多表join查询的时候,就要看这些表是怎么merge的——TD会去算是一大一小join,还是两个大表join。前者TD会复制小表到每个大表的"节点"上(大表肯定要分块存起来嘛),所以可以事先加collect statistics on *** column ***。后者就要费点脑子了,争取两个表的排序(PI)一致,这样TD join的时候就不需要对两个表都重新排列了是不是(merge join)?每一次重排都会占掉大量的临时空间呢。再者,查询结果储存到另外的永久或者临时表里面,就要注意primary index(简称PI)的选择,不要让TD再把查询结果重排...

2. 除了看primary index,有时候还要去注意partition by。有些已经建好的超级长的表需要去看是怎么真正"分块"存储的。对于partition by 的字段设定一个where条件,会让TD很快的知道你要查询和join的是哪些部分,大大缩短范围。一般说来,最常见的partition by就是时间了,缩短一个时间范围也不失为良策嘛。

3. 擅用cast()可以避免很多跟数据类型有关的错误,这个就不赘述了。

4. No space on ***说明没有永久表的存储空间了,这个就得去删过于古老的表和去要新的空间了。

5. 每段SQL不要太长,join不宜太多。熟悉TD的脾气之后,就张弛有度了,擅用临时表。

6. 多用group by少用distinct。

7. 最后终极野蛮办法,如果实在是没法两个大表join又没有partition by的话...手动按PI拆其中某个表吧。

----例行碎碎念----
那些在LinkedIn上endorse我R的朋友们,我真心感觉承受不起呀!至今依旧觉得我的R很烂,代码只停留在"可运行"的水平,效率大都很糟糕,基本就是折磨CPU的...哎,非科班出身终究是有莫大的差距呀。

Categories
Uncategorized 我的生活状态

逻辑问题

最近真心觉得自己的逻辑训练有问题了...SQL总是写错...脑子一点都不清醒。

@Pompei@Pompei

不觉得自己是个很粗心的人啊,但事实证明犯错的时候可以一犯一连串...

貌似每年11月我总得惹出一些祸来。06年11月,删掉了某网站的数据库...10年11月,西班牙语考的一塌糊涂...11年11月...12年11月,连续的在一件工作上犯错犯错再犯错。郁闷啊郁闷。

真想把自己丢在冬天的水里好好清醒一下,或者干脆回娘胎重新出生一回,这个脑子完全处于不灵光的状态。还最近连续做梦,各种寝食不安,各种乱七八糟,各种...

谁可以告诉我应该怎么解救自己...我是不是应该做点什么练一练自己的耐心和细心了?好讨厌自己这样子的状态啊,好讨厌啊。最近来上海欢迎教育我一下,煞煞莫名其妙的浮躁。好失败的感觉...连最最基本的细致都做不到,好失败...这样子的状态,还能做好什么呢?好讨厌好高骛远的心理啊...

Categories
日常应用

关于R的若干SQL等价问题

以前总是觉得不同的计算机语言之间只是语法问题,思路其实还是差不多的--后来才知道不尽然如此。比如用惯了R作分析,切换到其他语言顿时觉得效率降低了好多,尤其是很多一行命令在R里面就可以搞定的时候-思维习惯了一定程度的跳跃,常用的操作(尤其是数据整理!)封装成函数之后工作效率那叫一个倍增啊!结合knitr,原来的时候生成定期报告的效率极其之高,基本属于10倍以上的时间节省。

现在公司的数据平台是teradata,典型的SQL结构,各种join。在这么大的数据量下,不可能直接取数据到本机来分析,只能借助SQL进行一定程度的降维。而后剩下的收尾分析工作,可以由R完成。至于两者之间分工的界限在哪里,我还在摸索一个效率最高的平衡点。不得不吐槽一下,SQL的逻辑思维方式真心没效率,完全是为了数据库性能和空间单位平衡而设计的,做分析的时候就额外的痛苦许多——90%以上的时间都用来琢磨怎么鼓捣出来自己需要的数据格式,全在数据清理上了!

抱怨完毕,除了祈祷hadoopR和oracle连接起来彻底摆脱SQL阴影之外,暂时只能跟SQL硬战。下面说说最近常见的几个相同功能在R和SQL里面分别的实现方法。

1. 生成新变量

多见的明确的任务啊。如果是数值型,比如变量D是其他三个变量ABC的显性函数f(A,B,C),最简单诸如D=A+B+C,在R和SQL里面都是直接写。

  • R:
    my_dataframe$D <- my_dataframe$A+ my_dataframe$B + my_dataframe$C

    (当然还有更elegant的with()函数)

  • SQL(以select为例):
    SELECT A,B,C, A+B+C D from my_datatable;

    然后如果f()稍稍复杂的话,R的可以定义函数的优势就明显了,SQL只有macro模式显然不足够灵活强大。如,

R:

generate_D <- function(VarA=A, VarB=B, VarC=C) {
VarD <- VarA * VarB *(VarB %*% VarC)
return( VarD)}
my_dataframe$D <- generate_D(my_dataframe$A, my_dataframe$B, my_dataframe$C)

注:%*%代表向量内积或矩阵乘法,这里为一个数字。理论上这里可以调用任何R中函数。

如果新变量是字符型,R的优势就更明显了,字符串操作函数例如substr()取字符串其中一段,paste()连接多个字符串,grep()和sub()查找替换类,自然比SQL灵活的多。还是那句话,只要能用函数写出来,R都可以方便地搞定。你问我拿SQL跟R比这个有意思么?明显SQL就不是为了这个功能专门设计的啊。好吧,常见的生成新变量的情况:有条件的生成新变量,比如年龄分组等,基本就是按照若干已知条件生成一个新的变量。这里,SQL的case when确实方便,比如年龄分为老中青三组:

SQL:

SELECT CASE WHEN AGE>50 THEN 'old'
WHEN AGE between 25 and 50 THEN 'mid'
ELSE 'young'
END AGE_GROUP
FROM my_datatable

而R中,我一直用一种最笨的办法-刚刚搜了一下发现其实我的办法还是挺好用的。

My_dataframe$AGE_GROUP <- 'young'
My_dataframe[My_dataframe$AGE > 50,]$AGE_GROUP <- 'mid'
My_dataframe[(My_dataframe$AGE >=25 )& (My_datafame$AGE<= 50),]$AGE_GROUP <- 'mid'

当然也可以用ifelse()或者transform的方法,我倒是觉得没有这种笨办法清晰简洁易读,易于回头看代码。ifelse那堆括号哦!没有高亮匹配会死人的。

这里边界值随意,不考虑直接除法取整的情况。两种分类时可以直接用逻辑型简化,一行出结果;另,数值型离散化转换为factor型其实可以简单的用一个函数cut()搞定..(多谢yihui一语道破天机)

2. 分组加总等数据整理统计

要知道在很多时候,什么都比不上基本的求和均值方差有用,偶尔来个计数最大最小值就不错了。SQL一个group by 就神马都搞定了,比如对每组顾客购买的图书本书去重、求和。
SQL:

SELECT sum(TA.quantity) quantity ,
TB.book_type
FROM Table_A TA
OUTER JOIN Table_B TB
ON TA.book_id = TB.book_id
GROUP BY book_type

 

SELECT user_group, SUM(book_quantity) quantity, count(distinct book_id) sold_book
FROM my_datatable
GROUP BY user_group

那么相对应的,在R中,我们的解决策略是万能的data.table()。
R:

book_stat <- data.table(my_dataframe)[,list(quantity=sum(book_quantity), sold_book = length(unique(book_id))), by="user_group]

也不麻烦对嘛~可是,R里面还是有可以调用多种函数的优势哦。嘻嘻。

3. 表的连接和数据混合

咳咳,thanks to 著名的三大范式,SQL语句永远逃不掉各种各样的连接,内外左右,inner join, outer join, left join, right join 写来写去有没有!R里面呢,类似于SAS,有个神奇的merge()函数。每次看到讲left join 的教程示例的时候都觉得真心罗嗦难懂,相比而言R的merge()函数简洁明了了许多有木有!

依旧,假设我们第一个表, 两个字段 book_id, book_quantity, 然后第二个表两个字段,book_id, book_type,包含的是书的分类信息。现在需要分类统计书的数量。

SQL:

这里用外链接,既如果图书在TB中没有分类信息,会自动归于NULL这一列。

用R呢,嘻嘻,很简单。

Book_stat <- merge(TableA, TableB, by="book_id", all.x=T, all.y=T)

这里其实可以简写all=TRUE (T 在R中等价于逻辑值TRUE),只是为了更清晰所以把x,y分开了。多明显啊,我就是要保留两个表中所有的观测对象,如果任意表缺失标记为NA即可。很简单的,merge()的参数和四大连接的关系就是:
INNER JOIN 等价于 merge(all=F)
LEFT JOIN 等价于 merge(all.x=T, all.y=F)
RIGHT JOIN 等价于 merge(all.x=F, all.y=T)
OUTER JOIN 等价于 merge(all=T)

嗯啊,反正对我来说,这个更好理解...

至于SQL的where和having条件,基本就是R中对于行的选择,不再赘述,参见新变量生成那里对于行的选择。TOP或者limit也可以通过head或者直接指定行序号n:m来搞定。其他的常见的就不多了吧...过去两周的时间,我基本就在用R的整理数据框架思路来实现SQL语句撰写的煎熬中度过,多少次烦的时候都想直接砸了显示器或者哀叹如果能导出到R里面该多好...磨合期啊。

注:我现在理解SQL架构还有一项主要的feature就是索引,哈希表是个很强大的东东。这东西某种程度上类似R中factor类型的数据,但是貌似水要深很多,为了提升性能值得继续好好研究。

注2:NOSQL架构拯救分析师啊

注3:数据整理绝对是最耗分析师时间的活,如果思路不清晰不知道想要进入分析模型的数据长什么样子,那就真的悲剧了,往往一天两天就是徒劳。这也是我的小册子新版第一章加的就是数据整理,血泪的教训啊。
另外一个耗时间的就是excel或者word中作图配文字,这个绝对需要knitr来拯救-亲,对于每个分类统计是很简单,但是对于每个分类都画图的话,您难道还准备告诉excel作循环?然后一张张复制粘贴到word里面?省省时间吧,knitr会save your life的,绝对是工欲善其事,必先利其器。分析不是也不应该是体力活哦。下周的上海R沙龙,一定要好好称赞一下knitr,相比于 reproducible research,它对于业界的意义就在于没有BI系统之前,自动写报告...轻量化高效工具!

注4:ipad果然不适合码代码...有typo或不满排版的,容我稍后电脑上修改。

注5:开始研究RHadoop,各种沦落伤不起啊。

Categories
事儿关经济

数据库规范与SQL应用[3rd week, July]

以前也说过,这个暑假过得相当的“充实”,每天白天8节课(其实我也是三天打渔,两天晒网),晚上回到家练习练习做饭,然后就打开本本开始忙碌的工作。一边看无尽paper,一边绞尽脑汁的搜数据,遇到问题开始到处请教,日子倒也颇为充实。

记得春天的时候写那篇关于FDI的破文章的时候狠狠的感慨了一番国内统计年鉴质量之差,大部分时间都耗在搜集数据上(尤其是把某些图片格式的数据手动输入到数据库里)了,实在是让人哭笑不得。这次写论文用到一些微观数据,其中一部分是鼎鼎大名的中国营养健康调查(China Health and Nutrition Survey,简写CHNS),虽然下载下来的都是SAS格式,但是借助stat transfer还是很容易就转到stata格式了(我算是被“威逼”+“利诱”彻底背叛SPSS了)。然后接下来,我就无语了,开始面对如山的变量……把问卷下下来,又把变量说明下下来,一个个对照着理解……好痛苦啊。

然后需要筛选变量了。按照欣欣姐屡次的提醒,乖乖的开始写do file。其实我还是习惯直接在命令行里面输入……

郁闷的就是对Stata的命令实在是太陌生了,不得不一次次的help。drop...keep...merge...一个个看下来,花了不少时间。开始残念那个叫做SQL的东西,开始回忆当时老老实实学习数据库的日子……怎么也是标准的计算机学院教材训练出来的,还是对于SQL最熟悉,虽然时日已久开始淡忘……

网上搜了搜,好像SAS支持SQL但是Stata并不支持SQL查询和操作,唉。虽然stata各种简单的函数简化了许多操作,但是至少应该保留一个sql接口供习惯sql的人来使用嘛。SQL多好啊,简单的语句组合就那么万能无敌……标准的数据库操作语言啊~

其实stata终归还是一个统计分析软件,并不是数据库管理软件,也就是说它的任务是“分析”而不是“管理”数据。但是我实在是不知道怎么把数据整理好再交给stata分析,只能在stata里面整理了。实在是为难stata,也为难我了。呜呼!

我想,当初要是考虑留下一个sql窗口,会方便许多吧。不知道是为了简化操作还是什么的,非要单独出来一些函数。唉。

也没什么好办法了,相比于借助其他DMS(Data Management System,数据管理系统)整理好再转到stata格式,我还是直接在stata里面操作好了。或许后者效率还是稍稍高些吧,毕竟原理都一样。只是期待,某些约定俗成的标准和规范,应该更为广泛的传承和延续,节省大家的时间,专注于研究而不是无谓的浪费在过程之中。

无论如何,我还是希望尽快的弄完数据这块,专心于经济分析中。

Categories
日常应用

统计软件:R

其实前几天Taiyun Wei就给我留言说R是个很强的统计学软件。

关于统计软件及其在经济学中的应用,我的在SPSS 17中的简单尝试一文中仅有一些很少的介绍。平时也只是用到的时候才会去研究一下SPSS或者STATA(没办法,谁让陈老师和欣欣姐都这么钟情于STATA呢?),对于SAS直接望而生畏。

然而今天想起来去看看R之后,真的发现它实在是太强大了!

R的一些基本情况

R

R的使用

说实话,我到现在为止也只是简简单单的看了一下R的入门,有一份很好的《R导论》,可以http://www.biosino.org/R/R-doc/下载。

总体感觉就是,R很符合习惯面向对象操作的人……嘿嘿!

题外话

  • 我现在也很关注的一点就是“数据挖掘”,有兴趣的同学可以共同探讨一下!主要是最近对于统计年鉴直接无语了,而看到国外的标准格式的统计数据,想到可以直接用SQL操作,心里那个兴奋啊!
  • 此外,对于“统计学在经济学”中的应用,也颇有兴致了解一下。计量经济学到底发挥了什么作用?统计学到底能带给我们什么?(感谢Taiyun Wei这位统计学专业的给我的另一篇留言)这都是我在疑虑和思考的问题。貌似自从想清楚数学在经济学中的作用和地位了之后,从严谨和科学的态度出发,对于这些最基本的问题关注的越来越多。
  • 上次有位老师嘲讽我们的时候说,“谁能解释清楚价值规律和价值就能达到诺贝尔奖的平均水平了”,我很反感这种业内人士拿着业内观点来嘲讽学生的做法。价值理论是整个微观经济学的核心和最最基本的问题,作为老师不去谆谆善诱,而来嘲讽初学者,真不知道是为了鼓励还是为了显示自己的水准。想说的就是,对于基本问题的思考不仅不会中断,而且会越来越深入。可能同一个问题,在不同认知水准下的思考会是不同层次的,但是我相信,会是越来越接近本质的。其实上面说的那个问题本质就是:对于历史的态度。或者说,大数定理究竟会在多大程度上指导我们的实践和预测将来?究竟是指导,还是误导?