CODEWITHSUNDEEP
sqloracle-databasewindow-functions
br4lu
br4lu

Reputation: 11

Window function issue - max over partition

I try to rewrite such SQL statements (with many subqueries) to more efficient form using outer join and max/count/... over partition. Old statements:

select a.ID,
     (select max(b.valA) from something b where a.ID = b.ID_T and b.status != 0),
     (select max(b.valB) from something b where a.ID = b.ID_T and b.status != 0),
     (select max(b.valC) from something b where a.ID = b.ID_T and b.status != 0),
     (select max(b.valD) from something b where a.ID = b.ID_T)
from tool a;

What is important here - there is different condition for max(b.valD). Firstly I didn't noticed this difference and write something like this:

select distinct a.ID,
      max(b.valA) over (partition by b.ID_T),
      max(b.valB) over (partition by b.ID_T),
      max(b.valC) over (partition by b.ID_T),
      max(b.valD) over (partition by b.ID_T),
from tool a, 
     (select * from something
     where status != 0) b
where a.ID = b.ID_T(+);

Could I use somewhere in max over partition this condition of b.status != 0 ? Or should I better add 3rd table to join like this:

select distinct a.ID,
      max(b.valA) over (partition by b.ID_T),
      max(b.valB) over (partition by b.ID_T),
      max(b.valC) over (partition by b.ID_T),
      max(c.valD) over (partition by c.ID_T),
from tool a, 
     (select * from something 
      where status != 0) b, 
     something c
where a.ID = b.ID_T(+)
     and a.ID = c.ID_T(+);

The issue is with selecting and joining millions of rows, my example is just simplification of my query. Could anyone help me to achieve more efficient sql?

Upvotes: 1

Views: 1209

Answers (2)

valex
valex

Reputation: 24144

One more way is to use JOIN and group by subquery:

select a.ID,
     b.MAX_A,
     b.MAX_B,
     b.MAX_C,
     b2.MAX_D 
from tool a
LEFT JOIN
 (
    SELECT ID_T,max(valA) MAX_A, max(valB) MAX_B, max(valC) MAX_C
    FROM something 
    WHERE status != 0
    GROUP BY ID_T     
  ) b
  ON a.ID=b.ID_T
LEFT JOIN
 (
    SELECT ID_T, max(valD) MAX_D
    FROM something 
    GROUP BY ID_T     
  ) b2
  ON a.ID=b2.ID_T

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55524

You could try to do this using CASE:

select a.ID,
       max(CASE WHEN b.status=0 THEN b.valA END),
       max(CASE WHEN b.status=0 THEN b.valB END),
       max(CASE WHEN b.status=0 THEN b.valC END),
       max(b.valD)
  from tool a
  left join something b ON( b.ID_T = a.ID )
  group by a.ID;

Note that I replaced your implicit join by the "new" join-syntax for better readability.

Upvotes: 1

Related Questions