7.12 Association on interval: using the table join
Example 1:
We have the following data:
Table1.xlsx:
| Quantity | Price |
|---|---|
| 221 | |
| 87 | |
| 33 | |
| 73 | |
| 162 | |
| 227 | |
| 403 | |
| 288 | |
| 78 | |
| 213 | |
| 374 | |
| 152 |
Table2.xlsx:
| StartQuantity | EndQuantity | Price |
|---|---|---|
| 0 | 50 | 15 |
| 50 | 100 | 13.75 |
| 100 | 300 | 13 |
| 300 | 500 | 12.5 |
Calculate values of Price column in Table1. The rule is to query Table2 for each Quantity value, and, if the value is greater than StartQuantity and less than or equal to EndQuantity in a certain row, return the Price value in this row and write it to Price column in Table1.
SPL script:
| A | |
|---|---|
| 1 | =T(“Table2.xlsx”) |
| 2 | =T(“Table1.xlsx”).run(Price=A1.segp@r(StartQuantity,Quantity).Price) |
A2 Use pseg function to find the ordinal number of the segment that contains the current Quantity in the interval beginning from StartQuantity in Table2, and get the price in the corresponding row of Table2 to return. @r option represents a right-closed interval. The Quantity value 52, for instance, belongs to the segment in the first row.
Execution result:
| Quantity | Price |
|---|---|
| 221 | 13 |
| 87 | 13.75 |
| 33 | 15 |
| 73 | 13.75 |
| 162 | 13 |
| 227 | 13 |
| 403 | 12.5 |
| 288 | 13 |
| 78 | 13.75 |
| 213 | 13 |
| 374 | 12.5 |
| 152 | 13 |
Example 2:
Below is the electric vehicle charging data (Table3.xlsx):
| CardId | Starttime | Endtime | Quantity | Price |
|---|---|---|---|---|
| 0000012541 | 2018/4/30 17:11:28 | 2018/4/30 18:20:17 | 47.32 | |
| 0000012541 | 2018/4/30 15:04:54 | 2018/4/30 16:07:07 | 42.7 | |
| 0000012541 | 2018/4/30 14:49:22 | 2018/4/30 14:49:45 | 0 | |
| 0000012541 | 2018/4/30 13:56:16 | 2018/4/30 14:46:00 | 35.88 | |
| 0000012541 | 2018/4/24 18:00:19 | 2018/4/24 18:00:38 | 0 | |
| 0000012541 | 2018/4/24 11:31:54 | 2018/4/24 11:33:05 | 0.13 | |
| 0000012541 | 2018/4/24 11:31:00 | 2018/4/24 11:31:54 | 0 | |
| 0000012541 | 2018/4/24 11:04:45 | 2018/4/24 11:05:45 | 0.13 | |
| 0000012541 | 2018/4/21 17:34:06 | 2018/4/21 17:35:00 | 0.13 | |
| 0000012541 | 2018/4/21 17:32:32 | 2018/4/21 17:33:37 | 0.17 | |
| 0000012541 | 2018/4/21 17:27:25 | 2018/4/21 17:28:38 | 0.35 | |
| 0000012882 | 2018/5/31 19:20:05 | 2018/5/31 20:22:39 | 45.92 | |
| 0000012881 | 2018/5/28 6:46:01 | 2018/5/28 7:19:06 | 24.17 | |
| 0000012881 | 2018/5/24 6:52:40 | 2018/5/24 7:23:08 | 22.22 | |
| 0000012881 | 2018/5/24 6:50:33 | 2018/5/24 6:51:32 | 0 | |
| 0000012881 | 2018/5/19 15:08:47 | 2018/5/19 15:44:21 | 25.74 | |
| 0000012881 | 2018/5/19 15:07:14 | 2018/5/19 15:08:04 | 0.13 | |
| 0000012882 | 2018/5/18 14:36:07 | 2018/5/18 14:36:22 | 0 | |
| 0000012882 | 2018/5/18 14:23:44 | 2018/5/18 14:23:57 | 0 | |
| 0000012882 | 2018/5/18 14:22:29 | 2018/5/18 14:23:44 | 0 | |
| 0000012882 | 2018/5/18 8:51:31 | 2018/5/18 9:32:28 | 29.6 | |
| 0000012882 | 2018/5/18 8:47:20 | 2018/5/18 8:48:38 | 0.44 | |
| 0000012882 | 2018/5/18 8:46:01 | 2018/5/18 8:47:20 | 0 | |
| 0000012882 | 2018/5/18 8:40:14 | 2018/5/18 8:43:48 | 2.05 | |
| 0000012882 | 2018/5/18 8:39:23 | 2018/5/18 8:40:14 | 0 | |
| 0000012882 | 2018/5/18 8:36:52 | 2018/5/18 8:37:26 | 0 | |
| 0000012882 | 2018/5/18 8:23:55 | 2018/5/18 8:26:14 | 0.8 | |
| 0000014529 | 2018/5/15 15:45:25 | 2018/5/15 15:47:09 | 0.58 | |
| 0000014529 | 2018/5/15 15:44:04 | 2018/5/15 15:44:42 | 0 | |
| 0000014529 | 2018/5/15 15:43:03 | 2018/5/15 15:44:04 | 0 | |
| 0000012882 | 2018/5/15 15:41:29 | 2018/5/15 15:42:17 | 0.05 | |
| 0000012881 | 2018/5/15 15:26:44 | 2018/5/15 15:27:00 | 0 | |
| 0000012881 | 2018/5/15 14:39:42 | 2018/5/15 15:26:05 | 34.82 | |
| 0000015872 | 2018/5/15 14:36:48 | 2018/5/15 14:37:14 | 0 | |
| 0000012881 | 2018/5/15 14:30:08 | 2018/5/15 14:36:04 | 3.93 | |
| 0000012881 | 2018/5/15 14:29:05 | 2018/5/15 14:30:08 | 0 | |
| 0000012881 | 2018/5/15 14:27:47 | 2018/5/15 14:28:26 | 0 |
And the electricity price during the charging duration (Table4.xlsx):
| StartHour | 6 | 8 | 12 | 16 | 22 |
| EndHour | 8 | 12 | 16 | 22 | 6 |
| Price | 0.9094 | 1.0354 | 0.6574 | 0.9094 | 0.4054 |
Calculate values of Price column in Table3. The rule is to find price of the corresponding time interval in Table4 according to the hour in Starttime value in Table3.
SPL script:
| A | |
|---|---|
| 1 | =T@b(“Table4.xlsx”) |
| 2 | =T(“Table3.xlsx”).run(Price=A1(3).array().to(2,)(A1(1).array().to(2,).pseg(hour(Starttime)))) |
A2 Generate intervals based on a sequence of StartHour values (members begin from the value at row 1 and column 2) in Table4, find the ordinal number of segment to which the hour in each Starttime value in Table3 belongs, and get price of the corresponding segment from Table4’s row 3 and assign it to the current Price field of Table3.
Execution result:
| CardId | Starttime | Endtime | Quantity | Price |
|---|---|---|---|---|
| 0000012541 | 2018-04-30 17:11:28 | 2018-04-30 18:20:17 | 47.32 | 0.9094 |
| 0000012541 | 2018-04-30 15:04:54 | 2018-04-30 16:07:07 | 42.7 | 0.6574 |
| 0000012541 | 2018-04-30 14:49:22 | 2018-04-30 14:49:45 | 0 | 0.6574 |
| 0000012541 | 2018-04-30 13:56:16 | 2018-04-30 14:46:00 | 35.88 | 0.6574 |
| 0000012541 | 2018-04-24 18:00:19 | 2018-04-24 18:00:38 | 0 | 0.9094 |
| 0000012541 | 2018-04-24 11:31:54 | 2018-04-24 11:33:05 | 0.13 | 1.0354 |
| 0000012541 | 2018-04-24 11:31:00 | 2018-04-24 11:31:54 | 0 | 1.0354 |
| 0000012541 | 2018-04-24 11:04:45 | 2018-04-24 11:05:45 | 0.13 | 1.0354 |
| 0000012541 | 2018-04-21 17:34:06 | 2018-04-21 17:35:00 | 0.13 | 0.9094 |
| 0000012541 | 2018-04-21 17:32:32 | 2018-04-21 17:33:37 | 0.17 | 0.9094 |
| 0000012541 | 2018-04-21 17:27:25 | 2018-04-21 17:28:38 | 0.35 | 0.9094 |
| 0000012882 | 2018-05-31 19:20:05 | 2018-05-31 20:22:39 | 45.92 | 0.9094 |
| 0000012881 | 2018-05-28 06:46:01 | 2018-05-28 07:19:06 | 24.17 | 0.9094 |
| 0000012881 | 2018-05-24 06:52:40 | 2018-05-24 07:23:08 | 22.22 | 0.9094 |
| 0000012881 | 2018-05-24 06:50:33 | 2018-05-24 06:51:32 | 0 | 0.9094 |
| 0000012881 | 2018-05-19 15:08:47 | 2018-05-19 15:44:21 | 25.74 | 0.6574 |
| 0000012881 | 2018-05-19 15:07:14 | 2018-05-19 15:08:04 | 0.13 | 0.6574 |
| 0000012882 | 2018-05-18 14:36:07 | 2018-05-18 14:36:22 | 0 | 0.6574 |
| 0000012882 | 2018-05-18 14:23:44 | 2018-05-18 14:23:57 | 0 | 0.6574 |
| 0000012882 | 2018-05-18 14:22:29 | 2018-05-18 14:23:44 | 0 | 0.6574 |
| 0000012882 | 2018-05-18 08:51:31 | 2018-05-18 09:32:28 | 29.6 | 1.0354 |
| 0000012882 | 2018-05-18 08:47:20 | 2018-05-18 08:48:38 | 0.44 | 1.0354 |
| 0000012882 | 2018-05-18 08:46:01 | 2018-05-18 08:47:20 | 0 | 1.0354 |
| 0000012882 | 2018-05-18 08:40:14 | 2018-05-18 08:43:48 | 2.05 | 1.0354 |
| 0000012882 | 2018-05-18 08:39:23 | 2018-05-18 08:40:14 | 0 | 1.0354 |
| 0000012882 | 2018-05-18 08:36:52 | 2018-05-18 08:37:26 | 0 | 1.0354 |
| 0000012882 | 2018-05-18 08:23:55 | 2018-05-18 08:26:14 | 0.8 | 1.0354 |
| 0000014529 | 2018-05-15 15:45:25 | 2018-05-15 15:47:09 | 0.58 | 0.6574 |
| 0000014529 | 2018-05-15 15:44:04 | 2018-05-15 15:44:42 | 0 | 0.6574 |
| 0000014529 | 2018-05-15 15:43:03 | 2018-05-15 15:44:04 | 0 | 0.6574 |
| 0000012882 | 2018-05-15 15:41:29 | 2018-05-15 15:42:17 | 0.05 | 0.6574 |
| 0000012881 | 2018-05-15 15:26:44 | 2018-05-15 15:27:00 | 0 | 0.6574 |
| 0000012881 | 2018-05-15 14:39:42 | 2018-05-15 15:26:05 | 34.82 | 0.6574 |
| 0000015872 | 2018-05-15 14:36:48 | 2018-05-15 14:37:14 | 0 | 0.6574 |
| 0000012881 | 2018-05-15 14:30:08 | 2018-05-15 14:36:04 | 3.93 | 0.6574 |
| 0000012881 | 2018-05-15 14:29:05 | 2018-05-15 14:30:08 | 0 | 0.6574 |
| 0000012881 | 2018-05-15 14:27:47 | 2018-05-15 14:28:26 | 0 | 0.6574 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
