by Ciro Santilli (@cirosantilli, 32)
rm -f tmp.sqlite
sqlite3 tmp.sqlite "create table t (id integer, val integer)"
sqlite3 tmp.sqlite <<EOF
insert into t values
  (0, 0),
  (1, 5),
  (2, 10),
  (3, 14),
  (4, 15),
  (5, 16),
  (6, 20),
  (7, 25),
  (8, 29),
  (9, 30),
  (10, 30),
  (11, 31),
  (12, 35),
  (13, 40)
EOF
Show how many neighbours each column has with val between val - 2 and val + 2 inclusive: 
sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) FROM t;
EOF
Output: 
0|0|1
1|5|1
2|10|1
3|14|3
4|15|3
5|16|3
6|20|1
7|25|1
8|29|4
9|30|4
10|30|4
11|31|4
12|35|1
13|40|1
val - 1 and val + 1 inclusive instead: 
sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t;
EOF
Output: 
0|0|1
1|5|1
2|10|1
3|14|2
4|15|3
5|16|2
6|20|1
7|25|1
8|29|3
9|30|4
10|30|4
11|31|3
12|35|1
13|40|1
There seems to be no analogue to HAVING for window functions, so we can just settle for a subquery for once, e.g.: 
sqlite3 tmp.sqlite <<EOF
SELECT * FROM (
  SELECT id, val, COUNT(*) OVER (
    ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) as c FROM t
) WHERE c > 2
EOF
which outputs: 
4|15|3
8|29|3
9|30|4
10|30|4
11|31|3

Ancestors

  1. Window function
  2. SQL feature
  3. SQL
  4. Relational database management system
  5. Relational database
  6. Database
  7. Software
  8. Computer
  9. Information technology
  10. Area of technology
  11. Technology
  12. Index

Discussion (0)

Sign up or sign in create discussions.

There are no discussions about this article yet.

View article source