ログ発生パターンをSTRING_AGG関数で集計【Sexy Tech for You #4】

データサイエンティストの阿部です。先日、会社の仲間たちとAmong Usをプレイして盛り上がりました。ある種の極限状況をロールプレイできるのが面白かったです。リモートワークにおける懇親コンテンツとして便利そうだと感じました。

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第4話を配信しました。動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube

BigQuery クエリ

BigQueryの一般公開データセットbigquery-public-data.baseball.games_wideでの適用例

SELECT
    pattern,
    COUNT(1) AS cnt
FROM(
SELECT 
    gameId,
    hitterId,
    inningHalfEventSequenceNumber,
    STRING_AGG(outcomeDescription, ' -> ' ORDER BY atBatEventSequenceNumber) AS pattern
FROM
    `bigquery-public-data.baseball.games_wide`
WHERE
    inningEventType = 'AT_BAT'
GROUP BY
    1,2,3
)
GROUP BY
    1
ORDER BY
    2 DESC

結果（先頭10行）

pattern	cnt
Ground Out	2790
Single	2050
Strike Looking -> Ground Out	1695
Fly Out	1617
Ball -> Ground Out	1592
Ball -> Single	1170
Strike Looking -> Single	1068
Ball -> Fly Out	985
Ball -> Ball -> Ball -> Ball	981
Line Out	846

補足

最初のGROUP BYでCOUNT(1)をとると、パターンを構成する要素数のフィールドも作成できます。例えばログ解析の場合、pv数で足切りしたユーザーを分析対象とするケースなど、条件を統制したい場合も頻出するので、併せて作成しておくと便利でしょう。

また、例えばユーザーが利用したユニークなコンテンツ数を集計したいケースでは、STRING_AGG(DISTINCT )のように、DISTINCTを適用することができるのも便利です。

ちなみにSTRING_AGG関数に相当する関数として、MySQLではGROUP_CONCAT関数があります。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。