2016年3月6日 星期日

[SQL] 於多筆重複資料中取得該重複群組中最新一筆資料

最近要做一件特別的SQL查詢,有個一個Table1,如下所示

Table1 :
group_iduser_id
111
1111
111111
222
2222
333
3333

我們想要選出各group_id最大的user_id,也就是說我們希望的結果要像是如下這樣:
group_iduser_id
111111
2222
3333

先來看一下ROW_NUMBER()語句的,如下所示,詳細資料可參考這裡

ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

它可對傳回結果集的各分割區進行編列序號,各分割區序號從 1 開始,利用這個我們就可完成我們的需求。

思路是這樣的:先把Table1以group_id進行分組(partition),各組進行以user_id的DESC排序並標上編號(例如group_id=1的三組資料編號1,2,3、group_id=2的二組資料編號1,2),接著取出各組最上面那行資料,也就是各組編號為1的那行即可。

這時我們就可以使用rownumber, over, partition語法指令來幫助我們達成目標。

我們先用以下語法對Table1以group_id進行分組,並對各組以user_id做DESC排序,並為給組的排序標上編號,Sort
SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id Order By user_id DESC) As Sort 
FROM Table1

這樣就會得到以下結果:
group_iduser_idSort
1111111
11112
1113
22221
2222
33331
3331

接著在跟Table1結合並選出Sort=1但不會select Sort欄位就行了,可以使用如下語句:
SELECT group_id, user_id FROM (
         SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id Order By user_id DESC) As Sort
         FROM Table1
) SortTable
WHERE SortTable.Sort = 1
這樣就大功告成了。

參考資料:
  1. [SQL] 於多筆重複資料中取得該重複群組中最新一筆資料

Parse ISO 8601 duration 的方法

ISO 8601 Duration是ISO 8601的Duration標準,是用來表示持續時間的字串,例如
"P3Y6M4DT12H30M5S" 就表示 "3年6個月4天12小時又30分5秒",
像Youtube A{I回傳的影片長度就是用這個標準的字串來表示,
在Java中,除了自己想辨法解析外,也可以使用以下的方法:
  1. 在Java 1.8之後,只要使用java.time.Duration這個Class就可以輕易地解析由ISO 8601 Duration表示的持續時間,範例程式如下:
  2. import java.time.Duration;
    import java.text.NumberFormat;
    import java.text.DecimalFormat;
    
    public class HelloWorld
    {
      public static void main(String[] args)
      {
        Duration duration = Duration.parse("PT58S");
        NumberFormat formatter = new DecimalFormat("00");
    
        String hours = formatter.format(duration.toHours());
        String minutes = formatter.format(duration.toMinutes());
        String seconds = formatter.format(duration.getSeconds());
    
        System.out.print(hours + ":" + minutes + ":" + seconds); //00:00:58
      }
    }

  3. 而在Java 1.8之前,因為沒有java.time.Duration,所以可以借助第三方的Class,在這邊介紹一個很好用的第三方JAR,joda-time,到官網下載引用後,就可以使用它的Period, PeriodFormatter, ISOPeriodFormater等Class來幫助我們解析ISO 8601 Duration,範例程式如下:
  4. import java.text.DecimalFormat;
    import java.text.NumberFormat;
    import org.joda.time.Period;
    import org.joda.time.format.ISOPeriodFormat;
    import org.joda.time.format.PeriodFormatter;
    
    public class DurationTest {
    
     public DurationTest() {
      // TODO Auto-generated constructor stub
     }
    
     public static void main(String[] args) {
      PeriodFormatter periodformatter = ISOPeriodFormat.standard();
      Period p = periodformatter.parsePeriod("PT58S");
      NumberFormat numberformatter = new DecimalFormat("00");  
    
      String hours = numberformatter.format(p.getHours());
      String minutes = numberformatter.format(p.getMinutes());
      String seconds = numberformatter.format(p.getSeconds());
    
      System.out.println(hours + ":" + minutes + ":" + seconds); //00:00:58
     }
    }
參考資料:
  1. How to convert Youtube API V3 duration in Java