如何在PostgreSQL中创建一个循环调用另一个函数的函数？

Question

我正在使用PostgreSQL 9.3.9，我有一个名为list_all_upsells的过程，它接受一个月份的开始和结束。(请参见sqlfiddle.com/#!15/abd02获取示例数据)例如，下面的代码将列出10月份的升级帐户计数：

select COUNT(up.*) as "Total Upsell Accounts in October" from 
list_all_upsells('2015-10-01 00:00:00'::timestamp, '2015-10-31 23:59:59'::timestamp) as up
where up.user_id not in
(select distinct user_id from paid_users_no_more 
where concat(extract(month from payment_stop_date),'-',extract(year from payment_stop_date))<>
concat(extract(month from payment_start_date),'-',extract(year from payment_start_date)));

list_all_upsells过程如下：

DECLARE
payor_email_2 text;
   BEGIN
FOR payor_email_2 in select distinct payor_email from paid_users LOOP
return query
execute
'select paid_users.* from paid_users,
(
select payment_start_date as first_time from paid_users
where payor_email = $3
order by payment_start_date limit 1
) as dummy
where payor_email = $3
and payment_start_date > first_time
and payment_start_date between $1 and $2
and first_time < $1'
using a, b, payor_email_2;
END LOOP;
return;
END

我希望能够运行所有有记录的月份，并将数据查询合并在一个表格中，如下所示：

Month   | Total Upselled Accounts
---------------------------------
08/2014 | 23
09/2014 | 35
ETC...
10/2015 | 56

我有一个查询，可以获取我们从业以来每个月的第一天和最后一天：

select distinct date_trunc('month', payment_start_date)::date as startmonth
from paid_users ORDER BY startmonth;

月底处理：

SELECT distinct (date_trunc('MONTH', payment_start_date) + 
INTERVAL '1 MONTH - 1 day')::date as endmonth from paid_users 
ORDER BY endmonth;

现在，我如何创建一个函数来循环遍历list_all_upsells，并获取每个月的计数？即第一个查询对于startmonth给出了2014-03-01、2014-04-01，一直到2015-10-01，而第二个查询对于endmonth给出了2014-03-31、2014-04-30，一直到2015-10-31。我想要在每个月上运行list_all_sells，以便我可以得到每个月的升级帐户数的聚合计数。

我的paid_users表格看起来像这样：

CREATE TABLE paid_users
(
  user_id integer,
  user_email character varying(255),
  payor_id integer,
  payor_email character varying(255),
  payment_start_date timestamp without time zone DEFAULT now()
)

paid_users_no_more:

CREATE TABLE paid_users_no_more
(
  user_id integer,
  payment_stop_date timestamp without time zone DEFAULT now()
)

- Ashley I.

---

我真的不擅长Postgres，但是难道不能用适当的连接替换执行吗？ - GSazheniuk

嘿 @GSazheniuk 我不知道 :s - Ashley I.

当查看循环层时，将其转换为使用子查询、连接等组合查询几乎总是更快的。 - Craig Ringer

Answer 1

您的函数存在一些问题，让我们从那里开始。简而言之，(1) 您只需要一个参数来指示月份，使用月初和月末会导致问题；(2) 您不需要动态查询，因为您没有更改标识符（表或列名）；(3) 您不需要循环；以及 (4) 您的逻辑是错误的。我还可以提到 PostgreSQL 使用 函数，它们都以类似于 CREATE FUNCTION list_all_upsells(...) 的行开头，但那太挑剔了。

首先是逻辑：显然，一个由其电子邮件地址标识的用户从某个 payment_start_date 开始订阅，直到某个 payment_stop_date，并且可以多次这样做。您正在寻找那些在所询问的月份之前首次订阅，并在所询问的月份开始新订阅但不是首次订阅的用户。在这种情况下，过滤器 payment_start_date > first_time 是无用的，因为您已经过滤了在所询问的月份之前的第一次订阅 (first_time < $1) 和新订阅 (payment_start_date BETWEEN $1 AND $2)。

点(1)、(2)和(3)在函数内部重写查询时才变得明显。

CREATE FUNCTION list_all_upsells(<b>timestamp</b>) RETURNS SETOF paid_users AS $$
  SELECT paid_users.*
  FROM paid_users
  JOIN (  -- This JOIN keeps only those rows where the payor_email has a prior subscription
    SELECT DISTINCT payor_email,
           first_value(payment_start_date) OVER (PARTITION BY payor_email ORDER BY payment_start_date) AS dummy
    FROM paid_users
    WHERE payment_start_date < date_trunc('month', $1)
  ) dummy USING (payor_email)
  -- This filter keeps only those rows with new subscriptions in the month
  WHERE date_trunc('month', payment_start_date) = date_trunc('month', $1)
$$ LANGUAGE sql STRICT;

由于函数体现在只剩下一个 SQL 语句，该函数现在是一个sql语言函数，比plpgsql更高效。您现在只需提供一个参数，该参数可以是您想要数据的任何一个月中的任何时刻，因此list_all_upsells（LOCALTIMESTAMP）将为您提供当前月份的结果。就您发布的查询而言，它应该是这样的：

SELECT count(up.*) AS "Total Upsell Accounts in October"
FROM list_all_upsells(LOCALTIMESTAMP) up
WHERE up.user_id NOT IN 
  (SELECT DISTINCT user_id FROM paid_users_no_more 
   WHERE date_trunc('month', payment_stop_date) <>
         date_trunc('month', up.payment_start_date)
  );

顺便提一下，这确实引出了一个问题，为什么你要有表 paid_users_no_more 呢？为什么不只是在表 paid_users 中添加一个列 payment_stop_date 呢？当该列为 NULL 时，用户仍然订阅。但整个查询都相当奇怪，因为 list_all_upsells() 在月内返回新的订阅，那么为什么要在其他时间处理取消的订阅呢？

现在转到你真正的问题：

SELECT months.m "Month", coalesce(count(up.*), 0) "Total Upselled Accounts"
FROM generate_series('2014-08-01'::timestamp,
                     date_trunc('month', LOCALTIMESTAMP),
                     '1 month') AS months(m)
LEFT JOIN list_all_upsells(months.m) AS up ON date_trunc('month', payment_start_date) = m
GROUP BY 1
ORDER BY 1;

从某个起始月份生成一系列月份，直到当前月份，然后计算每个月的新订阅数量，可能为0。

SQLFiddle

- Patrick

---

这是一个非常好的答案，逻辑非常清晰。但是我尝试运行您的list_all_upsells创建函数，但在第5行附近出现了语法错误 - 为什么会这样？@Patrick - Ashley I.

哦，那是一个很糟糕的错误。与“SELECT”无关。我从你的代码开始工作，并将主查询中选择列表中的标量子查询更改为常规“JOIN”：你需要在第二行中的“FROM paid_users”后删除“，”。花了我一段时间才找到这个麻烦的小错误！ - Patrick

嘿，Patrick - 应该是CURRENT_DATE而不是CURRENT_TIME吗？我使用current_time时出现“ERROR：function list_all_upsells（带有时区的时间）不存在 LINE 2：FROM list_all_upsells（current_time）up ^ 提示：没有函数与给定的名称和参数类型匹配。您可能需要添加显式类型转换。” 当我尝试current_date时，我得到了0。 - Ashley I.

啊，CURRENT_TIME 返回一个带有时区的时间戳。应该使用 LOCALTIMESTAMP 替代它；这会返回一个普通的时间戳。我原本以为自动进行类型转换。已更新回答。 - Patrick

哼，我不知道为什么，但我得到的结果是0，而且当我运行最后一个查询时，我只得到十月份的返回结果。 - Ashley I.