rm -f tmp.sqlite
sqlite3 tmp.sqlite 'create table t(i integer)'
sqlite3 tmp.sqlite 'insert into t values (1), (2)'
sqlite3 tmp.sqlite 'with mycte as ( select * from t ) delete from mycte where i = 1'
sqlite3 tmp.sqlite 'select * from t'
How to implement Nested set model in SQL:
torchvision ResNet by Ciro Santilli 37 Updated 2025-07-16
That example uses a ResNet pre-trained on the COCO dataset to do some inference, tested on Ubuntu 22.10:
cd python/pytorch
wget -O resnet_demo_in.jpg https://upload.wikimedia.org/wikipedia/commons/thumb/6/60/Rooster_portrait2.jpg/400px-Rooster_portrait2.jpg
./resnet_demo.py resnet_demo_in.jpg resnet_demo_out.jpg
This first downloads the model, which is currently 167 MB.
We know it is COCO because of the docs: pytorch.org/vision/0.13/models/generated/torchvision.models.detection.fasterrcnn_resnet50_fpn_v2.html which explains that
FasterRCNN_ResNet50_FPN_V2_Weights.DEFAULT
is an alias for:
FasterRCNN_ResNet50_FPN_V2_Weights.COCO_V1
The runtime is relatively slow on P51, about 4.7s.
After it finishes, the program prints the recognized classes:
['bird', 'banana']
so we get the expected bird, but also the more intriguing banana.
By looking at the output image with bounding boxes, we understand where the banana came from!
Figure 1.
python/pytorch/resnet_demo_in.jpg
. Source.
Figure 2.
python/pytorch/resnet_demo_out.jpg
. The beak was of course a banana, not a beak!
Fish by Ciro Santilli 37 Updated 2025-07-16
This paraphyletic subgroup is easy to form the "acquatic only" (fishes) vs "things that come out of water" (tetrapods). Though mudfishes make that distinction harder.
Which kind of makes sense, why would you want for limbs unless you are going to stay out of water!
ROW_NUMBER by Ciro Santilli 37 Updated 2025-07-16
sqlite3 ':memory:'  'WITH t (i) AS (VALUES (-1), (-1), (-2)) SELECT *, row_number() over () FROM t'
Possible output:
-1|1
-1|2
-2|3
Gives them unique IDs.
With a partition by:
sqlite3 ':memory:'  'WITH t (i) AS (VALUES (-1), (-1), (-2)) SELECT *, row_number() over ( partition by i ) FROM t'
possible output:
-2|1
-1|1
-1|2
SQL window RANGE by Ciro Santilli 37 Updated 2025-07-16
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

Unlisted articles are being shown, click here to show only listed articles.