집계함수절차

집계함수절차

나는 두 가지 파라미터로 절차를 만들어야 합니다.kol그리고.agg.kol분명히 열과.agg는 집합 함수입니다.

다음 표가 있다고 가정해 보겠습니다.

+---------+---------+
| X (int) | Y (int) |
+---------+---------+
| 5       | 2       |
| 4       | 4       |
+---------+---------+

저는 절차를 호출하고 싶습니다.('X','sum')- 그 다음에 표시됩니다.SELECT SUM(x) FROM table그래서 9.('Y','avg')드라마들.SELECT AVG(y) FROM table그래서 3개.

제가 뭔가를 가지고 있지만, 그것은 그 일에 관해서는 효과가 없습니다.SUM,AVG,MIN그리고.MAX, 계산할 때만 작동합니다.SUM,AVG,MIN그리고.MAX표시 0, 이유 모름):

-- I skipped some lines in procedure, only this is necessary

BEGIN
  IF(kol IN (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where 
  TABLE_NAME='table')) THEN 

    -- getting every column for table above
    SELECT (
      CASE 
        WHEN agg='SUM' THEN sum(kol)
        WHEN agg='COUNT' THEN count(kol)
        WHEN agg='AVG' THEN avg(kol)
        WHEN agg='MAX' THEN max(kol)
        WHEN agg='MIN' THEN min(kol)
    END) Result FROM table;
  END IF;
END

다음과 같은 솔루션을 사용할 수 있습니다.

DELIMITER //

CREATE PROCEDURE testProc (IN col VARCHAR(10), IN agg VARCHAR(10))
  BEGIN
    SET @select = '';

    -- get the select part with the aggregation function.
    -- using UPPER to allow case-insensitive input.
    SELECT CASE 
      WHEN UPPER(agg) = 'SUM' THEN CONCAT('SUM(', col, ')')
      WHEN UPPER(agg) = 'COUNT' THEN CONCAT('COUNT(', col, ')')
      WHEN UPPER(agg) = 'AVG' THEN CONCAT('AVG(', col, ')')
      WHEN UPPER(agg) = 'MAX' THEN CONCAT('MAX(', col, ')')
      WHEN UPPER(agg) = 'MIN' THEN CONCAT('MIN(', col, ')')
    END
    INTO @select;

    -- create and prepare the full statement.
    SET @stmt = CONCAT('SELECT ', @select, ' AS Result FROM table_name');
    PREPARE stmtExec FROM @stmt;

    -- execute the statement.
    EXECUTE stmtExec;
  END//

귀사의 데이터로 이 솔루션을 사용해 본 결과 다음과 같은 결과가 나왔습니다.

CALL testProc('x', 'SUM');      -- 9
CALL testProc('y', 'SUM');      -- 6
CALL testProc('x', 'COUNT');    -- 2
CALL testProc('y', 'COUNT');    -- 2
CALL testProc('x', 'AVG');      -- 4.500
CALL testProc('y', 'AVG');      -- 3.000
CALL testProc('x', 'MAX');      -- 5
CALL testProc('y', 'MAX');      -- 4
CALL testProc('x', 'MIN');      -- 4
CALL testProc('y', 'MIN');      -- 2

열 매개 변수는 집계 함수의 매개 변수로 사용할 수 없습니다.그러나 문자열을 생성하여 준비된 문장에 사용할 수 있습니다(위의 솔루션 참조).

사용자 변수는 데이터 값을 제공하기 위한 것입니다.SQL 문에서 직접 식별자로 사용하거나 테이블 또는 데이터베이스 이름이 예상되는 컨텍스트와 같이 식별자의 일부로 사용하거나 SELECT와 같이 예약된 단어로 사용할 수 없습니다.

사용자 변수를 사용하여 식별자를 제공할 수 없다는 이 원칙의 예외는 나중에 실행할 준비된 문장으로 사용할 문자열을 구성하는 경우입니다.이 경우 사용자 변수를 사용하여 문의 모든 부분을 제공할 수 있습니다.

출처: https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

---

당신이 댓글에 추가 정보를 제공한 후 다음과 같은 솔루션으로 변경했습니다.결과에 대한 열 이름과 집계 함수도 확인할 수 있습니다.

DELIMITER //

CREATE PROCEDURE testProc (IN col VARCHAR(10), IN agg VARCHAR(10))
  BEGIN
    SET @select = '';

    -- get the select part with the aggregation function.
    -- using UPPER to allow case-insensitive input.
    SELECT CASE 
      WHEN UPPER(agg) = 'SUM' THEN CONCAT('SUM(', col, ')')
      WHEN UPPER(agg) = 'COUNT' THEN CONCAT('COUNT(', col, ')')
      WHEN UPPER(agg) = 'AVG' THEN CONCAT('AVG(', col, ')')
      WHEN UPPER(agg) = 'MAX' THEN CONCAT('MAX(', col, ')')
      WHEN UPPER(agg) = 'MIN' THEN CONCAT('MIN(', col, ')')
    END
    INTO @select;

    -- create and prepare the full statement.
    SET @stmt = CONCAT('SELECT CONCAT(\'', col, '|', UPPER(agg), '|\', ', @select, ') AS Result FROM table_name');
    PREPARE stmtExec FROM @stmt;

    -- execute the statement.
    EXECUTE stmtExec;
  END//

그래서 이 솔루션을 다시 테스트해 본 결과 다음과 같은 결과가 나왔습니다.

CALL testProc('x', 'SUM');      -- x|SUM|9
CALL testProc('y', 'SUM');      -- y|SUM|6
CALL testProc('x', 'COUNT');    -- x|COUNT|2
CALL testProc('y', 'COUNT');    -- y|COUNT|2
CALL testProc('x', 'AVG');      -- x|AVG|4.5000
CALL testProc('y', 'AVG');      -- y|AVG|3.0000
CALL testProc('x', 'MAX');      -- x|MAX|5
CALL testProc('y', 'MAX');      -- y|MAX|4
CALL testProc('x', 'MIN');      -- x|MIN|4
CALL testProc('y', 'MIN');      -- y|MIN|2

언급URL : https://stackoverflow.com/questions/47236993/aggregate-function-procedure