JIYIK CN >

Getting the distinct values ​​in a field is an important query. This article discusses how to get the distinct count of values ​​in a field.

Get distinct count of field values ​​in PostgreSQL

Consider a quiz_scoretable that records the score of each participant in a quiz game.

Here is the table CREATEstatement:

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)
);

Here is INSERTthe statement that populates the table with data:

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

We have inserted 10 million random scores into our table. Let's find out how many different scores there are in the table by running the following query:

SELECT COUNT(DISTINCT score) FROM quiz_score;

This query took 3 seconds 391 milliseconds and counted 101 distinct values.

We can also run this other query to find the number of distinct scores in the table:

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

This new query took 1 second and 572 milliseconds and counted 101 distinct values.

As we can see, the second query is faster. Either query would work fine, but in this case, the second query is faster.

Get distinct count of field values ​​based on another field in PostgreSQL

We will now introduce a Expertisenew column called . This field is populated based on the player's score.

ExpertiseThe values ​​are: Beginner, Intermediary, Expertand Master. A player's expertise is determined by the player's score as follows:

The newly updated quiz_scoretables are:

This is the statement used to add a new column to a table ALTER TABLE:

ALTER TABLE quiz_score ADD COLUMN expertise text;

Here is the statement that populates the expertise field 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);

In the previous example, we looked at the different scores in the table. In this example, let's find out how many different scores there are for each expertise in the table by running the following query:

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

The results are as follows:

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

From this query, we can see Beginnerthat major has 51 different scores, Intermediaryhas 10 different scores, Experthas 30 different scores, and Masterhas 10 different scores. The query took 14 seconds and 515 milliseconds.

We can also run this other query to find the number of distinct scores in the table:

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

This new query took 12 seconds and 165 milliseconds. In the second example, the second query is faster, but either query will work fine.

In this article, we discussed how to get distinct values ​​in a field and how to get distinct values ​​in a field based on distinct values ​​of another field.