Help us understand the problem. What is going on with this article?

AWS - Athenaで先週分がJSTで取れるビューを作る

More than 1 year has passed since last update.

良いやり方を教えていただきました


week_of_year で取りたいならもう少し楽だけど、昨日からの直近7日間が欲しい
たとえば日本時間の8月4日にクエリしたら (2018-07-28 < 2018-08-03) の範囲が欲しい

  • テーブルのパーティションが (year, month, day) で切られているのでそれを使いたい
  • ミリ秒単位のタイムスタンプが入っているカラム(timestamp)がある
CREATE VIEW v AS
SELECT
    t.*,
    from_unixtime(t.timestamp / 1000) utc_timestamp
FROM t
WHERE
    (
        (
            year = year(current_date - interval '1' day) AND
            month = month(current_date - interval '1' day) AND
            day = day(current_date - interval '1' day)
        ) OR (
            year = year(current_date - interval '2' day) AND
            month = month(current_date - interval '2' day) AND
            day = day(current_date - interval '2' day)
        ) OR (
            year = year(current_date - interval '3' day) AND
            month = month(current_date - interval '3' day) AND
            day = day(current_date - interval '3' day)
        ) OR (
            year = year(current_date - interval '4' day) AND
            month = month(current_date - interval '4' day) AND
            day = day(current_date - interval '4' day)
        ) OR (
            year = year(current_date - interval '5' day) AND
            month = month(current_date - interval '5' day) AND
            day = day(current_date - interval '5' day)
        ) OR (
            year = year(current_date - interval '6' day) AND
            month = month(current_date - interval '6' day) AND
            day = day(current_date - interval '6' day)
        ) OR (
            year = year(current_date - interval '7' day) AND
            month = month(current_date - interval '7' day) AND
            day = day(current_date - interval '7' day)
        ) OR (
            year = year(current_date - interval '8' day) AND
            month = month(current_date - interval '8' day) AND
            day = day(current_date - interval '8' day)
        )
    )
    AND date_trunc('day', current_timestamp AT TIME ZONE 'Asia/Tokyo')
        > from_unixtime(t.timestamp / 1000) AT TIME ZONE 'Asia/Tokyo'
    AND from_unixtime(t.timestamp / 1000) AT TIME ZONE 'Asia/Tokyo'
        >= date_trunc('day', current_timestamp AT TIME ZONE 'Asia/Tokyo' - interval '7' day)

もう少し簡潔に書けるような感じもするけど、パーティション使おうとすると難しい

テーブルのパーティションが (year, month, day) で切られているのでそれを使いたい

が、良いやり方あれば教えてください 🙏

ドキュメント

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away