在按另一列分组后选择符合某些条件的最大值行

here is the fiddle with data table

满足结果的条件。

  1. 始终首选c4列值不具有“ TIMECONDITION_16”的值。
  2. 最好将c5值的行作为ANSWER,如果对于特定c3不存在,则使用最大c5值。
  3. 对于最大值c5,行的c4值为“ TIMECONDITION_16”,请检查下一个最大值,然后选择不等于c4值的行“ TIMECONDITION_16”
  4. 按c3列分组
  5. 仅当c3的“ TIMECONDITION_16”之外没有其他c4值时,才使用该行在其中选择最大c5值。
  6. 我尝试过的查询在小提琴链接中。

这是预期的结果:

   c1              c2                    c3           c4      c5        c6
("5623",    "2020-04-17 11:00:43",  "16126448261",  "743",  "651",  "ANSWERED"),
("5589",    "2020-04-17 11:04:04",  "13144354833",  "262",  "375",  "ANSWERED"),    
("6053",    "2020-04-17 11:25:42",  "14356320423",  "328",  "217",  "ANSWERED"),    
("6518",    "2020-04-17 11:45:17",  "18032698599",  "TIMECONDITION_16", "8",    "ANSWERED"),
("7029",    "2020-04-17 12:05:34",  "15132930238",  "436",  "109",  "ANSWERED"),
("7202",    "2020-04-17 12:09:22",  "14055505637",  "398",  "216",  "ANSWERED"),    
("8166",    "2020-04-17 12:58:52",  "13134606503",  "642",  "1",    "NO ANSWER"),
("8665",    "2020-04-17 13:32:48",  "14042856627",  "259",  "651",  "ANSWERED"),
("0474",    "2020-04-17 15:11:25",  "18168245309",  "265",  "75",   "ANSWERED"),
("1893",    "2020-04-17 16:19:40",  "12762522792",  "265",  "90",   "ANSWERED"),
("0511",    "2020-05-05 09:28:04",  "4079676820",   "328",  "3",    "ANSWERED"),
("0838",    "2020-05-05 09:39:48",  "8153198873",   "398",  "86",   "ANSWERED"),
("0780",    "2020-05-05 09:40:11",  "5175419973",   "436",  "23",   "ANSWERED"),
评论