Oracle statistics by year or month

Here is a question about csdn. Correct the error.

The image function requires statistics on the data in a table.
Table definition (Omitted fields not used)
LB01_PURRECEIVEBOOK
(
Purreceivedate date, -- format: 2009/11/01
RECEIVEAMT NUMBER
)
① In annual statistics, if there is a blank year, the annual amount is 0.
Example:
2009/09/01 1,000
2009/11/01 3,000
2007/12/01 2,000
Extract:
2009 4,000
2008 0
2007 2,000
② When there is an empty month in monthly statistics, the monthly amount is 0.
2009/09/01 1,000
2009/11/01 3,000
2009/12/01 2,000
Extract:
2009/09 1,000
2009/10 0
2009/11 3,000
2009/12 2,000

Is this simple?

By year
View plaincopy to clipboardPRint?
WITH tmp_table AS (select max (TO_CHAR (T. PURRECEIVEDATE, yyyy ))-
MIN (TO_CHAR (T. PURRECEIVEDATE, yyyy) as num,
MIN (TO_CHAR (T. PURRECEIVEDATE, yyyy) AS MINYEAR
FROM LB01_PURRECEIVEBOOK T
),
Select_table (
Select tmp. YEARSUM, TMP. YEAR, TMP. YMD
FROM (select sum (T. RECEIVEAMT) OVER (partition by TO_CHAR (T. PURRECEIVEDATE, yyyy) order by t. purreceivedate desc) as yearsum,
TO_CHAR (T. PURRECEIVEDATE, yyyy) as year,
TO_CHAR (T. PURRECEIVEDATE, yyyy/mm/dd) as ymd,
ROW_NUMBER () OVER (partition by TO_CHAR (T. PURRECEIVEDATE, yyyy) order by t. PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
Where tmp. RN = 1

),
Creatyear_table (
SELECT tmp_table.MINYEAR + LEVEL-1 AS tmp_year from dual, tmp_table
Connect by level <= tmp_table.NUM + 1
)

SELECT
Ct. tmp_year,
NVL (st. YEARSUM, 0) AS YEARSUM
FROM
Creatyear_table ct,
Select_table st
WHERE
Ct. tmp_year = st. year (+)
WITH tmp_table AS (select max (TO_CHAR (T. PURRECEIVEDATE, yyyy ))-
MIN (TO_CHAR (T. PURRECEIVEDATE, yyyy) as num,
MIN (TO_CHAR (T. PURRECEIVEDATE, yyyy) AS MINYEAR
FROM LB01_PURRECEIVEBOOK T
),
Select_table (
Select tmp. YEARSUM, TMP. YEAR, TMP. YMD
FROM (select sum (T. RECEIVEAMT) OVER (partition by TO_CHAR (T. PURRECEIVEDATE, yyyy) order by t. purreceivedate desc) as yearsum,
TO_CHAR (T. PURRECEIVEDATE, yyyy) as year,
TO_CHAR (T. PURRECEIVEDATE, yyyy/mm/dd) as ymd,
ROW_NUMBER () OVER (partition by TO_CHAR (T. PURRECEIVEDATE, yyyy) order by t. PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
Where tmp. RN = 1

),
Creatyear_table (
SELECT tmp_table.MINYEAR + LEVEL-1 AS tmp_year from dual, tmp_table
Connect by level <= tmp_table.NUM + 1
)

SELECT
Ct. tmp_year,
NVL (st. YEARSUM, 0) AS YEARSUM
FROM
Creatyear_table ct,
Select_table st
WHERE
Ct. tmp_year = st. year (+)
Statistics by month
View plaincopy to clipboardprint?
WITH tmp_table AS (select max (TO_CHAR (T. PURRECEIVEDATE, mm ))-
MIN (TO_CHAR (T. PURRECEIVEDATE, mm) as num,
MIN (TO_CHAR (T. PURRECEIVEDATE, mm) as minmm,
MIN (TO_CHAR (T. PURRECEIVEDATE, YYYY) AS MINY
FROM LB01_PURRECEIVEBOOK T
),
Select_table (
Select tmp. MMSUM, TMP. MM, TMP. YMD
FROM (select sum (T. RECEIVEAMT) OVER (partition by TO_CHAR (T. PURRECEIVEDATE, MM) order by t. purreceivedate desc) as mmsum,
TO_CHAR (T. PURRECEIVEDATE, MM) as mm,
TO_CHAR (T. PURRECEIVEDATE, yyyy/mm/dd) as ymd,
ROW_NUMBER () OVER (partition by TO_CHAR (T. PURRECEIVEDATE, MM) order by t. PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
Where tmp. RN = 1
),
Creatyear_table (
SELECT tmp_table.MINMM + LEVEL-1 AS TMP_MM,
TO_CHAR (TO_DATE (MINY | tmp_table.MINMM + LEVEL-1, YYYY-MM), YYYY-MM) AS TMP_YM
From dual, tmp_table
Connect by level <= tmp_table.NUM + 1
)
SELECT
Ct. TMP_YM,
TO_CHAR (NVL (ST. MMSUM, 0), 9,999) AS MMSUM
FROM
Creatyear_table CT,
Select_table ST
WHERE
CT. TMP_MM = ST. MM (+)
ORDER
Ct. TMP_YM