sqlite計算連續匹配行的數量，同時排除它們

更新

即使有一個公認的答案，我仍然愿意接受其他建議。我需要這個在 sqlite 上作業，至少可以回到版本3.19.4（實際上是 Android 8），并且在 sqlite 版本之前，接受答案的最高效形式（使用視窗函式）是不可用3.28的。當被查詢的表包含數百行時，回退會導致設備停止并崩潰，所以我不能依賴這些。

原始問題

考慮一下我有一個messages使用這些列呼叫的 sqlite 表：

| id | type | text | time |
---------------------------

id是主鍵并且是唯一的。想象一下，我有 5 行按以下順序排列（為了清楚起見，將它們表示為 JSON 陣列）：

[
  {
    id: 'first',
    type: 'random',
    text: 'hey there',
    time: '2022-02-15T01:47:25.581'
  },
  {
    id: 'second',
    type: 'new_item',
    text: 'new shoe',
    time: '2022-02-15T01:48:25.581'
  }
  {
    id: 'third',
    type: 'new_item',
    text: 'new socks',
    time: '2022-02-15T01:49:25.581'
  }
  {
    id: 'fourth',
    type: 'random',
    text: 'what time is it',
    time: '2022-02-15T01:50:25.581'
  },
  {
    id: 'fifth',
    type: 'new_item',
    text: 'new shoe',
    time: '2022-02-15T01:51:25.581'
  }

I want to query these messages such that consecutive rows with type of new_item are represented as one, as well as the number of consecutive rows that are present for each unique new_item in the final output. Concretely, I would like output that gives me the information captured below (doesn't have to be the same schema, this is just an example of what I want):

[
  {
    id: 'first',
    type: 'random',
    text: 'hey there',
    time: '2022-02-15T01:47:25.581'
  },
  {
    id: 'second',
    type: 'new_item',
    text: 'new shoe',
    time: '2022-02-15T01:48:25.581',
    numConsecutiveItems: 2
  }
  {
    id: 'fourth',
    type: 'random',
    text: 'what time is it',
    time: '2022-02-15T01:50:25.581'
  },
  {
    id: 'fifth',
    type: 'new_item',
    text: 'new shoe',
    time: '2022-02-15T01:51:25.581',
    numConsecutiveItems: 1
  }

請注意，具有 id 的訊息third不在最終輸出中，因為它具有 type ofnew_item并且連續跟隨另一個具有 typenew_item的訊息，并且具有 id 的訊息second具有numConsecutiveItemsof2出于同樣的原因。更重要的是，具有 id 的訊息fifth存在是因為它不會立即跟隨另new_item一條訊息，并且它的值為numConsecutiveItemsis1出于同樣的原因。我可以通過單個查詢來實作這一點，并按time列排序嗎？這將是我的強烈偏好，但如果不是，那么理想情況下不超過 2 個查詢。謝謝！

uj5u.com熱心網友回復：

'new_item'使用視窗函式創建連續型別的組并計算每個組中有多少個：

WITH cte AS (
  SELECT *, 
         COUNT(*) OVER (PARTITION BY grp) count,
         ROW_NUMBER() OVER (PARTITION BY grp ORDER BY time) rn 
  FROM (       
    SELECT *, SUM(flag) OVER (ORDER BY time) grp
    FROM (
      SELECT *, (type <> LAG(type, 1, '') OVER (ORDER BY time)) flag
      FROM tablename
    )
  )  
)
SELECT id, type, text, time,
       CASE WHEN type = 'new_item' THEN count END numConsecutiveItems
FROM cte
WHERE numConsecutiveItems IS NULL OR rn = 1
ORDER BY time;

對于不支持視窗函式的 SQLite 版本，使用聚合和相關子查詢來模擬視窗函式：

WITH 
  prev_types AS (
    SELECT t1.*, t1.type <> COALESCE(t2.type, '') flag, MAX(t2.time) max_time 
    FROM tablename t1 LEFT JOIN tablename t2
    ON t2.time < t1.time
    GROUP BY t1.id
  ),
  sum_flags AS (
    SELECT pt1.*, SUM(pt2.flag) grp
    FROM prev_types pt1 INNER JOIN prev_types pt2
    ON pt2.time <= pt1.time
    GROUP BY pt1.id
  ),
  cte AS (
    SELECT sf1.*, 
           (SELECT COUNT(*) FROM sum_flags sf2 WHERE sf2.grp = sf1.grp) count,
           (SELECT COUNT(*) FROM sum_flags sf2 WHERE sf2.grp = sf1.grp AND sf2.time <= sf1.time) rn
    FROM sum_flags sf1
  )
SELECT id, type, text, time,
       CASE WHEN type = 'new_item' THEN count END numConsecutiveItems
FROM cte
WHERE numConsecutiveItems IS NULL OR rn = 1
ORDER BY time;

請參閱演示。

標籤：sql sqlite 通过...分组 案子 窗函数

上一篇：在jOOQ中如何表示自加入？

下一篇：K3SSQLite在NFS上運行緩慢