集計集約操作におけるROLLUP,GROUPING SETS,CUBEをTypescriptで実現するやり方

nap5

2023年10月20日 20:15

はじめに

こんにちわ。nap5です。

集計集約操作におけるROLLUP,GROUPING SETS,CUBEをTypescriptで実現するやり方の紹介です。

SQL操作でよく出てくるGroupBYの亜種になります。

サンプルデータ

このデータをもとにTidyjsで集計集約操作をしていきます。

  type Sale = {
    city: string;
    product: string;
    amount: number;
  };

  const sales: Sale[] = [
    { city: "Tokyo", product: "Apple", amount: 10 },
    { city: "Tokyo", product: "Banana", amount: 20 },
    { city: "Osaka", product: "Apple", amount: 15 },
    { city: "Osaka", product: "Banana", amount: 5 },
  ];

Group by rollup

期待される結果の集計は次のようになります。明細単位の集約（semiTotal）は一件にするため、Sumしています。集約できれば、Maxでも、Minでもいいかとは思います。

全都市、全商品の合計：50
東京の合計：30
大阪の合計：20
東京のリンゴの合計：10
東京のバナナの合計：20
大阪のリンゴの合計：15
大阪のバナナの合計：5

import { describe, test, expect } from "vitest";
import { bind, bindTo, map } from "fp-ts/lib/Identity";
import { pipe } from "fp-ts/lib/function";
import { groupBy, summarize, tidy, sum } from "@tidyjs/tidy";

describe("グルーピング", () => {
  type Sale = {
    city: string;
    product: string;
    amount: number;
  };

  const sales: Sale[] = [
    { city: "Tokyo", product: "Apple", amount: 10 },
    { city: "Tokyo", product: "Banana", amount: 20 },
    { city: "Osaka", product: "Apple", amount: 15 },
    { city: "Osaka", product: "Banana", amount: 5 },
  ];

  // @see https://docs.snowflake.com/ja/sql-reference/constructs/group-by-rollup
  test("GROUP BY ROLLUP", () => {
    const outputData = pipe(
      sales,
      bindTo("input"),
      bind("semiTotal", ({ input }) =>
        tidy(
          input,
          groupBy(
            ["city", "product"],
            [
              summarize({
                amount: sum("amount"),
              }),
            ]
          )
        )
      ),
      bind("subTotal", ({ input }) =>
        tidy(
          input,
          groupBy(
            ["city"],
            [
              summarize({
                product: (d) => "All Products",
                amount: sum("amount"),
              }),
            ]
          )
        )
      ),
      bind("total", ({ input }) =>
        tidy(
          input,
          summarize({
            city: (d) => "All Cities",
            product: (d) => "All Products",
            amount: sum("amount"),
          })
        )
      ),
      bind("output", ({ total, subTotal, semiTotal }) => ({
        summary: total.concat(subTotal).concat(semiTotal),
        detail: {
          total,
          subTotal,
          semiTotal,
        },
      })),
      map(({ output }) => output.summary)
    );
    expect(outputData).toStrictEqual([
      {
        city: "All Cities",
        product: "All Products",
        amount: 50,
      },
      {
        city: "Tokyo",
        product: "All Products",
        amount: 30,
      },
      {
        city: "Osaka",
        product: "All Products",
        amount: 20,
      },
      {
        city: "Tokyo",
        product: "Apple",
        amount: 10,
      },
      {
        city: "Tokyo",
        product: "Banana",
        amount: 20,
      },
      {
        city: "Osaka",
        product: "Apple",
        amount: 15,
      },
      {
        city: "Osaka",
        product: "Banana",
        amount: 5,
      },
    ]);
  });
});

Group by grouping sets

東京の全商品の合計：30
大阪の全商品の合計：20
全都市のリンゴの合計：25
全都市のバナナの合計：25
東京のリンゴの合計：10
東京のバナナの合計：20
大阪のリンゴの合計：15
大阪のバナナの合計：5

import { describe, test, expect } from "vitest";
import { bind, bindTo, map } from "fp-ts/lib/Identity";
import { pipe } from "fp-ts/lib/function";
import { groupBy, summarize, tidy, sum } from "@tidyjs/tidy";

describe("グルーピング", () => {
  type Sale = {
    city: string;
    product: string;
    amount: number;
  };

  const sales: Sale[] = [
    { city: "Tokyo", product: "Apple", amount: 10 },
    { city: "Tokyo", product: "Banana", amount: 20 },
    { city: "Osaka", product: "Apple", amount: 15 },
    { city: "Osaka", product: "Banana", amount: 5 },
  ];

  // @see https://docs.snowflake.com/ja/sql-reference/constructs/group-by-grouping-sets
  test("GROUP BY GROUPING SETS", () => {
    const outputData = pipe(
      sales,
      bindTo("input"),
      bind("semiTotal", ({ input }) =>
        tidy(
          input,
          groupBy(
            ["city", "product"],
            [
              summarize({
                amount: sum("amount"),
              }),
            ]
          )
        )
      ),
      bind("subTotal1", ({ input }) =>
        tidy(
          input,
          groupBy(
            ["city"],
            [
              summarize({
                product: (d) => "All Products",
                amount: sum("amount"),
              }),
            ]
          )
        )
      ),
      bind("subTotal2", ({ input }) =>
        tidy(
          input,
          groupBy(
            ["product"],
            [
              summarize({
                city: (d) => "All Cities",
                amount: sum("amount"),
              }),
            ]
          )
        )
      ),
      bind("output", ({ subTotal1, subTotal2, semiTotal }) => ({
        summary: subTotal1.concat(subTotal2).concat(semiTotal),
        detail: {
          subTotal1,
          subTotal2,
          semiTotal,
        },
      })),
      map(({ output }) => output.summary)
    );
    expect(outputData).toStrictEqual([
      {
        city: "Tokyo",
        product: "All Products",
        amount: 30,
      },
      {
        city: "Osaka",
        product: "All Products",
        amount: 20,
      },
      {
        product: "Apple",
        city: "All Cities",
        amount: 25,
      },
      {
        product: "Banana",
        city: "All Cities",
        amount: 25,
      },
      {
        city: "Tokyo",
        product: "Apple",
        amount: 10,
      },
      {
        city: "Tokyo",
        product: "Banana",
        amount: 20,
      },
      {
        city: "Osaka",
        product: "Apple",
        amount: 15,
      },
      {
        city: "Osaka",
        product: "Banana",
        amount: 5,
      },
    ]);
  });
});

Group by cube

全都市、全商品の合計：50
東京の全商品の合計：30
大阪の全商品の合計：20
全都市のリンゴの合計：25
全都市のバナナの合計：25
東京のリンゴの合計：10
東京のバナナの合計：20
大阪のリンゴの合計：15
大阪のバナナの合計：5

おわりに

Tidyjs便利です。簡単ですが、以上です。

この記事が気に入ったらサポートをしてみませんか？