RaqForum 41 No.
120 View  • 
     ...

SQL, compute cumulative sums according to the termination condition

 

In MSSQL database, data table stores elevator operation records, where turn is the order of a person entering the elevator. The maximum capacity of the elevator is 1,000 kg. There is the capacity limit for the number of passengers it can carry in each stop and the extra passenger(s) need to wait for the next round.

name

weight

turn

Alice

250

1

Bob

170

2

Alex

350

3

John

400

4

Winston

500

5

Marie

200

6

We want to get the list of the last passengers entering the elevator in all stops.

Alex

Winston

Marie

Write the following SPL code:

A

1

=sqlServer1.query("select * from data order by turn")

2

>cum=0

3

=A1.group@i(if( (cum+=weight)>1000, cum=weight, null))

4

=A3.(~.m(-1).name)

A1: Retrieve data in SQL and sort data by turn field.

A2: Set initial value for the cumulative variable.

A3: Group data by each elevator stop. When the cumulative value is greater than 1,000, create a new group and reset the cumulative value as the current total passenger weight.

A4: Get the name field of the last record from each group.

Source:https://stackoverflow.com/questions/78442803/list-of-all-the-last-people-to-enter-the-elevator


 
 •  120 View  •