Oracle中多列上的rank()函数

你现在面临的主要问题是:

max(travel_date) OVER (partition by travel_date)

其中包括分区中每个日期的时间部分,所以您实际上得到了每个日期/时间的最大值,即该日期/时间。您似乎希望每天的最大日期/时间,这样您就可以按 白天 通过使用 trunc() 在PARTITION BY子句中:

max(travel_date) OVER (partition by trunc(travel_date))

只是这个变化给了你:

   TRANSID TRAVEL_DATE                 RN        RN2
---------- ------------------- ---------- ----------
      2341 2018-04-04 10:00:00          1          1
      2341 2018-04-04 11:30:00          2          1
      2341 2018-04-05 11:30:00          3          2
      2891 2018-04-04 12:30:00          1          1
      2891 2018-04-04 18:30:00          2          1
      2891 2018-04-05 22:30:00          3          2

不过,外部查询中的分区也是错误的,您需要按“最早”日期(实际上是最晚的,但这并不重要)进行分区:

select transID,travel_date,rn,
    dense_rank () over (partition by transID,EarliestDate order by travel_date) as rn2
    from
    (SELECT transID,travel_date,
             ROW_NUMBER() OVER (PARTITION BY transID ORDER BY travel_date) AS rn, 
             max(travel_date) OVER (partition by trunc(travel_date)) as EarliestDate
      FROM travel_log_info
     ) t
     order by transID;

   TRANSID TRAVEL_DATE                 RN        RN2
---------- ------------------- ---------- ----------
      2341 2018-04-04 10:00:00          1          1
      2341 2018-04-04 11:30:00          2          2
      2341 2018-04-05 11:30:00          3          1
      2891 2018-04-04 12:30:00          1          1
      2891 2018-04-04 18:30:00          2          2
      2891 2018-04-05 22:30:00          3          1

但是您不需要这个最大值,也不需要您当前拥有的外部查询;如果您在 row_number() 分区(您当前没有真正使用的分区)可以:

SELECT transID,travel_date,
  ROW_NUMBER() OVER (PARTITION BY transID, trunc(travel_date) ORDER BY travel_date) AS rn
FROM travel_log_info;

   TRANSID TRAVEL_DATE                 RN
---------- ------------------- ----------
      2341 2018-04-04 10:00:00          1
      2341 2018-04-04 11:30:00          2
      2341 2018-04-05 11:30:00          1
      2891 2018-04-04 12:30:00          1
      2891 2018-04-04 18:30:00          2
      2891 2018-04-05 22:30:00          1

然后您可以将其包装在一个外部查询中以筛选 rn :

SELECT transID,travel_date
FROM (
  SELECT transID,travel_date,
    ROW_NUMBER() OVER (PARTITION BY transID, trunc(travel_date) ORDER BY travel_date) AS rn
  FROM travel_log_info
)
WHERE rn = 1
ORDER BY transID,travel_date;

   TRANSID TRAVEL_DATE        
---------- -------------------
      2341 2018-04-04 10:00:00
      2341 2018-04-05 11:30:00
      2891 2018-04-04 12:30:00
      2891 2018-04-05 22:30:00

您也可以在不使用子查询的情况下执行此操作;这将得到相同的结果 using first :

SELECT transID,
  min(travel_date) keep (dense_rank first order by travel_date) as travel_date
FROM travel_log_info
GROUP BY transID, trunc(travel_date)
ORDER BY transID, travel_date;