排序后的排名/分组结果

Question

我有一个SQL查询，我正在尝试对排序后显示的结果进行排名/分组。

这适用于从一个存储位置(SLOC)到另一个存储位置(SLOC)的情况(UNIQSN)。我正在尝试获取它在存储位置之间按时间顺序旅行的历史。

“代码”胜过千言万语！

我尝试了下面的查询：

SELECT 
  uniqsn,
  SLOC,
  Update_DATETIME,
  RANK() OVER (PARTITION BY SLOC ORDER BY Update_DATETIME) RANKING
FROM TABLE

我得到了

UNIQSN  SLOC    UPDATE_DATETIME RANKING
6039133 C114    2014/10/13 16:35:18 1
6039133 C114    2015/02/23 07:58:22 2
6039133 C119    2014/09/23 20:57:30 1
6039133 C119    2014/09/23 20:57:57 2
6039133 C119    2014/09/25 08:11:19 3
6039133 C119    2015/01/29 17:39:50 4
6039133 C119    2015/01/29 17:42:02 5
6039133 C119    2015/01/30 09:01:02 6
6039133 C119    2017/03/04 09:46:21 7
6039133 C119    2017/03/04 09:46:28 8
6039133 C119    2017/03/09 07:18:27 9
6039133 C11M    2014/09/25 08:11:19 1
6039133 C11M    2014/10/13 12:11:44 2
6039133 C11M    2014/10/13 16:35:17 3
6039133 C11M    2014/10/14 07:58:59 4
6039133 C11M    2014/10/14 07:59:27 5
6039133 C11M    2014/10/14 08:03:06 6
6039133 C11M    2015/01/30 09:01:19 7
6039133 C11M    2015/02/20 14:08:37 8
6039133 C11M    2015/02/23 07:58:21 9
6039133 C11M    2017/03/09 07:18:28 10
6039133 C11Z    2014/10/14 08:03:07 1

相反，我正在尝试实现下面的结果。有人能帮上忙吗？

UNIQSN  SLOC    UPDATE_DATETIME     RANKING
6039133 C119    2014/09/23 20:57:30     1
6039133 C119    2014/09/23 20:57:57     1
6039133 C119    2014/09/25 08:11:19     1
6039133 C11M    2014/09/25 08:11:19     2
6039133 C11M    2014/10/13 12:11:44     2
6039133 C11M    2014/10/13 16:35:17     2
6039133 C114    2014/10/13 16:35:18     3
6039133 C11M    2014/10/14 07:58:59     4
6039133 C11M    2014/10/14 07:59:27     4
6039133 C11M    2014/10/14 08:03:06     4
6039133 C11Z    2014/10/14 08:03:07     5
6039133 C119    2015/01/29 17:39:50     6
6039133 C119    2015/01/29 17:42:02     6
6039133 C119    2015/01/30 09:01:02     6
6039133 C11M    2015/01/30 09:01:19     7
6039133 C11M    2015/02/20 14:08:37     7
6039133 C11M    2015/02/23 07:58:21     7
6039133 C114    2015/02/23 07:58:22     8
6039133 C119    2017/03/04 09:46:21     9
6039133 C119    2017/03/04 09:46:28     9
6039133 C119    2017/03/09 07:18:27     9
6039133 C11M    2017/03/09 07:18:28     10

谢谢!

Answer 1

你有一个缝隙和岛屿问题。此外，您希望按最早的日期对组进行排序(据我所知)。

为此，请执行以下操作：

select t.*, dense_rank() over (partition by uniqsqn order by min_ud) as ranking               
from (select t.*,
             min(update_datetime) over (partition by uniqsqn, sloc, seqnum - seqnum_us) as min_ud
      from (select t.*,
                   row_number() over (partition by unisqn order by update_datetime) as seqnum,
                   row_number() over (partition by uniqsqn, sloc order by update_datetime) as seqnum_us
            from t
           ) t
     ) t;

Answer 2

;With Cte1
AS
(     
SELECT '6039133' AS UNIQSN, 'C114' AS SLOC,   '2014/10/13 16:35:18' AS UPDATE_DATETIME UNION ALL
SELECT '6039133', 'C114',   '2015/02/23 07:58:22'                                      UNION ALL
SELECT '6039133', 'C119',   '2014/09/23 20:57:30'                                      UNION ALL
SELECT '6039133', 'C119',   '2014/09/23 20:57:57'                                      UNION ALL
SELECT '6039133', 'C119',   '2014/09/25 08:11:19'                                      UNION ALL
SELECT '6039133', 'C119',   '2015/01/29 17:39:50'                                      UNION ALL
SELECT '6039133', 'C119',   '2015/01/29 17:42:02'                                      UNION ALL
SELECT '6039133', 'C119',   '2015/01/30 09:01:02'                                      UNION ALL
SELECT '6039133', 'C119',   '2017/03/04 09:46:21'                                      UNION ALL
SELECT '6039133', 'C119',   '2017/03/04 09:46:28'                                      UNION ALL
SELECT '6039133', 'C119',   '2017/03/09 07:18:27'                                      UNION ALL
SELECT '6039133', 'C11M',   '2014/09/25 08:11:19'                                      UNION ALL
SELECT '6039133', 'C11M',   '2014/10/13 12:11:44'                                      UNION ALL
SELECT '6039133', 'C11M',   '2014/10/13 16:35:17'                                      UNION ALL
SELECT '6039133', 'C11M',   '2014/10/14 07:58:59'                                      UNION ALL
SELECT '6039133', 'C11M',   '2014/10/14 07:59:27'                                      UNION ALL
SELECT '6039133', 'C11M',   '2014/10/14 08:03:06'                                      UNION ALL
SELECT '6039133', 'C11M',   '2015/01/30 09:01:19'                                      UNION ALL
SELECT '6039133', 'C11M',   '2015/02/20 14:08:37'                                      UNION ALL
SELECT '6039133', 'C11M',   '2015/02/23 07:58:21'                                      UNION ALL
SELECT '6039133', 'C11M',   '2017/03/09 07:18:28'                                      UNION ALL
SELECT '6039133', 'C11Z',   '2014/10/14 08:03:07'
)
,Cte2
AS
(
SELECT 
  uniqsn,
  SLOC,
  Update_DATETIME,
  RANK() OVER (PARTITION BY SLOC ORDER BY Update_DATETIME) RANKING
FROM Cte1
)
SELECT * from Cte2 Order by RANKING