How do I calculate a median-to-date variable using Oracle SQL?

I'm working with invoice data in Oracle SQL. I want to create a variable, "median account invoice amount to date", that gives the median invoice amount for an acco开发者_开发百科unt up to that given invoice date.

Oracle actually has a median function. To find the median of all accounts for every day:

select  median(amount)
from    invoices

Or to show the median per account per day:

select  AccountNr
,       to_char(InvoiceDate, 'yyyymmdd')
,       median(amount)
from    invoices
group by
        AccountNr
,       to_char(InvoiceDate, 'yyyymmdd')

Or to show the "running median":

select  i1.custID
,       i1.inv_date
,       median(i2.amount)
from    invoices i1
join    invoices i2
on      i2.custId = i1.custID
        and i2.inv_date <= i1.inv_date
group by
        i1.custID
,       i1.inv_date

From what I understand this is what you're after. @Andomar, as Median is the middle in a series of numbers ordering this series makes no difference to the result. The middle number between the highest and the lowest cannot change...

So, median by data and account would be:

select acc_no, median(amount) over ( partition by acc_no, to_char(invoice_date,'yyyymmdd'))
  from invoices
       -- if applicable
 where invoice_date < :date

median by account would be

select acc_no, median(amount) over ( partition by acc_no )
 from invoices
where invoice_date < :date