Creating a table with a sequence of values without inserting each one manually

Question

I (think I) want to create a view or temporary table that contains a sequence. The purpose of this table is simply to provide values needed to make a panel data set. What I would like to do is create this sequence programmatically, using every value between two periods like 0 and 365, with gaps of 7 (say to make a weekly panel).
Here is how it can be done "manually", inserting each of the cutoff days by hand.

create table time_periods (day_cutoff int); 
insert into time_periods values (7); 
insert into time_periods values (14); 
insert into time_periods values (28); 
insert into time_periods values (35); 
insert into time_periods values (42);

This table would then be used as so (doing a full cartesian join on an underling table of billing_records that contains ad hoc instances of when a billing was made.

select 
buyer
, seller
, day_cutoff
, sum(case when billing_day < day_cutoff 
      then amount 
      else 0.0 end) as cumulative_spend
from time_periods 
left join billing_records 
on 1 = 1 
group by buyer, seller, day_cutoff

Gordon Linoff · Accepted Answer · 2013-08-10 16:05:50Z

2

You can just use generate_series:

select *
from generate_series(7, 42, 7);

It is documented here.

Here is one way to write your query:

select buyer, seller, day_cutoff,
       sum(case when br.billing_day < day_cutoff then amount else 0.0 end) as cumulative_spend
from billing_records br cross join
     generate_series(7, 42, 7) as day_cutoff
group by buyer, seller, day_cutoff ;

answered Aug 10, 2013 at 16:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a table with a sequence of values without inserting each one manually

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related