抱歉，如果您以前在这里曾问过这个问题，但我似乎找不到。我一直在寻找每小时的总和，但是我的问题是关于另一列中定义的时间戳之间的SUM和COUNT。
我有一个表叫incoming_orders：它显示了预期的目标，并传入订单的时间戳。
我还有第二张表Scheduled_output：它显示每个目的地的每个计划输出时刻。
我有第三个表称为Outgoing_orders：它显示了实际的目的地以及发出订单的时间戳。
因此，数据可能是：
--Incoming_orders:
Destination Timestamp
ROUTE B 14/03/2018 7:48:00
ROUTE A 14/03/2018 7:58:00
ROUTE A 14/03/2018 12:48:00
ROUTE C 14/03/2018 13:28:00
--Scheduled_Output
ROUTE A 14/03/2018 8:00:00
ROUTE A 14/03/2018 11:00:00
ROUTE A 14/03/2018 12:00:00
ROUTE A 14/03/2018 17:00:00
ROUTE B 14/03/2018 8:00:00
ROUTE B 14/03/2018 10:00:00
ROUTE B 14/03/2018 12:00:00
ROUTE C 14/03/2018 07:00:00
ROUTE C 14/03/2018 14:00:00
ROUTE C 14/03/2018 17:00:00
--Which would lead to the following outgoing_orders:
ROUTE A 14/03/2018 8:00:00
ROUTE B 14/03/2018 8:00:00
ROUTE C 14/03/2018 14:00:00
ROUTE A 14/03/2018 17:00:00
现在，我要检查路由A的07:58传入顺序是否确实使其进入了路由A的08:00输出周期。我正在考虑创建一个像这样的表来显示它：
Destination output moment expected_output actual_output diff
Route A 8:00 1 1 0
Route A 11:00 0 0 0
Route A 12:00 0 0 0
Route A 17:00 1 1 0
但是问题是：如何计算Expected_output列？如何将12:48的路线A的接收订单分组到12：00-17：00组？它应该计算计划的输出时刻之间的所有订单，但是我不确定如何实现。
我能否将CEIL，FLOOR或ROUND设置为最接近schedule_output的值？还是可以通过行计数以某种方式在第n行和第n + 1行之间进行操作？还是有另一种更简单的方法？
我认为以这种方式或多或少地确定计划输出的先前时间，获得时间间隔是最简单的：
SELECT destination,
time_stamp,
( SELECT max( time_stamp )
FROM SCHEDULED_OUTPUT t1
WHERE t.destination = t1.destination
AND t1.time_stamp < t.time_stamp
) as previous_time_stamp
FROM SCHEDULED_OUTPUT t
order by 1,2
或更紧凑的形式使用分析功能：
SELECT destination,
time_stamp,
lag( time_stamp ) over (partition by destination order by time_stamp )
as previous_time_stamp
FROM SCHEDULED_OUTPUT t
order by 1,2
演示：http : //sqlfiddle.com/#!4/c7bc9/1
| DESTINATION | TIME_STAMP | PREVIOUS_TIME_STAMP |
|-------------|-----------------------|-----------------------|
| ROUTE A | 2018-03-14 08:00:00.0 | (null) |
| ROUTE A | 2018-03-14 11:00:00.0 | 2018-03-14 08:00:00.0 |
| ROUTE A | 2018-03-14 12:00:00.0 | 2018-03-14 11:00:00.0 |
| ROUTE A | 2018-03-14 17:00:00.0 | 2018-03-14 12:00:00.0 |
| ROUTE B | 2018-03-14 08:00:00.0 | (null) |
| ROUTE B | 2018-03-14 10:00:00.0 | 2018-03-14 08:00:00.0 |
| ROUTE B | 2018-03-14 12:00:00.0 | 2018-03-14 10:00:00.0 |
| ROUTE C | 2018-03-14 07:00:00.0 | (null) |
| ROUTE C | 2018-03-14 14:00:00.0 | 2018-03-14 07:00:00.0 |
| ROUTE C | 2018-03-14 17:00:00.0 | 2018-03-14 14:00:00.0 |
接下来，可以将上述结果集加入INCOMING_ORDERS以便计算计数：
SELECT x.destination, x.time_stamp as output_moment,
count( y.DESTINATION ) as expected_output
FROM (
SELECT destination,
time_stamp,
lag( time_stamp ) over (partition by destination order by time_stamp )
as previous_time_stamp
FROM SCHEDULED_OUTPUT t
) x
LEFT JOIN INCOMING_ORDERS y
ON x.DESTINATION = y.DESTINATION
AND y.TIME_STAMP <= x.TIME_STAMP
AND ( y.TIME_STAMP > x.previous_time_stamp OR x.previous_time_stamp IS NULL )
GROUP BY x.destination, x.time_stamp
ORDER BY 1,2
演示：http : //sqlfiddle.com/#!4/c3958/2
| DESTINATION | OUTPUT_MOMENT | EXPECTED_OUTPUT |
|-------------|-----------------------|-----------------|
| ROUTE A | 2018-03-14 08:00:00.0 | 1 |
| ROUTE A | 2018-03-14 11:00:00.0 | 0 |
| ROUTE A | 2018-03-14 12:00:00.0 | 0 |
| ROUTE A | 2018-03-14 17:00:00.0 | 1 |
| ROUTE B | 2018-03-14 08:00:00.0 | 1 |
| ROUTE B | 2018-03-14 10:00:00.0 | 0 |
| ROUTE B | 2018-03-14 12:00:00.0 | 0 |
| ROUTE C | 2018-03-14 07:00:00.0 | 0 |
| ROUTE C | 2018-03-14 14:00:00.0 | 1 |
| ROUTE C | 2018-03-14 17:00:00.0 | 0 |
这种情况：
AND y.TIME_STAMP <= x.TIME_STAMP
AND ( y.TIME_STAMP > x.previous_time_stamp OR x.previous_time_stamp IS NULL )
告诉您，如果某个订单在8:00:00下达，而该路线在同一时间8:00:00开始，则该订单仍会分配给该“开始”路线。如果这是不可能的（也就是说，当订单被放置在下一条路线的确切时间时，则必须将其分配给下一条路线），然后将条件更改为：
AND y.TIME_STAMP < x.TIME_STAMP
AND ( y.TIME_STAMP >= x.previous_time_stamp OR x.previous_time_stamp IS NULL )
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句