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"
}
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.