clickhouse - how get count datetime per 1minute or 1day ,

0

I have a table in Clickhouse. for keep statistics and metrics.


and structure is:

datetime|metric_name|metric_value

I want to keep statistics and limit number of accesses in 1 minute, 1 hour, 1 day and so on. So I need event counts in last minute, hour or day for every metric_name and I want to prepare statistics in a chart.


I do not know how to make a query. I get the count of metrics statistics based on the exact for example 1 minute, 1 hour, 1 day and so on.

I used to work on inflxdb:

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND time >= now() - 1h GROUP BY time(5m) fill(0)

In fact, I want to get the number of each metric per 5 minutes in the previous 1 hour.

I do not know how to use aggregations for this problem

clickhouse

share|improve this question

edited Jan 2 at 13:55

aziminia

asked Jan 2 at 13:49

aziminiaaziminia

15619

add a comment | 

0

I have a table in Clickhouse. for keep statistics and metrics.


and structure is:

datetime|metric_name|metric_value

I want to keep statistics and limit number of accesses in 1 minute, 1 hour, 1 day and so on. So I need event counts in last minute, hour or day for every metric_name and I want to prepare statistics in a chart.


I do not know how to make a query. I get the count of metrics statistics based on the exact for example 1 minute, 1 hour, 1 day and so on.

I used to work on inflxdb:

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND time >= now() - 1h GROUP BY time(5m) fill(0)

In fact, I want to get the number of each metric per 5 minutes in the previous 1 hour.

I do not know how to use aggregations for this problem

clickhouse

share|improve this question

edited Jan 2 at 13:55

aziminia

asked Jan 2 at 13:49

aziminiaaziminia

15619

add a comment | 

0

0

0

I have a table in Clickhouse. for keep statistics and metrics.


and structure is:

datetime|metric_name|metric_value

I want to keep statistics and limit number of accesses in 1 minute, 1 hour, 1 day and so on. So I need event counts in last minute, hour or day for every metric_name and I want to prepare statistics in a chart.


I do not know how to make a query. I get the count of metrics statistics based on the exact for example 1 minute, 1 hour, 1 day and so on.

I used to work on inflxdb:

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND time >= now() - 1h GROUP BY time(5m) fill(0)

In fact, I want to get the number of each metric per 5 minutes in the previous 1 hour.

I do not know how to use aggregations for this problem

clickhouse

share|improve this question

edited Jan 2 at 13:55

aziminia

asked Jan 2 at 13:49

aziminiaaziminia

15619

I have a table in Clickhouse. for keep statistics and metrics.


and structure is:

datetime|metric_name|metric_value

I want to keep statistics and limit number of accesses in 1 minute, 1 hour, 1 day and so on. So I need event counts in last minute, hour or day for every metric_name and I want to prepare statistics in a chart.


I do not know how to make a query. I get the count of metrics statistics based on the exact for example 1 minute, 1 hour, 1 day and so on.

I used to work on inflxdb:

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND time >= now() - 1h GROUP BY time(5m) fill(0)

In fact, I want to get the number of each metric per 5 minutes in the previous 1 hour.

I do not know how to use aggregations for this problem

clickhouse

clickhouse

share|improve this question

edited Jan 2 at 13:55

aziminia

asked Jan 2 at 13:49

aziminiaaziminia

15619

share|improve this question

edited Jan 2 at 13:55

aziminia

asked Jan 2 at 13:49

aziminiaaziminia

15619

share|improve this question

share|improve this question

edited Jan 2 at 13:55

aziminia

edited Jan 2 at 13:55

aziminia

edited Jan 2 at 13:55

aziminia

asked Jan 2 at 13:49

aziminiaaziminia

15619

asked Jan 2 at 13:49

aziminiaaziminia

15619

asked Jan 2 at 13:49

aziminiaaziminia

15619

15619

add a comment | 

add a comment | 

1 Answer
1

active

oldest

votes

1

ClickHouse has functions for generating Date/DateTime group buckets such as toStartOfWeek, toStartOfHour, toStartOfFiveMinute. You can also use intDiv function to manually divide value ranges. However the fill feature is still in the roadmap.

For example, you can rewrite the influx sql without the fill in ClickHouse like this,

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND 

time >= now() - 1h GROUP BY toStartOfFiveMinute(time)

You can also refer to this discussion https://github.com/yandex/ClickHouse/issues/379

update

There is a timeSlots function that can help generating empty buckets. Here is a working example

SELECT

    slot,

    metric_value_sum

FROM

(

    SELECT

        toStartOfFiveMinute(datetime) AS slot,

        SUM(metric_value) AS metric_value_sum

    FROM metrics

    WHERE (metric_name = 'k1') AND (datetime >= (now() - toIntervalHour(1)))

    GROUP BY slot

)

ANY RIGHT JOIN

(

    SELECT arrayJoin(timeSlots(now() - toIntervalHour(1), toUInt32(3600), 300)) AS slot

) USING (slot)

share|improve this answer

edited Jan 3 at 9:56

answered Jan 2 at 15:17

AmosAmos

1,80831029

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? stackoverflow.com/a/50325639/1348284
  
  – aziminia
  Jan 3 at 8:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @aziminia I've updated my answer
  
  – Amos
  Jan 3 at 9:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds.
  
  – aziminia
  Jan 3 at 10:40
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Here is the expression that rounds to every 10 seconds toDateTime(intDiv(toUInt32(now()), 10) * 10); hope you get the idea
  
  – Amos
  Jan 3 at 13:14
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007512%2fclickhouse-how-get-count-datetime-per-1minute-or-1day%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

1

ClickHouse has functions for generating Date/DateTime group buckets such as toStartOfWeek, toStartOfHour, toStartOfFiveMinute. You can also use intDiv function to manually divide value ranges. However the fill feature is still in the roadmap.

For example, you can rewrite the influx sql without the fill in ClickHouse like this,

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND 

time >= now() - 1h GROUP BY toStartOfFiveMinute(time)

You can also refer to this discussion https://github.com/yandex/ClickHouse/issues/379

update

There is a timeSlots function that can help generating empty buckets. Here is a working example

SELECT

    slot,

    metric_value_sum

FROM

(

    SELECT

        toStartOfFiveMinute(datetime) AS slot,

        SUM(metric_value) AS metric_value_sum

    FROM metrics

    WHERE (metric_name = 'k1') AND (datetime >= (now() - toIntervalHour(1)))

    GROUP BY slot

)

ANY RIGHT JOIN

(

    SELECT arrayJoin(timeSlots(now() - toIntervalHour(1), toUInt32(3600), 300)) AS slot

) USING (slot)

share|improve this answer

edited Jan 3 at 9:56

answered Jan 2 at 15:17

AmosAmos

1,80831029

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? stackoverflow.com/a/50325639/1348284
  
  – aziminia
  Jan 3 at 8:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @aziminia I've updated my answer
  
  – Amos
  Jan 3 at 9:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds.
  
  – aziminia
  Jan 3 at 10:40
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Here is the expression that rounds to every 10 seconds toDateTime(intDiv(toUInt32(now()), 10) * 10); hope you get the idea
  
  – Amos
  Jan 3 at 13:14
  

  
  
  
  

add a comment | 

1

ClickHouse has functions for generating Date/DateTime group buckets such as toStartOfWeek, toStartOfHour, toStartOfFiveMinute. You can also use intDiv function to manually divide value ranges. However the fill feature is still in the roadmap.

For example, you can rewrite the influx sql without the fill in ClickHouse like this,

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND 

time >= now() - 1h GROUP BY toStartOfFiveMinute(time)

You can also refer to this discussion https://github.com/yandex/ClickHouse/issues/379

update

There is a timeSlots function that can help generating empty buckets. Here is a working example

SELECT

    slot,

    metric_value_sum

FROM

(

    SELECT

        toStartOfFiveMinute(datetime) AS slot,

        SUM(metric_value) AS metric_value_sum

    FROM metrics

    WHERE (metric_name = 'k1') AND (datetime >= (now() - toIntervalHour(1)))

    GROUP BY slot

)

ANY RIGHT JOIN

(

    SELECT arrayJoin(timeSlots(now() - toIntervalHour(1), toUInt32(3600), 300)) AS slot

) USING (slot)

share|improve this answer

edited Jan 3 at 9:56

answered Jan 2 at 15:17

AmosAmos

1,80831029

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? stackoverflow.com/a/50325639/1348284
  
  – aziminia
  Jan 3 at 8:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @aziminia I've updated my answer
  
  – Amos
  Jan 3 at 9:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds.
  
  – aziminia
  Jan 3 at 10:40
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Here is the expression that rounds to every 10 seconds toDateTime(intDiv(toUInt32(now()), 10) * 10); hope you get the idea
  
  – Amos
  Jan 3 at 13:14
  

  
  
  
  

add a comment | 

1

1

1

ClickHouse has functions for generating Date/DateTime group buckets such as toStartOfWeek, toStartOfHour, toStartOfFiveMinute. You can also use intDiv function to manually divide value ranges. However the fill feature is still in the roadmap.

For example, you can rewrite the influx sql without the fill in ClickHouse like this,

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND 

time >= now() - 1h GROUP BY toStartOfFiveMinute(time)

You can also refer to this discussion https://github.com/yandex/ClickHouse/issues/379

update

There is a timeSlots function that can help generating empty buckets. Here is a working example

SELECT

    slot,

    metric_value_sum

FROM

(

    SELECT

        toStartOfFiveMinute(datetime) AS slot,

        SUM(metric_value) AS metric_value_sum

    FROM metrics

    WHERE (metric_name = 'k1') AND (datetime >= (now() - toIntervalHour(1)))

    GROUP BY slot

)

ANY RIGHT JOIN

(

    SELECT arrayJoin(timeSlots(now() - toIntervalHour(1), toUInt32(3600), 300)) AS slot

) USING (slot)

share|improve this answer

edited Jan 3 at 9:56

answered Jan 2 at 15:17

AmosAmos

1,80831029

ClickHouse has functions for generating Date/DateTime group buckets such as toStartOfWeek, toStartOfHour, toStartOfFiveMinute. You can also use intDiv function to manually divide value ranges. However the fill feature is still in the roadmap.

For example, you can rewrite the influx sql without the fill in ClickHouse like this,

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND 

time >= now() - 1h GROUP BY toStartOfFiveMinute(time)

You can also refer to this discussion https://github.com/yandex/ClickHouse/issues/379

update

There is a timeSlots function that can help generating empty buckets. Here is a working example

SELECT

    slot,

    metric_value_sum

FROM

(

    SELECT

        toStartOfFiveMinute(datetime) AS slot,

        SUM(metric_value) AS metric_value_sum

    FROM metrics

    WHERE (metric_name = 'k1') AND (datetime >= (now() - toIntervalHour(1)))

    GROUP BY slot

)

ANY RIGHT JOIN

(

    SELECT arrayJoin(timeSlots(now() - toIntervalHour(1), toUInt32(3600), 300)) AS slot

) USING (slot)

share|improve this answer

edited Jan 3 at 9:56

answered Jan 2 at 15:17

AmosAmos

1,80831029

share|improve this answer

share|improve this answer

edited Jan 3 at 9:56

edited Jan 3 at 9:56

edited Jan 3 at 9:56

answered Jan 2 at 15:17

AmosAmos

1,80831029

answered Jan 2 at 15:17

AmosAmos

1,80831029

answered Jan 2 at 15:17

AmosAmos

1,80831029

1,80831029

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? stackoverflow.com/a/50325639/1348284
  
  – aziminia
  Jan 3 at 8:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @aziminia I've updated my answer
  
  – Amos
  Jan 3 at 9:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds.
  
  – aziminia
  Jan 3 at 10:40
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Here is the expression that rounds to every 10 seconds toDateTime(intDiv(toUInt32(now()), 10) * 10); hope you get the idea
  
  – Amos
  Jan 3 at 13:14
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? stackoverflow.com/a/50325639/1348284
  
  – aziminia
  Jan 3 at 8:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @aziminia I've updated my answer
  
  – Amos
  Jan 3 at 9:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds.
  
  – aziminia
  Jan 3 at 10:40
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Here is the expression that rounds to every 10 seconds toDateTime(intDiv(toUInt32(now()), 10) * 10); hope you get the idea
  
  – Amos
  Jan 3 at 13:14
  

  
  
  
  

Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? stackoverflow.com/a/50325639/1348284

– aziminia
Jan 3 at 8:25

Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? stackoverflow.com/a/50325639/1348284

– aziminia
Jan 3 at 8:25

@aziminia I've updated my answer

– Amos
Jan 3 at 9:57

@aziminia I've updated my answer

– Amos
Jan 3 at 9:57

Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds.

– aziminia
Jan 3 at 10:40

Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds.

– aziminia
Jan 3 at 10:40

1

1

Here is the expression that rounds to every 10 seconds toDateTime(intDiv(toUInt32(now()), 10) * 10); hope you get the idea

– Amos
Jan 3 at 13:14

Here is the expression that rounds to every 10 seconds toDateTime(intDiv(toUInt32(now()), 10) * 10); hope you get the idea

– Amos
Jan 3 at 13:14

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007512%2fclickhouse-how-get-count-datetime-per-1minute-or-1day%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here