In Postgres 9.4+, you can use the hypothetical-set aggregate function percent_rank() in a LATERAL join like this:
SELECT *
FROM daily_data d, LATERAL (
SELECT round(percent_rank(d.daily_val) WITHIN GROUP (ORDER BY daily_val)::numeric
, 6) AS pctl_calc
FROM daily_data
WHERE company_id = d.company_id
AND trade_date < d.trade_date
) x
ORDER BY company_id, trade_date;
The CROSS JOIN LATERAL (, LATERAL for short) is safe, because a subquery with an aggregate function always returns a row. (Else you might want LEFT JOIN LATERAL (...) x ON true.)
Minor difference: The first row in each group returns 0, not 1 like in your example. Easily fixed, if you need it.
This would do the same job in Postgres 9.3:
SELECT d.*
, round(CASE WHEN x.ct = 0 THEN numeric '1' -- or 0 ?
ELSE x.ct_lt / x.ct END, 6) AS pctl_calc
FROM daily_data d, LATERAL (
SELECT count(daily_val) AS ct
, count(daily_val < d.daily_val OR NULL)::numeric As ct_lt
FROM daily_data
WHERE company_id = d.company_id
AND trade_date < d.trade_date
) x
ORDER BY company_id, trade_date;
I use count(daily_val) for the full count, not count(*) to ignore NULL values - necessary if daily_val can be NULL, .
SQL Fiddle.
Or, without LATERAL join for even older versions. May be faster, too:
SELECT company_id, trade_date, daily_val, pctl
, round(CASE WHEN ct = 0 THEN numeric '1' -- 0 ?
ELSE ct_lt / ct END, 6) AS pctl_calc
FROM (
SELECT d.*
, count(x.daily_val) AS ct
, count(x.daily_val < d.daily_val OR NULL)::numeric AS ct_lt
FROM daily_data d
LEFT JOIN daily_data x ON x.company_id = d.company_id
AND x.trade_date < d.trade_date
GROUP BY d.id -- pg 9.1+
) sub
ORDER BY company_id, trade_date;
Assuming id is the PK for a simplified GROUP BY, requires pg 9.1 though.
Best Answer
That is indeed not clear from the documentation, but a simple experiment shows that advisory locks are local to the database. That is, you can take the same advisory lock in two different databases without conflict.