oracle/postgresql 按天或按分钟进行累加统计

前言

   基于项目实际应用，SQL没什么难度，主要是记录一下，方便以后复用

应用场景

   一张日志表，记录数据量、状态、时间等信息；一张累加表，按天进行统计，只统计当天的数据量和状态。

日志表

数据示例

  累加表

数据示例

升级版累加表，按分钟统计，用于昨日同期对比，其实表结构是完全一样的

数据示例：

统计SQL－Oracle

 1）按天统计

MERGE INTO ACCESS_MONITOR_ADDUP addup USING (
SELECT
	ssm.ACCESS_SERVICE_ID AS SERVICE_ID,
	TO_CHAR( SYSDATE, 'yyyyMMdd' ) AS ADDUP_CYCLE,
	coalesce( SUM( ssm.DATA_VOLUME ), 0 ) AS TOTAL_QUANTITY,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS SUCCESS_QUANTITY,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS FAIL_QUANTITY,
	COUNT( 1 ) AS TOTAL_COUNT,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN 1 ELSE 0 END ), 0 ) AS SUCCESS_COUNT,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN 1 ELSE 0 END ), 0 ) AS FAIL_COUNT 
FROM
	ACCESS_SERVICE_MONITOR_INFO ssm 
WHERE
	ssm.CREATE_TIME > ( SELECT TRUNC( SYSDATE, 'dd' ) AS SYSTIMESTAMP FROM dual ) 
GROUP BY
	ssm.ACCESS_SERVICE_ID 
	) new_addup ON ( addup.SERVICE_ID = new_addup.SERVICE_ID AND addup.ADDUP_CYCLE = new_addup.ADDUP_CYCLE ) 
	WHEN MATCHED THEN
UPDATE 
	SET addup.ADDUP_TIME = SYSDATE,
	addup.TOTAL_QUANTITY = new_addup.TOTAL_QUANTITY,
	addup.SUCCESS_QUANTITY = new_addup.SUCCESS_QUANTITY,
	addup.FAIL_QUANTITY = new_addup.FAIL_QUANTITY,
	addup.TOTAL_COUNT = new_addup.TOTAL_COUNT,
	addup.SUCCESS_COUNT = new_addup.SUCCESS_COUNT,
	addup.FAIL_COUNT = new_addup.FAIL_COUNT 
	WHEN NOT MATCHED THEN
	INSERT ( SERVICE_ID, ADDUP_CYCLE, ADDUP_TIME, TOTAL_COUNT, SUCCESS_COUNT, FAIL_COUNT, TOTAL_QUANTITY, SUCCESS_QUANTITY, FAIL_QUANTITY )
VALUES
	(
	new_addup.SERVICE_ID,
	new_addup.ADDUP_CYCLE,
	SYSDATE,
	new_addup.TOTAL_COUNT,
	new_addup.SUCCESS_COUNT,
	new_addup.FAIL_COUNT,
	new_addup.TOTAL_QUANTITY,
	new_addup.SUCCESS_QUANTITY,
	new_addup.FAIL_QUANTITY 
	)

2） 按分钟统计（考虑到数据量比较大的情况，只统计最近五分钟的数据）

MERGE INTO ACCESS_MONITOR_ADDUP_MIN addup USING (
SELECT
	ssm.ACCESS_SERVICE_ID AS SERVICE_ID,
	TO_CHAR( ssm.CREATE_TIME, 'yyyyMMddHHMi' ) AS ADDUP_CYCLE,
	coalesce( SUM( ssm.DATA_VOLUME ), 0 ) AS TOTAL_QUANTITY,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS SUCCESS_QUANTITY,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS FAIL_QUANTITY,
	COUNT( 1 ) AS TOTAL_COUNT,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN 1 ELSE 0 END ), 0 ) AS SUCCESS_COUNT,
	coalesce( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN 1 ELSE 0 END ), 0 ) AS FAIL_COUNT 
FROM
	ACCESS_SERVICE_MONITOR_INFO ssm 
WHERE
	ssm.CREATE_TIME > SYSDATE - 5 / 1440 
GROUP BY
	ssm.ACCESS_SERVICE_ID,
	TO_CHAR( ssm.CREATE_TIME, 'yyyyMMddHHMi' ) 
	) new_addup ON ( addup.SERVICE_ID = new_addup.SERVICE_ID AND addup.ADDUP_CYCLE = new_addup.ADDUP_CYCLE ) 
	WHEN MATCHED THEN
UPDATE 
	SET addup.ADDUP_TIME = SYSDATE,
	addup.TOTAL_QUANTITY = new_addup.TOTAL_QUANTITY,
	addup.SUCCESS_QUANTITY = new_addup.SUCCESS_QUANTITY,
	addup.FAIL_QUANTITY = new_addup.FAIL_QUANTITY,
	addup.TOTAL_COUNT = new_addup.TOTAL_COUNT,
	addup.SUCCESS_COUNT = new_addup.SUCCESS_COUNT,
	addup.FAIL_COUNT = new_addup.FAIL_COUNT 
	WHEN NOT MATCHED THEN
	INSERT ( SERVICE_ID, ADDUP_CYCLE, ADDUP_TIME, TOTAL_COUNT, SUCCESS_COUNT, FAIL_COUNT, TOTAL_QUANTITY, SUCCESS_QUANTITY, FAIL_QUANTITY )
VALUES
	(
	new_addup.SERVICE_ID,
	new_addup.ADDUP_CYCLE,
	SYSDATE,
	new_addup.TOTAL_COUNT,
	new_addup.SUCCESS_COUNT,
	new_addup.FAIL_COUNT,
	new_addup.TOTAL_QUANTITY,
	new_addup.SUCCESS_QUANTITY,
	new_addup.FAIL_QUANTITY 
	)

统计SQL－Postgresql

 1）按天统计

INSERT INTO ACCESS_MONITOR_ADDUP ( SERVICE_ID, ADDUP_CYCLE, ADDUP_TIME, TOTAL_QUANTITY, SUCCESS_QUANTITY, FAIL_QUANTITY, TOTAL_COUNT, SUCCESS_COUNT, FAIL_COUNT ) SELECT
* 
FROM
	(
SELECT
	ssm.ACCESS_SERVICE_ID AS SERVICE_ID,
	to_number( TO_CHAR( now ( ), 'yyyyMMdd' ), '99999999' ) AS ADDUP_CYCLE,
	now ( ) AS ADDUP_TIME,
	COALESCE( SUM( ssm.DATA_VOLUME ), 0 ) AS TOTAL_QUANTITY,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS SUCCESS_QUANTITY,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS FAIL_QUANTITY,
	COUNT( 1 ) AS TOTAL_COUNT,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN 1 ELSE 0 END ), 0 ) AS SUCCESS_COUNT,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN 1 ELSE 0 END ), 0 ) AS FAIL_COUNT 
FROM
	ACCESS_SERVICE_MONITOR_INFO ssm 
WHERE
	ssm.CREATE_TIME >= CURRENT_DATE 
GROUP BY
	ssm.ACCESS_SERVICE_ID 
	) AS new_addup ON CONFLICT ( SERVICE_ID, ADDUP_CYCLE ) DO
UPDATE 
	SET ADDUP_TIME = now ( ),
	TOTAL_QUANTITY = EXCLUDED.TOTAL_QUANTITY,
	SUCCESS_QUANTITY = EXCLUDED.SUCCESS_QUANTITY,
	FAIL_QUANTITY = EXCLUDED.FAIL_QUANTITY,
	TOTAL_COUNT = EXCLUDED.TOTAL_COUNT,
	SUCCESS_COUNT = EXCLUDED.SUCCESS_COUNT,
	FAIL_COUNT = EXCLUDED.FAIL_COUNT

2) 按分钟统计

INSERT INTO ACCESS_MONITOR_ADDUP_MIN ( SERVICE_ID, ADDUP_CYCLE, ADDUP_TIME, TOTAL_QUANTITY, SUCCESS_QUANTITY, FAIL_QUANTITY, TOTAL_COUNT, SUCCESS_COUNT, FAIL_COUNT ) SELECT
* 
FROM
	(
SELECT
	ssm.ACCESS_SERVICE_ID AS SERVICE_ID,
	to_number( TO_CHAR( ssm.CREATE_TIME, 'yyyyMMddHH24mi' ), '999999999999' ) AS ADDUP_CYCLE,
	now ( ) AS ADDUP_TIME,
	COALESCE( SUM( ssm.DATA_VOLUME ), 0 ) AS TOTAL_QUANTITY,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS SUCCESS_QUANTITY,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN ssm.DATA_VOLUME ELSE 0 END ), 0 ) AS FAIL_QUANTITY,
	COUNT( 1 ) AS TOTAL_COUNT,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'SUCCESS' THEN 1 ELSE 0 END ), 0 ) AS SUCCESS_COUNT,
	COALESCE( SUM( CASE WHEN ACCESS_RESULT = 'FAIL' THEN 1 ELSE 0 END ), 0 ) AS FAIL_COUNT 
FROM
	ACCESS_SERVICE_MONITOR_INFO ssm 
WHERE
	ssm.CREATE_TIME >= ( now () - interval '5 minute' ) 
GROUP BY
	ssm.ACCESS_SERVICE_ID,
	TO_CHAR( ssm.CREATE_TIME, 'yyyyMMddHH24mi' ) 
	) AS new_addup ON CONFLICT ( SERVICE_ID, ADDUP_CYCLE ) DO
UPDATE 
	SET ADDUP_TIME = now ( ),
	TOTAL_QUANTITY = EXCLUDED.TOTAL_QUANTITY,
	SUCCESS_QUANTITY = EXCLUDED.SUCCESS_QUANTITY,
	FAIL_QUANTITY = EXCLUDED.FAIL_QUANTITY,
	TOTAL_COUNT = EXCLUDED.TOTAL_COUNT,
	SUCCESS_COUNT = EXCLUDED.SUCCESS_COUNT,
	FAIL_COUNT = EXCLUDED.FAIL_COUNT