Home » SQL & PL/SQL » SQL & PL/SQL » query datewise howmany records (oracle 12c)
query datewise howmany records [message #666493] |
Thu, 09 November 2017 00:23 |
|
mvrkr44
Messages: 132 Registered: December 2012
|
Senior Member |
|
|
I have two tables and inserted data as below
create table processed_requests(recseq number,id number,farmid number,created_date date,isvalid number,result number);
/
alter table processed_requests add constraint pl_pk promary key (recseq,id,farmid);
/
create table matched_records(recseq number,id number,farmid number,type number);
/
alter table matched_records add constraint fk_mr foreign key (recseq,id,farmid) reference processed_requests(recseq,id,farmid);
/
insert into processed_requests values(1,1,2,sysdate,1,111);
insert into processed_requests values(2,1,3,sysdate-1,1,103);
insert into processed_requests values(3,1,4,sysdate,1,111);
insert into processed_requests values(4,1,5,sysdate-1,1,103);
insert into processed_requests values(5,1,6,sysdate,1,111);
insert into processed_requests values(6,1,7,sysdate,1,103);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,3,26);
insert into matched_records values (1,1,3,26);
insert into matched_records values (1,1,3,56);
insert into matched_records values (1,1,4,26);
insert into matched_records values (1,1,4,39);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,5,39);
insert into matched_records values (1,1,5,26);
insert into matched_records values (1,1,6,38);
insert into matched_records values (1,1,7,28);
commit;
I need query datewise howmany records are created and out of which how many only type=26 records are present and their percentage along with recseq?
output should contain the below columns.
Please any one help me
date | recseq| total_records|only_type_26
Note:I have million of records in both tables and query should execute very fast.
Regards,
Rajesh
|
|
|
Re: query datewise howmany records [message #666496 is a reply to message #666493] |
Thu, 09 November 2017 02:23 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
Please try the following -- preparing data
WITH
PROCESSED_REQUESTS(RECSEQ, ID, FARMID, CREATED_DATE, ISVALID, RESULT)
AS
(SELECT 1, 1, 2, SYSDATE, 1, 111 FROM DUAL
UNION ALL SELECT 2 ,1 ,3 ,SYSDATE - 1 ,1 ,103 FROM DUAL
UNION ALL SELECT 3, 1, 4, SYSDATE, 1, 111 FROM DUAL
UNION ALL SELECT 4 ,1 ,5 ,SYSDATE - 1 ,1 ,103 FROM DUAL
UNION ALL SELECT 5, 1, 6, SYSDATE, 1, 111 FROM DUAL
UNION ALL SELECT 6, 1, 7, SYSDATE, 1, 103 FROM DUAL),
MATCHED_RECORDS(RECSEQ, ID, FARMID, TYPE)
AS
(SELECT 1, 1, 2, 26 FROM DUAL
UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
-- UNION ALL SELECT 1, 1, 2, 25 FROM DUAL
UNION ALL SELECT 1, 1, 3, 26 FROM DUAL
UNION ALL SELECT 1, 1, 3, 26 FROM DUAL
UNION ALL SELECT 1, 1, 3, 56 FROM DUAL
UNION ALL SELECT 1, 1, 4, 26 FROM DUAL
UNION ALL SELECT 1, 1, 4, 39 FROM DUAL
UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
UNION ALL SELECT 1, 1, 5, 39 FROM DUAL
UNION ALL SELECT 1, 1, 5, 26 FROM DUAL
UNION ALL SELECT 1, 1, 6, 38 FROM DUAL
UNION ALL SELECT 1, 1, 7, 28 FROM DUAL)
-- this is your query
SELECT PROCESSED_REQUESTS.CREATED_DATE
,MATCHED_RECORDS.RECSEQ
,SUM(PROCESSED_REQUESTS.RESULT) AS TOTAL_RECORDS
,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END) AS ONLY_TYPE_26
,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END) / SUM(PROCESSED_REQUESTS.RESULT) * 100 AS PERCENTAGE
FROM PROCESSED_REQUESTS
FULL OUTER JOIN MATCHED_RECORDS
ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, MATCHED_RECORDS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, MATCHED_RECORDS.RECSEQ
To show what overhead your example data produce I've used a FULL OUTER JOIN. Please use a normal JOIN to see only "valid data".
I've inserted an additional line to show that the percentage calculation works. Please remove the comment ('--') in the data preparation part to enable it.
[Updated on: Thu, 09 November 2017 02:35] Report message to a moderator
|
|
|
|
Re: query datewise howmany records [message #666499 is a reply to message #666498] |
Thu, 09 November 2017 03:04 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
To my understanding, this is exactly what I gave you. Please be more descriptive (an example of what you would expect would be great).
Edit:
Probably you want to have it grouped by the PROCESSED_REQUESTS.RECSEQ?
Then this might be your solution:SELECT PROCESSED_REQUESTS.CREATED_DATE
,PROCESSED_REQUESTS.RECSEQ
,SUM(PROCESSED_REQUESTS.RESULT) AS TOTAL_RECORDS
,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END) AS ONLY_TYPE_26
,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END) / SUM(PROCESSED_REQUESTS.RESULT) * 100 AS PERCENTAGE
FROM PROCESSED_REQUESTS
FULL OUTER JOIN MATCHED_RECORDS
ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
Or you just want to count the Records in MATCHED_RECORDS? Then this might help:SELECT PROCESSED_REQUESTS.CREATED_DATE
,PROCESSED_REQUESTS.RECSEQ
,SUM(CASE WHEN MATCHED_RECORDS.TYPE IS NULL THEN 0 ELSE 1 END) AS TOTAL_RECORDS
,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END) AS ONLY_TYPE_26
,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END) / COUNT(*) * 100 AS PERCENTAGE
FROM PROCESSED_REQUESTS
FULL OUTER JOIN MATCHED_RECORDS
ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
[Updated on: Thu, 09 November 2017 03:27] Report message to a moderator
|
|
|
Re: query datewise howmany records [message #666502 is a reply to message #666499] |
Thu, 09 November 2017 04:14 |
|
mvrkr44
Messages: 132 Registered: December 2012
|
Senior Member |
|
|
SELECT PROCESSED_REQUESTS.CREATED_DATE
,PROCESSED_REQUESTS.RECSEQ
,SUM(CASE WHEN MATCHED_RECORDS.TYPE IS NULL THEN 0 ELSE 1 END) AS TOTAL_RECORDS
,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END) AS ONLY_TYPE_26 ---> here i need to get the output as 1 only ,because for recseq:1 only I have type=26 records.so i need distinct recseq count as ONLY_TYPE_26 count
,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END) / COUNT(*) * 100 AS PERCENTAGE
FROM PROCESSED_REQUESTS
FULL OUTER JOIN MATCHED_RECORDS
ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
DATE RECSEQ TOTALCOUNT ONLY_TYPE_26
SYSDATE 1 4 1
TOTALCOUNT Total records came in the day
ONLY_TYPE_26 Recseq should have only 26 type record only,So recseq:1 have MULTIPLE RECORDS then I need to consider as one record only
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:35:58 CDT 2024
|