计算每组的前 N 名

【问题】

需求:

表 1 是股票发生大宗交易的股票代码、日期、大宗交易成交价格
表 2 是各个股票的历史记录

现在希望能够得到每个发生大宗交易的股票在这一天之后 3 天 (包括发生当天) 之内的价格记录

表 1 如下:

Code   Date1            Price1  
1          2014/3/12   5  
2          2014/3/13   2  
3          2014/3/13   3  

表 2 如下:

Code   Date2            Price2  
1          2014/3/10    4  
1          2014/3/11    4.09  
1          2014/3/12    4.01  
1          2014/3/13    4.05  
1          2014/3/14    4.10  
1          2014/3/15    4.50  
1          2014/3/16    4.45  
2          2014/3/10    4  
2          2014/3/11    4.09  
2          2014/3/12    4.01  
2          2014/3/13    4.05  
2          2014/3/14    4.10  
2          2014/3/15    4.50  
2          2014/3/16    4.45  
3          2014/3/10    4  
3          2014/3/11    4.09  
3          2014/3/12    4.01  
3          2014/3/13    4.05  
3          2014/3/14    4.10  
3          2014/3/15    4.50  
3          2014/3/16    4.45  
3          2014/3/17    4.50

得到的目标结果应该是:

Code   Date1      Price1      Date2          Price2  
1          2014/3/12   5           2014/3/12   4.01  
1          2014/3/12   5           2014/3/13   4.05  
1          2014/3/12   5           2014/3/14   4.10  
2          2014/3/13   2           2014/3/13   4.05  
2          2014/3/13   2           2014/3/14   4.10  
2          2014/3/13   2           2014/3/15   4.50  
3          2014/3/13   3           2014/3/13   4.05  
3          2014/3/13   3           2014/3/14   4.10  
3          2014/3/13   3           2014/3/15   4.50

最基本的连接我会写,但是要怎么限定在大宗交易发生之后 3 个交易日的记录不知道要怎么去限定。而且,因为交易存在双休日,是不能通过限定日期的,必须通过判断记录数才行。
求大神指导,跪谢!

【回答】

自然的思路是:关联两表后按 Code、Date1 和 Date2 排序,再按 Code 和 Date1 分组,取每组中 Date2 大于等于 Date1 的三条记录即可完成。但由于 SQL 分组后无法保存分组结果,而且 SQL 集合无序很难取出连续的三条记录。对于支持窗口函数的数据库实现要简单些:


select *  
from ( select  ROW_NUMBER() OVER( PARTITION by t1.code,t1.date1  ORDER BY  t2.date2) rn,  
               t1.code,  
               t1.date1,  
               t1.price1,  
               t2.date2,  
               t2.price2  
 from  t1  
 join t2  on  t1.code = t2.code  
 where  t2.date2 >= t1.date1) t  
 where rn <= 3

这类有序计算使用 SPL 会更方便,有集合数据和按位置操作的功能,代码通用直观,也能适用于没有窗口函数的数据库:


A

1

$(db1)select t1.code Code, t1.date1 Date1, t1.price1 Price1, t2.date2 Date2, t2.price2 Price2 from t0070_1 t1  join t0070_2 t2 on t1.code = t2.code where t2.date2 >= t1.date1 order by t1.code,t1.date1,t2.date2

2

=A1.group(Code,Date1).(~.m([1,2,3]).select(~)).conj()


关于更多 SPL 集合的使用,参考《SPL 中集合的使用