时间戳

首页 > 解决方案 > 如何从我的 postgresql 函数中消除重复项？

sql - 如何从我的 postgresql 函数中消除重复项？

问题描述

我有这个 vehicle_data 表：CREATE TABLE public.vehicle_data

CREATE TABLE vehicle_data
(
  model_name text NOT NULL,
  record_date text NOT NULL,
  inv_quantity integer,
  CONSTRAINT vehicle_data_pkey PRIMARY KEY (model_name, record_date)
)

我的桌子看起来像：

model_name  record_date
  car1         5-2015
  car1         1-2016
  car1         2-2015
  car2         2-2017
  car3         8-2016

当我运行我的函数来搜索任何汽车时，我想获得按月和按年对条目进行排序的结果，到目前为止，car1 应该是这样的：

model_name  record_date
  car1         2-2015
  car1         5-2015
  car1         1-2016

由于我的 record_date 是 TEXT，我认为在我的函数中我可以使用 split_part(record_date,'-',2) 拆分 TEXT 数组以获取年份值，将所有唯一值存储在一个数组中，然后运行我的选择查询每年。

CREATE OR REPLACE FUNCTION getdata(model text)
   RETURNS TABLE(a text, b text) AS
$BODY$
DECLARE  i int;
     list TEXT[]:= ARRAY(SELECT DISTINCT split_part(record_date,'-',2) as xyz
             from vehicle_data 
             order by xyz);
BEGIN
i:=0;
WHILE i < (select cardinality(list)-1) LOOP 
RETURN QUERY
    select model_name, record_date
    from vehicle_data
    where model_name LIKE model AND split_part(record_date,'-',2) LIKE list[i]
    order by length(record_date), record_date ASC;
i:=i+1;
END LOOP;
RETURN;
END;
$BODY$
  LANGUAGE plpgsql;

尽管该函数确实有效，但它复制了 68 次结果，而不是停止。

标签： sqlpostgresqlfunctionplpgsql

解决方案

要解决您的直接问题：迭代数组使用 FOREACH 循环，而不是 WHILE 循环。所以你应该把你的函数改成这样：

CREATE OR REPLACE FUNCTION getdata(p_model text)
   RETURNS TABLE(a text, b text) AS
$BODY$
DECLARE  
  l_year text;
  l_year_list TEXT[]:= ARRAY(SELECT DISTINCT split_part(record_date,'-',2) as xyz
                             from vehicle_data);
BEGIN
  foreach l_year in array l_year_list loop
    RETURN QUERY
      select model_name, record_date
      from vehicle_data
      where model_name LIKE p_model 
        AND split_part(record_date,'-',2) = l_year
      order by length(record_date), record_date ASC;
  END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;

由于不涉及通配符，我将其更改LIKE为=. 我还对参数和变量应用了不同的命名模式。

但是 Postgres 在 SQL 中具有非常强大的数组功能，因此可以将上面的内容重写为单个查询而无需循环：

CREATE OR REPLACE FUNCTION getdata(p_model text)
   RETURNS TABLE(a text, b text) AS
$BODY$
DECLARE  
  l_year_list TEXT[]:= ARRAY(SELECT DISTINCT split_part(record_date,'-',2) as xyz
                             from vehicle_data);
BEGIN
  RETURN QUERY
    select model_name, record_date
    from vehicle_data
    where model_name LIKE p_model 
      AND split_part(record_date,'-',2) = ANY(l_year_list)
    order by length(record_date), record_date ASC;
END;
$BODY$
  LANGUAGE plpgsql;

这会从表中获取所有年份并返回内部查询行中的年份（在第二个变体的情况下只有一个）等于数组中至少一个值的那些行，这对于所有行都是正确的，因为数组中的值是该列中所有现有的年份。因此，数组中的至少一个值将与处理查询时当前正在查看的行中的值匹配，这反过来意味着整个条件根本不是必需的。

你可以想象你的函数（逻辑上）是这样处理的：

  1. 将所有年份放入一个数组中，因此该数组包含 {2015, 2016, 2017}

  2. 查找模型名称匹配的所有行。对于您的示例，这给我们留下了

    型号名称 记录日期 car1 5-2015 car1 1-2016 car1 2-2015

  3. 遍历上面的行，查看 record_date 的年份部分是否与数组中的任何日期匹配。该条件将始终为真，因为数组包含该列的所有可能值。因此，该条件不会从结果中删除任何内容。

这反过来意味着，您的查询相当于：

select vd1.model_name, vd1.record_date
from vehicle_data vd1
where vd1.model_name LIKE = 'car1'
order by split_part(record_date, '-', 2)::int, 
         split_part(record_date, '-', 1)::int;

推荐阅读