GROUP BY、HAVING、ROLLUP/CUBE/GROUPING SETS

Oracle

-- Basic grouping + HAVING
SELECT city, COUNT(*) AS n
FROM customer
GROUP BY city
HAVING COUNT(*) > 5;

-- Subtotals and grand total with ROLLUP
SELECT city, district, SUM(balance)
FROM customer
GROUP BY ROLLUP(city, district);

GROUP BY は指定した列で行をグループ化して集計関数を適用する。HAVING はグループ化された結果をフィルタリングする(WHERE はグループ化前に行をフィルタリングする)。ROLLUP は階層的な小計 + 総計を、CUBE はすべての組み合わせを、GROUPING SETS は選択したグループ化を生成する。Oracle は 3 つすべてをサポートする。

PostgreSQL

SELECT city, district, SUM(balance)
FROM customer
GROUP BY GROUPING SETS ((city, district), (city), ());

-- CUBE produces all subset combinations
SELECT city, district, SUM(balance)
FROM customer
GROUP BY CUBE(city, district);

PostgreSQL 9.5+ は ROLLUP、CUBE、GROUPING SETS を標準準拠でサポートする。GROUPING() 関数は列が合計行(NULL)に含まれるかどうかを示す。HAVING は集計後に、WHERE は集計前にフィルタリングする。

MySQL

SELECT city, COUNT(*) AS n
FROM customer
GROUP BY city
HAVING n > 5;

-- ROLLUP in MySQL:
SELECT city, district, SUM(balance)
FROM customer
GROUP BY city, district WITH ROLLUP;

MySQL/MariaDB は GROUP BY ... WITH ROLLUP 構文で小計/総計を生成する。CUBE と GROUPING SETS はサポートされない。HAVING では SELECT のエイリアスを使用できる(Oracle/SQL Server では一般に動作しない)。ROLLUP の行では、グループ化された列は NULL として表示される。

SQL Server

SELECT city, COUNT(*) AS n
FROM customer
GROUP BY city
HAVING COUNT(*) > 5;

-- ROLLUP / CUBE / GROUPING SETS
SELECT city, district, SUM(balance)
FROM customer
GROUP BY ROLLUP(city, district);

SQL Server は GROUP BY に加えて、最新の構文で ROLLUP()、CUBE()、GROUPING SETS(2008+)をサポートする。旧来の WITH ROLLUP/WITH CUBE 形式は非推奨。GROUPING() と GROUPING_ID() は合計行の識別に役立つ。

MongoDB

// Closest equivalent: $group + $sum
db.customer.aggregate([
  { $group: { _id: "$city", n: { $sum: 1 } } },
  { $match: { n: { $gt: 5 } } }
])
// $facet is used for ROLLUP-like multi-level totals

MongoDB では $group が SQL の GROUP BY に相当する。_id がグループ化キーを設定し、アキュムレータ($sum、$avg、$max)が集計を定義する。グループ化後のフィルタリングは $match で行う(HAVING に相当)。ROLLUP/CUBE のような多階層の合計は、$facet で複数のパイプラインを実行して得る。

Redis

# Not applicable: core Redis has no GROUP BY.
# Aggregation with RediSearch:
FT.AGGREGATE customer_idx "*" GROUPBY 1 @city REDUCE COUNT 0 AS n

コアの Redis はグループ化を提供しない。RediSearch モジュールの FT.AGGREGATE コマンドが GROUPBY と REDUCE(COUNT、SUM、AVG)で SQL のような集計を行う。FILTER ステップは HAVING のようにグループ化された結果をフィルタリングする。モジュールがない場合、集計はアプリケーションで行う。

Elasticsearch

GET customer/_search
{
  "size": 0,
  "aggs": {
    "by_city": { "terms": { "field": "city" },
      "aggs": { "total": { "sum": { "field": "balance" } } } }
  }
}

Elasticsearch では、グループ化は 'terms' バケット集計で、集計値はメトリクス集計(sum、avg、cardinality)で行う。HAVING のようなフィルタリングには 'bucket_selector' パイプライン集計を使用する。多階層の小計はネストされた集計で構築する。