ID 및 날짜를 기준으로 한 Row_number

ID 및 날짜를 기준으로 한 Row_number

연속된 날짜가 있는 ID를 기준으로 비활성화된 데이터를 선택해야 합니다.

Sample Data:
2020-04-19,SQA0199,ACTIVE
2020-04-20,SQA0199,INACTIVE
2020-04-21,SQA0199,INACTIVE
2020-04-22,SQA0199,INACTIVE
2020-04-23,SQA0199,ACTIVE
2020-04-24,SQA0199,INACTIVE
2020-04-25,SQA0199,INACTIVE
2020-04-26,SQA0199,INACTIVE

Sample Script:
SELECT
  ROW_NUMBER() OVER (PARTITION BY SQA_ID ORDER BY timestamp) AS "row number",
  timestamp, SQA_ID
FROM SQA_SMS_INACTIVE where status='INACTIVE';

Desire Output:
2020-04-20,SQA0199,1
2020-04-21,SQA0199,2
2020-04-22,SQA0199,3
2020-04-24,SQA0199,1
2020-04-25,SQA0199,2
2020-04-26,SQA0199,3

스크립트의 출력은 행 번호에 계속 표시됩니다.이것 좀 도와주세요

와 함께LAG()그리고.SUM()window 함수에서는 행 번호의 기반이 되는 행 그룹을 만들 수 있습니다.

WITH 
  pre AS (
    SELECT *,
      DATEDIFF(
        timestamp,
        LAG(timestamp) OVER (PARTITION BY SQA_ID ORDER BY timestamp)
      ) <> 1 AS flag 
    FROM SQA_SMS_INACTIVE 
    WHERE status = 'INACTIVE'  
  ),
  cte AS (
    SELECT timestamp, SQA_ID,
      SUM(COALESCE(flag, 0) <> 0) OVER (PARTITION BY SQA_ID ORDER BY timestamp) grp
    FROM pre
  )  
SELECT timestamp, SQA_ID,
  ROW_NUMBER() OVER (PARTITION BY SQA_ID, grp ORDER BY timestamp) AS `row number`
FROM cte  

데모를 참조해 주세요.
결과:

| timestamp  | SQA_ID  | row number |
| ---------- | ------- | ---------- |
| 2020-04-20 | SQA0199 | 1          |
| 2020-04-21 | SQA0199 | 2          |
| 2020-04-22 | SQA0199 | 3          |
| 2020-04-24 | SQA0199 | 1          |
| 2020-04-25 | SQA0199 | 2          |
| 2020-04-26 | SQA0199 | 3          |

언급URL : https://stackoverflow.com/questions/61771569/row-number-based-on-the-id-and-date