时间戳

首页 > 解决方案 > DO 块工作正常，但函数循环太多次？

sql - DO 块工作正常，但函数循环太多次？

问题描述

我有一张vehicle_data桌子：

CREATE TABLE public.vehicle_data (
  model_name text NOT NULL,
  record_date text NOT NULL,
  actual_inv_days real,
  forecasted_inv_days real[],
  CONSTRAINT vehicle_data_pkey PRIMARY KEY (model_name, record_date)
)

因此我的表如下所示：

model_name   record_date  actual_inv_days  forecasted_inv_days
car1          1-2015         33            {22,33,11,22,33,44}
car1          2-2015         22            {25,35,10,22,30}
car1          3-2015         30            {32,30,20,11}

我想为用户选择的月份n创建一个合并数组，显示actual_inv_days从月份1..n开始，然后在一个数组forecasted_inv_days中显示月份n 。结果merged_array('car1',2015,3)将是：

{33,22,30,32,30,20,11}

下面的函数作为一个DO块工作得很好，但是当我将它修改为一个函数时，它似乎循环了 68 倍于必要的次数。这是我编写的每个函数都发生的情况，我不知道该怎么做。

-- DROP FUNCTION public.merged_array(text, integer, integer);
CREATE OR REPLACE FUNCTION public.merged_array(model text, yr integer, pivot integer)
  RETURNS real[] AS
$BODY$
DECLARE actual_arr real[];
    element real;
    final_arr real[];
BEGIN
    FOR i IN 1..pivot 
      LOOP
    element:= (
          SELECT actual_inv_days
          FROM   vehicle_data
          WHERE  model_name = model
          AND split_part(record_date,'-',2)::int = yr
          AND split_part(record_date,'-',1)::int = i);
        -- append new element to existing array
        actual_arr:= actual_arr||element;
    END LOOP;
    final_arr:= actual_arr||(
         SELECT forecasted_inv_days
         FROM   vehicle_data
         WHERE  model_name= model
         AND split_part(record_date,'-',2)::int = yr
         AND split_part(record_date,'-',1)::int = pivot);
    RETURN final_arr;
END$BODY$
  LANGUAGE plpgsql;

我试图做的是：

  1. actual_array在我们添加actual_inv_daysas 元素的位置创建model特定年份 ( yr) 的特定元素，所选月份为pivot。

  2. 逐月进行，直到达到“枢轴月”，模型和年份都得到满足。

  3. 创建一个将和final_array合并的数组。actual_arrayforecasted_inv_days

标签： sqlpostgresqlfunctionloopsplpgsql

解决方案

我认为您的代码中没有理由“循环 68 次比必要的次数”。

尽管如此，就像 a_horse 评论的那样，所有这些都应该是一个查询。而且您的数据模型可以从重新设计中获益——尤其是列record_date。一串模式1-2015是存储该信息的最不利方式之一。甚至不允许简单的范围查询。（2015-01那样会更有用，但仍然不是最理想的。）

两integer列会更好。
我会使用单个date，截断为月份，因为它最适合要存储的信息。占用 4 个字节。

然后您的表格可能如下所示（其他所有内容均未更改）：

CREATE TABLE vehicle_data (
  model_name  text NOT NULL
, record_date date NOT NULL
, actual_inv_days real
, forecasted_inv_days real[]
, CONSTRAINT record_date_truncated_to_month CHECK (date_trunc('month', record_date) = record_date)
, CONSTRAINT vehicle_data_pkey PRIMARY KEY (model_name, record_date)
);

然后你可以将查询包装到这个普通的 SQL 函数中，它可以替代旧的：

CREATE OR REPLACE FUNCTION public.merged_array(model text, yr integer, pivot integer)
  RETURNS real[] AS
$func$
   SELECT forecasted_inv_days
       || ARRAY (
            SELECT actual_inv_days
            FROM   vehicle_data
            WHERE  model_name = $1
            AND    record_date BETWEEN to_date(yr::text, 'YYYY')
                               AND     to_date(yr||'-'||pivot, 'YYYY-MM')
            ORDER  BY record_date
            )
   FROM   vehicle_data
   WHERE  model_name = model
   AND    record_date = to_date(yr||'-'||pivot, 'YYYY-MM')
$func$  LANGUAGE sql;

当然，您现在也可以传递一个实际日期（截断为月份）......

to_date()关于和关于的手册date_trunc()。

关于上述相关子查询中使用的 ARRAY 构造函数：

推荐阅读