杭州阿里巴巴第二轮电话面试，被BS好几回，特留此贴以及问题，请大家指点！

[oraclemch]

大家还记得我8天前发的阿里巴巴一面的帖子吗？过去了一个星期了，我以为事情已经黄了，所以就再也没有做相关准备，结果今天阿里巴巴第二轮笔试电话突然而至，搞的我措手不及啊！

1，如果有row_number() 分析函数，如果我有一张学生表student，里面有班级、学生名、分数，排名我想得到如下数据，该如何用row_number() 排序写一条sql获取，不能用union all来连接获取。
--班级、学生名、分数，排名
一班 李一 100 1
一班 李二 100 1
一班 李三 100 1
一班 李四 100 4
一班 李五 99 5
一班 李六 98 6
.......-- 一班很多学生，依次排下去。
二班 王一 100 1
二班 王二 100 1
二班 王三 99 3
二班 王四 98 4
二班 王五 80 5
...... 二班有很多学生，依次排下去。

我的答案是：select banji,name,score,row_number() over(order by banji, name,score desc)。结果他说不对，最近在做sqlserver开发，可能长时间没有碰oracle了，所以导致当时一下没有想出来，被严重BS了，事后仔细一想，其实很简单的，我以前还写过类似的sql，只是时间长了，忘记了，呵呵，不知道大家知道怎么写了么？

2，有关优化的事情，A表500万数据，B表2亿条数据。A与B的数据结构相同，select count(*) from A,B where A.id=B.id。问下，这条sql很耗时，如何优化这条sql，使之效率最高。

我答案，看执行计划，是否走索引，不走的话，就重建所以，他说，如果索引都是完好无损的，而且2亿条数据走索引时优化不过来的，问我还有别的方式来优化这条sql不？我一时就答不出来了，因为sql很简单了，不知道如何优化了，而且我们的oracle库也没有类似的查询，所以一下想不出来怎么回答，结果又被BS了。

3，数据仓库里面，如果你根据客户的需求写完了sql，取到了数据，你如何保证这些数据时准确的呢？

我回答，1，根据需求写一些测试sql来验证，比如数据总量，主要字段，主要信息，抽取几条验证。
2，找客户，跳一些数据来验证。
结果他反问我这些方法是我自己想出来的吗？我说是的，我感觉我的回答不太对，有点不妙。

还有一些我回答正确的问题，比如管理方面的，我就答的不错，我就不列出来了。反正今天在引以为强项的sql编写与优化上面被问到了，证明我的oracle开发基本功还是很差的，以后要加强这方面的练习了。

欢迎大家对上面的3个问题，发表下自己的看法，在此先行谢过！

[shancao]

我来解释一下第二个问题，大家看看对不对。
该sql最起码有两个地方需要优化，第一：count（*）需要使用count(1),因为数据量比较大，使用*会非常占内存。第二：select count(*) from A,B where A.id=B.id，大家知道对于oracle对于表解析是从又向左解析，where语句是从下往上解析，所以我们在表链接的时候要把小表放在右边，大表放左边，where条件是索引的放下边。所以综上上面的可以改成select count(1) from B，A where B.id=A.id

[dingjun123]

1.用rank,用row_number多余
2.要改写sql,两个大表还是用exists试试，然后count(*)改为count(主键)，必要的时候加上并行之类的东西，可能的话最好分区
3.测试呗，根据测试规范来

[碧水幽幽泉]

[xieyu_zy]

[Quote=引用 20 楼 xieyuooo 的回复:]
1、题目要求，需要用row_number()来做，那么看下这样写如何,题目意思应该是要获取每个班级前三名，包含并列份数人数：
SELECT banji,name,score FROM (
SELECT banji,name,score,row_number() OVER(PARTITION BY banji ORDER BY score DESC) RN
FROM student
)……
[/Quote]

搞错了，不好说意思，晕晕的，第一个题目使用rank()应该说才能很快出来结果：
SELECT banji,name,score,RANK() OVER(PARTITION BY banji ORDER BY score DESC) RN
FROM student;

第二个题目SQL拷贝错了，不然语法过不去，呵呵，今天在机器上加大数据量测试了，不过还是搞不到几百万关联几亿去，不过用分区+并行应该差不了太多，我模拟是大概十分之一的数据吧(我在A表和B表的ID上分别创建了一个普通索引，其实按照这种写法，A表的ID有没有索引无所谓了，这里是将A表作为驱动表吧，当然500万的驱动表太大，所以只有用分区技术了，加上并行应该有一定效果吧)：
SELECT SUM(NUM) FROM(
SELECT (
SELECT COUNT(t2.id)
FROM B t2
WHERE t2.id = t1.id
) NUM
FROM A t1);

第三个题目上面没啥该的，说了其实我觉得就是设计和测试。呵呵

[tianjusanren]

学习 ！

[oraclemch]

[Quote=引用 28 楼 xieyuooo 的回复:]
引用 20 楼 xieyuooo 的回复:
1、题目要求，需要用row_number()来做，那么看下这样写如何,题目意思应该是要获取每个班级前三名，包含并列份数人数：
SELECT banji,name,score FROM (
SELECT banji,name,score,row_number() OVER(PARTITION BY banji ORDER BY score DESC)……
[/Quote]

SELECT SUM(NUM) FROM(
SELECT (
SELECT COUNT(t2.id)
FROM B t2
WHERE t2.id = t1.id
) NUM
FROM A t1);

你的这个sql是社会那么意思啊？并行？

[vanjayhsu]

感谢楼主的分享啊。。。我以前有个同事去了阿里巴巴，好像笔试面试都没有这么难哦。。。

汗。。。看了下上面的问题，我也基本上回答不出来啊。。。希望前辈们多来指点，同时也非常感谢楼主的无私分享

[zidanexun]

学习，分享各位知识

[weg910]

第二的问题A,B表换一下位置吧。

[bobocici]

第二题用位图连接索引是不是好一些？
当然，还是得拿执行计划说话

[bobocici]

第一题明显要用dense_rank(),找不到必须用row_number()的理由

[gltyi99]

学习了，谢谢楼主的无私奉献了

[zfwjh]

谢谢，学习了

[xieyu_zy]

[Quote=引用 29 楼 oraclemch 的回复:]
引用 28 楼 xieyuooo 的回复:
引用 20 楼 xieyuooo 的回复:
1、题目要求，需要用row_number()来做，那么看下这样写如何,题目意思应该是要获取每个班级前三名，包含并列份数人数：
SELECT banji,name,score FROM (
SELECT banji,name,score,row_number() OVER(PARTITION BY ban……
[/Quote]

那个SQL没有并行，我机器上也模拟不了并行，呵呵，只是在我使用哪些数据模拟中，使用该方法获取结果是我目前使用很多办法中最快的，其余办法都会死机（用的大概是四十万关联三百万左右吧），至于顺序，COUNT写法都试过，最后用这种办法没有死而且得到结果的时间也不算长，我是说如果配合上并行的话，应该可以吧，你可以做个试验试一试，我也是在我本地的数据库上测试的。

[coolkisses]

算count的那题，提高效率可以用提示，也可以取count(1)和只取一个字段（select id from a）join的方式，减少查询开销

[闭门车]

第一题我觉得大家想多了吧，就是要按班级跟名次排序而已
select banji,name,score,mingci from
(
select banji,name,score,row_number() mingci from student
) order by banji,mingci

[伤心的贼]

2，有关优化的事情，A表500万数据，B表2亿条数据。A与B的数据结构相同，select count(*) from A,B where A.id=B.id。问下，这条sql很耗时，如何优化这条sql，使之效率最高。


count(1) 此处可优化。。然后表解析。先解析右边表。B.ID=A.ID就行了。。你想你是解析2E数据的表快，还是解析500W数据的表快

[geniusqiao]

[Quote=引用 3 楼 tangren 的回复:]
先谢谢楼主无私！

SQL code
--1.
with student as(
select '一班' class,'李一' name, 100 score from dual union all
select '一班' class,'李二' name, 100 score from dual union all
select '一班' class,'李三' name,……
[/Quote]

牛人那

[geniusqiao]

[Quote=引用 3 楼 tangren 的回复:]
先谢谢楼主无私！

SQL code
--1.
with student as(
select '一班' class,'李一' name, 100 score from dual union all
select '一班' class,'李二' name, 100 score from dual union all
select '一班' class,'李三' name,……
[/Quote]

牛人那

[zbo]

第二题把A.ID放在后边，这样先查较少的表。