是否有办法在ClickHouse做一个组按日/月/年()与时间戳值?在为ClickHouse重写MySQL查询时很难弄清楚。我的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中做聚合。
谢谢!
使用toYear, tommonth, 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 │
└──────┴───────┴─────┴─────────┘
*/