动态区间定位
【问题】
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：按交易表的时间段定位折扣表中的折扣值，生成新序表
运行结果：
