如何从couchbase获取缺少值的列名

gdx19jrr  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(400)

我正在运行一个没有当前日期值的查询,我尝试了ifmissing()函数来获取列名,但在某些情况下不起作用,我不知道原因。
我的问题是:

SELECT SPLIT(DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,'.')[0],7,‘hour'),‘T') [0],
    ADD (TONUMBER(SPLIT(SPLIT (ev.auditDetail.createTime, 'T')[1], ‘:')[0]),7) AS HOUR,
    COUNT (*) AS COUNT
    FROM data_KH ev
    WHERE type_ = ‘user’
    AND DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,‘.')[0],7,‘hour’) >
    CLOCK_TZ (‘Asia/Bangkok', '1111-11-11‘)
    AND SPLIT (loginId,‘@‘)[1] NOT IN [‘yopmail.com', ‘ymail.com', ‘accoliteindia.com']
    GROUP BY SPLIT (DATE_ADD_STR(SPLIT(ev. auditDetail.createTime,'.')[0],7,'hour'),‘T‘) [0],
    ADD (TONUMBER (SPLIT (SPLIT (ev.auditDetail.createTime, ‘T')[1], ‘:')[0]),7)
    ORDER BY SPLIT (DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,'.')[0],7,'hour'),‘T') [0],
    ADD (TONUMBER (SPLIT (SPLIT (ev. auditDetail.createTime, 'T')[1], ':')[0]),7)
3ks5zfa0

3ks5zfa01#

子查询结果是数组,如果没有结果,则长度为0(不丢失)。子查询结果上存在/不存在。因为您也需要结果,不希望重复查询两次。您也可以使用以下方法。
cb 6.5或以上

WITH result AS (SELECT DATE_FORMAT_STR(crdate, "2020-01-01") AS date,
                       DATE_PART_STR(crdate, "hour") AS hour,
                       COUNT (1) AS count
                FROM data_KH AS ev
                LET crdate = DATE_ADD_STR(SPLIT(ev.auditDetail.createTime, ".")[0], 7, "hour")
                WHERE type_ = "user"
                    AND crdate > CLOCK_TZ ("Asia/Bangkok", "1111-11-11")
                    AND SPLIT (loginId,"@")[1] NOT IN ["yopmail.com", "ymail.com", "accoliteindia.com"]
                GROUP BY DATE_FORMAT_STR(crdate, "2020-01-01"), DATE_PART_STR(crdate, "hour"))
SELECT d.*
FROM (CASE WHEN ARRAY_LENGTH(result) > 0 THEN result ELSE [{"date": CLOCK_TZ("Asia/Bangkok","2020-01-01"),
                                                      "hour":DATE_PART_STR(CLOCK_TZ("Asia/Bangkok"), "hour"),
                                                      "count":0}]
                                               END) AS d
ORDER BY d.date, d.hour;

cb 6.50之前

SELECT d.*
FROM ARRAY_FLATTEN ((SELECT RAW CASE WHEN ARRAY_LENGTH(result) > 0
                                    THEN result
                                    ELSE [{"date": CLOCK_TZ("Asia/Bangkok","2020-01-01"),
                                           "hour":DATE_PART_STR(CLOCK_TZ("Asia/Bangkok"), "hour"),
                                           "count":0}]
                                    END
                    LET result = (SELECT DATE_FORMAT_STR(crdate, "2020-01-01") AS date,
                                         DATE_PART_STR(crdate, "hour") AS hour,
                                         COUNT (1) AS count
                                  FROM data_KH AS ev
                                  LET crdate = DATE_ADD_STR(SPLIT(ev.auditDetail.createTime, ".")[0], 7, "hour")
                                  WHERE type_ = "user"
                                      AND crdate > CLOCK_TZ ("Asia/Bangkok", "1111-11-11")
                                      AND SPLIT (loginId,"@")[1] NOT IN ["yopmail.com", "ymail.com", "accoliteindia.com"]
                                  GROUP BY DATE_FORMAT_STR(crdate, "2020-01-01"), DATE_PART_STR(crdate, "hour"))),1) AS d
ORDER BY d.date, d.hour;

您也可以使用str\u to \u tz(ev.auditdetail.createtime,“亚洲/曼谷”)代替date\u add \u str(split(ev.auditdetail.createtime,“.”[0],7,“hour”)https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html

jtw3ybtb

jtw3ybtb2#

我已经修改了主查询有子查询,并创建了静态结果,如果没有在主查询中找到结果

SELECT [{'$1':split (DATE_ADD_STR(SPLIT(clock_utc(),'.')[0],7,hour),'T')[0], COUNT':0,
HOUR' : add(TONUMBER(SPLIT (clock_utc('11:11:11'), ':')[0],7)}] AS ‘alterData’,
(SELECT SPLIT(DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,'.')[0],7,‘hour'),‘T') [0],
    ADD (TONUMBER(SPLIT(SPLIT (ev.auditDetail.createTime, 'T')[1], ‘:')[0]),7) AS HOUR,
    COUNT (*) AS COUNT
    FROM data_KH ev
    WHERE type_ = ‘user’
    AND DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,‘.')[0],7,‘hour’) >
    CLOCK_TZ (‘Asia/Bangkok', '1111-11-11‘)
    AND SPLIT (loginId,‘@‘)[1] NOT IN [‘yopmail.com', ‘ymail.com', ‘accoliteindia.com']
    GROUP BY SPLIT (DATE_ADD_STR(SPLIT(ev. auditDetail.createTime,'.')[0],7,'hour'),‘T‘) [0],
    ADD (TONUMBER (SPLIT (SPLIT (ev.auditDetail.createTime, ‘T')[1], ‘:')[0]),7)
    ORDER BY SPLIT (DATE_ADD_STR(SPLIT (ev. auditDetail.createTime,'.')[0],7,'hour'),‘T') [0],
    ADD (TONUMBER (SPLIT (SPLIT (ev. auditDetail.createTime, 'T')[1], ':')[0]),7)) AS ‘data’

相关问题