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 |