SQL统计占比

假设access_log表有src_ip、user_agent、url三个字段，每一次请求产生一条记录(行)，现在想统计访问量前10的IP、访问量count及访问量占比percent

第一种方式：直接从多个表查询数据

SELECT
  a.src_ip,
  a.count,
  round(a.count * 100 / b.total, 2) as percent
FROM
  (
    SELECT
      src_ip,
      count() as count
    FROM
      access_log
    GROUP BY
      src_ip
    ORDER BY
      count DESC
    LIMIT
      10
  ) a,(
    SELECT
      count() as total
    FROM
      access_log
  ) b

第二种方式：子查询作为分母

SELECT
  src_ip,
  count() as count,
  round(
    count * 100 /(
      SELECT
        count() as total
      FROM
        access_log
    ),
    2
  ) as percent
FROM
  access_log
GROUP BY
  src_ip
ORDER BY
  count DESC
LIMIT
  10