The avatar for @abitrolly
Anatoli Babenia
Minsk, Belarus. Looking for a job.
Public
Edited
Nov 28, 2022
Fork of SQL + Chart
1 star
1
Insert cell
Insert cell
database = FileAttachment("packages.db").sqlite()
Insert cell
Insert cell
database
SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name
Insert cell
Insert cell
database.describe()
Insert cell
Insert cell
database
SELECT * FROM available
Insert cell
Insert cell
Insert cell
packages.db
SELECT *,
-- to search from the end, we need to operate on reversed string
reverse(substr(
-- "46_68x.73cf.41-1.9.0-ilc-8402" instead of "2048-cli-0.9.1-14.fc37.x86_64"
reverse(pkg),
-- substr with os identifier starts after the first found dot
charindex('.', reverse(pkg))+1,
-- and ends before the second found dot
charindex('.', substr(reverse(pkg), charindex('.', reverse(pkg))+1))-1
)) as os,
reverse(substr(reverse(pkg), 0, charindex('.', reverse(pkg)))) as arch
FROM available

Insert cell
osarch
SELECT os, COUNT(*) FROM osarch GROUP BY os ORDER BY os DESC
Insert cell
Insert cell
osarch
SELECT os, pkg, arch FROM osarch WHERE os NOT LIKE 'fc3%'
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Plot.plot({
marks: [
Plot.barX(notable, {
x: "count",
y: "os",
sort: { y: "x", reverse: true }
}),
Plot.ruleX([0])
]
})
Insert cell
Plot.plot({
caption: "Different `arch`",
x: {
transform: (d) => d / 1000 / 60,
label: "Minutes (s) →"
},
marks: [
Plot.ruleY([0]),
Plot.rectY(osarch, Plot.binX({ y: "sum" }, { x: "os" }))
]
})
Insert cell
Insert cell
packages.db
WITH RECURSIVE
-- create temp table 'cnt' with column 'x'
cnt(x) AS (
-- initialize first row
VALUES(1)
-- and append to it all rows below
UNION ALL
-- here we recursively generate row value as x+1 select
-- from previous row. every select produces new 'cnt'
-- table with just one row and new x value. generator
-- stops when WHERE returns NULL
SELECT x+1 FROM cnt WHERE x<1000000
)
-- select all rows from the generated temp table
SELECT x FROM cnt;
Insert cell
database
-- intersection between installed and available
SELECT available.pkg FROM available JOIN installed ON available.pkg == installed.pkg
Insert cell
database
-- available but not installed
SELECT available.pkg FROM available LEFT JOIN installed ON available.pkg == installed.pkg WHERE installed.pkg IS NULL
Insert cell
database
-- installed not available
SELECT installed.pkg FROM installed LEFT JOIN available ON available.pkg == installed.pkg WHERE available.pkg IS NULL
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more