ClickHouse聚合-按日/月/年(时间戳)分组？

Question

在ClickHouse中有没有办法使用时间戳值按日/月/年()进行分组？在将MySQL查询重写为ClickHouse时，很难弄清楚。我的MySQL查询看起来像这样...

SELECT COUNT(this), COUNT(that) FROM table WHERE something = x AND stamp BETWEEN startdate AND enddate

SELECT COUNT(this), COUNT(that) FROM table WHERE something = x AND stamp BETWEEN startdate AND enddate GROUP BY DAY(stamp)

SELECT COUNT(this), COUNT(that) FROM table WHERE something = x AND stamp BETWEEN startdate AND enddate GROUP BY MONTH(stamp)

SELECT COUNT(this), COUNT(that) FROM table WHERE something = x AND stamp BETWEEN startdate AND enddate GROUP BY YEAR(stamp)

在MySQL中相当简单和缓慢，但我不知道如何在ClickHouse中做聚合。

谢谢!

Answer 1

要获取部分日期，请通过以下方式使用函数toYear，toMonth，toDayOfMonth：

SELECT
    toMonth(time) AS month,
    count()
FROM 
(
    SELECT
        number,
        addDays(now(), number) AS time
    FROM numbers(8)
)
GROUP BY month

/*
┌─month─┬─count()─┐
│     1 │       7 │
│     2 │       1 │
└───────┴─────────┘
*/

要获取多个分组集，请使用WITH ROLLUP-modifier：

SELECT
    toYear(time) AS year,
    toMonth(time) AS month,
    toDayOfMonth(time) AS day,
    count()
FROM 
(
    SELECT
        number,
        addDays(now(), number) AS time
    FROM numbers(8)
)
GROUP BY
    year,
    month,
    day
    WITH ROLLUP

/*
┌─year─┬─month─┬─day─┬─count()─┐
│ 2021 │     2 │   1 │       1 │ // day
│ 2021 │     1 │  29 │       1 │ // day
│ 2021 │     1 │  31 │       1 │ // day
│ 2021 │     1 │  26 │       1 │ // day
│ 2021 │     1 │  25 │       1 │ // day
│ 2021 │     1 │  28 │       1 │ // day
│ 2021 │     1 │  30 │       1 │ // day
│ 2021 │     1 │  27 │       1 │ // day
│ 2021 │     1 │   0 │       7 │ // month
│ 2021 │     2 │   0 │       1 │ // month
│ 2021 │     0 │   0 │       8 │ // year
│    0 │     0 │   0 │       8 │
└──────┴───────┴─────┴─────────┘
*/