阿里云日志分析

在阿里云使用oss的时候，将其提供的日志导入到数据库中，需要对其进行分析。

 

SELECT
	SUM(SentBytes) SentBytes,
	CASE
		WHEN SentBytes > (1000 * 1000 * 1000 * 1000) THEN CONCAT(ROUND(SUM(SentBytes) / (1000 * 1000 * 1000 * 1000), 2), 'PB')
		WHEN SentBytes > (1000 * 1000 * 1000) THEN CONCAT(ROUND(SUM(SentBytes) / (1000 * 1000 * 1000), 2), 'GB')
		WHEN SentBytes > (1000 * 1000) THEN	CONCAT(ROUND(SUM(SentBytes) / (1000 * 1000), 2), 'MB')
		WHEN SentBytes > 1000 THEN CONCAT(ROUND(SUM(SentBytes) /(1000), 2),	'KB')
		ELSE SentBytes
	END SIZE
FROM
	(
		SELECT
			`key`,
			PV,
			UV,
			EV,
			SentBytes,
			CASE
				WHEN SentBytes > (1000 * 1000 * 1000 * 1000) THEN	CONCAT(ROUND(SentBytes / (1000 * 1000 * 1000 * 1000), 2), 'PB')
				WHEN SentBytes > (1000 * 1000 * 1000) THEN CONCAT(ROUND(SentBytes / (1000 * 1000 * 1000), 2), 'GB')
				WHEN SentBytes > (1000 * 1000) THEN	CONCAT(ROUND(SentBytes /(1000 * 1000), 2), 'MB')
				WHEN SentBytes > 1000 THEN CONCAT(ROUND(SentBytes /(1000), 2), 'KB')
				ELSE SentBytes
			END SIZE
		FROM
			(
				SELECT
					`key`,
					COUNT(DISTINCT RequestId) PV,
					COUNT(DISTINCT RemoteIP) UV,
					SUM(IF (ErrorCode = 'NoSuchKey', 1, 0)) EV,
					SUM(SentBytes) SentBytes
				FROM
					ods_down_log_three_day
				WHERE
					time BETWEEN FROM_DAYS(TO_DAYS(NOW())) AND FROM_DAYS(TO_DAYS(NOW()) + 1)
				AND `key` <> '-'
				GROUP BY
					`key`
				ORDER BY
					SentBytes DESC
			) t
		ORDER BY
			SentBytes DESC
	) t2
WHERE
	SentBytes > 99990528;