Oracle仅获得最近日期的排名

lymnna71  于 2022-12-18  发布在  Oracle
关注(0)|答案(3)|浏览(141)

我有原点表A:
| 日|c1|价值|
| - ------|- ------|- ------|
| 二○二二年十月一日|1个|1个|
| 二○二二年十月二日|1个|第二章|
| 二〇二二年十月三日|1个|三个|
| 二○二二年十月一日|第二章|四个|
| 二○二二年十月二日|第二章|第六章|
| 二〇二二年十月三日|第二章|五个|
目前,我通过以下方式获得了最新的dt的percent_rank

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'

演示:https://www.db-fiddle.com/f/rXynTaD5nmLqFJdjDSCZpL/0
预期结果如下所示:
| 日|c1|价值|恶作剧|
| - ------|- ------|- ------|- ------|
| 二〇二二年十月三日|1个|三个|1个|
| 二〇二二年十月三日|第二章|五个|0.5分|
这意味着在2022年10月3日,c1组的percent_rank历史值为100%,而c2组为66%。
但是这个sql会对每个分区进行排序,我认为它的时间复杂度是O(n log n)
我只需要最新日期的排名,我想我可以通过计算count(last_value > value)/count()来完成。
有什么建议吗?

uz75evzq

uz75evzq1#

您可以使用ROW_NUMBER()分析函数,而不是硬编码最大日期:

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
) t
WHERE  rn = 1

其中,对于示例数据:

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;

输出:
| 数据传输|C1|价值|普兰克|注册护士|
| - ------|- ------|- ------|- ------|- ------|
| 2022年10月3日00时00分|1个|三个|1个|1个|
| 2022年10月3日00时00分|第二章|五个|五分|1个|
fiddle
但是这个sql会对每个分区进行排序,我认为它的时间复杂度是O(nlogn)。
无论你做什么,你都需要迭代整个结果集。
我只需要最新日期的排名,我想我可以通过计算count(last_value > value)/count()来完成。
然后您需要找到最后一个值(除非你是硬编码的最后一个日期)将涉及使用索引或表扫描在所有的值在每个分区和排序的值,然后找到一个计数的更大的值将需要第二个索引或表扫描。你可以剖析这两个解决方案,但我希望你会发现使用解析函数将是同样有效的,如果不是更好的话,也比尝试使用聚合函数要好。
例如:

SELECT c1,
       dt,
       value,
       ( SELECT ( COUNT(CASE WHEN value <= t.value THEN 1 END) - 1 )
                / ( COUNT(*) - 1 )
         FROM   table_name c
         WHERE  c.c1 = t.c1
       ) AS prank
FROM   table_name t
WHERE  dt = DATE '2022-10-03'


如果要访问表两次,您可能会发现表访问的I/O成本将远远超过使用不同方法可能节省的任何成本。但是,如果查看解释计划(fiddle),则查询仍在执行聚集排序,因此使用此方法不会节省任何成本,而只会增加成本。

nszi6y05

nszi6y052#

试试这个

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;

或者简单到

SELECT * from TABLENAME ORDER BY dt DESC;
piah890a

piah890a3#

我摆弄了一下,它几乎是相同的答案MT0已经把。

select dt, c1, val, prank*100 as percent_rank  from   (select 
     t1.*, 
     percent_rank() over (partition by c1 order by val) as prank,
     row_number() over (partition by c1 order by dt desc) rn    from t1) where rn=1;

结果

DT          C1  VAL PERCENT_RANK
2022-10-03  1   3   100
2022-10-03  2   5   50

http://sqlfiddle.com/#!4/ec60a/23
我使用Row_number = 1来获取最新日期。并且还将percent_rank作为percent推送。这是您想要的吗?

相关问题