sqlite - 通過 id 的範圍區分具有相同值的列併計算該範圍，取較高的一個

我有一個小的 sqlite 表

CREATE TABLE IF NOT EXISTS intersection (
   cnt NUMERIC NOT NULL,
   sweep NUMERIC)

從我想得到“cnt”的最大值，然後是“掃描”的平均值，具體取決於不同“cnt”值的範圍/數量。

由於 sqlite 只有這個 rowid 選項，我在查詢中使用它：

SELECT rowid, cnt, sweep FROM intersection
WHERE cnt IN (SELECT MAX(cnt) FROM intersection)

這將返回：

+--------+------+-------+
| rowid  | cnt  | sweep |
| 106    | 7    | 21.07 |
| 107    | 7    | 21.17 |
| 108    | 7    | 21.27 |
| 109    | 7    | 21.37 |
| 110    | 7    | 21.47 |
| 111    | 7    | 21.57 |
| 112    | 7    | 21.67 |
| 113    | 7    | 21.78 |---split here
| 152    | 7    | 25.7  |
| 153    | 7    | 25.8  |
| 154    | 7    | 25.9  |
| 155    | 7    | 26    |
| 156    | 7    | 26.1  |
| 157    | 7    | 26.2  |
| 158    | 7    | 26.31 |
| 159    | 7    | 26.41 |
| 160    | 7    | 26.51 |
| 161    | 7    | 26.61 |
+--------+------+-------+

我需要兩個“cnt”範圍，但首先需要更多的 7s（從 id 152 向下）。所以我必鬚根據 id 中的差距來分割這個結果並繼續我的計算。

我用Google搜尋了很多，也在這裡查看了 stackexchange，這是我到目前為止找到的一個部分解決方案

SELECT f.rowid, f.cnt, f.sweep FROM intersection f
   WHERE cnt IN (SELECT MAX(cnt) FROM intersection)
   AND NOT EXISTS (
       SELECT * FROM intersection s
       WHERE s.cnt = f.cnt
       AND s.rowid = f.rowid+1)--or -1

它根據 +1 或 -1 返回 id 的下邊界或上邊界，但我無法計算給定範圍的掃描平均值。

最後，我需要一個查詢來查找最高的“cnt”值，一個範圍（在本例中為 7s）的最高計數被這兩組的 id 之間的差距分開。

我不知道如何到達那裡，因為我沒有那麼有經驗但願意學習很多東西，所以非常感謝一些建議！

想要的輸出：

+-------+-----------+
| count | avg_sweep |
| 8     | 21.42     |
| 10    | 26.15     |
+-------+-----------+

對於每個下邊界，搜尋匹配的上邊界：

WITH uppers(rowid) AS (
 SELECT f.rowid FROM intersection f
 WHERE cnt = (SELECT MAX(cnt) FROM intersection)
 AND NOT EXISTS (
     SELECT * FROM intersection s
     WHERE s.cnt = f.cnt
     AND s.rowid = f.rowid+1)
)
SELECT f.rowid AS lb,
      (SELECT u.rowid FROM uppers u
       WHERE u.rowid >= f.rowid
       ORDER BY u.rowid ASC LIMIT 1
      ) ub
FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
   SELECT * FROM intersection s
   WHERE s.cnt = f.cnt
   AND s.rowid = f.rowid-1);

磅
--- ---
106 113
152 161

然後可以將這些值用於 BETWEEN：

WITH uppers(rowid) AS (
 SELECT f.rowid FROM intersection f
 WHERE cnt = (SELECT MAX(cnt) FROM intersection)
 AND NOT EXISTS (
     SELECT * FROM intersection s
     WHERE s.cnt = f.cnt
     AND s.rowid = f.rowid+1)
),
bounds(lb, ub) AS (
 SELECT f.rowid,
        (SELECT u.rowid FROM uppers u
         WHERE u.rowid >= f.rowid
         ORDER BY u.rowid ASC LIMIT 1)
 FROM intersection f
 WHERE cnt = (SELECT MAX(cnt) FROM intersection)
 AND NOT EXISTS (
     SELECT * FROM intersection s
     WHERE s.cnt = f.cnt
     AND s.rowid = f.rowid-1)
)
SELECT (SELECT count(*) FROM intersection
       WHERE rowid BETWEEN bounds.lb AND bounds.ub
      ) AS count,
      (SELECT avg(sweep) FROM intersection
       WHERE rowid BETWEEN bounds.lb AND bounds.ub
      ) AS avg_sweep
FROM bounds;

引用自：https://dba.stackexchange.com/questions/199345