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

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 クエリにおけるテーブル式で最も重要な機能の一つです。 これらの機能を理解することで、さまざまな集計処理を行うことができます。