Browser User-Agent 정보를 통한 브라우저 분류하기

브라우저 패턴이 너무 다양함...

하는데까지..

SELECT browser, COUNT(browser) AS cnt
FROM (
	SELECT 
		CASE
			WHEN user_agent LIKE '%Windows%Chrome%' THEN 'Chrome'
			WHEN user_agent LIKE '%iPad%Chrome%' THEN 'ChromeIPad'
			WHEN user_agent LIKE '%iPhone%Chrome%' THEN 'ChromeIPhone'
			WHEN user_agent LIKE '%Android%Chrome%' THEN 'ChromeAndroid'
			WHEN user_agent LIKE '%Mac%Chrome%' THEN 'ChromeMacOs'     
			WHEN user_agent LIKE '%Linux%Chrome%' THEN 'ChromeLinux'
			WHEN user_agent LIKE '%Chrome%' THEN 'ChromeEtc'
			
			WHEN user_agent LIKE '%Windows%Safari%' THEN 'Safari'
			WHEN user_agent LIKE '%iPad%Safari%' THEN 'SafariIPad'
			WHEN user_agent LIKE '%iPhone%Safari%' THEN 'SafariIPhone'
			WHEN user_agent LIKE '%Android%Safari%' THEN 'SafariAndroid'
			WHEN user_agent LIKE '%Mac%Safari%' THEN 'SafariMacOs'     
			WHEN user_agent LIKE '%Linux%Safari%' THEN 'SafariLinux'   
			WHEN user_agent LIKE '%Safari%' THEN 'SafariEtc'
			
			WHEN user_agent LIKE '%Windows%Firefox%' THEN 'Firefox'
			WHEN user_agent LIKE '%iPad%Firefox%' THEN 'FirefoxIPad'
			WHEN user_agent LIKE '%iPhone%Firefox%' THEN 'FirefoxIPhone'
			WHEN user_agent LIKE '%Android%Firefox%' THEN 'FirefoxAndroid'
			WHEN user_agent LIKE '%Mac%Firefox%' THEN 'FirefoxMacOs'     
			WHEN user_agent LIKE '%Linux%Firefox%' THEN 'FirefoxLinux'      
			WHEN user_agent LIKE '%Firefox%' THEN 'FirefoxEtc'
			
			WHEN user_agent LIKE '%MSIE 6%' THEN 'IE6'
			WHEN user_agent LIKE '%MSIE 7%' THEN 'IE7'
			WHEN user_agent LIKE '%MSIE 8%' THEN 'IE8'
			WHEN user_agent LIKE '%MSIE 9%' THEN 'IE9'
			WHEN user_agent LIKE '%MSIE 10%' THEN 'IE10'
			WHEN user_agent LIKE '%rv:11%' THEN 'IE11'
			
			WHEN user_agent LIKE '%iPad%' THEN 'AppIPad'
			WHEN user_agent LIKE '%iPhone%' THEN 'AppIPhone'
			WHEN user_agent LIKE '%Android%' THEN 'AppAndroid'

			WHEN user_conn_info LIKE '%Opera%' THEN 'Opera'
			ELSE 'EtcBrowser'
		END browser
		FROM USER_AGENT_HISTORY
		WHERE LOGIN_DT >= '20160517000001' AND LOGIN_DT <= '20160517999999'
	) AS browsers 
GROUP BY browser 
ORDER BY cnt DESC