Q:

How can I count the number of times each edge is traveled given a trajectory using SQL?

The trajectory is represented as a table like the following, you can think of it as some object walks in a graph following the edges B->A->C->A->B.
+ ---- + ---- +
| time | node |
+ ---- + ---- +
| 1    | B    |
| 2    | A    |
| 3    | C    |
| 4    | A    |
| 5    | B    |
+ ---- + ---- +

I want to count the number of times each (directed) edge is traveled using SQL, how should I do this?

Right now what I did is duplicating each row with time+1 like this
+ ---- + ---- +
| time | node |
+ ---- + ---- +
| 1    | B    |
| 2    | B    |
| 2    | A    |
| 3    | A    |
| 3    | C    |
| 4    | C    |
| 4    | A    |
| 5    | A    |
| 5    | B    |
| 6    | B    |
+ ---- + ---- +

then I group by time and aggregate the node as described here, and I can get something like
+ ---- + ----- +
| edge | count |
+ ---- + ----- +
| B,A  | 1     |
| A,C  | 1     |
| C,A  | 1     |
| A,B  | 1     |
+ ---- + ----- +

However the problem is, I need to run it on some distributed environment, where it seems the order of group by results are not guaranteed, so sometimes B->A and A->B will both come out as A,B, which is not what I want since the edges are considered directed.
What can I do to preserve the order?
 Thanks in advance.

A:

You can do that using a window function:
select node, next_node, count(*)
from (
  select node, lead(node) over (order by time) as next_node
  from nodes
) t
where next_node is not null
group by node, next_node
order by node;

You didn't state your DBMS, but the above is standard SQL without any product specific syntax. 
Online example: http://rextester.com/WUVB19983

