实例展示
===========


可用实例::

    统计过去1小时发生Error最多的3个位置:
      severity: ERROR | select module ,count(*) as count GROUP BY  module  ORDER BY count DESC LIMIT 30


    统计过去15分钟各种日志级别产生的日志条数:
      * | select severity ,count(*) as count GROUP BY severity ORDER BY count DESC

     对比不同时间段的聚类日志数量:
     *  | select 
                v.signature,  
                v.pattern, 
                coalesce(v.cmp[1],0) as count_now, 
                coalesce(v.cmp[2],0) as count_before, 
                coalesce(v.cmp[1],0) - coalesce(v.cmp[2],0) as count_diff 
          from (
              select 
                  p.signature as signature ,
                  arbitrary(p.pattern) as pattern , 
                  compare(p.count,300) as cmp 
              from 
                  (select log_reduce() as p from log) 
              group by p.signature
          )v  
          order by count_diff desc

Nginx日志分析::

    按小时统计PV/UV统计(pv_uv):
      * | select approx_distinct("__tag__:__client_ip__") as uv ,
         count(1) as pv , 
         date_format(date_trunc('hour', __time__), '%m-%d %H:%i')  as time
         group by date_format(date_trunc('hour', __time__), '%m-%d %H:%i')
         order by time
         limit 1000

    访问地域分析(ip_distribution):
       * | select ip_to_province(client_ip) as address, count(1) as c
         group by ip_to_province(client_ip)  
         order by c desc limit 100

       * | select ip_to_geo("c-ip") as country, count(1) as c group by ip_to_geo("c-ip") limit 100


    访问前十地址(top_page):
       * | select split_part(request_uri,'?',1) as path, count(1) as pv  
         group by split_part(request_uri,'?',1) 
         order by pv desc limit 10

    请求方法占比(http_method_percentage):
       * | select request_method, count(1) as pv
           group by request_method
           order by pv DESC

    请求状态占比(http_status_percentage):    
      * | select status, count(1) as pv
         group by status
         order by pv DESC

    请求UA占比(user_agent):
       * | select count(1) as pv,
         case when http_user_agent like '%Chrome%' then 'Chrome' 
         when http_user_agent like '%Firefox%' then 'Firefox' 
         when http_user_agent like '%Safari%' then 'Safari'
         else 'unKnown' end as http_user_agent
         group by  http_user_agent
         order by pv desc
         limit 10

    前十访问来源(top_10_referer):
       * | select http_referer, count(1) as pv
         group by http_referer
         order by pv desc limit 10

    浏览流入流出统计(net_in_net_out):
      *| select 
          sum("body_bytes_sent") as net_out, 
          sum("request_length") as net_in ,
          date_format(date_trunc('hour', __time__), '%m-%d %H:%i') as time 
      group by date_format(date_trunc('hour', __time__), '%m-%d %H:%i') 
      order by time 
      limit 10000



Nginx访问日志诊断及优化::

    统计平均延时和最大延时:
      * | select from_unixtime(__time__ -__time__% 300) as time, 
          avg(request_time) as avg_latency ,
          max(request_time) as max_latency  
          group by __time__ -__time__% 300

    统计最大延时对应的请求页面:
      * | select from_unixtime(__time__ - __time__% 60) , 
          max_by(request_uri,request_time)  
          group by __time__ - __time__%60

    统计请求延时的分布(分为10个桶进行近似分布直方统计):
      * |select numeric_histogram(10,request_time)

    统计最大的十个延时:
      * | select max(request_time,10)

    对延时最大的页面调优:
      request_uri:"/url2" | select count(1) as pv,
          approx_distinct(remote_addr) as uv,
          histogram(method) as method_pv,
          histogram(status) as status_pv,
          histogram(user_agent) as user_agent_pv,
          avg(request_time) as avg_latency,
          max(request_time) as max_latency


其他实例::

    统计过去1小时;登录次数最多的三个用户:
    login | SELECT regexp_extract(message, 'userID=(?<userID>[a-zA-Z\d]+)', 1) AS userID, count(*) as count GROUP BY userID ORDER BY count DESC LIMIT 3

    统计过去15分钟;每个用户的付款总额:
    order | SELECT regexp_extract(message, 'userID=(?<userID>[a-zA-Z\d]+)', 1) AS userID, sum(cast(regexp_extract(message, 'amount=(?<amount>[a-zA-Z\d]+)', 1) AS double)) AS amount GROUP BY userID

    在整个公司的人员中;获取每个人的薪水在部门内排名
      * | select department, persionId, sallary , 
        rank() over(PARTITION BY department order by sallary desc) as sallary_rank  
        order by department,sallary_rank

    在整个公司的人员中;获取每个人的薪水在部门内的占比
      * | select department, persionId, sallary *1.0 / sum(sallary) over(PARTITION BY department ) as sallary_percentage

    按天统计;获取每天UV相对前一天的增长情况
      * | select day ,uv, uv *1.0 /(lag(uv,1,0) over() ) as diff_percentage from
        (
          select approx_distinct(ip) as uv, date_trunc('day',__time__) as day 
          from log group by day order by day asc
        )


    统计每小时的PV、UV和最高延时对应的用户请求，延时最高的10个延时:
    *|select date_trunc('hour',from_unixtime(__time__)) as time, 
     count(1) as pv, 
     approx_distinct(userid)  as uv,
     max_by(url,latency) as top_latency_url,
     max(latency,10) as top_10_latency
     group by  1
     order by time