聚合、分组和总结

Prisma 客户端允许你对记录进行计数、聚合数字字段并选择不同的字段值。

¥Prisma Client allows you to count records, aggregate number fields, and select distinct field values.

总计的

¥Aggregate

Prisma 客户端允许你对模型的数字字段（例如 Int 和 Float）进行 aggregate。以下查询返回所有用户的平均年龄：

¥Prisma Client allows you to aggregate on the number fields (such as Int and Float) of a model. The following query returns the average age of all users:

const aggregations = await prisma.user.aggregate({
  _avg: {
    age: true,
  },
})

console.log('Average age:' + aggregations._avg.age)

你可以将聚合与过滤和排序结合起来。例如，以下查询返回用户的平均年龄：

¥You can combine aggregation with filtering and ordering. For example, the following query returns the average age of users:

• 按 age 升序排列

  ¥Ordered by age ascending

• 其中 email 包含 prisma.io

  ¥Where email contains prisma.io

• 仅限 10 名用户

  ¥Limited to the 10 users

const aggregations = await prisma.user.aggregate({
  _avg: {
    age: true,
  },
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
  orderBy: {
    age: 'asc',
  },
  take: 10,
})

console.log('Average age:' + aggregations._avg.age)

聚合值可以为空

¥Aggregate values are nullable

在 2.21.0 及更高版本中，可空字段的聚合可以返回 number 或 null。这不包括 count，如果没有找到记录，它总是返回 0。

¥In 2.21.0 and later, aggregations on nullable fields can return a number or null. This excludes count, which always returns 0 if no records are found.

考虑以下查询，其中 age 在架构中可为空：

¥Consider the following query, where age is nullable in the schema:

const aggregations = await prisma.user.aggregate({
  _avg: {
    age: true,
  },
  _count: {
    age: true,
  },
})

Show CLI results

{
  _avg: {
    age: null
  },
  _count: {
    age: 9
  }
}

在以下情况下，查询返回 { _avg: { age: null } }：

¥The query returns { _avg: { age: null } } in either of the following scenarios:

• 没有用户

  ¥There are no users

• 每个用户的 age 字段的值为 null

  ¥The value of every user's age field is null

这使你可以区分真实聚合值（可能为零）和无数据。

¥This allows you to differentiate between the true aggregate value (which could be zero) and no data.

通过...分组

¥Group by

Prisma Client 的 groupBy() 允许你按一个或多个字段值对记录进行分组 - 例如 country、或 country 和 city，并对每个组执行聚合，例如查找居住在特定城市的人们的平均年龄。groupBy() 是 2.20.0 及之后的 GA。

¥Prisma Client's groupBy() allows you to group records by one or more field values - such as country, or country and city and perform aggregations on each group, such as finding the average age of people living in a particular city. groupBy() is a GA in 2.20.0 and later.

以下视频使用 groupBy() 总结了各洲的 COVID-19 病例总数：

¥The following video uses groupBy() to summarize total COVID-19 cases by continent:

以下示例按 country 字段对所有用户进行分组，并返回每个国家/地区的资料查看总数：

¥The following example groups all users by the country field and returns the total number of profile views for each country:

const groupUsers = await prisma.user.groupBy({
  by: ['country'],
  _sum: {
    profileViews: true,
  },
})

Show CLI results

如果 by 选项中有单个元素，则可以使用以下简写语法来表达你的查询：

¥If you have a single element in the by option, you can use the following shorthand syntax to express your query:

const groupUsers = await prisma.user.groupBy({
  by: 'country',
})

groupBy() 和过滤

¥groupBy() and filtering

groupBy() 支持两级过滤：where 和 having。

¥groupBy() supports two levels of filtering: where and having.

使用 where 过滤记录

¥Filter records with where

分组前使用 where 过滤所有记录。以下示例按国家/地区和汇总资料视图对用户进行分组，但仅包括电子邮件地址包含 prisma.io 的用户：

¥Use where to filter all records before grouping. The following example groups users by country and sums profile views, but only includes users where the email address contains prisma.io:

const groupUsers = await prisma.user.groupBy({
  by: ['country'],
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
  _sum: {
    profileViews: true,
  },
})

使用 having 过滤组

¥Filter groups with having

使用 having 按聚合值（例如字段的总和或平均值）过滤整个组，而不是单个记录 - 例如，仅返回平均值 profileViews 大于 100 的组：

¥Use having to filter entire groups by an aggregate value such as the sum or average of a field, not individual records - for example, only return groups where the average profileViews is greater than 100:

const groupUsers = await prisma.user.groupBy({
  by: ['country'],
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
  _sum: {
    profileViews: true,
  },
  having: {
    profileViews: {
      _avg: {
        gt: 100,
      },
    },
  },
})

having 的用例

¥Use case for having

having 的主要用例是过滤聚合。我们建议你在分组之前使用 where 尽可能减小数据集的大小，因为这样做✔减少数据库必须返回的记录数并且✔利用索引。

¥The primary use case for having is to filter on aggregations. We recommend that you use where to reduce the size of your data set as far as possible before grouping, because doing so ✔ reduces the number of records the database has to return and ✔ makes use of indices.

例如，以下查询对非瑞典或加纳的所有用户进行分组：

¥For example, the following query groups all users that are not from Sweden or Ghana:

const fd = await prisma.user.groupBy({
  by: ['country'],
  where: {
    country: {
      notIn: ['Sweden', 'Ghana'],
    },
  },
  _sum: {
    profileViews: true,
  },
  having: {
    profileViews: {
      _min: {
        gte: 10,
      },
    },
  },
})

以下查询在技术上实现了相同的结果，但在分组后排除了来自加纳的用户。这不会带来任何好处，也不推荐这样做。

¥The following query technically achieves the same result, but excludes users from Ghana after grouping. This does not confer any benefit and is not recommended practice.

const groupUsers = await prisma.user.groupBy({
  by: ['country'],
  where: {
    country: {
      not: 'Sweden',
    },
  },
  _sum: {
    profileViews: true,
  },
  having: {
    country: {
      not: 'Ghana',
    },
    profileViews: {
      _min: {
        gte: 10,
      },
    },
  },
})

注意：在 having 中，你只能筛选 by 中可用的聚合值或字段。

¥Note: Within having, you can only filter on aggregate values or fields available in by.

groupBy() 和排序

¥groupBy() and ordering

当你组合 groupBy() 和 orderBy 时，以下约束适用：

¥The following constraints apply when you combine groupBy() and orderBy:

• 你可以 by 中存在的 orderBy 字段

  ¥You can orderBy fields that are present in by

• 你可以 orderBy 聚合（2.21.0 及更高版本中预览）

  ¥You can orderBy aggregate (Preview in 2.21.0 and later)

• 如果将 skip 和/或 take 与 groupBy() 一起使用，则还必须在查询中包含 orderBy

  ¥If you use skip and/or take with groupBy(), you must also include orderBy in the query

按聚合组排序

¥Order by aggregate group

你可以按聚合组排序。Prisma ORM 在 2.21.0 版本中添加了对使用 orderBy 与关系数据库中的聚合组的支持，并在 3.4.0 中添加了对 MongoDB 的支持。

¥You can order by aggregate group. Prisma ORM added support for using orderBy with aggregated groups in relational databases in version 2.21.0 and support for MongoDB in 3.4.0.

以下示例按每个 city 组中的用户数量对该组进行排序（最大的组在前）：

¥The following example sorts each city group by the number of users in that group (largest group first):

const groupBy = await prisma.user.groupBy({
  by: ['city'],
  _count: {
    city: true,
  },
  orderBy: {
    _count: {
      city: 'desc',
    },
  },
})

Show CLI results

按字段排序

¥Order by field

以下查询按国家/地区对组进行排序，跳过前两组，并返回第三组和第四组：

¥The following query orders groups by country, skips the first two groups, and returns the 3rd and 4th group:

const groupBy = await prisma.user.groupBy({
  by: ['country'],
  _sum: {
    profileViews: true,
  },
  orderBy: {
    country: 'desc',
  },
  skip: 2,
  take: 2,
})

groupBy() FAQ

我可以将 select 与 groupBy() 一起使用吗？

¥Can I use select with groupBy()?

你不能将 select 与 groupBy() 一起使用。但是，by 中包含的所有字段都会自动返回。

¥You cannot use select with groupBy(). However, all fields included in by are automatically returned.

使用 where 和 having 与 groupBy() 有什么区别？

¥What is the difference between using where and having with groupBy()?

where 在分组之前过滤所有记录，having 过滤整个组并支持对聚合字段值进行过滤，例如该组中特定字段的平均值或总和。

¥where filters all records before grouping, and having filters entire groups and supports filtering on an aggregate field value, such as the average or sum of a particular field in that group.

groupBy() 和 distinct 有什么区别？

¥What is the difference between groupBy() and distinct?

distinct 和 groupBy() 均按一个或多个唯一字段值对记录进行分组。groupBy() 允许你聚合每个组内的数据 - 例如，返回丹麦帖子的平均浏览量 - 而 distinct 则不然。

¥Both distinct and groupBy() group records by one or more unique field values. groupBy() allows you to aggregate data within each group - for example, return the average number of views on posts from Denmark - whereas distinct does not.

数数

¥Count

计数记录

¥Count records

使用 count() 来统计记录或非 null 字段值的数量。以下示例查询对所有用户进行计数：

¥Use count() to count the number of records or non-null field values. The following example query counts all users:

const userCount = await prisma.user.count()

计数关系

¥Count relations

info

此功能通常在 3.0.1 及更高版本中提供。要在 3.0.1 之前的版本中使用此功能，需要启用 预览功能 selectRelationCount。

¥This feature is generally available in version 3.0.1 and later. To use this feature in versions before 3.0.1 the Preview feature selectRelationCount will need to be enabled.

要返回关系计数（例如，用户的帖子计数），请使用带有嵌套 select 的 _count 参数，如下所示：

¥To return a count of relations (for example, a user's post count), use the _count parameter with a nested select as shown:

const usersWithCount = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
})

Show CLI results

_count 参数：

¥The _count parameter:

• 可在顶层 include 或 select 内部使用

  ¥Can be used inside a top-level include or select

• 可与任何返回记录的查询一起使用（包括 delete、update 和 findFirst）

  ¥Can be used with any query that returns records (including delete, update, and findFirst)

• 可以返回 多重关系计数

  ¥Can return multiple relation counts

• 可以 过滤关系计数（从版本 4.3.0 开始）

  ¥Can filter relation counts (from version 4.3.0)

返回与 include 的关系计数

¥Return a relations count with include

以下查询在结果中包含每个用户的帖子计数：

¥The following query includes each user's post count in the results:

const usersWithCount = await prisma.user.findMany({
  include: {
    _count: {
      select: { posts: true },
    },
  },
})

Show CLI results

返回与 select 的关系计数

¥Return a relations count with select

以下查询使用 select 返回每个用户的帖子计数，不返回其他字段：

¥The following query uses select to return each user's post count and no other fields:

const usersWithCount = await prisma.user.findMany({
  select: {
    _count: {
      select: { posts: true },
    },
  },
})

Show CLI results

返回多个关系计数

¥Return multiple relation counts

以下查询返回每个用户的 posts 和 recipes 的计数，不返回其他字段：

¥The following query returns a count of each user's posts and recipes and no other fields:

const usersWithCount = await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: true,
        recipes: true,
      },
    },
  },
})

Show CLI results

过滤关系计数

¥Filter the relation count

info

此功能通常在 4.16.0 及更高版本中提供。要在版本 4.3.0 至 4.15.0 中使用此功能，需要启用 预览功能 filteredRelationCount。

¥This feature is generally available in version 4.16.0 and later. To use this feature in versions 4.3.0 to 4.15.0 the Preview feature filteredRelationCount will need to be enabled.

使用 where 过滤 _count 输出类型返回的字段。你可以在 标量场、关系字段 和 复合型 的字段上执行此操作。

¥Use where to filter the fields returned by the _count output type. You can do this on scalar fields, relation fields and fields of a composite type.

例如，以下查询返回标题为 "你好！" 的所有用户帖子：

¥For example, the following query returns all user posts with the title "Hello!":

// Count all user posts with the title "Hello!"
await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: { where: { title: 'Hello!' } },
      },
    },
  },
})

以下查询查找包含来自名为 "爱丽丝" 的作者的评论的所有用户帖子：

¥The following query finds all user posts with comments from an author named "Alice":

// Count all user posts that have comments
// whose author is named "Alice"
await prisma.user.findMany({
  select: {
    _count: {
      select: {
        posts: {
          where: { comments: { some: { author: { is: { name: 'Alice' } } } } },
        },
      },
    },
  },
})

对非 null 字段值进行计数

¥Count non-null field values

在 2.15.0 及更高版本中，你可以对所有记录以及非 null 字段值的所有实例进行计数。以下查询返回计数：

¥In 2.15.0 and later, you can count all records as well as all instances of non-null field values. The following query returns a count of:

• 所有 User 记录 (_all)

  ¥All User records (_all)

• 所有非 null name 值（不是不同的值，只是不是 null 的值）

  ¥All non-null name values (not distinct values, just values that are not null)

const userCount = await prisma.user.count({
  select: {
    _all: true, // Count all records
    name: true, // Count all non-null field values
  },
})

Show CLI results

过滤计数

¥Filtered count

count 支持过滤。以下示例查询对个人资料视图超过 100 次的所有用户进行计数：

¥count supports filtering. The following example query counts all users with more than 100 profile views:

const userCount = await prisma.user.count({
  where: {
    profileViews: {
      gte: 100,
    },
  },
})

以下示例查询对特定用户的帖子进行计数：

¥The following example query counts a particular user's posts:

const postCount = await prisma.post.count({
  where: {
    authorId: 29,
  },
})

选择不同的

¥Select distinct

Prisma 客户端允许你使用 distinct 从 Prisma 查询响应中过滤重复行到 findMany 查询。 distinct 通常与 select 结合使用，以标识表行中某些唯一的值组合。

¥Prisma Client allows you to filter duplicate rows from a Prisma Query response to a findMany query using distinct . distinct is often used in combination with select to identify certain unique combinations of values in the rows of your table.

以下示例返回具有不同 name 字段值的所有 User 记录的所有字段：

¥The following example returns all fields for all User records with distinct name field values:

const result = await prisma.user.findMany({
  where: {},
  distinct: ['name'],
})

以下示例返回不同的 role 字段值（例如 ADMIN 和 USER）：

¥The following example returns distinct role field values (for example, ADMIN and USER):

const distinctRoles = await prisma.user.findMany({
  distinct: ['role'],
  select: {
    role: true,
  },
})

Show CLI results

distinct 的引擎盖下

¥distinct under the hood

Prisma 客户端的 distinct 选项不使用 SQL SELECT DISTINCT。相反，distinct 使用：

¥Prisma Client's distinct option does not use SQL SELECT DISTINCT. Instead, distinct uses:

• SELECT 查询

  ¥A SELECT query

• 内存中后处理以选择不同的

  ¥In-memory post-processing to select distinct

它以这种方式设计是为了支持 select 和 include 作为 distinct 查询的一部分。

¥It was designed in this way in order to support select and include as part of distinct queries.

以下示例在 gameId 和 playerId 上选择不同的值（按 score 排序），以便返回每个玩家每场比赛的最高分。该查询使用 include 和 select 来包含附加数据：

¥The following example selects distinct on gameId and playerId, ordered by score, in order to return each player's highest score per game. The query uses include and select to include additional data:

• 选择 score（Play 上的字段）

  ¥Select score (field on Play)

• 选择相关玩家名称（Play 和 User 之间的关系）

  ¥Select related player name (relation between Play and User)

• 选择相关游戏名称（Play 和 Game 之间的关系）

  ¥Select related game name (relation between Play and Game)

Expand for sample schema

model User {
  id   Int     @id @default(autoincrement())
  name String?
  play Play[]
}

model Game {
  id   Int     @id @default(autoincrement())
  name String?
  play Play[]
}

model Play {
  id       Int   @id @default(autoincrement())
  score    Int?  @default(0)
  playerId Int?
  player   User? @relation(fields: [playerId], references: [id])
  gameId   Int?
  game     Game? @relation(fields: [gameId], references: [id])
}

const distinctScores = await prisma.play.findMany({
  distinct: ['playerId', 'gameId'],
  orderBy: {
    score: 'desc',
  },
  select: {
    score: true,
    game: {
      select: {
        name: true,
      },
    },
    player: {
      select: {
        name: true,
      },
    },
  },
})

Show CLI results

如果没有 select 和 distinct，查询将返回：

¥Without select and distinct, the query would return:

[
  {
    gameId: 2,
    playerId: 5
  },
  {
    gameId: 2,
    playerId: 10
  }
]