迹忆客 专注技术分享

获取字段中的不同值是一个重要的查询。本篇文章讨论如何获取字段中值的不同计数。

获取 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;

我们已将一千万个随机分数插入到我们的表中。让我们通过运行以下查询来找出表中有多少不同的分数：

SELECT COUNT(DISTINCT score) FROM quiz_score;

此查询耗时 3 秒 391 毫秒，计数为 101 个不同的值。

我们还可以运行这个其他查询来查找表中不同分数的数量：

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

这个新查询花费了 1 秒和 572 毫秒，计数了 101 个不同的值。

如我们所见，第二个查询更快。任何一个查询都可以正常工作，但在这种情况下，第二个查询更快。

根据 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 毫秒。在第二个示例中，第二个查询更快，但任何一个查询都可以正常工作。

在本篇文章中，我们讨论了如何在一个字段中获取不同的值，以及如何根据另一个字段的不同值在一个字段中获取不同的值。