sql - Postgresql dense_rank 操作

Question

我有一个这样的表:

     Name     | Time  |  
     Sam      | 10:58 | 
     Sam      | 10:59 | 
     Sam      | 11:10 |   
     Tom      | 1:16  |   
     Tom      | 1:17  |   
     Tom      | 2:10  |  
     Tom      | 3:44  | 
     Tom      | 3:45  | 

基本上，它是一个记录个人事件并显示事件时间的表格。出现在这张表上的任何东西都是违法的，它们通常被组合在一起，信息很少。根据经验，如果事件时间相隔不超过 3 分钟，则视为同一违规/违规行为。所以一个人可以在表中有多个条目，但他们可能属于同一个违规行为/有多个条目但属于不同的违规行为。

理想情况下，我希望表格看起来像这样:

 Name     | Time  | Infraction Number|  
 Sam      | 10:58 |         1        |
 Sam      | 10:59 |         1        |
 Sam      | 11:10 |         2        |  
 Tom      | 1:16  |         1        |  
 Tom      | 1:17  |         1        |  
 Tom      | 2:10  |         2        | 
 Tom      | 3:44  |         3        | 
 Tom      | 3:45  |         3        | 

无论如何我可以利用 dense_rank 在 postgresql 中做这样的事情吗？

Answer 1

SELECT Name,
       EXTRACT( HOUR FROM  time1 )||':'||EXTRACT( MINUTE FROM  time1 ) AS             Newtime ,       
       DENSE_RANK() OVER ( PARTITION BY name ORDER BY new_time ) AS Infraction_Number
  FROM
    (
        SELECT name,
               time1,             
               CASE WHEN ( EXTRACT( EPOCH FROM time1 ) - EXTRACT( EPOCH FROM time1_lag ) )/ 60 IS NULL OR 
                         ( EXTRACT( EPOCH FROM time1_lead ) - EXTRACT( EPOCH FROM time1 ) )/ 60 = 1 
                    THEN time1
                    WHEN ( EXTRACT( EPOCH FROM time1 ) - EXTRACT( EPOCH FROM time1_lag ) )/ 60 = 1
                    THEN time1_lag
                    WHEN ( EXTRACT( EPOCH FROM time1 ) - EXTRACT( EPOCH FROM time1_lag ) )/ 60 <> 1
                    THEN time1
                    WHEN ( EXTRACT( EPOCH FROM time1 ) - EXTRACT( EPOCH FROM time1_lag ) )/ 60 IS NULL OR
                         ( EXTRACT( EPOCH FROM time1_lead ) - EXTRACT( EPOCH FROM time1 ) )/ 60 = 1
                    THEN time1 
                END AS new_time                        
          FROM
            (
                SELECT name, 
                       time1, 
                       LAG( time1, 1) OVER ( PARTITION BY name ORDER BY time1 ) AS time1_lag,
                       LEAD( time1, 1) OVER ( PARTITION BY name ORDER BY time1 ) AS time1_lead
                  FROM Yourtable
            )  
    )   ;