Product, analytics, viz.
Product analyst @ShowwcaseHQ.
Public
Edited
Apr 5, 2024
am4 = duck.query(`
WITH input AS (
SELECT
-- latest version of duckdb should use try_strptime
-- instead of this cursed pattern
strptime(left(date, 10) ||
' ' ||
if(substr(date, 12, 5) = '', '00:00',
substr(date, 12, 5)), '%Y-%m-%d %H:%M') AS date,
row_number() OVER (ORDER BY date) AS x,
demand AS y
FROM 'rawdata.csv'
), min_max AS (
SELECT
min(x) AS over_min_x, max(x) AS over_max_x,
(max(x) - min(x)) AS delta_x
FROM input
), am4 AS (
SELECT
round(${bin-1} * (x - over_min_x) / delta_x) AS bin,
min(date) AS min_x, arg_min(y, date) AS arg_min_yx,
max(date) AS max_x, arg_max(y, date) AS arg_max_yx,
arg_min(date, y) AS arg_min_xy, min(y) AS min_y,
arg_max(date, y) AS arg_max_xy, max(y) AS max_y,
FROM input, min_max
GROUP BY 1
), unioned AS (
SELECT min_x AS x, arg_min_yx AS y FROM am4
UNION SELECT max_x, arg_max_yx FROM am4
UNION SELECT arg_min_xy, min_y FROM am4
UNION SELECT arg_max_xy, max_y FROM am4
)
SELECT * FROM unioned ORDER BY x;
`)
rawdata = FileAttachment("rawdata.csv").csv()
.then(d => d.map(d => ({
date: new Date(d.date),
demand: +d.demand
})))
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.
