从单个选择中插入多行

I am using redshift postgres

我正在使用redshift postgres

I want the output to be a daily

我希望输出是每天

Output Table monthly_user_activity

输出表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:

输入表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

我的输入只是一行代表一天中的用户活动，对于下表中的每一行，我想在未来创建30天的日期，这就是我计算每日MAU的方式

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

我试过这样的事情，下面但我必须运行这个查询30次，看起来不是很干。

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
)

1 个解决方案

#1

After PostgreSQL 8.4:

在PostgreSQL 8.4之后：

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

使用generate_series（开始，停止，步骤间隔），您可以填充每月插入数据的日期：

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

请查看此链接以获取更多信息generate_series

before PostgreSQL 8.4 you can create your own function:

在PostgreSQL 8.4之前，您可以创建自己的函数：

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

#1