Функция PostgreSQL для выполнения серии операций с текущей записью, а затем возвращает результат

Question

Я не знаком с функциями и хранимыми процедурами PostgreSQL, но я приложил все усилия, чтобы учиться в течение последних нескольких дней. Я очень старался, прежде чем спросить здесь.

По сути, у меня есть ситуация, в которой я не могу использовать простой SQL и в которой функция будет наиболее полезной. (Это потому, что я отправляю запрос через AJAX в веб-сервис на основе Postgres, который возвращает JSONP, и, поскольку запрос построен в JavaScript на основе непредопределенного числа переменных, он может расти после символа URL 2000 или около того допустимый предел в MSIE.)

Скажем, у меня есть таблица с именем клиентов:

+-------------+-------------------+-------------+---------------+
|   CLIENT    | MONTHLY_PURCHASES | SALES_VALUE | RETURNS_VALUE |
+-------------+-------------------+-------------+---------------+
| Mercury Ltd | 3                 | 400000      | 30000         |
| Saturn Plc  | 11                | 150000      | 30000         |
| Uranus Ltd  | 4                 | 80000       | 1000          |
+-------------+-------------------+-------------+---------------+

Предполагается, что запрос возвращает клиентов, ранжированных по различным критериям, содержащимся в столбцах. Количество столбцов может увеличиться в будущем.

Например, если я хочу получить топ-10 лучших клиентов, ранжированных от 100 (лучший) до 0 (худший), запрос SQL будет выглядеть так:

WITH var AS (
    --we need the min and max values for each criteria, to calculate the rank later
    SELECT 
      MIN(monthly_purchases) AS min_pur,
      MAX(monthly_purchases) AS max_pur,
      MIN(sales_value) AS min_sales,
      MAX(sales_value) AS max_sales,
      MIN(returns_value) AS min_returns,
      MAX(returns_value) AS max_returns
    FROM clients
),
--standardise values to a 0 to 100 range, so we can compare apples with oranges, and assign weights to each criteria (from 0 to 1)
weights AS (        
    SELECT client,
      --the higher the number of purchases the better. Weight: 0.2 out of 1.
      0.2 * (clients.monthly_purchases - var.min_pur) / (var.max_pur - var.min_pur) * 100 AS rnk_pur,
      --the higher the value of sales, the better. Weight: 0.4 out of 1.
      0.4 * (clients.sales_value - var.min_sales) / (var.max_sales - var.min_sales) * 100 AS rnk_sales,
      --the lower the value of returns the better. Weight: 0.4 out of 1.
      0.4 * (1 - (clients.returns_value - var.min_returns) / (var.max_returns - var.min_returns)) * 100 AS rnk_returns
    FROM clients, var
)
SELECT weights.client, weights.rnk_pur + weights.rnk_sales + weights.rnk_returns as overall_rank FROM weights ORDER BY overall_rank DESC LIMIT 10

Все хорошо, но на самом деле количество столбцов больше (около 40), и пользователь может использовать для ранжирования сразу все от 1 до 15.

Таким образом, маршрут SQL неосуществим. Я попытался создать функцию, которая, по крайней мере, выполняла бы стандартизацию значений:

--Firstly, a function to find the highest value in an array
DROP FUNCTION IF EXISTS array_max(float[]);

CREATE OR REPLACE FUNCTION array_max(float[])
RETURNS float
AS $$
  select max(x) from unnest($1)x order by 1;
$$
LANGUAGE 'sql';

--Secondly, a function to find the lowest value in an array
DROP FUNCTION IF EXISTS array_min(float[]);

CREATE OR REPLACE FUNCTION array_min(float[])
RETURNS float
AS $$
  select min(x) from unnest($1)x order by 1;
$$
LANGUAGE 'sql';

--Finally, our function
DROP FUNCTION IF EXISTS standardise(float[], float);

CREATE OR REPLACE FUNCTION standardise(myarray float[], val float)
RETURNS float AS
$$

DECLARE
  minimum float;
  maximum float;
  calc_result float;
BEGIN
  minimum = array_min(myarray);
  maximum = array_max(myarray);

  calc_result = (val - minimum) / (maximum - minimum) * 100;

  RETURN calc_result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

Не удивительно, что функция довольно медленная. Если используется так:

SELECT 0.5 * стандартизировать ((массив SELECT (SELECT sales_value ОТ клиентов)), clients.sales_value) AS rnk_sales
ОТ клиентов

... это приемлемо. Все, что связано с упорядочением, замедляет процесс сканирования. То есть:

SELECT 0.5 * стандартизировать ((массив SELECT (SELECT sales_value ОТ клиентов)), clients.sales_value) AS rnk_sales
ОТ клиентов ЗАКАЗАТЬ ПО ПРЕДЕЛУ rnk_sales 10

Есть ли способ, которым я могу улучшить скорость вышеупомянутой функции. Или, может быть, совершенно другой подход? Любая помощь приветствуется. Спасибо!

ОБНОВИТЬ:

Я запустил EXPLAIN ANALYZE с последним запросом. Для этого я выбрал только образец из всей таблицы, так как это заняло слишком много времени. Я отменил запрос после ожидания в течение 10 минут. Это на столе с 1000 клиентов:

ОБЪЯСНИТЕ АНАЛИЗ SELECT 0.5 * стандартизировать ((массив SELECT (SELECT sales_value FROM clients_sample)), clients_sample.sales_value) AS rnk_sales
FROM client_sample ORDER BY rnk_sales LIMIT 10

Результат:

Предел (стоимость =78,82,78,83 строки = 10 ширина =8) (фактическое время =357.806..357.822 строки = 10 циклов =1)
  InitPlan 2 (возвращает $1)
    -> Результат (стоимость =12.00..12.00 строк = 1 ширина = 0) (фактическое время =1.267..1.268 строк = 1 петля =1)
          InitPlan 1 (возвращает $0)
            ->  Seq Scan на client_sample clients_sample_1  (стоимость = 0,00..12,00 строк = 1000 ширины =8) (фактическое время = 0,002..0,666 строк = 1000 циклов =1)
  -> Сортировка (стоимость =66.82..67.32 строк = 1000 ширина =8) (фактическое время =357.805..357.809 строк = 10 циклов = 1)
        Ключ сортировки: ((0,5:: двойная точность * стандартизировать ($1, clients_sample.sales_value)))
        Метод сортировки: топ-N heapsort Память: 25 КБ
        -> Seq Scan на client_sample  (стоимость = 0,00..62,50 строк = 1000 ширины =8) (фактическое время = 1,870.,356,742 строк = 1000 циклов = 1)
Общее время выполнения: 357,850 мс

Answer 1

Санированные вспомогательные функции

CREATE OR REPLACE FUNCTION array_max(float[])
  RETURNS float AS
'SELECT max(x) from unnest($1) x'
LANGUAGE sql;

ORDER BY 1 было бы бесполезно, так как max(x) в любом случае возвращает одну строку.
То же самое для array_min(float[]);

Однако не используйте эти функции. Дешевле получить min() а также max() в один звонок.

Основная функция:

Вместо этого используйте простую функцию SQL:

CREATE OR REPLACE FUNCTION standardise(_arr float[], _val float)
  RETURNS float AS
$func$
SELECT ((_val - min_x) * 100) / (max_x - min_x)
FROM (
   SELECT min(x) AS min_x, max(x) AS max_x
   FROM   unnest($1) x
   ) sub
$func$
LANGUAGE sql IMMUTABLE;

• Использование подзапроса для получения обоих агрегатов одновременно.
• Умножьте сначала для обычно более высокой точности.
• Не цитируйте название языка.