Я не знаком с функциями и хранимыми процедурами PostgreSQL, но за последние несколько дней я сделал все возможное, чтобы научиться. Я очень старался, прежде чем спрашивать здесь.
По сути, у меня есть ситуация, в которой я не могу использовать простой SQL и в которой функция была бы наиболее полезной. (Это потому, что я отправляю запрос через AJAX в веб-службу на основе Postgres, которая возвращает JSONP, и, поскольку запрос построен на JavaScript на основе непредопределенного количества переменных, он может вырасти за пределы 2000 или около того символов URL. ограничение разрешено в MSIE.)
Скажем, у меня есть таблица с именем clients:
+-------------+-------------------+-------------+---------------+
| 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 * standardise((SELECT array(SELECT sales_value FROM clients)), clients.sales_value) AS rnk_sales FROM clients
... это приемлемо. Все, что связано с заказом, замедляет процесс сканирования. То есть:
SELECT 0.5 * standardise((SELECT array(SELECT sales_value FROM clients)), clients.sales_value) AS rnk_sales FROM clients ORDER BY rnk_sales LIMIT 10
Есть ли способ, которым я могу улучшить скорость вышеуказанной функции. Или, может быть, совсем другой подход? Любая помощь приветствуется. Спасибо!
ОБНОВЛЕНИЕ:
Я запустил EXPLAIN ANALYZE с последним запросом. Для этого я выбрал только образец из всей таблицы, так как это занимало слишком много времени. Я отменил запрос после ожидания в течение 10 минут. Это на столе с 1000 клиентов:
EXPLAIN ANALYZE SELECT 0.5 * standardise((SELECT array(SELECT sales_value FROM clients_sample)), clients_sample.sales_value) AS rnk_sales FROM clients_sample ORDER BY rnk_sales LIMIT 10
Результат:
Limit (cost=78.82..78.83 rows=10 width=8) (actual time=357.806..357.822 rows=10 loops=1) InitPlan 2 (returns $1) -> Result (cost=12.00..12.00 rows=1 width=0) (actual time=1.267..1.268 rows=1 loops=1) InitPlan 1 (returns $0) -> Seq Scan on clients_sample clients_sample_1 (cost=0.00..12.00 rows=1000 width=8) (actual time=0.002..0.666 rows=1000 loops=1) -> Sort (cost=66.82..67.32 rows=1000 width=8) (actual time=357.805..357.809 rows=10 loops=1) Sort Key: ((0.5::double precision * standardise($1, clients_sample.sales_value))) Sort Method: top-N heapsort Memory: 25kB -> Seq Scan on clients_sample (cost=0.00..62.50 rows=1000 width=8) (actual time=1.870..356.742 rows=1000 loops=1) Total runtime: 357.850 ms
var
иweights
никогда не упоминаются. (ДОБАВЬТЕ несколько корреляционных имен, чтобы прояснить ситуацию) - person wildplasser   schedule 28.04.2014explain analyze your-last-query-with-order-by-and-limit
и вставить результат в конец своего вопроса? - person Mike Sherrill 'Cat Recall'   schedule 28.04.2014