科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql - Window over query not retrieving what I need - Stack Overflow

    sql - Window over query not retrieving what I need - Stack Overflow

    programmeradmin5浏览0评论

    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
    • sql
    • trino
    Share Improve this question asked Jan 17 at 20:55 Luis GarciaLuis Garcia 1,4017 gold badges20 silver badges41 bronze badges
    Add a comment  | 

    3 Answers 3

    Reset to default 1

    Syntax is in SQL Server as I dont have trino to test.

    I have broken down it into two CTEs.

    • label CTE ranks the valid labels ct-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 latest ct-primary and ends with out 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 NU/ND 3 3
    A Ct-Primary 10 13
    A Safety Mtg 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)

    1. Subquery a calculate running sum.
    2. Subquery b takes only rows with Label_2 in('Ct-Primary','Out Track') and calculate groupValue 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

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论