현재 레코드로 일련의 작업을 수행 한 다음 결과를 반환하는 PostgreSQL 함수

PostgreSQL 함수와 저장 프로 시저에 익숙하지 않지만 지난 며칠 동안 배우기 위해 최선을 다했습니다. 여기서 물어보기 전에 열심히 노력했습니다.

기본적으로 간단한 SQL을 사용할 수없고 함수가 가장 도움 이되는 상황 이 있습니다. (이는 AJAX를 통해 JSONP를 반환하는 Postgres 기반 웹 서비스로 쿼리를 보내고 있기 때문이며 쿼리가 미리 결정되지 않은 수의 변수를 기반으로 JavaScript로 빌드 되었기 때문에 2000 개 정도의 URL 문자를 넘어서 성장할 수 있습니다. MSIE에서 허용되는 제한.)

클라이언트 라는 테이블이 있습니다 .

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

쿼리는 열에 포함 된 다양한 기준에 따라 순위가 매겨진 클라이언트를 반환해야합니다. 열 수는 향후 증가 할 수 있습니다.

예를 들어 100 (최고)에서 0 (최악)으로 순위가 매겨진 상위 10 개의 최고 클라이언트를 얻으려면 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

결과:

제한 (비용 = 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 반환) 
            -> clients_sample clients_sample_1에 대한 Seq Scan (비용 = 0.00..12.00 행 = 1000 너비 = 8) (실제 시간 = 0.002 ..0.666 rows = 1000 loops = 1) 
  -> Sort (cost = 66.82..67.32 rows = 1000 width = 8) (실제 시간 = 357.805..357.809 rows = 10 loops = 1) 
        Sort Key : ((0.5 :: 배정 밀도 * standardise ($ 1, clients_sample.sales_value))) 
        정렬 방법 : 상위 N 힙 정렬 메모리 : 25kB
        -> clients_sample에서 Seq Scan (비용 = 0.00..62.50 행 = 1000 너비 = 8) (실제 시간 = 1.870..356.742 행 = 1000 루프 = 1) 
총 런타임 : 357.850ms