Googleスプレッドシートでqueryを利用して、日付を検索条件として値を表示したい
これはなに？
Googleスプレッドシートでqueryを使うとSQLっぽいものが書けるようなので使っていたのですが、日付を検索条件とした時にハマったので解消するまでのメモを残します。
Googleスプレッドシートのqueryとは
Googleのドキュメント エディタ ヘルプには、Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。という記載があります。
使い方について
マニュアルに記載されている構文や使用例は以下のようになります。
構文 QUERY(データ, クエリ, [見出し])
使用例 QUERY(A2:E6,"select avg(A) pivot B") QUERY(A2:E6,F2,FALSE)
同一シートや別シートの利用方法については以下の内容が分かりやすかったです。
検索条件や結果の表示については以下の内容がとても参考になりました。
ハマったこと
例えば以下のような表がスプレッドシートのA列からC列にあるとします。
| 日付 | 食べたもの | 金額 |
|---|---|---|
| 2018/04/01 | ラーメン | 700 |
| 2018/04/02 | チャーハン | 800 |
| 2018/04/03 | ぶっかけうどん | 350 |
| 2018/04/04 | インドカレー | 800 |
ここの、日付と一致する内容を基に表示する以下のような式を書いたのですがうまくいきませんでした。
=query($A$2:C,"select B, C where A = '"&E:E&"'")
A2:Cの範囲にある内容から、 A列の日付と、E列の日付が一致したら表示しようとしましたが、クエリがからの出力で完了しました。というエラーが出たので検索がうまく行ってないようでした。
検索条件の部分を where A = '2018/04/01'"と記述してもダメでした。
解決
色々と検索していると以下のところで解決しました。
まず、日付の部分を以下のようにすると表示できるようになりました。画像はF2の部分のみ変更しています。
=query($A$2:C,"select B, C where A = date '2018-04-01'")
where A = date '2018-04-XX'"と1行ずつ書くのはしんどいので、where A = date '"&E:E&"'"のようにしたいのですが、これではダメです。
関数 QUERY のパラメータ 2 のクエリ文字列を解析できません: Invalid date literal [43191]. Date literals should be of form yyyy-MM-dd.というメッセージが出ているので、日付のフォーマットを変更すると良いようでした。
=query($A$2:C,"select B, C where A = date '"&text(E:E,"YYYY-MM-DD")&"'")
F2に記載した式をF3以降に記載すると、意図通りの内容が表示されるようになりました。
F6については2018/04/05のデータが無いので #N/Aで良さそうですが、IFERRORで何も表示しないようにしようと思います。
=iferror(query($A$2:C,"select B, C where A = date '"&text(E:E,"YYYY-MM-DD")&"'"),"")
これで、一致しない部分は何も表示されないようになりました。
感想とまとめ
queryを利用して色々なことが出来るのですが、検索条件が日付の場合は要注意ですね。理解していくと便利に使えそうだったのでもう少しうまく使えるようになりたいです。
追記（2018.9.3）
データが書いてあるシートが別だった場合は以下のような式にすると良さそうでした。
=iferror(query('別のシート名'!$A$2:C,"select B, C where A = date '"&text(E:E,"YYYY-MM-DD")&"'"),"")
何かとシートを分けることが多いのでたまに調べたりしてたのですが、とりあえず追記してみました。
散歩中に撮った写真
OLYMPUSのOMDを買ってからほぼ毎日のように写真を撮ってます。
ほとんどは我が家のワンコの写真を撮っているのですが、風景を撮ったりとか家族を撮ったりとか、この前旅行に行った時の写真を撮っています。
旅行中の写真はまた今度公開するとして、ここ数日で撮っている写真を幾つか。
まずは公園の写真です。ここ数日朝から暖かかったりしたので木陰を散歩したりしてました。
咲いているお花の写真もあります。名前は分からないのですが、きれいなお花でした。
次は住宅街の写真です。普段よく見ているところも撮ったりしました。
PEN-Liteの頃もよく撮ってたのですが、OMDは撮ってて楽しいですね。もう少し上手く使えるようになろうと思います。
お花見
今日は天気が良かったのでお昼に家族でお花見に行ってきました。
朝散歩してた時は誰も居なかったのですが、お昼に行くとお花見してる人達とか川で遊んでる子供達がたくさん居て春を感じました。
色々写真を撮ったものの、色んな人の顔が写ってしまってたので写真は少なめですが、撮影日和でした。
毎年恒例のお花見の家族写真を撮れたので今年も良い春になりました。
BigQueryでUTCをJSTに変換したい
googleであれこれ探したのですが、レガシーSQLしか見つからず、標準SQLの項目がなかなか見つからなかったのでメモを残します。
Qiitaにて発見しました。
Qiitaに載っていたのはunixtimeですが、TIMESTAMP_SECONDS(unixtime)の部分をtimestamp型のカラム名を設定して具合よく表示されました。
-- unixtimeをjstに変更 FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(unixtime), 'Asia/Tokyo')
色々調べて思ったのですが、そろそろ標準SQLが優先されて検索結果が表示されると個人的には嬉しかったりします。いつも公式ドキュメントの標準SQLへの移行を眺めつつ、検索結果で見つかったレガシーSQLとの対比表を見たり、実際にクエリを書いたりしているのですが地味に効率が悪いんですよね。
まあ効率が悪いのは自分のスキルの問題なので日々精進ですね。
今日も色々撮ってきました。
今日もまた色々撮ってきました。花が咲く季節は良いですね。
どんな感じに撮るのがいいのか分かって無いのですが、撮ってて楽しいカメラは良いですね。
春になると毎年花や木の名前を覚えて行こうかなと思うのですが、相変わらず覚えられずに今に至ります。何にでも興味を持てるようになりたいです。
