Search code examples
sqloracle-databaseoracle11g

Join 2 SQL queries into one

could someone help me to join 2 queries into one? Basically it's the same query with different clauses (please notice the FIELD3 filters and related counters into the subquery):

SELECT  A.FIELD1,A.FIELD2,A.FIELD3
   FROM 
   TABLE A 
   INNER JOIN
   (
    SELECT FIELD1, COUNT(1) 
    FROM TABLE
    where SUBSTR(FIELD3,1,5)='33377' and
    timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
    GROUP BY FIELD1
    HAVING COUNT(1) >= 100
    ) B
    ON A.FIELD1 = B.FIELD1 where ...some other clauses who interacts with FIELD4,5,6,etc... ;

SELECT  A.FIELD1,A.FIELD2,A.FIELD3
   FROM 
   TABLE A 
   INNER JOIN
   (
    SELECT FIELD1, COUNT(1) 
    FROM TABLE
    where SUBSTR(FIELD3,1,5)!='33377' and
    timestamp between sysdate - interval '20' minute and sysdate - interval '2' minute
    GROUP BY FIELD1
    HAVING COUNT(1) >= 150
    ) B
    ON A.FIELD1 = B.FIELD1 where ...some other clauses who interacts with FIELD4,5,6,etc... ;

What's my goal? Let me explain better. I have an Oracle DB with a table with some fields, for this query a need only 3 fields as shown below. I'm trying to make a query with multiple counts. What I need is a query that output a list who exceeded a specific count based on the first 5 digits of FIELD3. Let me do a specific example:

This is what I have into the DB:

FIELD1               FIELD2               FIELD3          
1234567314           333776543585218      333771434591151
1234567871           333771451776784      333771432365581
1234567314           333776543585218      333771240553976
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567337           333773660813075      333773650804767
1234567137           333773660798439      333771222628311
1234567319           333776543585219      333773660667594
1234567314           333776543585218      333901451463400
1234567314           333776543585218      333901451463400

Now, I want to output numbers in field1 in the following way:

OUTPUT FIELD1 (and related FIELD2 and FIELD3) who are exceeded a COUNT1 (EG: 3) based on FIELD3 having the same 5 first digits (33377)
OUTPUT FIELD1 (and related FIELD2 and FIELD3) who are exceeded a COUNT2 (EG: 10) based on FIELD3 NOT having the same 5 first digits (33377)

So, in the upper example, my output will be:

1234567314           333776543585218      333771434591151
1234567314           333776543585218      333771240553976
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333773861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333023861003473
1234567314           333776543585218      333901451463400
1234567314           333776543585218      333901451463400

33377 = 4 occurencies, Others = 5 occurences. First threshold exceeded the count so, report all rows.

Basically the 2 queries reported above works perfect, but I would like to join them into one to minimize query time and have an unique output.

Many thanks. Lucas

Solution

  • Combining the two queries is easy enough. Two suggestions, though. First, use a CTE rather than inline view. It doesn't change the performance -- it just looks...cleaner, less cluttered (imho). Second, if you're going to combine two queries like this, add a field that identifies each row with the reason it is there. Makes the analyst's job a little easier.

    with
Tablecounts( Field1, Hits, Misses )As(
    Select  Field1,
            Sum( Case When Substr( Field3, 1, 5) = '33377' Then 1 Else 0 End ),
            Sum( Case When Substr( Field3, 1, 5) = '33377' Then 0 Else 1 End )
    From    Table
    Where   Timestamp Between Sysdate - Interval '20' Minute And Sysdate - Interval '2' Minute
    Group By Field1
)
Select  Rd.Field1, Rd.Field2, Rd.Field3,
        case when tc.Hits > 100
             then 'This is a hit'
             else 'This is a miss...or something' end as Why
From    Table     Rd
Join    Tablecounts Tc
    On  Tc.Field1 = Rd.Field1
    and( tc.Hits > 100 or tc.Misses > 150 );

    EDIT: I rewrote using analytics. Except for slight differences in style, it is the same as Gordon's. But the comments under Gordon's answer suggest there was a problem. It looks to me like it should work. Was there actually a problem and, if so, what was it?

    with
Counts( Field1, Field2, Field3, Hits, Misses )As(
    Select  Field1, Field2, Field3,
            Sum( Case When Field3 Like '33377%' Then 1 Else 0 End ) Over( Partition By Field1 ),
            Sum( Case When Field3 Like '33377%' Then 0 Else 1 End ) Over( Partition By Field1 )
    From    Table
    Where   Timestamp Between Sysdate - Interval '20' Minute And Sysdate - Interval '2' Minute
)
Select  Field1, Field2, Field3,
        Case When Hits > 3
             Then 'This is a hit'
             else 'This is a miss...or something' end as Why
From    Counts
where   Hits > 100 or Misses > 150;