PostgreSQL クエリにおけるテーブル式: GROUP BY と HAVING 句

2024-04-02

PostgreSQL クエリにおけるテーブル式: GROUP BY と HAVING 句

概要

GROUP BY 句は、1つ以上の列に基づいて行をグループ化します。 グループ化された行に対して、集計関数を使用して統計情報などを計算することができます。

例:

SELECT country, COUNT(*) AS total_population
FROM customers
GROUP BY country;

このクエリは、customers テーブルの country 列に基づいて行をグループ化し、各国の総人口 (COUNT(*)) を計算します。

HAVING 句は、GROUP BY 句でグループ化された結果に対して、条件を指定して絞り込みます。

例:

SELECT country, COUNT(*) AS total_population
FROM customers
GROUP BY country
HAVING COUNT(*) > 1000000;

このクエリは、人口が100万人を超える国のみを抽出します。

GROUP BY と HAVING 句の使い分け

GROUP BY 句は、グループ化を行うためのものです。 HAVING 句は、グループ化結果に対して条件を指定して絞り込むためのものです。

WHERE 句との違い

WHERE 句は、GROUP BY 句の前に使用し、グループ化を行う前に条件を絞り込むためのものです。

例:

SELECT country, COUNT(*) AS total_population
FROM customers
WHERE country IN ('Japan', 'USA')
GROUP BY country;

このクエリは、日本とアメリカの顧客のみを対象に、各国の総人口を計算します。

GROUP BY 句と HAVING 句を組み合わせることで、複雑な集計処理を行うことができます。



PostgreSQL クエリにおけるテーブル式: GROUP BY と HAVING 句のサンプルコード

商品カテゴリー別売上合計と平均単価

SELECT
  category,
  SUM(quantity_sold) AS total_sales,
  SUM(quantity_sold) / COUNT(DISTINCT order_id) AS average_price
FROM orders
GROUP BY category;

年齢層別顧客数と平均購入金額

SELECT
  age_range,
  COUNT(*) AS customer_count,
  AVG(total_amount) AS average_purchase_amount
FROM customers
GROUP BY age_range
HAVING COUNT(*) > 100;

このクエリは、年齢層別顧客数と平均購入金額を計算し、顧客数が100人以上の年齢層のみを抽出します。

地域別売上トップ3の製品

SELECT
  region,
  product_id,
  product_name,
  SUM(quantity_sold) AS total_sales
FROM orders
GROUP BY region, product_id, product_name
ORDER BY total_sales DESC
LIMIT 3;

このクエリは、地域別売上トップ3の製品を抽出します。

過去3ヶ月間の月別売上推移と前月比

SELECT
  date_trunc('month', order_date) AS month,
  SUM(quantity_sold) AS total_sales,
  CASE
    WHEN LAG(total_sales) OVER (ORDER BY month) IS NULL THEN 0
    ELSE (total_sales - LAG(total_sales) OVER (ORDER BY month)) / LAG(total_sales) OVER (ORDER BY month)
  END AS monthly_growth
FROM orders
WHERE order_date BETWEEN CURRENT_DATE - INTERVAL '3 months' AND CURRENT_DATE
GROUP BY month
ORDER BY month;

このクエリは、過去3ヶ月間の月別売上推移と前月比を計算します。

従業員別売上と顧客満足度

SELECT
  employee_id,
  employee_name,
  SUM(total_amount) AS total_sales,
  AVG(customer_satisfaction) AS average_satisfaction
FROM orders
GROUP BY employee_id, employee_name
HAVING AVG(customer_satisfaction) > 4;

このクエリは、従業員別売上と顧客満足度を計算し、顧客満足度が4以上の従業員のみを抽出します。

  • HAVING 句で複数の条件を指定するには、AND や OR 演算子を使用します。
  • GROUP BY 句で複数の列を指定するには、カンマで区切ります。
  • 集計関数以外にも、CASE 式やその他の関数を使用することができます。

GROUP BY 句と HAVING 句を組み合わせることで、さまざまな集計処理を行うことができます。 サンプルコードを参考に、ニーズに合ったクエリを作成してみてください。



PostgreSQL クエリにおけるテーブル式: GROUP BY と HAVING 句のその他の方法

ウィンドウ関数

例:

SELECT
  category,
  SUM(quantity_sold) OVER (PARTITION BY category) AS total_sales,
  SUM(quantity_sold) OVER (PARTITION BY category) / COUNT(DISTINCT order_id) OVER (PARTITION BY category) AS average_price
FROM orders;

このクエリは、ウィンドウ関数 PARTITION BY を使用して、商品カテゴリー別売上合計と平均単価を計算します。

サブクエリ

GROUP BY 句と HAVING 句の代わりに、サブクエリを使用して同様の結果を得ることができます。 サブクエリは、別のクエリを呼び出すクエリです。

例:

SELECT
  category,
  (SELECT SUM(quantity_sold) FROM orders WHERE category = o.category) AS total_sales,
  (SELECT COUNT(DISTINCT order_id) FROM orders WHERE category = o.category) AS customer_count
FROM orders AS o;

このクエリは、サブクエリを使用して、商品カテゴリー別売上合計と顧客数を計算します。

どの方法を使用するべきかは、状況によって異なります。 一般的には、GROUP BY 句と HAVING 句が最もシンプルで効率的な方法です。 ただし、複雑な集計処理を行う場合は、ウィンドウ関数やサブクエリの方が便利な場合があります。

GROUP BY 句と HAVING 句は、PostgreSQL クエリにおけるテーブル式で最も重要な機能の一つです。 これらの機能を理解することで、さまざまな集計処理を行うことができます。




PostgreSQL での tsquery 以外の全文検索方法

tsquery の基本tsquery 型は、クエリツリー 構造としてテキスト検索条件を表現します。クエリツリーは、ノード と呼ばれる個々の要素で構成されます。各ノードは、特定の検索条件を表します。tsquery の構成要素語彙素: 単語やフレーズなどの検索対象となる文字列



PostgreSQL WITHクエリ: SELECTを活用して複雑なクエリをシンプルに

WITHクエリは、CTE(Common Table Expressions)と呼ばれる一時的なテーブルを定義し、そのテーブルを後続のSELECTクエリで参照できる機能です。複雑なクエリを複数の小さなクエリに分割することで、コードの可読性と保守性を向上させることができます。



PostgreSQL smallint データ型を使用したパフォーマンスのヒント

比較的小さな範囲の整数を扱う場合ディスク容量を節約したい場合商品IDユーザーID年齢点数負の数値を格納する場合、符号ビットを含めて16ビット分の表現範囲となるため、-32, 768から32, 767までの範囲を超える値を格納することはできません。


PostgreSQL での tsquery 以外の全文検索方法

tsquery の基本tsquery 型は、クエリツリー 構造としてテキスト検索条件を表現します。クエリツリーは、ノード と呼ばれる個々の要素で構成されます。各ノードは、特定の検索条件を表します。tsquery の構成要素語彙素: 単語やフレーズなどの検索対象となる文字列


PostgreSQL EXCEPTの代替方法:NOT EXISTS、LEFT JOIN、CASE式など

PostgreSQLのEXCEPTは、2つのクエリ結果の差集合を返す演算子です。つまり、クエリ1にのみ存在する行を取得します。構文例2つのテーブルusersとordersがあるとします。usersテーブルにのみ存在するユーザーを取得するには、次のようにEXCEPTを使用します。


PostgreSQLにおけるmacaddr8データ型とは?

PostgreSQLのmacaddr8データ型は、EUI-64形式のMACアドレスを格納するために使用されます。従来のmacaddrデータ型と異なり、こちらは8バイトのサイズを持ち、より新しいMACアドレス形式に対応することができます。特徴


timestamp データ型から日付・時刻の取得

PostgreSQLのtimestampデータ型は、タイムスタンプを表すために使用されます。タイムスタンプは、日付と時刻を組み合わせた値で、時間の経過を追跡するために使用されます。データ型timestampデータ型は、以下の形式で表されます。