1

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     |
+-------+-----------+
  • What is the wanted output? – ypercubeᵀᴹ Mar 4 '18 at 22:04
  • I added a table. You can edit your question, too. – ypercubeᵀᴹ Mar 4 '18 at 22:45
  • thanks, too puzzled today to see that tiny link ;) – xibalba Mar 4 '18 at 22:58
0

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;
  • very nice! thanks a lot, this solved my problem! could you maybe explain why i would have to use with? is it something like a placeholder or temporary variable? what was your plan to solve this special kind of problem, how did you slice the single steps? And a new problem arose, i would have to select the highest count and delete it. How would i achieve this? – xibalba Mar 5 '18 at 13:37
  • WITH is the same as a view or a subquery. In this case, upper is used only once, so you could just as well insert it there as a subquery. To ask a question, use the "Ask Question" button. – CL. Mar 5 '18 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.