The avatar for @jzavala-gonzalez
José M. Zavala González
Public
Edited
Feb 5, 2024
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
-- It's necessary that the input table have _specifically_ this name
-- so the macro can read from it
with xmr_input_table as (
select * from my_data
where i between ${start_index} and ${start_index + num_sampled}
)
select *
from xmr("Close")
Insert cell
create_xmr_macro_query = await dbref.query(`

create or replace macro xmr(X_col) AS TABLE (
with run_variables as (
select
X_col as X,
abs(X - lag(X) over ()) as mR,
from xmr_input_table
),

aggregate_calcs as (
select
mean(X) as X_average,
mean(mR) as mR_average,
X_average - 2.66 * mR_average as LNPL,
X_average + 2.66 * mR_average as UNPL,
mR_average * 3.269 as URL,
from run_variables
)

from aggregate_calcs
);
`)
Insert cell
db
select * from my_data
where i between ${start_index} and ${start_index + num_sampled}
Insert cell
Insert cell
dbref = DuckDBClient.of({
my_data: my_data
})
Insert cell
db = create_clamp_macro_query, create_xmr_macro_query, dbref
Insert cell
my_data = aapl.map((d,i) => Object({i, ...d}))
Insert cell
dbref
from my_data
Insert cell
create_clamp_macro_query = await dbref.query(`
create or replace macro clamp(x, x_floor, x_ceiling) as
case when x < x_floor
then x_floor
when x > x_ceiling
then x_ceiling
else x
end
;
`)
Insert cell

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.
Learn more