From 1d6d582a9a0f0ace4c13484515b55c2be16c793e Mon Sep 17 00:00:00 2001 From: alex Date: Thu, 15 May 2025 21:46:52 +0200 Subject: [PATCH] Indent --- weight/schema.sql | 110 ++++++++++++++++++++++++---------------------- 1 file changed, 57 insertions(+), 53 deletions(-) diff --git a/weight/schema.sql b/weight/schema.sql index c057aef..80a8adc 100644 --- a/weight/schema.sql +++ b/weight/schema.sql @@ -1,55 +1,55 @@ create table weight.weight ( - measured_at timestamp with time zone primary key default now(), - value numeric(4,1) not null + measured_at timestamp with time zone primary key default now(), + value numeric(4,1) not null ); create table weight.bp ( - measured_at timestamp with time zone primary key default now(), - systolic numeric(3, 0) not null, - diastolic numeric(3, 0) not null, - kind text check (kind in ('standard', 'home', 'doctor')) default 'standard' not null + measured_at timestamp with time zone primary key default now(), + systolic numeric(3, 0) not null, + diastolic numeric(3, 0) not null, + kind text check (kind in ('standard', 'home', 'doctor')) default 'standard' not null ); create table weight.pressure_medication ( - taken_at timestamp with time zone primary key default now(), - dose_mg numeric(3, 1) not null + taken_at timestamp with time zone primary key default now(), + dose_mg numeric(3, 1) not null ); create schema zqxjk; create view zqxjk.weight as ( - select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, - measured_at || ' ' || value as _display, - value - from weight.weight + select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, + measured_at || ' ' || value as _display, + value + from weight.weight ); create view zqxjk.bp_standard_measure as ( - select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, - measured_at || ' ' || systolic || '-' || diastolic as _display, - systolic, - diastolic - from weight.bp + select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, + measured_at || ' ' || systolic || '-' || diastolic as _display, + systolic, + diastolic + from weight.bp ); create view zqxjk.pressure_medication as ( - select to_char(taken_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, - taken_at || ' ' || dose_mg || 'mg' as _display, - dose_mg - from weight.pressure_medication + select to_char(taken_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, + taken_at || ' ' || dose_mg || 'mg' as _display, + dose_mg + from weight.pressure_medication ); create view zqxjk.admin_weight as ( - select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, - measured_at || ' ' || value as _display, - measured_at, - value - from weight.weight + select to_char(measured_at, 'YYYY-MM-DD"T"HH24:MI:SSOF') as _id, + measured_at || ' ' || value as _display, + measured_at, + value + from weight.weight ); create table zqxjk._tables ( - name text primary key, - default_sort text[] + name text primary key, + default_sort text[] ); insert into zqxjk._tables(name, default_sort) values ('weight', '{"_id", "desc"}'); @@ -60,32 +60,36 @@ insert into zqxjk._tables(name, default_sort) values ('pressure_medication', '{" create schema reporting; create view reporting.weekly_blood_pressure as ( - with limits as ( - select date_trunc('week', min(taken_at)) as min_week, - date_trunc('week', max(taken_at)) as max_week - from weight.pressure_medication - ) -select week.week, - min(systolic) as min_systolic, - max(systolic) as max_systolic, - avg(systolic) as avg_systolic, - min(diastolic) as min_diastolic, - max(diastolic) as max_diastolic, - avg(diastolic) as avg_diastolic -from generate_series((select min_week from limits), (select max_week from limits), '7 days') as week -left join weight.bp on date_trunc('week', week.week) = date_trunc('week', bp.measured_at) and bp.kind = 'standard' -group by week.week order by week.week); + with limits as ( + select date_trunc('week', min(taken_at)) as min_week, + date_trunc('week', max(taken_at)) as max_week + from weight.pressure_medication + ) + select week.week, + min(systolic) as min_systolic, + max(systolic) as max_systolic, + avg(systolic) as avg_systolic, + min(diastolic) as min_diastolic, + max(diastolic) as max_diastolic, + avg(diastolic) as avg_diastolic + from generate_series((select min_week from limits), (select max_week from limits), '7 days') as week + left join weight.bp on date_trunc('week', week.week) = date_trunc('week', bp.measured_at) and bp.kind = 'standard' + group by week.week + order by week.week +); create view reporting.weekly_medication as ( - with limits as ( - select date_trunc('week', min(taken_at)) as min_week, - date_trunc('week', max(taken_at)) as max_week - from weight.pressure_medication - ) -select week.week, - sum(dose_mg)/7 as average_daily_dose -from generate_series((select min_week from limits), (select max_week from limits), '7 days') as week -left join weight.pressure_medication on date_trunc('week', week.week) = date_trunc('week', pressure_medication.taken_at) -group by week.week order by week.week); + with limits as ( + select date_trunc('week', min(taken_at)) as min_week, + date_trunc('week', max(taken_at)) as max_week + from weight.pressure_medication + ) + select week.week, + sum(dose_mg)/7 as average_daily_dose + from generate_series((select min_week from limits), (select max_week from limits), '7 days') as week + left join weight.pressure_medication on date_trunc('week', week.week) = date_trunc('week', pressure_medication.taken_at) + group by week.week + order by week.week +); create view reporting.weekly_summary as select * from reporting.weekly_blood_pressure full join reporting.weekly_medication using(week); -- 2.47.3