CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
JMS
JMS

Reputation: 193

How can I change this Oracle SQL for this new format

I am new to Oracle and I need to change this SQL for the new output.

table name: access_log
col name: activity

sample data from the field

Download file:/webdocs/data/3589/casemanagement/01/CR-CLOSE/01_31_30_9_1050073559.pdf
Download file:/webdocs/data/3589/casemanagement/01/CR-CLOSE/01_31_42_29_1070032338.pdf
Download file:/webdocs/data/3589/casemanagement/01/CR-CLOSE/01_31_47_16_1050909430.pdf
Download file:/webdocs/data/3423/casemanagement/01/debit_disputes/01_24_38_29_0001105562.pdf
Download file:/webdocs/data/3423/fraud/01/0130_FRAUD_CLAIM_OF_FRAUD_AND_FORGERY_RPT_3423.XLS

so here is the output I need

enter image description here

The SQL I have right now is the following but I need to change it for the new format

select regexp_replace(activity, '^.*/(.*)/.*$', '\1') AS FILENAME,
COUNT (regexp_replace(activity, '^.*/(.*)/.*$', '\1')) AS DOWNLOADS
FROM sa.web_access_log where application_id = 5339 and time_stamp BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd') AND TO_DATE ('2014/02/02', 'yyyy/mm/dd')
GROUP BY regexp_replace(activity, '^.*/(.*)/.*$', '\1')
ORDER BY DOWNLOADS DESC;

So filename is from the 2nd to the last "/" to the first "/" folder is from the 4th from the left "/" to the 5th "/" and download is the count of matching filenames in the folder.. So who can help me and get this working

Upvotes: 0

Views: 85

Answers (2)

Abecee
Abecee

Reputation: 2393

If you prefer regex:

SELECT REGEXP_REPLACE(activity, '^(.*?/){4}(.*)/.*$', '\2') FROM access_log;

SQL Fiddle

Upvotes: 1

Dharmesh Porwal
Dharmesh Porwal

Reputation: 1406

try this one ,result as per data and output given by you,result is in case when all required field occurs at the same place in the given sample data also column and table names are my assumption for that you have to replace with the original names :-

Please find the sqlfiddle link for the below examples 

select folder,filename,count(1) downloads
 from 
   (
     select substr(detail,instr(detail,'/',1,4)+1,instr(detail,'/',1,5)
     -instr(detail,'/',1,4)-1) folder,
      SUBSTR(DETAIL,INSTR(DETAIL,'/',-1,2)+1,INSTR(DETAIL,'/',-1,1)
     -INSTR(DETAIL,'/',-1,2)-1) filename
     from examd
    ) 
  group by  folder,filename ;

Here is the solution with regexp_replace function as required by you :-

   select folder,filename,count(1) downloads
   from 
      (
        select regexp_replace(detail, '(.*?/){4}(.*)/.*$', '\2') folder,
        regexp_replace(detail, '.*/(.*)/.*', '\1') as filename
        from examd 
       )
   group by folder,filename
   order by folder,downloads desc;

one more you can try

select folder,filename,count(1) downloads
from
  (
    select regexp_replace(detail, '(.*?/){4}(.*)/.*$', '\2') folder,
    regexp_replace(detail, '(.*{2}?/)(.*)/.*$', '\2') filename from examd
  ) 
 group by folder,filename
 order by folder,downloads desc;

Upvotes: 1

Related Questions