从单个选择中插入多行

Question

我正在使用红移postgres

我想要每天的产出

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

输入表user_activity：

id  date        userid
1   2016-10-10  MjA1MDATG2TDUwOTE2MD    

我的输入只是表示一天中的用户活动的单个行，对于下表中的每一行，我希望在未来创建30天的副本，这就是我将如何计算每天的MAU。

下面我尝试了类似的方法，但是我必须运行这个查询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
)

从注释更新是正确的方式，使用您发送给我的链接。

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
)

Answer 1

在PostgreSQL 8.4之后：

使用generate_series(start, stop, step interval)，您可以填充每月插入数据的日期：

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)

查看此链接以获得更多信息系列

在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