Statistician, map-maker, dataviz consultant and trainer, Observable ambassador & R instructor. I enjoy explaining things i love in detail, the most clearly i can, and building live analytical opendataflows.
@ericmauviere
colname = 'MED21' //MED21 ou TP6021
nbclasses = 8 // 2, 4 ou 8 ou 16 ou 32
macro_sql = {
const conn = await db.connect()
await conn.query(`
CREATE OR REPLACE FUNCTION nested_means(tbname, varnum, nb := 4) AS (
-- breaks est la liste des seuils, qui à chaque itération s'augmente des moyennes interstitielles nouvelles
-- breaks comprend le min et le max, qu'on retirera au besoin en fin de macro
WITH RECURSIVE means(iter, breaks) AS (
FROM query_table(tbname)
SELECT 1, [min(varnum), avg(varnum), max(varnum)] -- première ligne : [min, moyenne, max]
UNION ALL (
WITH t1 AS (
FROM means SELECT unnest(breaks) b
), t2 AS (
FROM t1
SELECT b, lead(b) over(ORDER BY b) next_b
QUALIFY next_b IS NOT null
), t3 AS (
FROM t2
SELECT b, (
FROM query_table(tbname) SELECT avg(varnum)
WHERE varnum BETWEEN b AND next_b
) new_break
), t4 AS (
FROM t3
SELECT list(new_break) new_breaks
) FROM t4, means
SELECT means.iter + 1,
list_concat(means.breaks, new_breaks).list_sort()
WHERE means.iter < log(nb) / log(2)
GROUP BY all
)
)
FROM means
SELECT last(breaks)[2:-2] breaks -- retrait min et max
)`)
await conn.close()
return "Macro nested_means créée dans DuckDB"
}
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.
