Prismaで集計処理をしてみよう！

これは1人アドベントカレンダーの2日目の記事です。
1日目は個人ブログに書いてます↓

早速ですが、下記のコードは、弊社のプロダクトHCCloudのコードの一部を説明用に改変したものです。

   const average = await this.prisma.metricValue.groupBy({
      by: ['metricId', 'year', 'statementType'],
      _avg: { value: true },
      _count: { value: true },
      where: {
        company: {
          jpxIndustryCode: industryCode,
        },
      },
    });
    return average;

これはどのような処理かと言いますと、
指標ID(metricId)、年度(year)、報告タイプ(statementType)をグループ化した上で、グループ内のvalueの平均値と、グループ内の要素の総数(=平均を出すための母数ですね)を取得しています。

スキーマ的にはこんな感じのテーブルに対する処理です。

MetricValue.prisma

model MetricValue {
  id            Int      @id @default(autoincrement())
  companyId     Int      @map("company_id")
  year          Int
  metricId      String   @map("metric_id")
  date          DateTime @db.Timestamptz(6)
  value         Float
  company       Company  @relation(fields: [companyId], references: [id], onDelete: Cascade)
  metric        Metric   @relation(fields: [metricId], references: [kind], onDelete: Cascade)
  statementType String   @default("UNCONSOLIDATED") @map("statement_type")

  @@unique([companyId, metricId, year, statementType])
  @@map("metric_value")
}

PrismaのgroupByとaggregate関数の組み合わせ

Prismaではこのような集計関数(というかSQLを作ってくれるだけ)が用意されています。

レスポンスはこのように返ってきます。
年数×指標数×報告タイプの数だけ要素が返却されます。

  {
    _avg: { value: 22.5 },
    _count: { value: 2 },
    metricId: 'number_of_occupational_accidents_occurred',
    year: 2023,
    statementType: 'CONSOLIDATED'
  },
  {
    _avg: { value: 69 },
    _count: { value: 1 },
    metricId: 'number_of_people_with_disabilities_employed',
    year: 2022,
    statementType: 'CONSOLIDATED'
  },
  {
    _avg: { value: 15.69169398907105 },
    _count: { value: 183 },
    metricId: 'price_earnings_ratio',
    year: 2021,
    statementType: 'CONSOLIDATED'
  },
  {
    _avg: { value: 6615876885.057471 },
    _count: { value: 261 },
    metricId: 'net_income',
    year: 2017,
    statementType: 'UNCONSOLIDATED'
  },
  ... 584 more items
]

あとはこれをテーブルにインサートするなりして自由に使えます！

HCCloudではテーブルに格納し、自社データと平均値を比較する機能に使われています。

膨大なデータを集計するので、ユーザーから実行するのではなく運営側で必要に応じて実行してます。

HCプロデュースでは2人目のエンジニアを絶賛募集中です！