sql - Creating a table with a sequence of values without inserting each one manually -
i (think i) want create view or temporary table contains sequence. purpose of table provide values needed make panel data set. create sequence programmatically, using every value between 2 periods 0 , 365, gaps of 7 (say make weekly panel).
here how can done "manually", inserting each of cutoff days hand.
create table time_periods (day_cutoff int); insert time_periods values (7); insert time_periods values (14); insert time_periods values (28); insert time_periods values (35); insert time_periods values (42);
this table used (doing full cartesian join on underling table of billing_records
contains ad hoc instances of when billing made.
select buyer , seller , day_cutoff , sum(case when billing_day < day_cutoff amount else 0.0 end) cumulative_spend time_periods left join billing_records on 1 = 1 group buyer, seller, day_cutoff
you can use generate_series
:
select * generate_series(7, 42, 7);
it documented here.
here 1 way write query:
select buyer, seller, day_cutoff, sum(case when br.billing_day < day_cutoff amount else 0.0 end) cumulative_spend billing_records br cross join generate_series(7, 42, 7) day_cutoff group buyer, seller, day_cutoff ;
Comments
Post a Comment