集計、グループ化、要約
Prisma Clientでは、レコードのカウント、数値フィールドの集計、および個別のフィールド値の選択が可能です。
集計
Prisma Clientでは、モデルの数値フィールド (IntやFloatなど) に対してaggregateを実行できます。以下のクエリは、すべてのユーザーの平均年齢を返します。
const aggregations = await prisma.user.aggregate({
_avg: {
age: true,
},
})
console.log('Average age:' + aggregations._avg.age)
集計をフィルタリングや順序付けと組み合わせることができます。例えば、以下のクエリはユーザーの平均年齢を返します。
ageで昇順にソートemailにprisma.ioを含むもの- ユーザー10人に限定
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)
集計値はnull可能
2.21.0以降では、null許容フィールドに対する集計はnumberまたはnullを返すことができます。これは、レコードが見つからない場合に常に0を返すcountを除くものです。
以下のクエリを考えてみましょう。ここでは、スキーマでageがnull許容です。
const aggregations = await prisma.user.aggregate({
_avg: {
age: true,
},
_count: {
age: true,
},
})
{
_avg: {
age: null
},
_count: {
age: 9
}
}
以下のいずれかのシナリオで、クエリは{ _avg: { age: null } }を返します。
- ユーザーがいない場合
- すべてのユーザーの
ageフィールドの値がnullの場合
これにより、真の集計値（ゼロになる可能性もある）とデータがない場合を区別できます。
グループ化
Prisma ClientのgroupBy()を使用すると、country、またはcountryとcityなどの1つ以上のフィールド値でレコードをグループ化し、各グループで集計を実行して、特定の都市に住む人々の平均年齢を見つけることができます。groupBy()は2.20.0以降でGA (一般利用可能) になりました。
以下のビデオでは、groupBy()を使用して大陸ごとのCOVID-19症例総数を要約しています。
以下の例では、すべてのユーザーをcountryフィールドでグループ化し、各国のプロファイルビューの合計数を返します。
const groupUsers = await prisma.user.groupBy({
by: ['country'],
_sum: {
profileViews: true,
},
})
byオプションに単一の要素がある場合、以下の省略構文を使用してクエリを表現できます。
const groupUsers = await prisma.user.groupBy({
by: 'country',
})
groupBy()とフィルタリング
groupBy()は、whereとhavingの2つのレベルのフィルタリングをサポートしています。
whereでレコードをフィルタリング
whereを使用して、グループ化する前にすべてのレコードをフィルタリングします。以下の例では、ユーザーを国別にグループ化し、プロファイルビューを合計しますが、電子メールアドレスにprisma.ioを含むユーザーのみを含めます。
const groupUsers = await prisma.user.groupBy({
by: ['country'],
where: {
email: {
contains: 'prisma.io',
},
},
_sum: {
profileViews: true,
},
})
havingでグループをフィルタリング
havingを使用して、個々のレコードではなく、フィールドの合計や平均などの集計値によってグループ全体をフィルタリングします。たとえば、平均profileViewsが100を超えるグループのみを返します。
const groupUsers = await prisma.user.groupBy({
by: ['country'],
where: {
email: {
contains: 'prisma.io',
},
},
_sum: {
profileViews: true,
},
having: {
profileViews: {
_avg: {
gt: 100,
},
},
},
})
havingのユースケース
havingの主なユースケースは、集計のフィルタリングです。データセットのサイズをグループ化する前に可能な限り削減するためにwhereを使用することをお勧めします。そうすることで、✔ データベースが返すレコードの数が減り、✔ インデックスが使用されます。
たとえば、以下のクエリは、スウェーデンまたはガーナ出身ではないすべてのユーザーをグループ化します。
const fd = await prisma.user.groupBy({
by: ['country'],
where: {
country: {
notIn: ['Sweden', 'Ghana'],
},
},
_sum: {
profileViews: true,
},
having: {
profileViews: {
_min: {
gte: 10,
},
},
},
})
以下のクエリは技術的には同じ結果を達成しますが、ガーナ出身のユーザーをグループ化後に除外します。これは何の利点もなく、推奨される方法ではありません。
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で利用可能なフィールドのみをフィルタリングできます。
groupBy()と順序付け
groupBy()とorderByを組み合わせる場合、以下の制約が適用されます。
byに存在するフィールドでorderByできます- 集計で
orderByできます (2.21.0以降でプレビュー機能) groupBy()でskipやtakeを使用する場合は、クエリにorderByも含む必要があります。
集計グループによる並べ替え
集計グループで並べ替えができます。Prisma ORMは、リレーショナルデータベースで集計グループとorderByを使用するサポートをバージョン2.21.0で追加し、MongoDBのサポートを3.4.0で追加しました。
以下の例では、各cityグループをそのグループ内のユーザー数でソートします (大きいグループが最初)。
const groupBy = await prisma.user.groupBy({
by: ['city'],
_count: {
city: true,
},
orderBy: {
_count: {
city: 'desc',
},
},
})
フィールドによる並べ替え
以下のクエリは、国別にグループを並べ、最初の2つのグループをスキップし、3番目と4番目のグループを返します。
const groupBy = await prisma.user.groupBy({
by: ['country'],
_sum: {
profileViews: true,
},
orderBy: {
country: 'desc',
},
skip: 2,
take: 2,
})
groupBy() FAQ
groupBy()でselectを使用できますか？
groupBy()でselectを使用することはできません。ただし、byに含まれるすべてのフィールドは自動的に返されます。
groupBy()でwhereとhavingを使用する際の違いは何ですか？
whereはグループ化する前にすべてのレコードをフィルタリングし、havingはグループ全体をフィルタリングし、そのグループ内の特定のフィールドの平均や合計など、集計フィールド値でのフィルタリングをサポートします。
groupBy()とdistinctの違いは何ですか？
distinctとgroupBy()の両方とも、1つ以上のユニークなフィールド値でレコードをグループ化します。groupBy()は、デンマークからの投稿の平均ビュー数などを返すように、各グループ内でデータを集計できますが、distinctはできません。
カウント
レコードのカウント
count()を使用して、レコード数または非nullフィールド値の数をカウントします。以下の例のクエリは、すべてのユーザーをカウントします。
const userCount = await prisma.user.count()
リレーションのカウント
リレーションのカウント（例：ユーザーの投稿数）を返すには、以下の例に示すように、ネストされたselectと_countパラメータを使用します。
const usersWithCount = await prisma.user.findMany({
include: {
_count: {
select: { posts: true },
},
},
})
_countパラメータ
- トップレベルの
includeまたはselect内で使用できます。 - レコードを返すすべてのクエリ（
delete、update、findFirstを含む）で使用できます。 - 複数のリレーションカウントを返せます。
- リレーションカウントをフィルタリングできます（バージョン4.3.0から）。
includeでリレーションカウントを返す
以下のクエリは、各ユーザーの投稿数を結果に含めます。
const usersWithCount = await prisma.user.findMany({
include: {
_count: {
select: { posts: true },
},
},
})
selectでリレーションカウントを返す
以下のクエリは、selectを使用して各ユーザーの投稿数を他のフィールドなしで返します。
const usersWithCount = await prisma.user.findMany({
select: {
_count: {
select: { posts: true },
},
},
})
複数のリレーションカウントを返す
以下のクエリは、各ユーザーのpostsとrecipesのカウントを、他のフィールドなしで返します。
const usersWithCount = await prisma.user.findMany({
select: {
_count: {
select: {
posts: true,
recipes: true,
},
},
},
})
リレーションカウントをフィルタリング
whereを使用して、_count出力タイプによって返されるフィールドをフィルタリングします。スカラフィールド、リレーションフィールド、および複合型のフィールドに対してこれを行うことができます。
例えば、以下のクエリは、タイトルが「Hello!」のすべてのユーザー投稿を返します。
// Count all user posts with the title "Hello!"
await prisma.user.findMany({
select: {
_count: {
select: {
posts: { where: { title: 'Hello!' } },
},
},
},
})
以下のクエリは、作者が「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フィールド値のカウント
2.15.0以降では、すべてのレコードと、非nullフィールド値のすべてのインスタンスをカウントできます。以下のクエリは、以下の数を返します。
- すべての
Userレコード (_all) - すべての非
nullのname値 (個別の値ではなく、nullではない値)
const userCount = await prisma.user.count({
select: {
_all: true, // Count all records
name: true, // Count all non-null field values
},
})
フィルタリングされたカウント
countはフィルタリングをサポートしています。以下の例のクエリは、100を超えるプロファイルビューを持つすべてのユーザーをカウントします。
const userCount = await prisma.user.count({
where: {
profileViews: {
gte: 100,
},
},
})
以下の例のクエリは、特定のユーザーの投稿数をカウントします。
const postCount = await prisma.post.count({
where: {
authorId: 29,
},
})
一意の選択
Prisma Clientでは、distinctを使用することで、Prisma Query応答のfindManyクエリから重複する行をフィルタリングできます。distinctは、テーブルの行内の特定の一意の値の組み合わせを識別するために、selectと組み合わせてよく使用されます。
以下の例は、一意のnameフィールド値を持つすべてのUserレコードのすべてのフィールドを返します。
const result = await prisma.user.findMany({
where: {},
distinct: ['name'],
})
以下の例は、一意のroleフィールド値 (例: ADMINおよびUSER) を返します。
const distinctRoles = await prisma.user.findMany({
distinct: ['role'],
select: {
role: true,
},
})
distinctの内部構造
Prisma Clientのdistinctオプションは、SQLのSELECT DISTINCTを使用しません。代わりに、distinctは以下を使用します。
SELECTクエリ- 一意なものを選択するためのインメモリ後処理
これは、distinctクエリの一部としてselectとincludeをサポートするために、このように設計されました。
以下の例は、gameIdとplayerIdで一意なものを選び、scoreで順序付けすることで、各プレイヤーのゲームごとの最高スコアを返します。このクエリは、追加データを含めるためにincludeとselectを使用しています。
score（Playのフィールド）を選択- 関連するプレイヤー名（
PlayとUser間のリレーション）を選択 - 関連するゲーム名（
PlayとGame間のリレーション）を選択
サンプルスキーマを展開
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,
},
},
},
})
selectとdistinctがない場合、クエリは以下を返します。
[
{
gameId: 2,
playerId: 5
},
{
gameId: 2,
playerId: 10
}
]