PostgreSQL の個別のフィールド値の数

1. PostgreSQL のフィールド値の個別の数を取得する
2. PostgreSQL の別のフィールドに基づいてフィールド値の個別のカウントを取得する

フィールドで個別の値を取得することは、知っておくべき重要なクエリです。このチュートリアルでは、フィールド内の値の個別のカウントを取得する方法について説明します。

PostgreSQL のフィールド値の個別の数を取得する

クイズゲームの各参加者のスコアを追跡する quiz_score テーブルについて考えてみます。

テーブルの CREATE ステートメントは次のとおりです。

CREATE TABLE quiz_score
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    player_id integer NOT NULL,
    score integer NOT NULL,
    CONSTRAINT quiz_score_pkey PRIMARY KEY (id)
);

そして、これがテーブルにデータを入力するための INSERT ステートメントです。

INSERT INTO quiz_score (player_id, score)
SELECT i, floor(random()*(100-0+1))
FROM generate_series(1,10000000) i;

テーブルに 1,000 万のランダムスコアを挿入しました。このクエリを実行して、テーブルにいくつの異なるスコアがあるかを調べましょう。

SELECT COUNT(DISTINCT score) FROM quiz_score;

このクエリには 3 秒 391 ミリ秒かかり、101 個の異なる値がカウントされました。

この他のクエリを実行して、テーブル内の個別のスコアの数を見つけることもできます。

SELECT COUNT(*) FROM (SELECT DISTINCT score FROM quiz_score) AS DistinctScores;

この新しいクエリには 1 秒と 572 ミリ秒かかり、101 個の異なる値がカウントされました。

ご覧のとおり、2 番目のクエリの方が高速でした。どちらのクエリも正常に機能しますが、この場合、2 番目のクエリの方が高速でした。

PostgreSQL の別のフィールドに基づいてフィールド値の個別のカウントを取得する

ここで、Expertise という新しい列を紹介します。このフィールドは、プレーヤーのスコアに基づいて入力されます。

Expertise の値は、Beginner、Intermediary、Expert、および Master です。プレーヤーの専門知識は、次に示すように、プレーヤーのスコアによって決まります。

新しく更新された quiz_score テーブルは次のとおりです。

新しい列をテーブルに追加するための ALTER TABLE ステートメントは次のとおりです。

ALTER TABLE quiz_score ADD COLUMN expertise text;

そして、これが専門分野に入力するための UPDATE ステートメントです。

UPDATE quiz_score
SET expertise =
(CASE
    WHEN score >= 0
        AND score <= 50 THEN 'Beginner'
    WHEN score > 50
        AND score <= 80 THEN 'Intermediary'
    WHEN score > 80
        AND score <= 90 THEN 'Expert'
    WHEN score > 90 THEN 'Master'
END);

前の例では、テーブル内の個別のスコアを確認しました。この例では、次のクエリを実行して、テーブル内の専門知識ごとに異なるスコアがいくつあるかを調べます。

SELECT expertise, COUNT(DISTINCT score)
FROM quiz_score
GROUP BY expertise

結果は次のとおりです。

  expertise   | count
--------------+-------
 Beginner     |    51
 Intermediary |    10
 Expert       |    30
 Master       |    10

このクエリから、Beginner の専門知識は 51 点、Intermediary は 10 点、Expert は 30 点、そして Master は 10 点であることが分かります。このクエリには 14 秒と 515 ミリ秒を要しました。

この他のクエリを実行して、テーブル内の個別のスコアの数を見つけることもできます。

SELECT
    DISTINCT ON (expertise) expertise,
    COUNT(DISTINCT score)
FROM
    quiz_score
GROUP BY expertise

この新しいクエリには 12 秒と 165 ミリ秒かかりました。2 番目の例では、2 番目のクエリの方が高速でしたが、どちらのクエリも正常に機能します。

このチュートリアルでは、フィールドで個別の値を取得する方法と、別のフィールドの個別の値に基づいてフィールドで個別の値を取得する方法について説明しました。