Turn to the great god SQL passengers statistics-CodePudding

Everyone a great god, I met a problem, I now have so a table
USER_ID TIME IN_ID OUT_ID
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
00001 2017/1/1 8:00 01 05
00001 2017/1/1 20:00 05 01
00025 2017/1/1 14:00 07 91
00006 2017/1/1 9:00 02 45
.
.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
USER_ID on behalf of the user number, TIME represents the journey TIME, IN_ID represents the inbound platform code, OUT_ID on behalf of the outbound coding platform. A total of 93 sites, there are 93 coding,

Loop: A passenger in A day by standing in A B standing out, again from B stand in A stand out, A - B - A it to form A loop, as long as there is A passenger is the journey, loop occurs, such as article 1 and article 2 data, to form A 01 -- 05-01 loop

Now I need to query a table, a week data from 2017/1/1 -- 2017/1/7
The first column is time, such as 2017/1/1
Today the second column is the number of all the kinds of circuits, such as A - B - A and B - A - B are two circuits,
The third column is the sum of all of the circuit today, is today's passenger travel path for loop,

If not be a query, you can get into two tables then merged,

Thank you all for the great god

CodePudding user response:

This complex queries on SQL alone not so easy to make out the
Even if written may be difficult to memory, it is hard to understand, hard to correction
Write a stored procedure should consider what
Or to handle with external advanced language, such as written in python scripts

CodePudding user response:

1, judging circuit, I immediately thought of using lag
2, the merged data back to the round
3, grouping statistics
No time, etc. In the evening to write

CodePudding user response:

Can use statistical analysis function, table or stupid way is to check for 2 times, one is A pit stop and B outbound, one is B into A outbound, then merge the results