[用正确的工具，做正确的事] 遇到超级大表，还在用Vlookup? 试试SQL吧

lunzi

楼主的日常工作，就是和各种Excel表格打交道，也习惯了用Excel处理、统计各种数据。
话说，今天遇到一个数据表（表一），内容很简单，就5列数据。但数据量狂大，约480万条。当然，这个表是CSV格式的。最新版的excel2013最多支持1048576行数据。

表一

表二

要做的工作是，从这个表中筛选出部分数据，筛选依据是根据另外一张表（表二），筛选条件是：“表1D列的内容，存在于表2B列的内容中”用习惯excel，第一步想到的是Vlookup。如果数据量不大，写一个公式，根据查询结果在做一次筛选，就可以将需要的数据提取出来。

1. =VLOOKUP(D2,'表二'!$B:$B,1,FALSE)

复制代码

但用excel处理数据，遇到的问题是：
1、数据表太大，Excel无法打开；当然可以将数据表拆分后，通过Excel再处理
2、数据量大，Vlookup效率太低，很容易造成excel假死。

怎么办呢？实际上，处理大数据，本来就不是Excel的本职工作。这次，我们借用Access＋SQL查询，分分钟就可以搞定。步骤如下：
1、将两个数据表导入到Access中
2、建立一个SQL查询，输入语句：

1. SELECT * FROM 表1 WHERE [EXT_OID] In (select [ne_dn] from 表2)

复制代码

3、将查询结果导出为Excel文件，这就是筛选后的结果。可以根据需要，在Excel中再进行其他的统计工作了

选对了方法，就这么简单。

使用正确的工具，作正确的事。

lunzi

o。是的。首先是先导入到了access中，从最后一张截图可以看出，查询是在accss中作的。
否则确实很慢。
谢谢：）

gpk

lunzi 发表于 2014-6-10 13:20
感觉速度还行。请教：还有更好的做法么？exits？

10万行的数据在excel中用ole-db的方式用in =  like都很慢

下次吧数据导入到access中试试看

lunzi

搜到的一篇帖子：

SQL里的EXISTS与in、not exists与not in

系统要求进行SQL优化，对效率比较低的SQL进行优化，使其运行效率更高，其中要求对SQL中的部分in/not in修改为exists/not exists



修改方法如下：

in的SQL语句

SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
FROM tab_oa_pub WHERE is_check=1 and
category_id in (select id from tab_oa_pub_cate where no='1')
order by begintime desc

修改为exists的SQL语句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
FROM tab_oa_pub WHERE is_check=1 and
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1')
order by begintime desc



分析一下exists真的就比in的效率高吗？



    我们先讨论IN和EXISTS。
    select * from t1 where x in ( select y from t2 )
    事实上可以理解为：
    select *
      from t1, ( select distinct y from t2 ) t2
     where t1.x = t2.y;
    ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。
    select * from t1 where exists ( select null from t2 where y = x )
    可以理解为：
    for x in ( select * from t1 )
    loop
       if ( exists ( select null from t2 where y = x.x )
       then
          OUTPUT THE RECORD!
       end if
    end loop
    ——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。
    综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

我们要根据实际的情况做相应的优化，不能绝对的说谁的效率高谁的效率低，所有的事都是相对的

lunzi

gpk 发表于 2014-6-10 13:07
楼主你480万行数据用in  运行不慢吗？

感觉速度还行。请教：还有更好的做法么？exits？

gpk

楼主你480万行数据用in  运行不慢吗？

看见星光

函数本来就只适合处理小范围的数据，建议楼主到数据透析表版块看下SQL的相关，同时感谢分享哦。

d873651130

楼主，你这个是查找出2个表格中的相同内容。
但是，我想请教你：如何用SQL语句查找出2个表格中的不同内容？

wbcfly

其实导入数据也挺费时间吧？