Clickhouse Group Statistics Alarm Bug

[frank-lam]

When you use clickhouse sql to draw a graph chart in Grafana, if the SQL statement group by is performed for fields other than t, the following hexadecimal information is displayed in the alarm result. As a result, the value of the grouped field cannot be properly displayed, which is unfriendly to alarm experience. If only t is grouped, the above problem does not occur. It is hoped that this problem will be resolved in later versions.

grafana version: 7.5.17

SELECT
    toUnixTimestamp(toStartOfInterval(`timestamp`, INTERVAL 5 minute)) * 1000 as t,
    brand,
    count(*) as cnt
FROM xxx.xxx
WHERE
    $timeFilter64ByColumn(timestamp)
    and brand is not null and  brand != ''
GROUP BY t,brand
ORDER BY t,brand asc

this is test rule result：

2:Object
message:"Condition[0]: Eval: true, Metric: 0xc0001245b0 {brand=0xc0001245d0}, Value: 2386.000"
data:null
3:Object
message:"Condition[0]: Eval: false, Metric: 0xc000124640 {brand=0xc000124660}, Value: 4.000"
data:null
4:Object
message:"Condition[0]: Eval: true, Metric: 0xc0001246d0 {brand=0xc0001246f0}, Value: 25.000"
data:null

---

The preceding problem does not occur when the elasticsearch plug-in is used for query.

[Slach]

try to change SQL query to

SELECT
    t,
    groupArray((brand, cnt)) AS groupArr
FROM (

SELECT
    toUnixTimestamp(toStartOfInterval(`timestamp`, INTERVAL 5 minute)) * 1000 as t,
    brand,
    count(*) as cnt
FROM xxx.xxx
WHERE
    $timeFilter64ByColumn(timestamp)
    and brand is not null and  brand != ''
GROUP BY t,brand
ORDER BY t,brand
)
GROUP BY t
ORDER BY t

[frank-lam]

it works! thanks

[frank-lam]

@Slach However, if I have more than 1 aggregate latitude, how do I write SQL. Help!

        select
            toUnixTimestamp(
                toStartOfInterval(`timestamp`, INTERVAL 1 minute)
            ) * 1000 as t,
            JSONExtractString(args, 'creatorOrgId') as creatorOrgId,
            confId,
            count(1) as cnt
        FROM
            meeting_ops_dlg.meeting_cmas_conference
        WHERE
            $timeFilter64ByColumn(timestamp)
            and eventId = 'MMR_CONF_PARTICIPANT_NUM'
        GROUP BY
            t,
            creatorOrgId,
            confId
        ORDER BY
            t asc

[Slach]

SELECT
    t,
    groupArray((metric_name, cnt)) AS groupArr
FROM 
(
 SELECT
            toUnixTimestamp(
                toStartOfInterval(`timestamp`, INTERVAL 1 minute)
            ) * 1000 as t,
            concat(JSONExtractString(args, 'creatorOrgId') as creatorOrgId,'::',confId) AS metric_name,
            count(1) as cnt
        FROM
            meeting_ops_dlg.meeting_cmas_conference
        WHERE
            $timeFilter64ByColumn(timestamp)
            and eventId = 'MMR_CONF_PARTICIPANT_NUM'
        GROUP BY
            t,
            metric_name
        ORDER BY
            t asc
)
GROUP BY t
ORDER BY t