采样重复序列的初始值

为了避免重复，这里只做一个查询是不够的，你需要一个循环。我试图在 PL\SQL 中实现这一点，但不知何故，由于缺乏经验，我遇到了很多错误。我用TSQL写了一个概念，如果我能把它翻译成Oracle，那么一切都会如其所愿

-- Это исходная таблица
CREATE TABLE #Table1 (ID INT, DDATE DATETIME, SSTATUS VARCHAR(255))

INSERT #Table1 (ID, DDATE, SSTATUS) VALUES
(  1 , '2000-01-01 01:00:00.0' , 'aaaaaa'),
(  1 , '2000-01-01 02:00:00.0' , 'aaaaaa'),
(  1 , '2000-01-01 03:00:00.0' , 'aaaaaa'),
(  1 , '2000-01-01 04:00:00.0' , 'aaaaaa'),
(  1 , '2000-02-01 05:00:00.0' , 'bbbbbb'),
(  1 , '2000-02-01 06:00:00.0' , 'cccccc'),
(  1 , '2000-02-01 07:00:00.0' , 'cccccc'),
(  1 , '2000-02-01 08:00:00.0' , 'bbbbbb'),
(  1 , '2000-02-01 09:00:00.0' , 'cccccc'),
(  1 , '2000-02-01 10:00:00.0' , 'cccccc'),
(  1 , '2000-02-01 11:00:00.0' , 'cccccc'),
(  1 , '2000-02-01 12:00:00.0' , 'dddddd'),
(  2 , '2000-01-01 13:00:00.0' , 'aaaaaa'),
(  2 , '2000-02-01 14:00:00.0' , 'aaaaaa'),
(  2 , '2000-02-01 15:00:00.0' , 'aaaaaa'),
(  2 , '2000-02-01 16:00:00.0' , 'aaaaaa'),
(  2 , '2000-03-01 17:00:00.0' , 'bbbbbb'),
(  2 , '2000-03-01 18:00:00.0' , 'cccccc'),
(  2 , '2000-03-01 19:00:00.0' , 'cccccc'),
(  2 , '2000-04-01 20:00:00.0' , 'bbbbbb'),
(  2 , '2000-04-01 21:00:00.0' , 'cccccc'),
(  2 , '2000-04-01 22:00:00.0' , 'cccccc'),
(  2 , '2000-04-01 23:00:00.0' , 'cccccc'),
(  2 , '2000-04-01 23:30:00.0' , 'dddddd')

-- Это промежуточная таблица с полем IDENTITY (AUTO_INCREMENT) с отсортированными данными по ID и DDATE
CREATE TABLE #Table2 (RowNum INT IDENTITY, ID INT, DDATE DATETIME, SSTATUS VARCHAR(255))

INSERT INTO #Table2 (ID, DDATE, SSTATUS)
SELECT t.ID, t.DDATE, t.SSTATUS FROM #Table1 t ORDER BY t.ID, t.DDATE

-- Результирующая таблица
CREATE TABLE #Table3 (ID INT, DDATE DATETIME, SSTATUS VARCHAR(255))

DECLARE @CurrentRowNum INT
DECLARE @CurrentId INT = NULL
DECLARE @CurrenDate DATETIME = NULL
DECLARE @CurrentStatus VARCHAR(255) = NULL

DECLARE @OldId INT = NULL
DECLARE @OldStatus VARCHAR(255) = NULL

-- Начнём с минимальной записи, ей будет соответствовать 
-- пара минимального ID и DDATE
SELECT @CurrentRowNum = MIN(RowNum) FROM #Table2 t

-- Циклом бежим по всем записям, пока не кончатся
WHILE @CurrentRowNum IS NOT NULL
BEGIN
    SELECT
        @CurrentId = t.ID,
        @CurrenDate = t.DDATE,
        @CurrentStatus = t.SSTATUS 
    FROM #Table2 t
    WHERE t.RowNum = @CurrentRowNum
    
    -- Ищем различие с предыдущей записью по ID или по STATUS
    IF @CurrentId <> ISNULL(@OldId, 0) OR @CurrentStatus <> ISNULL(@OldStatus, '')
    BEGIN
        INSERT #Table3 (ID, DDATE, SSTATUS) SELECT @CurrentId, @CurrenDate, @CurrentStatus
        
        SELECT
            @OldId = @CurrentId,
            @OldStatus = @CurrentStatus 
    END
    
    SELECT @CurrentRowNum = MIN(RowNum) FROM #Table2 t WHERE t.RowNum > @CurrentRowNum
END

-- Выводим необходимый результат
SELECT * FROM #Table3 t ORDER BY t.ID, t.DDATE

DROP TABLE #Table1
DROP TABLE #Table2
DROP TABLE #Table3

但是一个请求的解决方案，我在 Oracle g12 上检查了它，一切正常：

SELECT 
    SQ3.ID,
    SQ3.DDATE,
    SQ3.SSTATUS
FROM 
(
    SELECT  
        (CASE WHEN SQ.RowNumber = 1 THEN SQ.ID ELSE SQ2.ID END) AS ID,
        (CASE WHEN SQ.RowNumber = 1 THEN SQ.DDATE ELSE SQ2.DDATE END) AS DDATE,
        (CASE WHEN SQ.RowNumber = 1 THEN SQ.SSTATUS ELSE SQ2.SSTATUS END) AS SSTATUS,
        (CASE WHEN (SQ.SSTATUS <> SQ2.SSTATUS) OR SQ.RowNumber = 1 THEN 1 ELSE 0  END) AS IsChangeStatus
    FROM
    (
        SELECT 
            ROW_NUMBER() OVER ( ORDER BY t1.ID, t1.DDATE ) AS RowNumber, 
            t1.* 
        FROM Table1 t1
    ) SQ
    LEFT JOIN 
    (
        SELECT 
            ROW_NUMBER() OVER ( ORDER BY t1.ID, t1.DDATE ) AS RowNumber,
            t1.* 
        FROM Table1 t1   
    ) SQ2 ON SQ.RowNumber + 1 = SQ2.RowNumber
 ) SQ3
 WHERE SQ3.IsChangeStatus = 1
 ORDER BY SQ3.ID, SQ3.DDATE

决定这样：

我创建了一个中间表，对所有行Table1进行编号（对行进行编号时，按 id 和日期排序）：

CREATE TABLE TABLE2 (
  "RowNum" NUMBER GENERATED ALWAYS AS IDENTITY (
  MAXVALUE 1000),
  ID       NUMBER,
  DDATE    DATE,
  SSTATUS  NVARCHAR2(50)
);

INSERT INTO TABLE2 (ID, DDATE, SSTATUS)
SELECT ID, DDATE, SSTATUS FROM Table1 ORDER BY ID, DDATE;

然后我创建了一个空的 TABLE3：

CREATE TABLE TABLE3 (
  ID       NUMBER,
  DDATE    DATE,
  SSTATUS  NVARCHAR2(50)
);

在循环中，我在那里插入了必要的行：

DECLARE
  laststatus VARCHAR2(50) := 'st';
  currentst VARCHAR2(50);
  currentid NUMBER;
  currentdate DATE;

BEGIN
  FOR i IN 1..74 -- тут костыль на моё количество строк в TABLE2
  LOOP
    BEGIN
      SELECT ID, DDATE, SSTATUS INTO currentid, currentdate, currentst FROM TABLE2 WHERE "RowNum" = i;
      IF currentst != laststatus THEN
        INSERT INTO TABLE3 (ID, DDATE, SSTATUS) VALUES (currentid, currentdate, currentst);
      END IF;
    END;
    laststatus := currentst;
  END LOOP;
END;