clickhouse的几个进阶语法

一、背景

  前面发布了一篇关于clickhouse常用的基础语法，有很多浏览量，这次给小伙伴分享几个进阶语法，比如如何实现分时统计，当然是通过我们的toStartOfDay()语法实现的，如何实现聚合某一列，如何更新操作，我会举几个例子供大家参考使用。
   想了解其他数据库语法，请看。

clickhouse的常用语法你知道吗
Mysql数据库常用命令总结
关于PostgreSQL数据增删改查的日常总结（主要jsonb类型）

二、更新语法

1、更新满足条件的数据列

ALTER TABLE mytable.mytable
UPDATE
    mytableType = 'A'
WHERE
    `time` >'2021-10-07'

2、更新表注释

ALTER TABLE table_with_comment MODIFY COMMENT 'new comment on a table';

1、重命名表的某一列

ALTER TABLE visits RENAME COLUMN webBrowser TO browser

1、更新某一列的字段类型

ALTER TABLE visits MODIFY COLUMN browser Array(String)

三、分时统计

  在开发中我们可能会有需求去计算每一条的订单数量返回给前台，假如我们通过常规的方法，开始时间大于早上零点，结束时间小于晚上24点，虽然可以满足需求，但是需要for循环多次查询获取每一条的数据，肯定效率低了，而且我们用的是clickhouse，号称最快的数据库，这么搞肯定没有用到极致，那么我们可以使用这么多方法，一次查询，返回所以结果，如下，我们有这么多方法，看到字面意思应该就明白了吧。

• toStartOfHour()

• toStartOfInterval()

• toStartOfFifteenMinutes()

• toStartOfFiveMinute()

• toStartOfMonth()

• toStartOfQuarter()

• toStartOfWeek()

• toStartOfDay()

1、按天分组求每一天的订单数

SELECT
    toStartOfDay(time) time_interval ,
    sum(order_num) num
FROM
    mytable.mytable
GROUP BY
    toStartOfDay(order_num)

我们有这么多方法，看到字面意思应该就明白了吧。

2、完成间隔5分钟的分时统计

select 
toStartOfInterval(time, INTERVAL 5  minute) as minute,
count() as sumcount
from table_all
group by minute 
order by sumcount desc;

3、获取过去七天的分时统计数据

  numbers(7)是我们的参照表，假如不使用，当某一天数据库里没有数据就不会查询出来，这样我们想获取七天的数据就会不够七天，不方便我们在代码中写业务逻辑，所以我们使用了一个参照表。

SELECT
        any(toDate(time)),
        any(num)
FROM
        (
                SELECT
                        toStartOfDay(toDate(toUnixTimestamp(now64(3), 'Asia/Shanghai')-number*3600*24)) time,
                        toUInt16(0) AS num
                FROM
                        numbers(7)

                UNION ALL

                SELECT
                        toStartOfDay(time) AS time,
                        count(*)           as num
                FROM
                        mytable.mytable
                group by
                        time
        )
GROUP BY
        time
ORDER BY
        time

4、带where条件的分时统计

  获取过去1000天的统计，当天没有数据的赋值为0。

SELECT
        toDate(time) as time,
        num
FROM
        (
                SELECT
                        toStartOfDay(toDate(toUnixTimestamp(now64(3), 'Asia/Shanghai')-number*3600*24)) time,
                        toUInt16(0) AS num
                FROM
                        numbers(1000)

                UNION ALL

                SELECT
                        toStartOfDay(time) AS time,
                        count(*)           as num
                FROM 
                        mytable.mytable   
                group by
                        time
        )


        where time >'2021-10-01'  and time <'2021-10-06'
GROUP BY
        time ,num
ORDER BY
        time   

5、按秒查询进行统计

SELECT
        time as time,
        num
FROM
        (
                SELECT
                        toStartOfSecond(toDateTime64(toUnixTimestamp(now()), 3)-number) time,
                        0 as num
                FROM
                        numbers(100000)

                UNION ALL

                SELECT
                        toStartOfSecond(thisTime) AS thisTime,
                        count(*)           as num
                FROM
                        mytable.mytable  where devIp ='1'
                group by
                        thisTime
        )


        where time >'2022-01-01'  and time <'2022-01-12'
GROUP BY
        time ,num
ORDER BY
        time

四、聚合某列

1、按地区分组聚合手机号

这个vm_concat方法可以将某个字段聚合到一个字段里，以逗号分割，我们查询结束后在代码中通过split（","）可以很方便获取某一个组的数据。

SELECT
    phone_attr AS area,
    wm_concat(phone_ip) AS phoneIps
FROM
    xda_phone
GROUP BY
    phone_attr

三、总结

  以上就是就是关于clickhouse数据库进阶语法，包含如何实现分时统计，如何实现聚合某一列，如何更新操作，可以参考一下，觉得不错的话，欢迎微信搜索关注java基础笔记，后面会不断更新相关知识，大家一起进步。