动态区间定位

【问题】

i have two tables, for example :

Table 1 - Transaction

TransID |User   | Date

1       | Andrew |2015-06-1613:13:00

2       | Andrew |2015-06-1613:15:00

3       | Andrew |2015-06-1613:17:00

4       | Andrew |2015-06-1614:15:00

5       | Andrew |2015-06-1614:18:00

6       | Andrew |2015-06-1614:25:00

7       | Andrew |2015-06-1614:35:00

8       | Andrew |2015-06-1614:55:00

Table2 - DISCOUNT

DiscountID |User | Date | DISCOUNT

1 | Andrew |2015-06-1613:00:00 |30 

2 | Andrew |2015-06-1614:00:00 |25

2 | Andrew |2015-06-1614:30:00 |20

I want to apply the ‘Discount’ according to the time frame in ‘Table 2’, and become like this below.

Table outcome

User | Date | Discount

Andrew |2015-06-1613:13:00 |30

Andrew |2015-06-1613:15:00 |30

Andrew |2015-06-1613:17:00 |30

Andrew |2015-06-1614:15:00 |25

Andrew |2015-06-1614:18:00 |25

Andrew |2015-06-1614:25:00 |25

Andrew |2015-06-1614:35:00 |20

Andrew |2015-06-1614:55:00 |20

Please help me for this problem, i’m already frustrated.

Thank you very muchh.

【回答】

对 DISCOUNT 表做跨行运算拼出区间，用 JOIN 不好写了，读出来用 SPL 能够实现：

	A
1	$select*fromTransaction
2	$select*from Discountorder by Dat
3	=A2.(Date)
4	=A1.new(User,Date,A2(A3.pseg(Date)).Discount:d)

A1：查找 Transaction 表

A2：查找 Discount 表并按时间排序

A3：获取折扣表的时间

A4：按交易表的时间段定位折扣表中的折扣值，生成新序表

运行结果：