clickhouse常用函数

本文记录一些工作中实际用到的clickhouse函数场景，涉及array、tuple、json、date等函数，文章内容会持续更新

• 生成最近30天日期

-- 今天是2023-02-16
select dateadd(today(), -1 * arrayJoin(range(30))) day

输出结果如下：

┌────────day─┐
│ 2023-02-16 │
│ 2023-02-15 │
│ 2023-02-14 │
......
│ 2023-01-19 │
│ 2023-01-18 │
└────────────┘

• 生成从昨天0点开始的48小时内的时间

select
  arrayJoin(
    arrayMap(
      i -> (
        formatDateTime(addHours(yesterday(), i), '%m-%d %H:%M')
      ),
      range(48)
    )
  ) hours

输出结果如下：

┌─hours───────┐
│ 02-15 00:00 │
│ 02-15 01:00 │
│ 02-15 02:00 │
......
│ 02-16 22:00 │
│ 02-16 23:00 │
└─────────────┘

• 将{"a": 1, "b": 2, "c": 3}格式的数据转换成只有2列的表格

SELECT tp.1 as k,tp.2 as v from (
    select arrayJoin(JSONExtractKeysAndValues('{"a": 1, "b": 2, "c": 3}', 'String')) as tp 
)

输出结果如下：

┌─k─┬─v─┐
│ a │ 1 │
│ b │ 2 │
│ c │ 3 │
└───┴───┘

• 将[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6},{"a":7,"b":8,"c":9}]格式的数据转换成只有a,b,c三列的表格

SELECT
  tp.1 as a,
  tp.2 as b,
  tp.3 as c
from
  (
    select
      arrayJoin(
        arrayMap(
          arr -> (
            JSONExtractString(arr, 'a'),
            JSONExtractString(arr, 'b'),
            JSONExtractString(arr, 'c')
          ),
          JSONExtract('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6},{"a":7,"b":8,"c":9}]', 'Array(String)')
        )
      ) as tp
  )

输出结果如下：

┌─a─┬─b─┬─c─┐
│ 1 │ 2 │ 3 │
│ 4 │ 5 │ 6 │
│ 7 │ 8 │ 9 │
└───┴───┴───┘

• 将数据库中两个json数组类型的字段合并成含有2列的一张表，例如["2023-02-09","2023-02-10","2023-02-11","2023-02-12","2023-02-13","2023-02-14","2023-02-15","2023-02-16"]和[59,60,60,66,59,61,57,55]两个数组

select
  tp.1 as data_date,
  tp.2 as data
from
  (
    select
      arrayJoin(
        arrayZip(
          JSONExtract('["2023-02-09","2023-02-10","2023-02-11","2023-02-12","2023-02-13","2023-02-14","2023-02-15","2023-02-16"]', 'Array(String)'),
          JSONExtract('[59,60,60,66,59,61,57,55]', 'Array(Int)')
        )
      ) as tp
  )

输出结果如下：

┌─data_date──┬─data─┐
│ 2023-02-09 │   59 │
│ 2023-02-10 │   60 │
│ 2023-02-11 │   60 │
│ 2023-02-12 │   66 │
│ 2023-02-13 │   59 │
│ 2023-02-14 │   61 │
│ 2023-02-15 │   57 │
│ 2023-02-16 │   55 │
└────────────┴──────┘