On 05-Jul-2016 18:38 -0500, Englander, Douglas wrote:
Does anyone know if it is possible to perform a SQL OVER(PARTITION
BY...) as a subselect? I want to only perform the OVER(PARTITION BY
for the row that is being selected in a SELECT statement. I would
rather not execute a OVER(PARTITION BY over the whole second table,
since it causes 166,000,000+ rows to be partitioned that don't need
to be.
A query with an OLAP-specification can be coded in a correlated
subquery. More inline\tailing comments follow.
I am selecting rows from a main table, and using a column in that
table to narrow down the PARTITION BY to only select rows in the
second table that match what is selected from the main table. I need
the partition by as a further criteria on the main table select so I
only include rows from the main where a specific selection exists in
the second table, and that condition needs to be set up as a
partition by.
For example:
Select a, b, c, d
from tablea
where A = 100
and B = 2
and C in ( select *
from (OVER(PARTITION BY SYNTAX
built only over rows in tableb
that have a value in a column ZZ
that equals the value in C ) )
)
We are on 7.1
The example is unclear. Some DDL as sample tables and some DML
providing data to populate the tables, along with the expected output
from a query, could go a long way to elucidating the scenario.
The asterisk seems probable to represent more than one column, yet
the one column C as the first operand of the IN predicate can only have
one [compatible] column of selected values as the second operand for the
IN predicate. And that the column tableB.ZZ must equal the value in
tableA.C for a predicate involving tableA.C seems flawed; perhaps values
for tableB.ZZ should be identifying which values of tableA.C are
included [per the IN predicate], and that some other correlated
selection limits the data in the OLAP subquery.?
The following is a contrived but functional example of a query using
a subquery with an OLAP specification, correlated with the data from the
outer query, to effect limiting the data generated in that OLAP query:
select od1.odlbnm, od1.odobnm
, od1.odobtp, od1.odobat
, od1.odobow, od1.odobsz
from od as od1
where (odobtp, odobat, 1) in
( select
odobtp
, odobat
, dense_rank()
over(partition by odlbnm, odobtp, odobat
order by odobsz desc ) as rank_size
from od2 as od2
where od2.odobow = od1.odlbnm
and od2.odobsz <= od1.odobsz
and od2.odobnm != od1.odobnm
)
order by
odlbnm, od1.odobtp, od1.odobat, od1.odobsz desc
The following is a contrived but functional example of a query using
a LATERAL join specification, referencing data from the prior file to
limit the data generated in the OLAP query as the joined-to derived table:
select od1.odlbnm, od1.odobnm , lq.odobnm
, od1.odobtp, lq .odobtp
, od1.odobat, lq .odobat
, od1.odobow, od1.odobsz
, lq .odobsz, lq .ranking
from od as od1
left join lateral
( select
odobtp
, odobat
, odobnm
, odobsz
, dense_rank()
over(partition by odlbnm, odobtp, odobat
order by odobsz desc ) as ranking
from od2 as od2
where odobow = od1.odlbnm
and odobsz <= od1.odobsz
and odobnm != od1.odobnm
) lq
on od1.odobtp = lq .odobtp
and od1.odobat = lq .odobat
where lq .ranking = 1
order by
odlbnm, od1.odobtp, od1.odobat, od1.odobsz desc
midrange.com
