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


반응형

'db > mysql' 카테고리의 다른 글

mysql 쿼리 자료 OUTFILE INFILE query  (0) 2010.11.08
mysql 통계자료 이전을 위한 agent 자료 구축  (0) 2010.10.27
mysqldump 옵션 스키마, 데이터만 백업  (0) 2010.10.22
mysql error 2013 Lost connection to MySQL server during query  (0) 2010.10.20
mysql error 2006 "MySQL server has gone away"  (0) 2010.10.19

'db/mysql'의 다른글

  • 현재글mysql 통계자료 이전 쿼리

관련글

댓글

티스토리툴바