Lambdaカクテル

京都在住Webエンジニアの日記です

Invite link for Scalaわいわいランド

ログの前月比を出すSQL

雑なタイトルですがやりたいことはこういうことです

  • 時刻timeとなんかいろいろが記録されたログテーブルlogがある
  • timeは秒単位で記録されている
  • 時間単位で丸めてCOUNT(1)して件数を出したい

ここまでだったら

SELECT
 date_trunc('hour', date_parse(time, '%d/%b/%Y:%H:%i:%s +0900')) as hour,
 COUNT(time) as access_num
FROM log
WHERE dt >= to_iso8601(current_date - interval '30' day)
GROUP BY
 date_trunc('hour', date_parse(time, '%d/%b/%Y:%H:%i:%s +0900'))
ORDER BY
 hour ASC
;

すればよいのだけれど,前月比,ここではより簡単にするために30日前との比,を出したいとする.

MySQL8.0以降ではWINDOW関数が使えるようになるので何かと便利なのだけれど,不幸にも使えないということがある.

素朴に2回引く

30日ずれてないバージョンと,30日ずれてるバージョンとを引いて,サブクエリで結合する

SELECT current.hour as hour, (current.access_num * 1.0) / (past.access_num * 1.0) as q
 FROM (
  SELECT date_trunc('hour', date_parse(time, '%d/%b/%Y:%H:%i:%s +0900')) as hour, COUNT(time) as access_num
  FROM log
     WHERE dt >= to_iso8601(current_date - interval '30' day)
     GROUP BY
      date_trunc('hour', date_parse(time, '%d/%b/%Y:%H:%i:%s +0900'))
 ) AS current LEFT JOIN (
  SELECT date_trunc('hour', date_parse(time, '%d/%b/%Y:%H:%i:%s +0900')) as hour, COUNT(time) as access_num
   FROM log
   WHERE dt >= to_iso8601(current_date - interval '60' day)
   GROUP BY
    date_trunc('hour', date_parse(time, '%d/%b/%Y:%H:%i:%s +0900'))
 ) AS past ON current.hour - interval '30' day = past.hour
ORDER BY
 hour ASC
 ;

もっとシンプルに書けそうだけど動いたのでこれで諦めた.よかったですね.

★記事をRTしてもらえると喜びます
Webアプリケーション開発関連の記事を投稿しています.読者になってみませんか?