<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clip

支持case when： <div class="highlight highlight-source-sql notranslate position-relat

<a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/us

请问这个可以写case when吗？ about elasticsearch-sql HOT 2 OPEN

qidai77777 commented on June 8, 2024

请问这个可以写case when吗？

from elasticsearch-sql.

Comments (2)

shi-yuan commented on June 8, 2024 1

支持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"
      }
    }
  }
}

from elasticsearch-sql.

TommyLemon commented on June 8, 2024

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

from elasticsearch-sql.

Recommend Projects