I have origin table A:

Currently I got the latest dt's percent_rank by:

select * from
(
select
*,
percent_rank() over (partition by c1 order by value) as prank
from A
) as pt
where pt.dt = Date'2022-10-3'

Demo: https://www.db-fiddle.com/f/rXynTaD5nmLqFJdjDSCZpL/0

the excepted result looks like:

Which means at 2022-10-3, the value in c1 group's percent_rank in history is 100% while in c2 group is 66%.

But this sql will sort evey partition which I thought it's time complexity is O(n log n).

I just need the latest date's rank and I thought I could do that by calculating count(last_value > value)/count().

Any suggestions?

CodePudding user response：

Try this

select t.c1, t.dt, t.value
from TABLENAME t
inner join (
    select c1, max(dt) as MaxDate
    from TABLENAME
    group BY dt
) tm on t.c1 = tm.c1 and t.dt = tm.MaxDate ORDER BY dt DESC;

Or as simple as

SELECT * from TABLENAME ORDER BY dt DESC;

CodePudding user response：

Rather than hard-coding the maximum date, you can use the ROW_NUMBER() analytic function:

SELECT *
FROM   (
  SELECT t.*,
         PERCENT_RANK() OVER (PARTITION BY c1 ORDER BY value) AS prank,
         ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY dt DESC) AS rn
  FROM   table_name t
)
WHERE  rn = 1

Which, for the sample data:

CREATE TABLE table_name (dt, c1, value) AS
SELECT DATE '2022-10-01', 1, 1 FROM DUAL UNION ALL
SELECT DATE '2022-10-02', 1, 2 FROM DUAL UNION ALL
SELECT DATE '2022-10-03', 1, 3 FROM DUAL UNION ALL
SELECT DATE '2022-10-01', 2, 4 FROM DUAL UNION ALL
SELECT DATE '2022-10-02', 2, 6 FROM DUAL UNION ALL
SELECT DATE '2022-10-03', 2, 5 FROM DUAL;

Outputs:

fiddle