system.query_log常用SQL

-- 每天的慢SQL数量
select event_date, count(*) num from system.query_log ql where
    ql.query_duration_ms > 1000
    and event_date >= '2022-07-01'
    and event_date < '2022-08-03'
group by event_date


-- 一条SQL在这一天执行多少次
select count(*) from (
    select query,count(*)
    from system.query_log
        where query_duration_ms > 1000
            and event_date >= toDate('2022-07-23')
            and event_date <= toDate('2022-07-23')
            and query like '%SELECT sum(duration) AS sum_duration, toYYYYMMDD(toDate(end_time)) AS play_date FROM dss_prod.apr_view_student_v1%'
        group by query having count(*) < 2) c


select
    -- count(*)
    user,
    formatDateTime(query_start_time, '%Y%m%d %T') AS start_time,
    query_duration_ms / 1000 AS query_duration_s,
    query
from system.query_log
where 
    query_duration_ms > 3000
    and type = 2
    and event_date >= toDate('2022-07-23')
    and event_date <= toDate('2022-07-23')
order by query_duration_s DESC
limit 100


select
    -- count(*)
    user,
    formatDateTime(query_start_time, '%Y%m%d %H:%M') AS start_time,
    query_duration_ms / 1000 AS query_duration_s,
    query
from system.query_log
where
    query_duration_ms > 1000
    and type = 2
    and event_date >= toDate('2022-07-23')
    and event_date <= toDate('2022-07-23')
order by start_time ASC
-- group by start_time
limit 100


-- 一条SQL多少个
select
    -- formatDateTime(query_start_time, '%Y%m%d %H:%M') AS start_time,
    count(*) as num,
    -- query_duration_ms / 1000 AS query_duration_s,
    query
from system.query_log
where
    query_duration_ms > 1000
    and type = 2
    and event_date >= toDate('2022-07-23')
    and event_date <= toDate('2022-07-23')
group by
    -- start_time,
    -- query_duration_ms / 1000,
    query
having count(*) > 10
order by num DESC


-- 1min多少条SQL
select
    formatDateTime(query_start_time, '%Y%m%d %H:%M') AS start_time,
    count(*)
    -- query_duration_ms / 1000 AS query_duration_s,
    -- query
from system.query_log
where
    query_duration_ms > 1000
    and type = 2
    and event_date >= toDate('2022-07-23')
    and event_date <= toDate('2022-07-23')
group by
    start_time
    -- query_duration_ms / 1000 ,
    -- query
having count(*) > 50
order by start_time ASC