當值發生變化時，增量為1

我無法理解這一部分

這就是資料

而當我運行下面的代碼時

 WITH data AS (
              SELECT 'person1' person, 'day1' day, 'Y' flag FROM double
    UNION ALL SELECT 'person1' person, 'day2' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day3' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day4' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day5' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day6' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day7' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day8' day, 'Y' flag FROM dual
   )                     
    SELECT person, DAY, flag,
                 CASE WHEN flag = lag(flag) over (PARTITION BYperson
                                                   訂單 BY 日)
                       THEN0
                       ELSE 1
                  END gap
             FROM DATA; 

給我這個輸出

到這里我明白了。我想知道，當我在上面的查詢中加入以下部分時

SELECT person, DAY, flag, SUM(gap) over (PARTITION BYperson
                                            ORDER BY DAY) grp

像這樣

WITH data AS (
              SELECT 'person1' person, 'day1' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day2' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day3' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day4' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day5' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day6' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day7' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day8' day, 'Y' flag FROM dual
   )
   
   SELECT person, DAY, flag, SUM(gap) over (PARTITION BYperson
                                            order BY DAY) grp
   From
   ( SELECT person, DAY, flag,
                 CASE WHEN flag = lag(flag) over (PARTITION BYperson
                                                   訂單 BY 日)
                       THEN0
                       ELSE 1
                  END gap
             FROM DATA) ;

為什么它給出的結果是這樣的？它在person1的所有磁區上做sum，這里的值是如何被加起來的？誰能幫助我理解這里？我無法理解第4列是如何得出的。

person1 day1 Y 1
person1 day2 Y 1
person1 day3 Y 1 1
person1 day4 N 2 2
person1 day5 N 2 2
person1 day6 Y 3 3
person1 day7 Y 3 3
person1 day8 Y 3 3

uj5u.com熱心網友回復：

這是一個間隙和島嶼的問題，每個島嶼被定義為屬于同一個人和旗幟值的記錄群組，按日期列排序。 我們可以在這里使用行數差異法和其他一些技巧。

WITH cte AS (
    SELECT t. *, ROW_NUMBER() OVER（PARTITION BY人 ORDER BY day) rn1,
                ROW_NUMBER() OVER (PARTITION BY person, flag ORDER BY day) rn2
    FROM data t
),
cte2 AS (
    SELECT t. *, MIN(day) OVER (PARTITION BY person, rn1 - rn2) min_day
    FROM cte t
)

SELECT person, day, flag, DENSE_RANK() OVER (ORDER BY min_day) GRP
FROM cte2
ORDER BY person, day;

SUM(gap)over (PARTITION BY person ORDER BY DAY DESC) grp

SUM(gap)over (PARTITION BY person) grp

uj5u.com熱心網友回復：

來自分析函式檔案：

如果你完全省略windowing_clause，那么默認是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

所以你的查詢實際上是：

WITH data AS (
              SELECT 'person1' person, 'day1' day, 'Y' flag FROM double
    UNION ALL SELECT 'person1' person, 'day2' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day3' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day4' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day5' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day6' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day7' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day8' day, 'Y' flag FROM dual
)
SELECT person,
       DAY,
       標志。
       SUM(gap) over (
         PARTITION BY person
         訂單 by 日
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS grp
From (
  SELECT person,
         DAY,
         標志。
         CASE WHEN flag = lag（ flag） over (
                            PARTITION BY person
                            訂單 BY 日
                          )
         THEN 0
         ELSE1
         END AS gap
  FROM DATA
);

這意味著，對于每一行（對于每個按天排序的人），你只執行flag在這一天和之前所有日子的變化的SUM。

所以：

• 當day是day1時，SUM只針對day1行的標志變化。
• 當day是day2時，SUM超過day1和day2行的標志變化。
• 當day是day3時，SUM超過day1、day2和day3行的標志變化。
• 等。

從Oracle 12開始，你可以使用MATCH_RECOGNIZE以更簡單的格式撰寫查詢：

WITH data AS (
              SELECT 'person1' person, 'day1' day, 'Y' flag FROM double
    UNION ALL SELECT 'person1' person, 'day2' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day3' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day4' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day5' day, 'N' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day6' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day7' day, 'Y' flag FROM dual
    UNION ALL SELECT 'person1' person, 'day8' day, 'Y' flag FROM dual
)
SELECT person,
       DAY,
       標志。
       語法
FROM DATA
MATCH_RECOGNIZE(
  PARTITION BY person
  訂單 BY 日
  衡量標準
    MATCH_NUMBER() AS grp
  ALL ROWS PER MATCH
  PATTERN (same_flags )
  DEFINE same_flags AS FIRST(flag) = flag
);

db<>fiddle here