一旦值更改，在分区内重新启动编号

Question

我正在努力解决非常简单(我认为(的主题，我无法运行。

示例数据：

|ready_signal|timestamp          |
|4           |2017-03-17 17:58:25|
|4           |2017-03-17 17:58:24|
|4           |2017-03-17 17:58:23|
|0           |2017-03-17 17:58:22|
|0           |2017-03-17 17:58:21|
|0           |2017-03-17 17:58:19|
|4           |2017-03-17 17:58:18|
|4           |2017-03-17 17:58:15|
|0           |2017-03-17 17:58:10|
|0           |2017-03-17 17:58:09|
|0           |2017-03-17 17:58:04|
|4           |2017-03-17 17:58:03|

现在我想要实现的是，在每次ready_signal更改中获取时间戳的最大值。所以结果应该看起来像：

|ready_signal|timestamp          |
|4           |2017-03-17 17:58:25|
|0           |2017-03-17 17:58:22|
|4           |2017-03-17 17:58:18|
|0           |2017-03-17 17:58:10|
|4           |2017-03-17 17:58:09|

我正在尝试使用分区功能，ROW_NUMBER等，但没有成功。我无法对这些列进行分区。对ready_signal进行分区将仅返回两个值(并在分区中使用 ORDER BY(。

我想，肯定有人有同样的问题。就像，我需要单个唯一的分区号，但每次ready_signal值都会更改。

抱歉，没有发布示例代码。这是我正在尝试的：

SELECT ready, 
    timestamp,
    ROW_NUMBER() OVER(PARTITION BY ready ORDER BY timestamp DESC) AS readyTime
FROM bubu
ORDER BY timestamp DESC

我还尝试获取一些最大值：

SELECT ready, 
    timestamp,
    ROW_NUMBER() OVER(PARTITION BY ready ORDER BY timestamp DESC) AS readyTime
FROM bubu
ORDER BY timestamp DESC

Answer 1

这很接近，但遇到了我对这个问题发表评论的问题 - 因为时间戳不是唯一的，所以可以获得不同的结果集。在这种情况下，我经常得到另一0行2017-03-17 17:58:09因为可以想象其中一个发生在4之前。

无论如何，编写

此方法的方法是，您要选择没有后继项或后继项具有不同值的行 ready_state .一旦你像这样重新陈述问题，代码实际上就会自己编写：

declare @t table (ready_signal int,timestamp datetime)
insert into @t(ready_signal,timestamp) values
(4,'2017-03-17T17:58:25'),
(4,'2017-03-17T17:58:24'),
(4,'2017-03-17T17:58:23'),
(0,'2017-03-17T17:58:22'),
(0,'2017-03-17T17:58:21'),
(0,'2017-03-17T17:58:19'),
(4,'2017-03-17T17:58:18'),
(4,'2017-03-17T17:58:15'),
(0,'2017-03-17T17:58:10'),
(0,'2017-03-17T17:58:09'),
(0,'2017-03-17T17:58:09'),
(4,'2017-03-17T17:58:09')
;With Numbered as (
    select ready_signal,timestamp,
        ROW_NUMBER() OVER (ORDER BY timestamp) as rn
    from @t
)
select
    t1.ready_signal,t1.timestamp
from
    Numbered t1
        left join
    Numbered t2
        on
            t1.rn = t2.rn - 1
where
    t2.rn is null or --No successor
    t2.ready_signal != t1.ready_signal --Successor different

结果：

ready_signal timestamp
------------ -----------------------
0            2017-03-17 17:58:09.000
4            2017-03-17 17:58:09.000
0            2017-03-17 17:58:10.000
4            2017-03-17 17:58:18.000
0            2017-03-17 17:58:22.000
4            2017-03-17 17:58:25.000

(如果结果集的顺序对您很重要，则可以添加显式ORDER BY(

Answer 2

尝试下面的查询，这将为您提供问题中所述的确切所需输出。

DECLARE @SAMPLEDATA TABLE(READYSIGNAL INT,TIMESTAMPP DATETIME)
INSERT INTO @SAMPLEDATA VALUES
(4,'2017-03-17 17:58:25'),
(4,'2017-03-17 17:58:24'),
(4,'2017-03-17 17:58:23'),
(0,'2017-03-17 17:58:22'),
(0,'2017-03-17 17:58:21'),
(0,'2017-03-17 17:58:19'),
(4,'2017-03-17 17:58:18'),
(4,'2017-03-17 17:58:15'),
(0,'2017-03-17 17:58:10'),
(0,'2017-03-17 17:58:09'),
(0,'2017-03-17 17:58:09'),
(4,'2017-03-17 17:58:09')
;WITH SAMPLEDATA
AS
(
SELECT *,1 COL FROM (SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 100))SNO,*
 FROM @SAMPLEDATA)T WHERE SNO=1
UNION ALL
SELECT T2.SNO,T2.READYSIGNAL,T2.TIMESTAMPP,
    CASE WHEN T1.READYSIGNAL=T2.READYSIGNAL THEN T1.COL ELSE T1.COL+1 END
        FROM SAMPLEDATA T1 JOIN (SELECT ROW_NUMBER()OVER(ORDER BY (SELECT     100))SNO,*
 FROM @SAMPLEDATA) T2 ON T1.SNO=T2.SNO-1
)
SELECT READYSIGNAL,TIMESTAMPP FROM
    (SELECT READYSIGNAL,MAX(TIMESTAMPP)TIMESTAMPP,MAX(SNO)SNO FROM     SAMPLEDATA     GROUP BY COL,READYSIGNAL)RESULT ORDER BY SNO 

输出

------------------------------------------
--READYSIGNAL   TIMESTAMPP
------------------------------------------
    4           2017-03-17 17:58:25.000
    0           2017-03-17 17:58:22.000
    4           2017-03-17 17:58:18.000
    0           2017-03-17 17:58:10.000
    4           2017-03-17 17:58:09.000
-----------------------------------------