title

有一table紀錄某資源的使用情形，包含使用日期(record_date)與使用數(qty)，如何查詢出
1.每月的最高單日使用數?
2.每月最後一筆紀錄?

每月最後一筆、各部門最多的一筆、各部門每月最多的一筆...這種查詢需求也遇過不只一次了，只是每次遇到還是會卡一下，這次來做個紀錄加強下記憶

先依照此需求結構在sqltest建立一些簡單的測試資料，簡化了日期條件(最小單位為天):

CREATE TABLE record_test 
( 
    record_date date, 
    qty number
); 

insert into record_test values (to_date('2020/05/06', 'yyyy/mm/dd'), 20);
insert into record_test values (to_date('2020/05/18', 'yyyy/mm/dd'), 42);
insert into record_test values (to_date('2020/05/27', 'yyyy/mm/dd'), 25);
insert into record_test values (to_date('2020/06/07', 'yyyy/mm/dd'), 37);
insert into record_test values (to_date('2020/06/19', 'yyyy/mm/dd'), 19);
insert into record_test values (to_date('2020/06/30', 'yyyy/mm/dd'), 29);

這邊使用的方法就是partition by，以下就是找出"最高單日使用數"資料的語法，有max與row_number的寫法

注意如果有多筆並列最大時，row_number方法只會找出第一筆，視需求調整方法或是使用rank

select to_char(record_date, 'yyyy/mm/dd') as record_date, qty from (
  select record_date, 
         qty,
         max(qty) over(partition by to_char(record_date, 'yyyymm')) as maxQty 
  from record_test
)
where qty = maxQty

select to_char(record_date, 'yyyy/mm/dd') as record_date, qty from (
   select t.record_date,
          t.qty,
          row_number() over(partition by to_char(record_date, 'yyyymm') order by qty desc) as maxRn
     from record_test t
)
where maxRn = 1

每月最後一筆紀錄依樣畫葫蘆即可:

select to_char(record_date, 'yyyy/mm/dd') as record_date, qty from (
  select record_date, 
         qty,
         max(record_date) over(partition by to_char(record_date, 'yyyymm')) as maxRecord_date 
  from record_test
)
where record_date = maxRecord_date

select to_char(record_date, 'yyyy/mm/dd') as record_date, qty from (
   select t.record_date,
          t.qty,
          row_number() over(partition by to_char(record_date, 'yyyymm') order by record_date desc) as maxRn
     from record_test t
)
where maxRn = 1