We are trying to create a query to do an aggregation on the data below. This is a skinny table, with log data for operations happening in a set of equipment. The equipment is identified in the "ID" column. In this sample, there are two equipment. We need to total the column "Value" based on the label in column "Label_2". The aggregation starts from the latest "Ct-Primary" label, and ends when it finds the label 'Out Track'. So for equipment "C409EA83-A2C6-39F5-A8BC-C9E91BA7A756" it would start aggregating at Time "5/2/2023 14:30" and end at time "5/5/2023 6:00" and then again from time "5/9/2023 0:00" and ends at "5/11/2023 18:00." It would do the same for the rest of the equipment ID in the list.So the result I expect for this data sample would be:
ID Total
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 153.00
97B398D7-9BBC-D036-1CC2-C828F0F22243 118.50
So far I have tried a few things with the over function, but that only picks by the ID and aggregates all the values in that ID. I tried adding an over by ID and one of the labels and it gives me the total value for "Ct-Primary" only but not the window I want to aggregate. Sample:
Time Value Label_2 ID
5/2/2023 12:00 0.75 Rolling C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 12:45 1.75 Ct-Primary C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 14:30 9.50 Ct-Primary C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 15:30 1.00 NU/ND WH/XT C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 16:30 4.00 NU/ND C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 20:30 0.50 NU/ND C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 21:00 3.00 NU/ND C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 0:00 3.00 Casing C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 3:00 3.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 6:00 14.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 20:00 4.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 7:00 5.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 12:00 8.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 20:00 4.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 0:00 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 6:00 7.00 Out Track C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 10:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 10:30 13.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/6/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/6/2023 7:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/6/2023 7:30 16.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 7:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 7:30 12.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 20:00 4.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 7:00 0.50 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 7:30 13.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 21:00 3.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 0:00 5.00 Ct-Primary C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 6:00 2.25 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 8:15 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 8:30 10.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 19:00 5.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 0:00 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 6:00 6.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 12:30 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 12:45 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 13:00 2.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 15:30 2.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 18:00 1.00 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 19:00 5.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 0:00 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 6:00 0.50 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 6:30 11.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 18:00 9.00 Out Track C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 0:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 0:30 3.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 4:00 0.75 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 4:45 1.25 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 6:00 0.50 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 6:30 5.75 Rig Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 12:15 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 12:30 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 12:00 1.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 12:45 0.50 Safety Mtg 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 14:30 4.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 15:30 2.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 16:30 2.75 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 20:30 0.75 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 21:00 12.00 Ct-Primary 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 0:00 2.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 3:00 1.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 6:00 2.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 20:00 6.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 0:00 12.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 7:00 6.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 12:00 6.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 20:00 2.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 0:00 9.50 NU/ND 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 6:00 3.00 NU/ND 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 10:00 0.50 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 10:30 2.50 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/6/2023 0:00 3.00 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/6/2023 7:00 3.00 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/6/2023 7:30 6.50 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 0:00 2.50 RU/RD 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 7:00 3.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 7:30 6.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 20:00 2.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 0:00 4.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 7:00 14.50 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 7:30 3.50 PU/LD BA 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 21:00 3.50 PU/LD Pipe 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 0:00 1.00 Out Track 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 6:00 1.00 PU/LD Pipe 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 8:15 0.50 Test Csg 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 8:30 7.00 PU/LD Pipe 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 19:00 1.50 RU/RD 97B398D7-9BBC-D036-1CC2-C828F0F22243
We are trying to create a query to do an aggregation on the data below. This is a skinny table, with log data for operations happening in a set of equipment. The equipment is identified in the "ID" column. In this sample, there are two equipment. We need to total the column "Value" based on the label in column "Label_2". The aggregation starts from the latest "Ct-Primary" label, and ends when it finds the label 'Out Track'. So for equipment "C409EA83-A2C6-39F5-A8BC-C9E91BA7A756" it would start aggregating at Time "5/2/2023 14:30" and end at time "5/5/2023 6:00" and then again from time "5/9/2023 0:00" and ends at "5/11/2023 18:00." It would do the same for the rest of the equipment ID in the list.So the result I expect for this data sample would be:
ID Total
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 153.00
97B398D7-9BBC-D036-1CC2-C828F0F22243 118.50
So far I have tried a few things with the over function, but that only picks by the ID and aggregates all the values in that ID. I tried adding an over by ID and one of the labels and it gives me the total value for "Ct-Primary" only but not the window I want to aggregate. Sample:
Time Value Label_2 ID
5/2/2023 12:00 0.75 Rolling C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 12:45 1.75 Ct-Primary C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 14:30 9.50 Ct-Primary C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 15:30 1.00 NU/ND WH/XT C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 16:30 4.00 NU/ND C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 20:30 0.50 NU/ND C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 21:00 3.00 NU/ND C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 0:00 3.00 Casing C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 3:00 3.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 6:00 14.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/3/2023 20:00 4.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 7:00 5.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 12:00 8.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/4/2023 20:00 4.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 0:00 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 6:00 7.00 Out Track C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 10:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/5/2023 10:30 13.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/6/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/6/2023 7:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/6/2023 7:30 16.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 7:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 7:30 12.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/7/2023 20:00 4.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 0:00 7.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 7:00 0.50 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 7:30 13.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/8/2023 21:00 3.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 0:00 5.00 Ct-Primary C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 6:00 2.25 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 8:15 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 8:30 10.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/9/2023 19:00 5.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 0:00 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 6:00 6.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 12:30 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 12:45 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 13:00 2.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 15:30 2.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 18:00 1.00 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/10/2023 19:00 5.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 0:00 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 6:00 0.50 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 6:30 11.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/11/2023 18:00 9.00 Out Track C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 0:00 0.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 0:30 3.50 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 4:00 0.75 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 4:45 1.25 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 6:00 0.50 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 6:30 5.75 Rig Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 12:15 0.25 Safety Mtg C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/12/2023 12:30 6.00 EQ Mob C409EA83-A2C6-39F5-A8BC-C9E91BA7A756
5/2/2023 12:00 1.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 12:45 0.50 Safety Mtg 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 14:30 4.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 15:30 2.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 16:30 2.75 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 20:30 0.75 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/2/2023 21:00 12.00 Ct-Primary 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 0:00 2.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 3:00 1.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 6:00 2.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/3/2023 20:00 6.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 0:00 12.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 7:00 6.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 12:00 6.00 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/4/2023 20:00 2.50 EQ Mob 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 0:00 9.50 NU/ND 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 6:00 3.00 NU/ND 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 10:00 0.50 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/5/2023 10:30 2.50 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/6/2023 0:00 3.00 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/6/2023 7:00 3.00 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/6/2023 7:30 6.50 BP Test 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 0:00 2.50 RU/RD 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 7:00 3.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 7:30 6.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/7/2023 20:00 2.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 0:00 4.00 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 7:00 14.50 EQ Repair 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 7:30 3.50 PU/LD BA 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/8/2023 21:00 3.50 PU/LD Pipe 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 0:00 1.00 Out Track 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 6:00 1.00 PU/LD Pipe 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 8:15 0.50 Test Csg 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 8:30 7.00 PU/LD Pipe 97B398D7-9BBC-D036-1CC2-C828F0F22243
5/9/2023 19:00 1.50 RU/RD 97B398D7-9BBC-D036-1CC2-C828F0F22243
Share
Improve this question
asked Jan 17 at 20:55
Luis GarciaLuis Garcia
1,4017 gold badges20 silver badges41 bronze badges
3 Answers
Reset to default 1Syntax is in SQL Server as I dont have trino to test.
I have broken down it into two CTEs.
label
CTE ranks the valid labelsct-primary
,out track
for each id.
label
CTE outputs
select id,time,label_2,
row_number() over (partition by id order by time) as rn
from log
where label_2 in ('ct-primary', 'out track')
id | time | label_2 | rank |
---|---|---|---|
97B398D7-9BBC-D036-1CC2-C828F0F22243 | 2023-05-02 21:00:00.000 | Ct-Primary | 1 |
97B398D7-9BBC-D036-1CC2-C828F0F22243 | 2023-05-09 00:00:00.000 | Out Track | 2 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-02 12:45:00.000 | Ct-Primary | 1 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-02 14:30:00.000 | Ct-Primary | 2 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-05 06:00:00.000 | Out Track | 3 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-09 00:00:00.000 | Ct-Primary | 4 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-11 18:00:00.000 | Out Track | 5 |
- Next is
valid_pairs
CTE which SELF JOINS where the label starts with latestct-primary
and ends without track
and there would be a difference of 1 in the rank between the consecutive records, so it would return something like this
id | start_time | end_time |
---|---|---|
97B398D7-9BBC-D036-1CC2-C828F0F22243 | 2023-05-02 21:00:00.000 | 2023-05-09 00:00:00.000 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-02 14:30:00.000 | 2023-05-05 06:00:00.000 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2023-05-09 00:00:00.000 | 2023-05-11 18:00:00.000 |
Finally the valid_pairs
CTE is joined with the log
table based on id
and the time between valid pairs's start_time
and end_time
and calculate the aggregated value grouped by id
select l.id,sum(l.value) as total
from log l
inner join valid_pairs vp
on l.id = vp.id and l.time between vp.start_time and vp.end_time
group by l.id
Final Query :
Demo Fiddle
with labels as (
select id,time,label_2,
row_number() over (partition by id order by time) as rank
from log
where label_2 in ('ct-primary', 'out track')
),
valid_pairs as
(
select l1.id, l1.time as start_time, l2.time as end_time
from labels l1
inner join labels l2 on l1.id = l2.id and l2.rank = l1.rank + 1
where l1.label_2 = 'ct-primary' and l2.label_2 = 'out track'
)
select l.id,sum(l.value) as total
from log l
inner join valid_pairs vp
on l.id = vp.id and l.time between vp.start_time and vp.end_time
group by l.id
order by l.id;
Output
id | total |
---|---|
97B398D7-9BBC-D036-1CC2-C828F0F22243 | 118.5 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 153 |
You can generate a counter over the set by running backward so that every Out Track defines the start of a new block. Then within each block you seek the Ct-Primary labels in order to rope off the desired sub-block of values to keep. When looking in reverse you'll always be grabbing the first one and discarding the rest.
You do have to do an adjustment so that Ct-Primary will become the last row rather then the first row of the new sub-block. A lagged value or a window frame could handle this but I just used a simple case
expression that checks whether the current label is Ct-Primary to deduct one.
In the end you just use those generated (sub)block numbers to filter out the rows you don't want:
with b1 as (
select *,
count(case when Label_2 = 'Out Track' then 1 end)
over (partition by ID order by "Time" desc) as block1
from T
), b2 as (
select *,
count(case when Label_2 = 'Ct-Primary' then 1 end)
over (partition by ID, block1 order by "Time" desc)
+ case when Label_2 = 'Ct-Primary' then -1 else 0 end + 1 as block2
from b1
)
select ID, sum(Value)
from b2
where block1 >= 1 and block2 = 1
group by ID;
https://dbfiddle.uk/fwlmAskB?hide=2
(This does assume there won't be multiple Out Track labels between successive Ct-Primary labels.)
If the values are all positive, here's another approach that uses running sums to derive the desired sum:
with b as (
select *,
count(case when Label_2 = 'Out Track' then 1 end)
over (partition by ID order by "Time" desc) as blk,
sum(Value)
over (partition by ID order by "Time" desc) as rs
from T
), s as (
select ID,
min(case when Label_2 = 'Ct-Primary' then rs end) - min(rs - value) as s
from b
where blk >= 1
group by ID, blk
)
select ID, sum(s)
from s
group by ID;
I suggest use running sum (total)
of value
.
Id | Label_2 | Value | runningTotal | groupValue |
---|---|---|---|---|
A | 3 | 3 | ||
A | Ct-Primary | 10 | 13 | |
A | 5 | 18 | ||
A | Out Track | 22 | 40 | (40-13+10)=37=(10+5+22) |
In this solution, I like that only one sorting (or covering index (id,time)include(Label2,value)) is needed.
All window functions use (partition by id order by time)
- Subquery
a
calculate running sum. - Subquery
b
takes only rows with Label_2 in('Ct-Primary','Out Track') and calculategroupValue
for rows with (Label_2='Out Track') as
- (+) score (running sum) for current row (Label_2='Out Track')
- (-) score of previous row (Label_2='Ct-Primary')
- (+) value of previous row (Label_2='Ct-Primary')
See example
select id
,sum(groupValue) tot
from(
select *
,lag(Label_2)over(partition by id order by time)prevLabel_2
,score -lag(score,1,0)over(partition by id order by time)
+lag(value,1,0)over(partition by id order by time)groupValue
from(
select * ,sum(value)over(partition by id order by time)score
from log
) a
where Label_2 in('Ct-Primary','Out Track')
)b
where Label_2='Out Track' and prevLabel_2='Ct-Primary'
group by id
id | tot |
---|---|
97B398D7-9BBC-D036-1CC2-C828F0F22243 | 118.5 |
C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 153 |
Before filter where Label_2='Out Track' and prevLabel_2='Ct-Primary'
and grouping
select *
,lag(Label_2)over(partition by id order by time)prevLabel_2
,score -lag(score,1,0)over(partition by id order by time)
+lag(value,1,0)over(partition by id order by time)groupValue
from(
select * ,sum(value)over(partition by id order by time)score
from log
) a
where Label_2 in('Ct-Primary','Out Track')
Time | Value | Label_2 | ID | score | prevLabel_2 | groupValue |
---|---|---|---|---|---|---|
2023-05-02 21:00:00.000 | 12 | Ct-Primary | 97B398D7-9BBC-D036-1CC2-C828F0F22243 | 23.5 | null | 23.5 |
2023-05-09 00:00:00.000 | 1 | Out Track | 97B398D7-9BBC-D036-1CC2-C828F0F22243 | 130 | Ct-Primary | 118.5 =130-23.5+12 |
2023-05-02 12:45:00.000 | 1.75 | Ct-Primary | C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 2.5 | null | 2.5 |
2023-05-02 14:30:00.000 | 9.5 | Ct-Primary | C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 12 | Ct-Primary | 11.25 |
2023-05-05 06:00:00.000 | 7 | Out Track | C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 81.5 | Ct-Primary | 79 |
2023-05-09 00:00:00.000 | 5 | Ct-Primary | C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 172.5 | Out Track | 98 |
2023-05-11 18:00:00.000 | 9 | Out Track | C409EA83-A2C6-39F5-A8BC-C9E91BA7A756 | 241.5 | Ct-Primary | 74 |
fiddle