How can I join tables based on timestamp ranges?

1 次查看（过去 30 天）

Jon Glassman 2019-3-16

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/450357-how-can-i-join-tables-based-on-timestamp-ranges

I have three tables of a few million rows each, as follows:

Table A has one row for each of 1 million devices, with a column with a unique identifier for each device.

Table B has one row for each change of state for each device in Table A. This table includes the unique device id, a unique state id for each row, and the timestamp when the state change occured.

Table C is a table of every mouse click for each device in table A. This table includes the unique device id, a unique mouseclick id for each row, and the timestamp of the mouseclick.

It is trivial to join A to B and A to C based on the device id. What I need help with is joining B to C based on not only the device id, but also the timestamp. What i want to know is, for every mouseclick in Table C, what state was that device in. If the mouseclick occured on or after the timestamp for status ”m”, but before the timestamp for status “n”, it would join to status ”m”.