Sqlite – Distinguish a column with the same value by range of id and count this range, take the higher one

gaps-and-islandssqlite

I have a small sqlite table

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

from what i want to get the maximum value of "cnt" and later on the average of "sweep" depending on the range/amount of distinct "cnt"-values.

Since sqlite only has this rowid option i use it within my query:

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

This returns:

+--------+------+-------+
| 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 |
+--------+------+-------+

I need both "cnt"-ranges but the higher count of 7s (from id 152 downwards) first. So i would have to split this result according to the gap in id and proceed with my calculations.

I googled a lot and also looked here on stackexchange, this is one partial solution i found so far

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

It returns, depending on +1 or -1, the lower or upper boundary of id but with that i can't compute the average of sweep for the given range.

Finally i need a query that looks for the highest "cnt"-value, the highest count of one range of (in this case 7s) split by the gap between the id's of these two groups.

I have no idea how to get there since i'm not that experienced but willing to learn a lot, so a little advice would be greatly appreciated!

Wanted output:

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

Best Answer

For each lower boundary, search the matching upper boundary:

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);

lb  ub
--- ---
106 113
152 161

These values can then be used for 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;
Related Question