yabchexu yabchexu - 4 months ago 20
SQL Question

get median in overlap time range

vertica db, for example, have a table called

revenue
:

date revenue
2016-07-12 1
2016-07-12 10
2016-07-12 5
2016-07-12 3
2016-07-13 7
2016-07-13 120
2016-07-13 22
2016-07-14 5
2016-07-14 17


The tricky thing is I don't want median for each date but I want to calculate the median revenue for the timerange >= given each day, for example the result would be like:

daterange median_revenue
>= 2016-07-12 7
>= 2016-07-13 17
>= 2016-07-14 11


to be clear:

7 = median(1,10,5,3,7,120,22,5,17)
17 = median(7,120,22,5,17)
11 = median(5,17)


How could I write a sql script for these daterange? Is there an easy way to query? I don't want to calculate in each daterange then union because there are many days.

Answer

Would this help?

SELECT
    date_table.[date],
    MEDIAN (r.revenue) AS median_revenue
FROM 
    (SELECT DISTINCT [date] FROM revenue) date_table
LEFT JOIN revenue r ON r.[date] >= r_main.[date]
GROUP BY
    date_table.[date]