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
我的生活状态

七月的上海

7月7日,上海,已然盛夏。标志:烦躁的鸣个不停的蝉,30摄氏度的早晨八点。

DSC03993

第一个在上海度过的夏天。措不及防。每天坚持着来往12KM之外的公司,只是越来越迷恋五角场的生活气息。

读书,读很多书。清一色的发现自己读过的80%~90%的书籍都跟经济学有关的时候,有点落寞。桌子上摆了好久的《中国哲学史》,连打开的勇气都没有。旁边的一本本数学书却被翻了许多次。悲哀。

写无尽的代码,感慨那曾经用来发散思维的右脑居然慢慢的形成了程序化思维。悲哀。

只是暂时的代价吧。至少,还能维持一种简单的美好的生活姿态。一杯冰饮,几瓣西柚。对付着火烧火燎的喉咙。应景一下,哼一句:

七月七日长生殿,半夜无人私语时。
在天愿做比翼鸟,在地愿为连理枝。

Categories
我的生活状态

读书、写代码

在重温 Competition Policy: Theory and Practice 这本书。我不得不承认,anti-trust一直是一个很有意思的领域,最直接的和业界、经济政策相联。看看这些故事,看看背后的各种出于“市场效率”的考量,真的觉得世界是很美妙的。

没想到这个月会有这么多代码可以写。或许,像我这么一个极度讨厌“重复劳动”和copy、paste的人,思维永远会是,“这东西怎么写代码啊”……可惜啊,当年没好好学C,现在被R惯坏了,什么都用R来做,也越来越懒了。很多问题,一时R解决不了,就只能郁郁的手动处理掉。然后心里在想,“真心的,我会成为一个非常好的research assistant的”……呃,这是不是一个意外的“收获”呢?

有的时候在想,应该如何定义“工作”一词?不断的、重复性的劳动,不厌其烦么?很显然,这不是我喜欢的,虽然明知道很符合亚当·斯密眼中的“分工”。很不喜欢浪费自己的时间,尤其是在做一些让我觉得本科研究生都白读的事情上。这个时候,“完美癖”就显得非常不合适了,看着很多东西就这么流出去了,最后索性任其自由……

哎,很多时候,连别人对自己的误解,都懒的解释了,这么的“顺其自然”好了。或许很多东西,一不值得挽回、二不值得留恋。因此,又何必浪费自己的时间与口舌……有时间,还是多读读书、看看论文吧。

Categories
事儿关经济

写论文 or 写程序?

毕业论文写了几天了,越写越痛苦,时间太紧,很多东西来不及细致的想想只能先加上去,唉。

这次的论文比较的,诡异。我第一次感觉到写一篇经济学论文需要用这么多代码。以前写论文的时候,最多有几个方程需要解一下,或者一些数据扔到计量软件里面几行命令跑一下就OK。所以就算我算法很烂,写程序的水准也很懒,但是还是可以勉强应付的。但是这一次,因为算一个概率和期望算不出来,只好求助taiyun。没想到很快的taiyun就给了我一个程序,用来模拟结果。虽然模拟的不是真实值,但是大致的趋势出来了,很漂亮~

接着,我又突发奇想,来个“蜂窝状”的东西。搜了半天不知道蜂窝图对应的矩阵格式应该怎么写,于是乎就开始求助另一个程序高手——X。此人不愧是学计算机的,一边比着编程大赛,一边三下五除二就把程序写出来了。可是问题是,他用的是C语言,我机子上却没装C的编译器……我琢磨了半天想把它改成R的,结果死活有问题。X很仗义的开始帮我改,先是自己安了个R(嘿嘿,怎么我也成功一次诱惑/强迫别人装上了R),又开始研究R的语法(X语录:我这代码服务真到位...)。最后在我俩都快面临崩溃的时候,他说,要不给我个编译完的C程序,我自己调用吧。我想了想,OK啊,只要输出的是文本文件,我在R里面读入就可以了嘛。于是乎,他很快的给了我一个很好用的程序,然后我就开始舒舒服服的输出txt,最后开始舒舒服服在R里面舞蹈弄枪,望着出来的蜂窝图傻傻的嘿嘿笑。

到最后我也不知道我是在写论文还是在玩乐了,把程序的参数调一下,然后自己就嘿嘿的对着屏幕听着CPU小风扇转悠的声音美滋滋的看结果。在几张图的衬托下,很快论文就写了七八页了。不过不知道最后能不能交英文的论文啊,我怕自己偷懒就一直用英文写下来的,要不用中文写完了肯定不会脑子抽风的翻译成英文。天知道……其实我觉得最boring的事儿就是把自己写的英文的东西翻译成中文,这不是一边翻译一边让自己鄙视自己的英文表达能力多么差么?看着中文那飞舞的流光溢彩,再瞅瞅干巴巴的英文句子,只想说“我不认识写文章那个人”。特别能体会到一句话,貌似是老舍还是茅盾或者谁谁说的,“翻译体现的是一个人使用译过来的语言的能力和文化底蕴”。一个中文积蕴深厚且能读懂英文的人,往往比一个中文平平而英文出口成章的,更能译出好的中文文章。不过话说回来,论文这种东西,不就是干巴巴的么?除非你对某个模型感兴趣,否则我估计没几个人会愿意读一篇学术论文的——真的很boring。说起来翻译,暑假如果有时间,我真的很想翻译一本书啊,但是不知道有没有出版社肯去买版权,呃……这年头,我理想主义,不代表就能找到理想主义的出版商来搞定这事儿。

最后,这一次深深的接受了“题目要起小一点”——从初中开始学习写作文的时候老师就一直唠叨的话。这次很豪气干云的写了一个很简短的题目,写完了就知道自己挖了个坑只能自己跳下去了。很显然,能把这么简单的题目论述好是一件很难很难的事儿。于是乎,假惺惺的写了一句“this is the first part”。反正只是我的manuscript嘛,别人也看不到。别人看到的肯定是包装好的。本来模型中想加入动态啊,博弈啊,不确定性啊等等时髦的东西,后来发现还是一个“代表性人物”分析起来比较简单,于是乎就跟博弈没关系了。“不确定性”加不加似乎也没啥影响,反正我只能模拟结果又不能真正用扰动项去表达啥。至于动态……好吧,一个贴现因子搞定。本来还很复杂的设什么时间之类的,后来发现还不如一个贴现因子delta好用。于是就狠狠的把一大片的废话论述“注释”掉了(还是没舍得一口气删了,不过在lyx里面注释标签一闭合,也能做到眼不见为净)。

写着写着,和自己起初的想法有点越走越远,每次写论文都是这样,最后写出来的肯定不是自己打谱要写的——除非写实证。不知道能不能够应付毕业……唉。其实还是很想研究研究元胞自动机的,taiyun一提这个东西我就精神起来了,看了半天介绍好像有点眉目了,但是想不出怎么拿来玩玩。于是乎,先扔到那里吧,看看毕业论文怎么能应付交差再说。