获取实时浏览量和IP数（mysql）

前言

在统计的时候，获取实时的浏览量和ip数，需要用到带有时间格式的sql语句

浏览量 今日实时获取数据

SELECT COUNT(t2.HOUR) dateCount FROM (
    SELECT DATE_FORMAT(@cdate :=DATE_ADD(@cdate,INTERVAL-1 HOUR),'%y-%m-%d %H') HOUR FROM (
    SELECT @cdate :=DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d %H'),INTERVAL+1 HOUR) FROM tc_statistics_oper_log) t0 LIMIT 24) t1 LEFT JOIN (
    SELECT ip,DATE_FORMAT(create_time,'%y-%m-%d %H') HOUR FROM tc_statistics_oper_log WHERE DATE_FORMAT(create_time,'%y-%m-%d')=DATE_FORMAT(NOW(),'%y-%m-%d')) t2 ON t1.HOUR=t2.HOUR WHERE DATE_FORMAT(t1.HOUR,'%y-%m-%d')=DATE_FORMAT(NOW(),'%y-%m-%d') GROUP BY t1.HOUR ORDER BY t1.HOUR ASC

浏览量 获取昨日24小时数据

SELECT t1.HOUR date,COUNT(t2.HOUR) dateCount FROM (
    SELECT DATE_FORMAT(@cdate :=DATE_ADD(@cdate,INTERVAL-1 HOUR),'%y-%m-%d %H') HOUR FROM (
    SELECT @cdate :=DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d %H'),INTERVAL+1 HOUR) FROM tc_statistics_oper_log) t0 LIMIT 48) t1 LEFT JOIN (
    SELECT DATE_FORMAT(create_time,'%y-%m-%d %H') HOUR FROM tc_statistics_oper_log WHERE DATE_FORMAT(create_time,'%y-%m-%d')=DATE_FORMAT(date_sub(NOW(),interval 1 day),'%y-%m-%d')) t2 ON t1.HOUR=t2.HOUR WHERE DATE_FORMAT(t1.HOUR,'%y-%m-%d')=DATE_FORMAT(date_sub(NOW(),interval 1 day),'%y-%m-%d') GROUP BY t1.HOUR ORDER BY t1.HOUR ASC

IP数 实时获取IP数

SELECT COUNT(DISTINCT t2.ip) ipCount FROM (
    SELECT DATE_FORMAT(@cdate :=DATE_ADD(@cdate,INTERVAL-1 HOUR),'%y-%m-%d %H') HOUR FROM (
    SELECT @cdate :=DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d %H'),INTERVAL+1 HOUR) FROM tc_statistics_oper_log) t0 LIMIT 24) t1 LEFT JOIN (
    SELECT ip,DATE_FORMAT(create_time,'%y-%m-%d %H') HOUR FROM tc_statistics_oper_log WHERE DATE_FORMAT(create_time,'%y-%m-%d')=DATE_FORMAT(NOW(),'%y-%m-%d')) t2 ON t1.HOUR=t2.HOUR WHERE DATE_FORMAT(t1.HOUR,'%y-%m-%d')=DATE_FORMAT(NOW(),'%y-%m-%d') GROUP BY t1.HOUR ORDER BY t1.HOUR ASC

IP数 获取昨日24小时数据

SELECT t1.HOUR date,COUNT(DISTINCT t2.ip) dateCount FROM (
    SELECT DATE_FORMAT(@cdate :=DATE_ADD(@cdate,INTERVAL-1 HOUR),'%y-%m-%d %H') HOUR FROM (
    SELECT @cdate :=DATE_ADD(DATE_FORMAT(NOW(),'%y-%m-%d %H'),INTERVAL+1 HOUR) FROM tc_statistics_oper_log) t0 LIMIT 48) t1 LEFT JOIN (
    SELECT ip,DATE_FORMAT(create_time,'%y-%m-%d %H') HOUR FROM tc_statistics_oper_log WHERE DATE_FORMAT(create_time,'%y-%m-%d')=DATE_FORMAT(date_sub(NOW(),interval 1 day),'%y-%m-%d')) t2 ON t1.HOUR=t2.HOUR WHERE DATE_FORMAT(t1.HOUR,'%y-%m-%d')=DATE_FORMAT(date_sub(NOW(),interval 1 day),'%y-%m-%d') GROUP BY t1.HOUR ORDER BY t1.HOUR ASC