トレジャーデータで1週間単位の集計

トレジャーデータのUDFである、TD_DATE_TRUNCの紹介記事です。この記事ではHiveではなく、Prestoを利用することを前提としています。

色々な集計業務を行う中で、週単位の集計を行う必要が出てくる場面は結構あります。トレジャーデータでは、そのような場面に備えて、タイムスタンプを週単位の値に変換してくれるUDFが容易されています。それが、冒頭に挙げたTD_DATE_TRUNCです。

ドキュメント: TD_DATE_TRUNC

使い方は簡単で、以下の構文で利用します。

TD_DATE_TRUNC(
    '集計したい時間の単位を示す文字列',
    元のタイムスタンプ,
    'タイムゾーン文字列'
)

集計したい時間の単位を示す文字列は次の単語が使えます。
minute/ hour/ day/ week/ month/ quarter/ year

ただし、後述しますが、日単位(day)や月単位(month)などで集計したい場合は、TD_TIME_FORMATを使った方が手軽に目当ての結果を得やすいので、実際に使う機会があるのは、週単位(week) かクオーター(quarter)に限られると思います。
(そのため、この記事のタイトルも1週間単位の集計としました)

さて、実際に使ってみましょう。例えば元の時刻が ‘2021-08-11 13:24:16’ だったとします。
(日本時間の場合)この時刻のタイムスタンプは、1628655856 です。そこで、これを週単位で切り詰めると次のようになります。

SELECT
    TD_DATE_TRUNC(
        'week',
        1628655856,
        'JST'
    )

上のクエリの結果は、 1628434800 です。このタイムスタンプがいつを表すかというと、’2021-08-09 00:00:00′ となり、元の時刻と同じ週の月曜日のちょうど0時となります。

この例で分かる通り、 TD_DATE_TRUNC はLONG型のタイムスタンプを受け取って、LONG型のタイムスタンプを返します。
実用上はタイムスタンプで結果を得てもいつのことなのかわかりにくいので、TD_TIME_FORMATで整形することになるでしょう。
time 列を週ごとにまとめて集計するであれば次のような書き方になると思います。

SELECT
    TD_TIME_FORMAT(
        TD_DATE_TRUNC(
            'week',
            time,
            'JST'
        ),
        'yyyy-MM-dd',
        'JST'
    )
FROM
    table_name

元々の時刻として、time列ではなく、’2021-08-11 13:24:16’のような文字列で時刻が入っている列を使う場合、タイムスタンプに変換する必要があるので、事前に、TD_TIME_PARSE する必要があります。例えば、 created_at という列を週単位に集計するのであれば次のようなクエリになります。

SELECT
    TD_TIME_FORMAT(
        TD_DATE_TRUNC(
            'week',
            TD_TIME_PARSE(created_at, 'JST'),
            'JST'
        ),
        'yyyy-MM-dd',
        'JST'
    )
FROM
    table_name

以上のようにして、週単位での集計ができるようになりました。
四半期単位での集計がしたい時は、 week を quarter に置き換えることで同様に実行できます。

さて、最初の方で少し述べましたが、 TD_DATE_TRUNC は day や month でも使うことができます。しかし、日単位や月単位で集計したい場合は、 TD_TIME_FORMAT だけで目的を果たすことができるので、TD_DATE_TRUNCはむしろ使わない方がクエリがスッキリすると思います。

例えば、次の二つのクエリ内の2列は結果が同じです。明らかに2列目の方が記述が少なくて楽ですね。

SELECT
    -- TD_DATE_TRUNCを使った月単位の集計
    TD_TIME_FORMAT(
        TD_DATE_TRUNC(
            'month',
            time,
            'JST'
        ),
        'yyyy-MM-dd',
        'JST'
    ),
    -- TD_TIME_FORMAT を使った月単位の集計
    TD_TIME_FORMAT(time, 'yyyy-MM-01', 'JST')
FROM
    table_name

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル