Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb


12.19.1 GROUP BY (集約) 関数

表 12.24 集約 (GROUP BY) 関数

名前 説明
AVG() 引数の平均値を返します
BIT_AND() ビット単位の And を返します
BIT_OR() ビット単位の OR を返します
BIT_XOR() ビット単位の XOR を返します
COUNT() 返された行数のカウントを返します
COUNT(DISTINCT) 異なる値のカウントを返します
GROUP_CONCAT() 連結された文字列を返します
MAX() 最大値を返します
MIN() 最小値を返します
STD() 母標準偏差を返します
STDDEV() 母標準偏差を返します
STDDEV_POP() 母標準偏差を返します
STDDEV_SAMP() 標本標準偏差を返します
SUM() 集計を返します
VAR_POP() 母標準分散を返します
VAR_SAMP() 標本分散を返します
VARIANCE() 母標準分散を返します

このセクションでは、値のセットを演算するグループ (集約) 関数について説明します。特に指定されていなければ、グループ関数では NULL 値が無視されます。

GROUP BY 句を含まないステートメントでグループ関数を使用する場合は、すべての行をグループ化することと同等になります。詳細は、セクション12.19.3「MySQL での GROUP BY の処理」を参照してください。

数値の引数の場合、分散および標準偏差関数が DOUBLE 値を返します。SUM() および AVG() 関数は、正確な値の引数 (整数または DECIMAL) の場合は DECIMAL 値を返し、近似値の引数 (FLOAT または DOUBLE) の場合は DOUBLE 値を返します。

SUM() および AVG() 集計関数は時間値を扱いません。(これらは値を数字に変換するので、最初の数字以外の文字のあとのすべての情報が失われます。)この問題を回避するには、数値単位に変換し、集計操作を実行してから、時間値に戻します。例:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

数値引数を取る SUM()AVG() などの関数は、必要に応じて引数を数値にキャストします。SETENUM 値の場合、キャスト演算でベースとなる数値が使用されます。

  • AVG([DISTINCT] expr)

    expr の平均値を返します。DISTINCT オプションを使用すると、個別の expr 値の平均を返すことができます。

    一致する行がなかった場合、AVG()NULL を返します。

    mysql> SELECT student_name, AVG(test_score)
        ->        FROM student
        ->        GROUP BY student_name;
  • BIT_AND(expr)

    expr 内のすべてのビットのビット単位の AND を返します。計算は 64 ビット (BIGINT) の精度で実行されます。

    一致する行がなかった場合、この関数は 18446744073709551615 を返します。(これは、すべてのビットが 1 に設定された符号なし BIGINT 値の値です。)

  • BIT_OR(expr)

    expr 内のすべてのビットのビット単位の OR を返します。計算は 64 ビット (BIGINT) の精度で実行されます。

    一致する行がなかった場合、この関数は 0 を返します。

  • BIT_XOR(expr)

    expr 内のすべてのビットのビット単位の XOR を返します。計算は 64 ビット (BIGINT) の精度で実行されます。

    一致する行がなかった場合、この関数は 0 を返します。

  • COUNT(expr)

    SELECT ステートメントで取得された行に含まれる expr の非 NULL 値の数を返します。結果は BIGINT 値になります。

    一致する行がなかった場合、COUNT()0 を返します。

    mysql> SELECT student.student_name,COUNT(*)
        ->        FROM student,course
        ->        WHERE student.student_id=course.student_id
        ->        GROUP BY student_name;

    COUNT(*) は、NULL 値が含まれるかどうかに関係なく、取得された行の数を返すという点で少し異なります。

    COUNT(*) は、SELECT が 1 つのテーブルから取得し、その他のカラムは取得されず、WHERE 句がない場合に、非常に迅速に返すように最適化されています。例:

    mysql> SELECT COUNT(*) FROM student;

    この最適化は、MyISAM テーブルにのみ適用されます。その理由は、正確な行数は、このストレージエンジンで格納されることで、非常にすばやくアクセスできるためです。InnoDB などのトランザクショナルストレージエンジンで正確な行数を格納すると、複数のトランザクションが発生し、それぞれが数に影響を与える可能性があるため、問題が発生する可能性が高くなります。

  • COUNT(DISTINCT expr,[expr...])

    さまざまな非 NULL expr 値を含む行の数を返します。

    一致する行がなかった場合、COUNT(DISTINCT)0 を返します。

    mysql> SELECT COUNT(DISTINCT results) FROM student;

    MySQL では、式のリストを指定することで、NULL が含まれない個別の式の組み合わせ数を取得できます。標準 SQL では、COUNT(DISTINCT ...) 内部で、すべての式の連結を行う必要があります。

  • GROUP_CONCAT(expr)

    この関数は、グループから連結された非 NULL 値を含む文字列の結果を返します。非 NULL 値がない場合は、NULL を返します。完全な構文は次のとおりです。

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                     [ASC | DESC] [,col_name ...]]
                 [SEPARATOR str_val])
    mysql> SELECT student_name,
        ->     GROUP_CONCAT(test_score)
        ->     FROM student
        ->     GROUP BY student_name;

    または:

    mysql> SELECT student_name,
        ->     GROUP_CONCAT(DISTINCT test_score
        ->               ORDER BY test_score DESC SEPARATOR ' ')
        ->     FROM student
        ->     GROUP BY student_name;

    MySQL では、式の組み合わせを連結した値を取得できます。重複する値を除去するには、DISTINCT 句を使用します。結果の値をソートするには、ORDER BY 句を使用します。逆順でソートするには、ORDER BY 句のソートするカラムの名前に DESC (降順) キーワードを追加します。デフォルトは昇順です。これは、ASC キーワードを使用することで明示的に指定できます。グループ内の値間のデフォルトの区切り文字は、カンマ (,) です。区切り文字を明示的に指定するには、SEPARATOR に続けて、グループ値の間に挿入される文字列リテラル値を指定します。区切り文字を完全に除去するには、SEPARATOR '' を指定します。

    結果は、group_concat_max_len システム変数で指定された最大長まで切り捨てられます。その変数のデフォルト値は 1024 です。さらに高い値にも設定できますが、戻り値の有効な最大長は、max_allowed_packet の値によって制約されます。実行時に group_concat_max_len の値を変更するための構文は、次のとおりです。ここで、val は符号なし整数です。

    SET [GLOBAL | SESSION] group_concat_max_len = val;

    戻り値は、引数が非バイナリとバイナリのどちらの文字列であるのかに応じて、非バイナリ文字列またはバイナリ文字列になります。結果の型は、group_concat_max_len が 512 以下の場合 (この場合、結果の型は VARCHAR または VARBINARY です) を除いて、TEXT または BLOB です。

    CONCAT() および CONCAT_WS(): セクション12.5「文字列関数」も参照してください。

  • MAX([DISTINCT] expr)

    expr の最大値を返します。MAX() には、文字列の引数を指定できます。このような場合は、最大の文字列値が返されます。セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。DISTINCT キーワードを使用すると、個別の expr 値の最大を検索できます。ただし、DISTINCT を省略した場合と同じ結果が生成されます。

    一致する行がなかった場合、MAX()NULL を返します。

    mysql> SELECT student_name, MIN(test_score), MAX(test_score)
        ->        FROM student
        ->        GROUP BY student_name;

    現在、MySQL の MAX() では、ENUMSET カラムが、セット内の文字列の相対位置ではなく、文字列値について比較されます。これは、ORDER BY による比較方法とは異なります。これは、今後の MySQL リリースで反映される予定です。

  • MIN([DISTINCT] expr)

    expr の最小値を返します。MIN() には、文字列の引数を指定できます。このような場合は、最小の文字列値が返されます。セクション8.3.1「MySQL のインデックスの使用の仕組み」を参照してください。DISTINCT キーワードを使用すると、個別の expr 値の最小を検索できます。ただし、DISTINCT を省略した場合と同じ結果が生成されます。

    一致する行がなかった場合、MIN()NULL を返します。

    mysql> SELECT student_name, MIN(test_score), MAX(test_score)
        ->        FROM student
        ->        GROUP BY student_name;

    現在、MySQL の MIN() では、ENUMSET カラムが、セット内の文字列の相対位置ではなく、文字列値について比較されます。これは、ORDER BY による比較方法とは異なります。これは、今後の MySQL リリースで反映される予定です。

  • STD(expr)

    expr の母標準偏差を返します。これは、標準 SQL の拡張です。代わりに、標準 SQL 関数 STDDEV_POP() を使用できます。

    一致する行がなかった場合、この関数は NULL を返します。

  • STDDEV(expr)

    expr の母標準偏差を返します。この関数は、Oracle との互換性を確保するために提供されています。代わりに、標準 SQL 関数 STDDEV_POP() を使用できます。

    一致する行がなかった場合、この関数は NULL を返します。

  • STDDEV_POP(expr)

    expr の母標準偏差 (VAR_POP() の平方根) を返します。STD() または STDDEV() を使用することもできます。これらは同等ですが、標準 SQL ではありません。

    一致する行がなかった場合、STDDEV_POP()NULL を返します。

  • STDDEV_SAMP(expr)

    expr の標本標準偏差 (VAR_SAMP() の平方根) を返します。

    一致する行がなかった場合、STDDEV_SAMP()NULL を返します。

  • SUM([DISTINCT] expr)

    expr の集計を返します。戻り値のセットに行が含まれていない場合、SUM()NULL を返します。DISTINCT キーワードを使用すると、個別の expr 値のみを集計できます。

    一致する行がなかった場合、SUM()NULL を返します。

  • VAR_POP(expr)

    expr の母標準分散を返します。行は標本ではなく、母集団全体とみなされるため、行の数が分母とみなされます。また、VARIANCE() を使用することもできます。これは同等ですが、標準 SQL ではありません。

    一致する行がなかった場合、VAR_POP()NULL を返します。

  • VAR_SAMP(expr)

    expr の標本分散を返します。つまり、分母は行の数から 1 を引いたものです。

    一致する行がなかった場合、VAR_SAMP()NULL を返します。

  • VARIANCE(expr)

    expr の母標準分散を返します。これは、標準 SQL の拡張です。代わりに、標準 SQL 関数 VAR_POP() を使用できます。

    一致する行がなかった場合、VARIANCE()NULL を返します。


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Martin Lester on October 1, 2010
If you are writting queries where you want a COUNT (or SUM) for when a field has a specific value, there is a nice optimisation. E.g.

SELECT COUNT( IF(x=1, 1, NULL) ) AS c;
SELECT SUM( IF(x=1, 1, 0) ) AS c;
SELECT COUNT(x=1 OR NULL) AS c;

The above 3 statements are equivalent. Though the last is not immediately obvious how its working. The condition x=1 is evaluated as TRUE or FALSE. If it is TRUE, then the lazy evaluation of the OR means that it exits and runs COUNT(TRUE). When x=1 is FALSE, you get FALSE OR NULL, which evaluates to NULL, so you get COUNT(NULL). This means you only COUNT the lines where x=1.

It is also worth noting that in all cases above you can have any condition e.g. x>1, x IN (1,2,3), (x=1 AND y=2)
i.e. COUNT( (x=1 AND y=2) OR NULL ) as c

  Posted by t tomtul2 on December 15, 2010
Note that Variance is quite different from Coefficient of Variation.

Variance() and Var_pop() return the Variance. The variance of a random variable or distribution is the mean of the squared deviation of that variable from its mean.

Coefficient of Variation must be calcuated by STDDEV_POP(x)/Avg(x), as far as I know there is not a built in function.

http://en.wikipedia.org/wiki/Coefficient_of_variation
http://en.wikipedia.org/wiki/Variance

  Posted by ronel licayan on April 26, 2011
To Erel Segal:

FIRST Aggregate Function MySQL equivalent is MIN

MIN returns the lowest number for numeric fields
MIN returns the first line in the GROUP BY Clause base on Sort Order for non numeric fields
  Posted by Henry Hayes on July 24, 2011
Here's a good example of how to limit the GROUP CONCAT function.

http://www.websitefactors.co.uk/mysql/2011/07/mysql-limit-the-values-in-the-group_concat-function/
  Posted by Biju Mon on June 15, 2012
An Example of Running Total Calculations
--------------------------------------------

SELECT
B.Year_Enter,
B.Month_Enter,
B.MonthName_Enter,
@IT_Open := @IT_Open + B.IT AS IT_Open,
@CS_Open := @CS_Open + B.CS AS CS_Open
FROM
(SELECT
A.Year_Enter,
A.Month_Enter,
A.MonthName_Enter,
SUM(IF(A.Month_Enter <> A.Month_Close, A.IT, 0)) AS IT,
SUM(IF(A.Month_Enter <> A.Month_Close, A.CS, 0)) AS CS
FROM
(SELECT
YEAR(I.Date_Entered) AS Year_Enter,
MONTH(I.Date_Entered) AS Month_Enter,
MONTHNAME(I.Date_Entered) AS MonthName_Enter,
IFNULL(MONTH(I.Closed_Date),0) AS Month_Close,
IFNULL(MONTHNAME(I.Closed_Date),'') AS MonthName_Close,
SUM(IF(U.Department = 'IT',1,0)) AS IT,
SUM(IF(U.Department = 'CS',1,0)) AS CS
FROM
Incident I,
User U
WHERE
I.Initiated_By = U.userLogin
GROUP BY
YEAR(I.Date_Entered),
MONTH(I.Date_Entered),
MONTHNAME(I.Date_Entered),
MONTH(I.Closed_Date),
MONTHNAME(I.Closed_Date)
ORDER BY
YEAR(I.Date_Entered),
MONTH(I.Date_Entered),
MONTH(I.Closed_Date)) A
GROUP BY
A.Year_Enter,
A.Month_Enter) B,
(SELECT
@IT_Open := 0,
@CS_Open := 0
FROM DUAL) C
  Posted by David Cooke on August 25, 2012
Yet another approach to finding the MEDIAN of a group of numbers. It requires entering your table specification and column name in only one place. It uses prepared statements, so it may not be applicable to creating a stored function, but it works for one-off uses by copying and pasting into your MySQL client.

First it runs a query to count the (non-null) values and saves the count in variable @N_VALS.

Then it runs a query containing a subquery:
- Inner query (named SQ1): from the values, selects the median value (if the number of values is odd) or the two middle values. During the selection, stores the value(s) selected in variables @MID1 and @MID2, leaving @MID1 null or @MID1<=@MID2. To accomplish this the values are ordered using an index or sort. The full scan required by some approaches, after the ordering, to assign a sequence number to every row is avoided. By using the LIMIT keyword, we can proceed directly to (at worst) a scan over half the values (also required where sequence numbers are assigned) to fetch the median value or the two middle values. We use LIMIT with OFFSET to identify the one or two values we need by position in the ordered list of values.
- Outer query: Uses @N_VALS, @MID1 and @MID2 to calculate median.

SET @TABLE =
/* Your table (or schema.table) > */ 'the_table_specification';
SET @COLUMN =
/* Your column > */ 'the_column_name';
#
SET @MEDIAN_QUERY1 = CONCAT('SELECT COUNT(', @COLUMN,') FROM ', @TABLE,' INTO @N_VALS');
PREPARE MEDIAN_PREPARED1 FROM @MEDIAN_QUERY1;
EXECUTE MEDIAN_PREPARED1;
DEALLOCATE PREPARE MEDIAN_PREPARED1;
SET @ODD = @N_VALS%2;
SET @MEDIAN_QUERY2 = CONCAT('SELECT CAST(IF(@ODD, @MID2, (@MID1+@MID2)/2) AS CHAR) AS `MEDIAN(', @COLUMN, ')` FROM (SELECT @MID1:=@MID2, @MID2:=', @COLUMN, ' FROM ',
@TABLE, ' WHERE ', @COLUMN, ' IS NOT NULL ORDER BY ', @COLUMN, ' LIMIT ', 2-@ODD, ' OFFSET ', @N_VALS DIV 2 - 1*(@N_VALS>'0') + @ODD,') SQ1 LIMIT 1');
PREPARE MEDIAN_PREPARED2 FROM @MEDIAN_QUERY2;
EXECUTE MEDIAN_PREPARED2;
DEALLOCATE PREPARE MEDIAN_PREPARED2;

  Posted by Miklos Oszkar on September 4, 2012
Here's a good example of how to limit the GROUP CONCAT function.

http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/

http://www.qubed.ro
  Posted by Alexander Cheprasov on November 19, 2013
Easy way to use LIMIT in GROUP_CONCAT function:

SELECT GROUP_CONCAT(`t`.`id`) FROM
(SELECT `id` FROM `table` LIMIT 5) `t`
  Posted by Mike Jorgenstam on February 14, 2014
@David Cooke...

Great function but 1) Cannot run in stored function so must be procedure. 2) You forgot to ad " INTO @SomeOutputVariable" in the final line I believe...

Here is a corrected version...

-- ------------------------------------------------------------------------------------
-- Dynamic SQL not permitted in stored functions, thus using a stored procedure instead.
-- Median procedure => MEDIAN(Using Column,From Table,InOut Result)
--
-- Use by...
-- CALL MEDIAN('YourColumn','YourTable',@res);
-- SELECT @res AS MEDIAN;
-- ------------------------------------------------------------------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `MEDIAN`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `MEDIAN`(UsingCol varchar(64),FromTbl varchar(64),INOUT res decimal(50,5))
BEGIN

SET @ires = 0;

SET @TABLE = FromTbl;
/* Your table (or schema.table) > 'the_table_specification';*/
SET @COLUMN = UsingCol;
/* Your column > 'the_column_name'; */
#
SET @MEDIAN_QUERY1 = CONCAT('SELECT COUNT(', @COLUMN,') FROM ', @TABLE,' INTO @N_VALS');
PREPARE MEDIAN_PREPARED1 FROM @MEDIAN_QUERY1;
EXECUTE MEDIAN_PREPARED1;
DEALLOCATE PREPARE MEDIAN_PREPARED1;
SET @ODD = @N_VALS%2;
SET @MEDIAN_QUERY2 = CONCAT('SELECT CAST(IF(@ODD, @MID2, (@MID1+@MID2)/2) AS CHAR) AS `MEDIAN(', @COLUMN, ')` FROM (SELECT @MID1:=@MID2, @MID2:=', @COLUMN, ' FROM ',
@TABLE, ' WHERE ', @COLUMN, ' IS NOT NULL ORDER BY ', @COLUMN, ' LIMIT ', 2-@ODD, ' OFFSET ', @N_VALS DIV 2 - 1*(@N_VALS>'0') + @ODD,') SQ1 LIMIT 1 INTO @ires');
PREPARE MEDIAN_PREPARED2 FROM @MEDIAN_QUERY2;
EXECUTE MEDIAN_PREPARED2;
DEALLOCATE PREPARE MEDIAN_PREPARED2;

SET res = @ires;

END;
$$

DELIMITER ;

--- Now for the rest...

Just a few samples without needing to learn C / C++ or get a separate compiler outside MySQL

-- ------------------------------------------
-- Faculty function FACULTY(x) => x!
-- input Max value = 65, min value = 0
-- ------------------------------------------
DELIMITER $$
DROP FUNCTION IF EXISTS `FACULTY`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `FACULTY`(x tinyint(3) UNSIGNED) RETURNS bigint(20) UNSIGNED
BEGIN

IF x = 1 OR x = 0 THEN
SET @res = 1;
ELSE

SET @res = 1;
SET @i = x;
WHILE @i >= 1 DO
SET @res = (@res * @i);
SET @i = @i - 1;
END WHILE;

END IF;

RETURN @res;

END;
$$

DELIMITER ;

-- ------------------------------------------
-- Combination function COMBINATIONS(x,y) => x!/y!/(x-y)!
-- input Max value = 65, min value = 0
-- ------------------------------------------
DELIMITER $$
DROP FUNCTION IF EXISTS `COMBINATIONS`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `COMBINATIONS`(x tinyint(3) UNSIGNED,y tinyint(3) UNSIGNED) RETURNS bigint(20) UNSIGNED
BEGIN

IF (x < y) THEN

SET @res = 0;

ELSEIF (x = y) OR (x = 0) OR (y = 0) THEN

SET @res = 1;

ELSE

SET @varX = FACULTY(x);
SET @varY = FACULTY(y);
SET @varZ = FACULTY(x-y);
SET @res = (@varX / @varY / @varZ);

END IF;

RETURN @res;

END;
$$

DELIMITER ;
Sign Up Login You must be logged in to post a comment.