Oracle查询一段时间内每一天的数据统计

这个sql语句能帮助我们查询一段时间内，每一天的数据统计，计算百分比

-- 计算拨出比
-- 除数为0 解决办法 decode(b,0,0,a/b*100) decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
-- 	100 * round( c."PAY" / d."INCOME" , 4 ) || '%'  AS 拨出比
SELECT
	c."TIME" AS 日期,
	c."PAY" AS 支出,
	d."INCOME" AS 收入,
	decode( d."INCOME", 0, '0%', 100 * round( c."PAY" / d."INCOME", 4 ) || '%' ) AS 拨出比
FROM
	(
SELECT
a."TIME" AS TIME,
nvl( b.money, 0 ) AS pay 
FROM
	(
	SELECT
		to_char( to_date( '2019-09-25', 'yyyy-mm-dd' ) + ROWNUM - 1, 'yyyy-mm-dd' ) AS time 
	FROM
		dual CONNECT BY ROWNUM <= trunc(
		to_date( '2019-09-29', 'yyyy-mm-dd' ) - to_date( '2019-09-25', 'yyyy-mm-dd' )) + 1 
	) a
	LEFT JOIN (
	SELECT
		"TIME",
		SUM( "MONEY" ) AS money 
	FROM
		(
		SELECT
			to_char( to_date( TIME, 'yyyy-MM-dd hh24:mi:ss' ), 'yyyy-MM-dd' ) AS time,
			"NUMBER" AS money 
		FROM
			SALES_BONUS 
		WHERE
			"TIME" BETWEEN to_char( to_date( '2019-09-25', 'yyyy-MM-dd' ), 'yyyy-MM-dd' ) 
			AND to_char( to_date( '2019-09-29', 'yyyy-MM-dd' ) + 1, 'yyyy-MM-dd' ) 
		) 
	WHERE
		"MONEY" > 0 
	GROUP BY
		"TIME" 
	) b ON a."TIME" = b."TIME" 
	) c,
	(
	SELECT
		a."TIME" AS TIME,
		nvl( b.cost, 0 ) AS income 
	FROM
		(
		SELECT
			to_char( to_date( '2019-09-25', 'yyyy-mm-dd' ) + ROWNUM - 1, 'yyyy-mm-dd' ) AS time 
		FROM
			dual CONNECT BY ROWNUM <= trunc(
			to_date( '2019-09-29', 'yyyy-mm-dd' ) - to_date( '2019-09-25', 'yyyy-mm-dd' )) + 1 
		) a
		LEFT JOIN (
		SELECT
			"TIME",
			sum( "COST" ) AS COST 
		FROM
			(
			SELECT
				to_char( to_date( "ACTIVA_TIME", 'yyyy-MM-dd hh24:mi:ss' ), 'yyyy-MM-dd' ) AS time,
				"ACTIVATED_COST" AS cost 
			FROM
				SALES_USER_SALES 
			WHERE
				"ACTIVA_TIME" BETWEEN to_char( to_date( '2019-09-25', 'yyyy-MM-dd' ), 'yyyy-MM-dd' ) 
				AND to_char( to_date( '2019-09-29', 'yyyy-MM-dd' ) + 1, 'yyyy-MM-dd' ) 
			) 
		WHERE
			"COST" > 0 
		GROUP BY
			"TIME" 
		) b ON a."TIME" = b."TIME" 
	) d 
WHERE
	c."TIME" = d."TIME" 
ORDER BY
c."TIME";

使用方法：只需要改表名，字段名，然后传查询的时间就行