CODEWITHSUNDEEP
statisticsaverageclickhouse
Kirill
Kirill

Reputation: 8341

Calculate peek and average timestamps per minute in ClickHouse

I have log entries in ClickHouse database, each log has a timestamp. I need to calculate two numbers:

timestamp                      | entry   |
-------------------------------|---------|
2022-03-08T22:28:02.177113916Z | message |

To solve this right now I created a simple Python script to analyze downloaded logs, but this doesn't work with actual amount of data, only small slice which I can download.

Can I calculate just by running query without downloading anything locally?

Upvotes: 0

Views: 724

Answers (1)

vladimir
vladimir

Reputation: 15226

Try this query:

SELECT avg(c) average_count, max(c) peak_count
FROM (
    SELECT count() c
    FROM logs
    /* WHERE timestamp >= 'time1' AND timestamp < 'time2' */
    GROUP BY toStartOfMinute(timestamp)
    )

Upvotes: 2

Related Questions