Conditionally Sum values in a Column Based on Row

175
November 24, 2017, at 10:03 AM

I have a table that describes a closed path of bus stops, as well as the buses that drive along the path:

stop_id | order_in_route | time_from_last_stop | bus_id
------------------------------------------------------
3       | 1              | 5                   | 3
6       | 2              | 10                  | NULL
2       | 3              | 5                   | NULL
1       | 4              | 15                  | 2
9       | 5              | 10                  | NULL

Things to note:

  1. stop_id is unique
  2. order_in_route is unique
  3. time_from_last_stop is not unique
  4. bus_id is unique (unless NULL)
  5. the table must contain at least 1 NON-NULL bus_id

This basically means that each loop of the path crosses every stop once and only once, and each stop has its own unique order in the route. Finally, there is at least 1 bus driving along the route at any given time, and if multiple buses are driving, they are unique, and are never at the same stop at once.

In this case, the circular path would be (with the time between each stop noted within the ->):

... stop 9 -5> stop 3 -10> stop 6 -5> stop 2 -15> stop 1 -10> stop 9 -5> stop 3...

Currently, there is a bus at stop 3 and stop 1. So, the time for a bus to arrive at these 2 stops is 0, and the bus arriving at each stop is the bus currently there:

stop_id | order_in_route | time_for_bus_to_arrive | bus_id
-----------------------------------------------------------
3       | 1              | 0                      | 3
1       | 4              | 0                      | 2

To calculate the time for a bus to arrive at every stop without a bus currently there, sum the time_from_last_stops for yourself and every stop behind you without a bus currently at the stop, until you reach a stop with a bus currently at the stop. Also, to calculate the bus that will arrive at it, you find the closest bus behind you.

For stop 6:

time_for_a_bus_to_arrive(stop 6) = time_from_last_stop(stop 6)
                                 = 10
bus_id(stop 6) = 3

For stop 2:

time_for_a_bus_to_arrive(stop 2) = time_from_last_stop(stop 2) + time_from_last_stop(stop 6)
                                 = 5 + 10
                                 = 15
bus_id(stop 2) = 3

For stop 9:

time_for_a_bus_to_arrive(stop 9) = time_from_last_stop(stop 9)
                                 = 10
bus_id(stop 9) = 2

So the final table should be:

stop_id | order_in_route | time_for_bus_to_arrive | bus_id
------------------------------------------------------
3       | 1              | 10                     | 3
6       | 2              | 5                      | 3
2       | 3              | 15                     | 3
1       | 4              | 10                     | 2
9       | 5              | 5                      | 2

My question, is how to achieve this with a single SELECT query in MySQL? I do not know how to implement the human-readable logic above to a statement in MySQL.

What I've tried so far is (#'s note commented code that I do not know how to structure):

SELECT stop_id, order_in_route, SUM(time_from_last_stop
  WHERE bus_id IS NULL # AND order_in_route is earlier in path AND is after a bus_id that is NOT NULL
) AS time_for_bus_to_arrive, (SELECT bus_id
  FROM firstTable
  WHERE bus_id IS NOT NULL # AND closest behind in path
) AS bus_id
FROM firstTable;

This is clearly improper syntax, but I think the idea is clear.

Rent Charter Buses Company
READ ALSO
How to Return a “COMMENT” set to a “TABLE”?

How to Return a “COMMENT” set to a “TABLE”?

In MySQL-Workbench you're able to see comments that are set to tables (see image below), but I've been unable to find a way to pull that information via SQL QueryIf you can write the COMMENT value, you should be able to read it as well, right?

244
(Hibernate, mysql) Caused by: java.sql.SQLException: Field 'id' doesn't have a default value

(Hibernate, mysql) Caused by: java.sql.SQLException: Field 'id' doesn't have a default value

I have an application which was running on H2 database and Hibernate is the ORM toolCurrently, I am changing this application to use mysql database instead of H2 database and while doing this I came to this issue when saving flagjp entity

333
loopback auto create stored procedure on npm run init

loopback auto create stored procedure on npm run init

I am working on database and I found loopback supports stored procedure

225