请问这个可以写case when吗？

[qidai77777]

    select
        sum(case when DATE_FORMAT(data_time,'%H')>0 and DATE_FORMAT(data_time,'%H')<9 then data_value else 0 end) freeTime,
        sum(case when DATE_FORMAT(data_time,'%H')>9 and DATE_FORMAT(data_time,'%H')<18 then data_value else 0 end) dayTime,
        sum(data_value)/count(DISTINCT DATE_FORMAT(data_time,'%Y-%m-%d')) avgCount,
        sum(data_value) totalCount
            from
        table_name
这种条件可以一条sql写出来吗？

[shi-yuan]

支持case when：

select a,case when c='1' then 'haha' when c='2' then 'book' else 'hbhb' end as gg from tbl_a group by a,gg

不过上面语句不支持，可以用script：

select
sum(script("","def df = Integer.parseInt(DateTimeFormatter.ofPattern('HH').withZone(ZoneId.systemDefault()).format(Instant.ofEpochMilli(doc['data_time'].value.getMillis())));if(df>0 && df<9){doc['data_value'].value} else {0}")) freeTime,
sum(script("","def df = Integer.parseInt(DateTimeFormatter.ofPattern('HH').withZone(ZoneId.systemDefault()).format(Instant.ofEpochMilli(doc['data_time'].value.getMillis())));if(df>9 && df<18){doc['data_value'].value} else {0}")) dayTime,
count(DISTINCT script("","DateTimeFormatter.ofPattern('yyyy-MM-dd').withZone(ZoneId.systemDefault()).format(Instant.ofEpochMilli(doc['data_time'].value.getMillis()))")) countDateTime,
sum(data_value) totalCount
from
table_name

{
  "from": 0,
  "size": 0,
  "_source": {
    "includes": [
      "sum",
      "sum",
      "count",
      "sum"
    ],
    "excludes": []
  },
  "aggregations": {
    "freeTime": {
      "sum": {
        "script": {
          "source": "def df = Integer.parseInt(DateTimeFormatter.ofPattern('HH').withZone(ZoneId.systemDefault()).format(Instant.ofEpochMilli(doc['data_time'].value.getMillis())));if(df>0 && df<9){doc['data_value'].value} else {0}",
          "lang": "painless"
        }
      }
    },
    "dayTime": {
      "sum": {
        "script": {
          "source": "def df = Integer.parseInt(DateTimeFormatter.ofPattern('HH').withZone(ZoneId.systemDefault()).format(Instant.ofEpochMilli(doc['data_time'].value.getMillis())));if(df>9 && df<18){doc['data_value'].value} else {0}",
          "lang": "painless"
        }
      }
    },
    "countDateTime": {
      "cardinality": {
        "script": {
          "source": "DateTimeFormatter.ofPattern('yyyy-MM-dd').withZone(ZoneId.systemDefault()).format(Instant.ofEpochMilli(doc['data_time'].value.getMillis()))",
          "lang": "painless"
        }
      }
    },
    "totalCount": {
      "sum": {
        "field": "data_value"
      }
    }
  }
}

[TommyLemon]

@shi-yuan 希望能直接支持，这样能兼容更广泛的应用场景。
APIJSON 支持 SQL 及各种 SQL 方言，但不支持这个 script（看起来不是存储过程，而是类似 Java 的自定义脚本）。
目前 APIJSON 就是通过 elasticsearch-sql 来支持 Elasticsearch：
https://github.com/Tencent/APIJSON