CODEWITHSUNDEEP
sqlamazon-redshift
Robert I
Robert I

Reputation: 1527

Insert multiple rows from a single select

I am using redshift postgres

I want the output to be a daily

Output Table monthly_user_activity

id  maudate     date       userid
1   2016-10-10  2016-10-10 MjA1MDATG2TDUwOTE2MD 
2   2016-10-11  2016-10-10 MjA1MDATG2TDUwOTE2MD
3   2016-10-12  2016-10-10 MjA1MDATG2TDUwOTE2MD
4   2016-10-13  2016-10-10 MjA1MDATG2TDUwOTE2MD
5   2016-10-14  2016-10-10 MjA1MDATG2TDUwOTE2MD
6   2016-10-15  2016-10-10 MjA1MDATG2TDUwOTE2MD
7   2016-10-16  2016-10-10 MjA1MDATG2TDUwOTE2MD
...
30   2016-11-09 2016-10-10 MjA1MDATG2TDUwOTE2MD

Input Table user_activity:

id  date        userid
1   2016-10-10  MjA1MDATG2TDUwOTE2MD

My input is just a single row that represents a users activity on a day, for each row in this table below I want to create 30 copies of days in the future, this is how I will calculate a daily MAU

I tried something like this, below but i would have to run this query 30 times and it seems not very DRY.

INSERT INTO monthly_user_activity 
( maudate, date, userid )
SELECT 
original_date::date + interval '0 day',
original_date,
userid
FROM (
SELECT
date as original_date,
userid
FROM
    user_activity
)

INSERT INTO monthly_user_activity 
( maudate, date, userid )
SELECT 
original_date::date + interval '1 day',
original_date,
userid
FROM (
SELECT
date as original_date,
userid
FROM
    user_activity
)

INSERT INTO monthly_user_activity 
( maudate, date, userid )
SELECT 
original_date::date + interval '2 day',
original_date,
userid
FROM (
SELECT
date as original_date,
userid
FROM
    user_activity
)

---...
INSERT INTO monthly_user_activity 
( maudate, date, userid )
SELECT 
original_date::date + interval '30 day',
original_date,
userid
FROM (
SELECT
date as original_date,
userid
FROM
    user_activity
)

update from comments Is this the right way using that link you sent me.

SELECT
id
d::date,
date as original_date,
userid
FROM user_activity,
(
select (dateadd(day, +row_number() over (order by true), date)) as d
    from user_activity limit 30
)

Upvotes: 1

Views: 242

Answers (1)

light souls
light souls

Reputation: 728

After PostgreSQL 8.4:

With generate_series(start, stop, step interval) you can populate dates for your monthly insert data:

SELECT
t.d::date,
date as original_date,
user_id
FROM user_activity,
generate_series(date,date+ interval '1 month - 1 day','1 day') t(d)

look at this link for more information generate_series

before PostgreSQL 8.4 you can create your own function:

create or replace function generate_series_date(begin_date integer,end_date integer)
RETURNS integer AS $$

declare

affected_rows integer :=0;
integer_var integer:=0;

begin
 FOR i IN begin_date..end_date LOOP
  INSERT INTO monthly_user_activity 
     ( mau_date, date, user_id )
  select
  date+ "interval"(''''||i||' day'''),
  date,
  user_id
  FROM user_activity;

  GET DIAGNOSTICS integer_var  = ROW_COUNT;
  affected_rows = affected_rows+integer_var;

  END LOOP;

  return affected_rows;

  end;
$$  LANGUAGE plpgsql

Upvotes: 3

Related Questions