sql - 在两个日期上排名 - 每个日期迭代

Question

如何查询下表中每一天的“值(value)”排名？例如:IT 应该在 20 日列出所有值的“我的日期”、“值”、“排名”，然后在 21 日对所有值执行新的排名()？谢谢...

create table tv (mydate,value)
as
select to_date('20/03/2010 00','dd/mm/yyyy HH24'),98 from dual union all
select to_date('20/03/2010 01','dd/mm/yyyy HH24'),124 from dual union all
select to_date('20/03/2010 02','dd/mm/yyyy HH24'),140 from dual union all
select to_date('20/03/2010 03','dd/mm/yyyy HH24'),138 from dual union all
select to_date('20/03/2010 04','dd/mm/yyyy HH24'),416 from dual union all
select to_date('20/03/2010 05','dd/mm/yyyy HH24'),196 from dual union all
select to_date('20/03/2010 06','dd/mm/yyyy HH24'),246 from dual union all
select to_date('20/03/2010 07','dd/mm/yyyy HH24'),176 from dual union all
select to_date('20/03/2010 08','dd/mm/yyyy HH24'),124 from dual union all
select to_date('20/03/2010 09','dd/mm/yyyy HH24'),128 from dual union all
select to_date('20/03/2010 10','dd/mm/yyyy HH24'),32010 from dual union all
select to_date('20/03/2010 11','dd/mm/yyyy HH24'),384 from dual union all
select to_date('20/03/2010 12','dd/mm/yyyy HH24'),368 from dual union all
select to_date('20/03/2010 13','dd/mm/yyyy HH24'),392 from dual union all
select to_date('20/03/2010 14','dd/mm/yyyy HH24'),374 from dual union all
select to_date('20/03/2010 15','dd/mm/yyyy HH24'),350 from dual union all
select to_date('20/03/2010 16','dd/mm/yyyy HH24'),248 from dual union all
select to_date('20/03/2010 17','dd/mm/yyyy HH24'),396 from dual union all
select to_date('20/03/2010 18','dd/mm/yyyy HH24'),388 from dual union all
select to_date('20/03/2010 19','dd/mm/yyyy HH24'),360 from dual union all
select to_date('20/03/2010 20','dd/mm/yyyy HH24'),194 from dual union all
select to_date('20/03/2010 21','dd/mm/yyyy HH24'),234 from dual union all
select to_date('20/03/2010 22','dd/mm/yyyy HH24'),328 from dual union all
select to_date('20/03/2010 23','dd/mm/yyyy HH24'),216 from dual union all
select to_date('21/03/2010 00','dd/mm/yyyy HH24'),224 from dual union all
select to_date('21/03/2010 01','dd/mm/yyyy HH24'),292 from dual union all
select to_date('21/03/2010 02','dd/mm/yyyy HH24'),264 from dual union all
select to_date('21/03/2010 03','dd/mm/yyyy HH24'),132 from dual union all
select to_date('21/03/2010 04','dd/mm/yyyy HH24'),142 from dual union all
select to_date('21/03/2010 05','dd/mm/yyyy HH24'),328 from dual union all
select to_date('21/03/2010 06','dd/mm/yyyy HH24'),184 from dual union all
select to_date('21/03/2010 07','dd/mm/yyyy HH24'),240 from dual union all
select to_date('21/03/2010 08','dd/mm/yyyy HH24'),224 from dual union all
select to_date('21/03/2010 09','dd/mm/yyyy HH24'),496 from dual union all
select to_date('21/03/2010 10','dd/mm/yyyy HH24'),370 from dual union all
select to_date('21/03/2010 11','dd/mm/yyyy HH24'),352 from dual union all
select to_date('21/03/2010 12','dd/mm/yyyy HH24'),438 from dual union all
select to_date('21/03/2010 13','dd/mm/yyyy HH24'),446 from dual union all
select to_date('21/03/2010 14','dd/mm/yyyy HH24'),426 from dual union all
select to_date('21/03/2010 15','dd/mm/yyyy HH24'),546 from dual union all
select to_date('21/03/2010 16','dd/mm/yyyy HH24'),546 from dual union all
select to_date('21/03/2010 17','dd/mm/yyyy HH24'),684 from dual union all
select to_date('21/03/2010 18','dd/mm/yyyy HH24'),568 from dual union all
select to_date('21/03/2010 19','dd/mm/yyyy HH24'),504 from dual union all
select to_date('21/03/2010 20','dd/mm/yyyy HH24'),392 from dual union all
select to_date('21/03/2010 21','dd/mm/yyyy HH24'),256 from dual union all
select to_date('21/03/2010 22','dd/mm/yyyy HH24'),236 from dual union all
select to_date('21/03/2010 23','dd/mm/yyyy HH24'),168 from dual

Answer 1

想按时间订购:

SELECT mydate
       , value
       , rank() over (partition by trunc(mydate) order by mydate)   
FROM tv;

想要按值(value)排序:

SELECT mydate
      , value
      , rank() over (partition by trunc(mydate) order by value) 
FROM tv;