db/mysql

mysql 통계자료 이전 쿼리

C/H 2010. 10. 26. 08:30

INSERT INTO sta.sta 
  SELECT
    CONCAT(
      replace(IF(a._request_time IS NOT NULL, 
          DATE(FROM_UNIXTIME(a._request_time)),
          DATE(CONCAT(a.request_year, '-', a.request_month, '-', a.request_day)) ),
        '-', '')
      , IF(mem_idx IS NOT NULL, CONCAT('-', mem_idx), '-' )
      , IF(uri_idx IS NOT NULL, CONCAT('-', uri_idx), '-' )
      , IF(browser_idx IS NOT NULL, CONCAT('-', browser_idx), '-')
      , IF(host_idx IS NOT NULL, CONCAT('-', host_idx), '-')
      , IF(platform_idx IS NOT NULL, CONCAT('-', platform_idx), '-')
      , IF(ref_idx IS NOT NULL, CONCAT('-', ref_idx), '-')
      , IF(req_idx IS NOT NULL, CONCAT('-', req_idx), '-')
      , IF(mobile IS NOT NULL, CONCAT('-', mobile), '-')
      , IF(crawler IS NOT NULL, CONCAT('-', crawler), '-')
      , IF(remote_addr IS NOT NULL, CONCAT('-',  INET_ATON(remote_addr)), '-')
    ) AS sta_idx
    ,IF(a._request_time!=NULL, 
      DATE(FROM_UNIXTIME(a._request_time)),
      DATE(CONCAT(a.request_year, '-', a.request_month, '-', a.request_day))
    ) AS sta_date
    ,mem_idx
    ,uri_idx
    ,browser_idx
    ,host_idx
    ,platform_idx
    ,ref_idx
    ,req_idx
    ,mobile
    ,crawler
    ,INET_ATON(a.remote_addr) AS addr
    ,count(*) AS cnt
  FROM pickup.analysis AS a
    LEFT JOIN sta.agent ON agent.agent_md5=md5(a.http_user_agent) AND agent.agent_name=a.http_user_agent
    LEFT JOIN sta.browser ON browser.browser_name=CONCAT(agent.browser_name, IF(agent.browser_version='0', NULL, CONCAT(' ', agent.browser_version)))
    LEFT JOIN sta.platform ON platform.platform_name=agent.platform_name
    LEFT JOIN sta.host ON host.host_name=a.remote_host
    LEFT JOIN sta.ref ON ref.ref_md5=md5(a.http_referer) AND ref.ref_uri=a.http_referer
    LEFT JOIN sta.req ON req.req_md5=md5(IF(substr(a.request_uri, 0, 2)='//', substr(a.request_uri, 1), a.request_uri))
  WHERE
    a.analysis_idx > 0
    AND a.analysis_idx < 100000
  GROUP BY sta_idx
  --ON DUPLICATE KEY UPDATE sta_cnt=sta_cnt+cnt 
  -- cnt 는 적용이 안됨. 아 하하... 부끄러워라...
INSERT INTO sta.sta 
SELECT *, count(sta_idx)
FROM (
  SELECT
    LOWER(
      CONCAT(CONV(CONCAT(REPLACE(IF(a._request_time IS NOT NULL, DATE(FROM_UNIXTIME(a._request_time)), CONCAT(a.request_year, a.request_month, a.request_day)),'-', ''), CASE WHEN HOUR(FROM_UNIXTIME(a._request_time)) IS NOT NULL THEN HOUR(FROM_UNIXTIME(a._request_time)) ELSE '0' END), 10, 36)
      , CONCAT('-', CASE WHEN conv(mem_idx, 10, 36) IS NOT NULL THEN conv(mem_idx, 10, 36) ELSE '0' END)
      , CONCAT('-', CASE WHEN conv(uri_idx, 10, 36) IS NOT NULL THEN conv(uri_idx, 10, 36) ELSE '0' END)
      , CONCAT('-', CASE WHEN conv(host_idx, 10, 36) IS NOT NULL THEN conv(host_idx, 10, 36) ELSE '0' END)
      , CONCAT('-', CASE WHEN conv(ref_idx, 10, 36) IS NOT NULL THEN conv(ref_idx, 10, 36) ELSE '0' END)
      , CONCAT('-', CASE WHEN conv(req_idx, 10, 36) IS NOT NULL THEN conv(req_idx, 10, 36) ELSE '0' END)
      , CONCAT('-', CASE WHEN conv(agent_idx, 10, 36) IS NOT NULL THEN conv(agent_idx, 10, 36) ELSE '0' END)
      , CONCAT('-', CASE WHEN conv(INET_ATON(remote_addr), 10, 36) IS NOT NULL THEN conv(INET_ATON(remote_addr), 10, 36) ELSE '0' END)
    )) AS sta_idx
    ,IF(a._request_time IS NOT NULL, 
      YEAR(FROM_UNIXTIME(a._request_time)),
      a.request_year
    ) AS sta_y
    ,IF(a._request_time IS NOT NULL, 
      MONTH(FROM_UNIXTIME(a._request_time)),
      a.request_month
    ) AS sta_m
    ,IF(a._request_time IS NOT NULL, 
      DAY(FROM_UNIXTIME(a._request_time)),
      a.request_day
    ) AS sta_d				
    ,IF(a._request_time IS NOT NULL,
      HOUR(FROM_UNIXTIME(a._request_time)),
      '0'
    ) AS sta_h
    ,mem_idx
    ,uri_idx
    ,host_idx
    ,ref_idx
    ,req_idx
    ,agent_idx
    ,INET_ATON(a.remote_addr) AS addr
  FROM pickup.analysis AS a
    LEFT JOIN sta.agent ON agent.agent_md5=md5(a.http_user_agent)
    LEFT JOIN sta.host ON host.host_name=a.remote_host
    LEFT JOIN sta.ref ON ref.ref_md5=md5(a.http_referer) AND ref.ref_uri=a.http_referer
    LEFT JOIN sta.req ON req.req_md5=md5(IF(substr(a.request_uri, 0, 2)='//', substr(a.request_uri, 1), a.request_uri))
  WHERE
    #a.analysis_idx < 14463
    a.request_year = '2010'
    and a.request_month = '1'
    #and a.request_day >= '1' and a.request_day <= '15'
  GROUP BY a.analysis_idx
) AS tmp
GROUP BY sta_idx

DBA는 아무나 하는것도 아닌가봐.
INSERT INTO sta.sta
SELECT 
  LOWER(CONCAT(
    CONV(CONCAT(sta_y, sta_m, sta_d, sta_h) , 10, 36)
    , CONCAT('-', CASE WHEN conv(mem_idx, 10, 36) IS NOT NULL THEN conv(mem_idx, 10, 36) ELSE '0' END)
    , CONCAT('-', CASE WHEN conv(uri_idx, 10, 36) IS NOT NULL THEN conv(uri_idx, 10, 36) ELSE '0' END)
    , CONCAT('-', CASE WHEN conv(host_idx, 10, 36) IS NOT NULL THEN conv(host_idx, 10, 36) ELSE '0' END)
    , CONCAT('-', CASE WHEN conv(ref_idx, 10, 36) IS NOT NULL THEN conv(ref_idx, 10, 36) ELSE '0' END)
    , CONCAT('-', CASE WHEN conv(req_idx, 10, 36) IS NOT NULL THEN conv(req_idx, 10, 36) ELSE '0' END)
    , CONCAT('-', CASE WHEN conv(agent_idx, 10, 36) IS NOT NULL THEN conv(agent_idx, 10, 36) ELSE '0' END)
    , CONCAT('-', CASE WHEN conv(addr, 10, 36) IS NOT NULL THEN conv(addr, 10, 36) ELSE '0' END)
  )) AS sta_idx
  , tmp.*
  , count(*) as sta_cnt
FROM (
  SELECT
    IF(a._request_time IS NOT NULL, 
      YEAR(FROM_UNIXTIME(a._request_time)),
      a.request_year
    ) AS sta_y
    ,IF(a._request_time IS NOT NULL, 
      MONTH(FROM_UNIXTIME(a._request_time)),
      a.request_month
    ) AS sta_m
    ,IF(a._request_time IS NOT NULL, 
      DAY(FROM_UNIXTIME(a._request_time)),
      a.request_day
    ) AS sta_d				
    ,IF(a._request_time IS NOT NULL,
      HOUR(FROM_UNIXTIME(a._request_time)),
      '0'
    ) AS sta_h
    ,mem_idx
    ,uri_idx
    ,(SELECT host_idx FROM sta.host WHERE host.host_name=a.remote_host LIMIT 1) AS host_idx			
    ,(SELECT ref_idx FROM sta.ref WHERE ref.ref_md5=md5(a.http_referer) AND ref.ref_uri=a.http_referer) AS ref_idx
    ,(SELECT req_idx FROM sta.req WHERE req.req_md5=md5(IF(substr(a.request_uri, 0, 2)='//', substr(a.request_uri, 1), a.request_uri)) LIMIT 1) AS req_idx
    ,(SELECT agent_idx FROM sta.agent WHERE agent.agent_md5=md5(a.http_user_agent) LIMIT 1) AS agent_idx
    ,INET_ATON(a.remote_addr) AS addr
  FROM pickup.analysis AS a
  WHERE
    a.request_year = '2010'
    and a.request_month = '2'
) AS tmp
GROUP BY sta_idx
INSERT INTO sta.sta
SELECT 
  LOWER(CONCAT(
    CONV(CONCAT(sta_y, sta_m, sta_d, sta_h) , 10, 36)
    , '-', IFNULL(CONV(mem_idx, 10, 36), 0)
    , '-', IFNULL(CONV(uri_idx, 10, 36), 0)
    , '-', IFNULL(CONV(host_idx, 10, 36), 0)
    , '-', IFNULL(CONV(ref_idx, 10, 36), 0)
    , '-', IFNULL(CONV(req_idx, 10, 36), 0)
    , '-', IFNULL(CONV(agent_idx, 10, 36), 0)
    , '-', IFNULL(CONV(addr, 10, 36), 0)
  )) AS sta_idx
  , tmp.*
  , count(*) as sta_cnt
FROM (
  SELECT
    IFNULL(YEAR(FROM_UNIXTIME(a._request_time)), a.request_year) AS sta_y
    ,IFNULL(MONTH(FROM_UNIXTIME(a._request_time)), a.request_month) AS sta_m
    ,IFNULL(DAY(FROM_UNIXTIME(a._request_time)), a.request_day) AS sta_d				
    ,IFNULL(HOUR(FROM_UNIXTIME(a._request_time)), '0') AS sta_h
    ,mem_idx
    ,uri_idx
    ,(SELECT host_idx FROM sta.host WHERE host.host_name=a.remote_host LIMIT 1) AS host_idx			
    ,(SELECT ref_idx FROM sta.ref WHERE ref.ref_md5=md5(a.http_referer) AND ref.ref_uri=a.http_referer) AS ref_idx
    ,(SELECT req_idx FROM sta.req WHERE req.req_md5=md5(IF(substr(a.request_uri, 0, 2)='//', substr(a.request_uri, 1), a.request_uri)) LIMIT 1) AS req_idx
    ,(SELECT agent_idx FROM sta.agent WHERE agent.agent_md5=md5(a.http_user_agent) LIMIT 1) AS agent_idx
    ,INET_ATON(a.remote_addr) AS addr
  FROM pickup.analysis AS a
  WHERE
    a.request_year = '2010'
    and a.request_month = '5'
) AS tmp
GROUP BY sta_idx


반응형