COUNT(DISTINCT) WITH ORDER BY in an analytic function
478360Dec 22 2005 — edited Dec 29 2005-- I create a table with three fields: Name, Amount, and a Trans_Date.
CREATE TABLE TEST
(
NAME VARCHAR2(19) NULL,
AMOUNT VARCHAR2(8) NULL,
TRANS_DATE DATE NULL
);
-- I insert a few rows into my table:
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Anna', '110', TO_DATE('06/01/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Anna', '20', TO_DATE('06/01/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Anna', '110', TO_DATE('06/02/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Anna', '21', TO_DATE('06/03/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Anna', '68', TO_DATE('06/04/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Anna', '110', TO_DATE('06/05/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Anna', '20', TO_DATE('06/06/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Bill', '43', TO_DATE('06/01/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Bill', '77', TO_DATE('06/02/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Bill', '221', TO_DATE('06/03/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Bill', '43', TO_DATE('06/04/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
INSERT INTO TEST ( TEST.NAME, TEST.AMOUNT, TEST.TRANS_DATE ) VALUES ( 'Bill', '73', TO_DATE('06/05/2005 08:00:00 PM', 'MM/DD/YYYY HH12:MI:SS PM') );
commit;
/* I want to retrieve all the distinct count of amount for every row in an analytic function with COUNT(DISTINCT AMOUNT) sorted by name and ordered by trans_date where I get only calculate for the last four trans_date for each row (i.e., for the row "Anna 110 6/5/2005 8:00:00.000 PM," I only want to look at the previous dates from 6/2/2005 to 6/5/2005 and get the distinct count of how many amounts there are different for Anna). Note, I cannot use the DISTINCT keyword in this query because it doesn't work with the ORDER BY */
select NAME, AMOUNT, TRANS_DATE, COUNT(/*DISTINCT*/ AMOUNT) over ( partition by NAME
order by TRANS_DATE range between numtodsinterval(3,'day') preceding and current row ) as COUNT_AMOUNT
from TEST t;
This is the results I get if I just count all the AMOUNT without using distinct:
NAME AMOUNT TRANS_DATE COUNT_AMOUNT
Anna 110 6/1/2005 8:00:00.000 PM 2
Anna 20 6/1/2005 8:00:00.000 PM 2
Anna 110 6/2/2005 8:00:00.000 PM 3
Anna 21 6/3/2005 8:00:00.000 PM 4
Anna 68 6/4/2005 8:00:00.000 PM 5
Anna 110 6/5/2005 8:00:00.000 PM 4
Anna 20 6/6/2005 8:00:00.000 PM 4
Bill 43 6/1/2005 8:00:00.000 PM 1
Bill 77 6/2/2005 8:00:00.000 PM 2
Bill 221 6/3/2005 8:00:00.000 PM 3
Bill 43 6/4/2005 8:00:00.000 PM 4
Bill 73 6/5/2005 8:00:00.000 PM 4
The COUNT_DISTINCT_AMOUNT is the desired output:
NAME AMOUNT TRANS_DATE COUNT_DISTINCT_AMOUNT
Anna 110 6/1/2005 8:00:00.000 PM 1
Anna 20 6/1/2005 8:00:00.000 PM 2
Anna 110 6/2/2005 8:00:00.000 PM 2
Anna 21 6/3/2005 8:00:00.000 PM 3
Anna 68 6/4/2005 8:00:00.000 PM 4
Anna 110 6/5/2005 8:00:00.000 PM 3
Anna 20 6/6/2005 8:00:00.000 PM 4
Bill 43 6/1/2005 8:00:00.000 PM 1
Bill 77 6/2/2005 8:00:00.000 PM 2
Bill 221 6/3/2005 8:00:00.000 PM 3
Bill 43 6/4/2005 8:00:00.000 PM 3
Bill 73 6/5/2005 8:00:00.000 PM 4
Thanks in advance.