我有一个状态机,它从状态1(true)开始,可以移到状态2(临时false),从临时false移回状态1或状态3(false)。状态3可以自行循环或转到状态1。
如果一个状态从状态2变为状态3,那么状态机是 false
从它处于状态2到它回到状态1(从状态3)。但是,如果它从状态2返回到状态1,那么它就永远不会处于 false
州。
我将转换记录在数据库中,使其看起来像:
+----+------------------+---------------+-----------+---------------+---------------------+
| id | state_machine_id | current_state | new_state | last_event_id | ts |
+----+------------------+---------------+-----------+---------------+---------------------+
| 1 | 1 | 0 | 1 | NULL | 2018-12-26 16:18:51 |
| 2 | 1 | 1 | 2 | 1 | 2018-12-26 18:21:01 |
| 3 | 1 | 2 | 1 | 2 | 2018-12-26 18:35:24 |
| 4 | 1 | 1 | 2 | 3 | 2018-12-26 18:51:01 |
| 5 | 1 | 2 | 1 | 4 | 2018-12-26 18:51:42 |
| 6 | 1 | 1 | 2 | 5 | 2018-12-27 20:03:01 |
| 7 | 2 | 0 | 1 | NULL | 2018-12-28 02:07:49 |
| 8 | 1 | 2 | 3 | 6 | 2018-12-28 02:15:01 |
+----+------------------+---------------+-----------+---------------+---------------------+
状态0是起始状态和最后状态的占位符 last_event_id
指特定状态机的最后一个事件。
问题:
接收startdate、enddate和state\u machine\u id
返回二维数组,其中每个元素都有一个日期,以及状态机在该天是否为false。
例子:
输入:start='2018-12-26',end='2018-12-30',statemachine=1
输出:
+------------+-----------+
| day | is_false |
+------------+-----------+
| 2018-12-26 | 0 |
| 2018-12-27 | 0 |
| 2018-12-28 | 1 |
| 2018-12-29 | 1 |
| 2018-12-30 | 1 |
+------------+-----------+
我以编程方式完成了上述操作,下面是我的psuedo代码:
transitionsInSpan = db->run('SELECT * FROM transitions where ts >= $start and ts <= $end and state_machine_id = $stateMachine')->all()
if transitionsInSpan empty: //edge cases
$closestToStart = db->run('SELECT current_state, new_state from transitions WHERE state_machine_id = $stateMachine AND ts < $start ORDER BY ts DESC LIMIT 1')->first()
if(current_state = 2 and new_state = 3):
return dates with all true
else if(new_state = 2):
get the closestToEnd ... all true if new_state = 3
return false for all dates
else:
listOfDates = dates between start and end dates (inclusive)
iterate through listOfDates:
...this gets kind of messy and convoluted
我现在正在尝试mysql:
SELECT MAX(
IF
(
new_state = 3,
true,
IF
(
id IN
(
SELECT last_event_id from transitions WHERE new_state = 3
), true, false
)
)
) as is_false,
date(ts) as ts
FROM transitions
WHERE state_machine_id = 1
GROUP BY ts;
以上结果如下:
+-----------+------------+
| is_false | ts |
+-----------+------------+
| 0 | 2018-12-26 |
| 1 | 2018-12-27 |
| 1 | 2018-12-28 |
+-----------+------------+
但是我仍然需要用正确的日期和布尔值来填充结尾(例如,包括12-29和12-30作为真值),而且我不知道如何捕捉在这两者之间丢失的日期。。。
暂无答案!
目前还没有任何答案,快来回答吧!