MySQL で累積和を求める

SUM() と JOIN を使えば書ける．JOIN してるのでオーダーは小さくなさそうな気がする． COUNT(*) をよしなに所望のものに変えれば好きなものの累積和が取れる． テーブル名なども適宜読み替えてください．

SELECT
    t1.accum_date
  , t1.count
  , t1.count + SUM(t2.count) AS accum
FROM (
    SELECT
        DATE(created_at) AS accum_date
      , COUNT(*) AS count
    FROM
        images
    GROUP BY accum_date
    ORDER BY accum_date
) AS t1
JOIN (
    SELECT
        DATE(created_at) AS accum_date
      , COUNT(*) AS count
    FROM
        images
    GROUP BY accum_date
    ORDER BY accum_date
) AS t2
ON
    t1.accum_date > t2.accum_date
WHERE
    t1.accum_date >= TIMESTAMP '2012-07-01 00:00:00'
AND t1.accum_date < TIMESTAMP '2017-09-01 00:00:00'
GROUP BY t1.accum_date
ORDER BY t1.accum_date

t1 に t2 を，「それより accum_date が古い」という条件で自己結合させると，SUM(t2.count) が t1 の手前までの累積和となる．

一時テーブルを使えばシュッと書けそうな気がしたが，一時テーブルは自己結合ができないので，同じサブクエリを2回書いている． PostgreSQL ならウィンドウ関数でもっと楽に書けそうなんだよなあ～～*1

gyazo.com

関係ないけど，とうとう収集してる画像の枚数が80000枚を超えました．これはそれを表すグラフです．

追記

https://www.postgresql.jp/document/9.6/html/tutorial-window.html

どれくらい短く書けるかと思ったら1行だった……．

*1:まだ試してない．

SQL で id を使ったページネーションの処理を書く

よく知られているように SQL の OFFSET は遅い*1ため，速さを求める我々は OFFSET に頼らずにページネーションを実現する必要があります．

ところで，たいていの場合は id が主キーとなっており，これを使ってページネーションするのがいいのでは？ と考えます．

たとえば，最新の投稿から per_page 件ずつ前に出てくる掲示板を考えます．また，ページの中で最初に出てくる投稿の id を newest_id，最後に出てくる投稿の id を oldest_id とします．

このとき，次のページ (つまり，古い投稿) を見るときのクエリ*2は次のようになります．

SELECT *
FROM posts
WHERE
  id < oldest_id
ORDER BY id DESC
LIMIT per_page

前のページ (つまり，新しい投稿) を見るときのクエリは少し考える必要があります*3．

SELECT *
FROM (
  SELECT *
  FROM posts
  WHERE
    id > newest_id
  ORDER BY id ASC
  LIMIT per_page
) AS t
ORDER BY id DESC

ここで SELECT * FROM posts WHERE id > newest_id ORDER BY id DESC LIMIT per_page と書くと，最新の投稿から順に表示されてしまいます．なぜなのかは下の図で確認してください．

max(id) = 100, newest_id = 10, per_page = 5 のとき

[100][99][98][97][96] ... [15][14][13][12][11]  [10] ... [2][1]
  (ここが取得される)      (本当に欲しいところ)   (取得対象でない)
(id=100 から id=11 の順に取得される)

そのため，まず前のページの投稿を昇順で per_page 件取ってきて，それを降順に並べかえる，という処理をしています*4．

[1][2] ... [10]  [11][12][13][14][15] ... [96][97][98][99][100]
(取得対象でない)   (ここが取得される)       (取得されない)
(id=11 から id=100 の順に取得される)

ところで

この方法ではページ数を指定して取得することができないので，諦めてください．どうすればええんや……*5

*1:OFFSET で読み飛ばした分の行も走査するため．EXPLAIN すると分かります．

*2:ここでは MySQL を仮定していますが，他でもだいたいこんな感じだと思います．

*3:サブクエリに名前を付けないと怒られるので付けた．

*4:もしくは昇順で取ってきて，アプリ側で逆順にソートするなどでもいいです．

*5:idが連続という仮定をすればうまくいけそうな気はするが，不連続だとポシャるし，そもそも単語検索のときはどうするの……？

SQLite3 から MySQL にデータを移行した

utgwkk.hateblo.jp

この記事の続き．SQLite → MySQL のデータ形式変換の話はこっちに書いている．

おととい詰まっていたのは，絵文字を含む VARCHAR を INSERT するときにエラーが出る，というところで，これは結局設定ファイルに付け加えることで解決した．

/etc/mysql/conf.d/ 以下にこう書いた設定ファイルを置く．

[mysqld]
character-set-server = utf8mb4

[client]
default-character-set = utf8mb4

こうすることによって mysqld と mysql コマンドで utf8mb4 を使うようになる．

あとはライブラリを入れ替えたり微妙にSQLを書き換えたりすれば動くようになるが，クライアント側で encoding を utf8mb4 に設定する必要があるので，忘れないようにする．

たとえば Ruby (mysql2) ならこんな感じ．

db = Mysql2::Client.new(
  :host => DB_PATH,
  :username => DB_USERNAME,
  :password => DB_PASSWORD,
  :database => DB_NAME,
  :encoding => 'utf8mb4' # これ
)

こうしてデータもアプリケーションも無事に MySQL に全面移行できたが，このあたりはふつうは ActiveRecord などを噛ませるんだろうなあ……．

SQLite3 から MySQL にデータを移行しようとしているができていない状態

今まで溜めていたデータを SQLite から MySQL に移行しようとしてハマって一日終了した．

github.com

とりあえずダンプファイル形式を変換するスクリプトがあるので当てる． テーブルはあらかじめ作っていたので CREATE TABLE は消したが，消す必要はなかったな……． 先頭・末尾にそれぞれ START TRANSACTION; と COMMIT; を入れないと，途中どこかでエラーが出てもそこまでのデータが挿入されてしまう．

歴史的経緯で TIMESTAMP が実数型になっていたので，雑に変換するスクリプトを書いて当てた．

import sys
import re
import time
pattern = re.compile(r",(\d+(\.\d+(e\+\d+)?)?)\);")

for line in sys.stdin:
    line = line.rstrip()
    m = pattern.search(line)
    if m:
        target = m.group(1)
        unixtime = float(target)
        timestruct = time.localtime(unixtime)
        timestr = "'{}'".format(time.strftime('%Y-%m-%d %H:%M:%S', timestruct))
        print(line.replace(target, timestr))
    else:
        print(line)

データを流し込んでみたところエラーが出る．

ERROR 1366 (HY000) at line 79013: Incorrect string value: '\xF0\x9F\x98\x8C\xF0\x9F...' for column 'comment' at row 1

ググってみたところどうやら絵文字っぽい雰囲気があった． CREATE DATABASE db DEFAULT CHARACTER SET utf8mb4 してみたが直らない……．

疲れたし喉も目も終わったので今日はここまでです．

THE IDOLM@STER CINDERELLA GIRLS 5thLIVE TOUR Serendipity Parade!!! SSA公演に行った，

ざっくり

気持ちになって箇条書きでまとめたものをそのまま掲載します． どこかに吐き出しておかないと一生気持ちが終わったままになってしまうと思ったため．

1日目 (LV)

• 1曲目で原田さんが旗を持って出てくるの，軽いサプライズ感あった
  • だいぶパフォーマンスやってた気がする
  • 開幕YPTだけ抜けたという感じ?
• 中島さんの乙倉くん良すぎた……爽やかキュートという感じ
• 命燃やして恋せよ乙女で，楓さんの3Dモデルが踊るの，そういう演出というの4thで知っていたけど，若干サプライズを期待してしまった
  • さすがにここで出てくることはないでしょとは思っていたが……
  • とりあえずこれで，今回はそういう舞台演出ということが分かったので以降は心構えができた
• DJぴにゃはいい仕事をした
  • 個々の曲について，もっとしっかり聞きたいというのはいっぱいありますが，最高でした
• Neo Beautiful Pain また見れて，パフォーマンスがもっと上がっていて最高だった
  • 松井さんは福岡に引き続きフラスタにイラスト描いてて，あ〜〜〜〜
  • Never say never やっていて，凛に〜〜うあ〜〜〜

2日目 (現地)

• メリーゴーランドの飛鳥くんの衣装チェンジよかった
• 出だしの熱血乙女Aはつかみ最高だった
  • マジでステージを走りまわるの，あ〜〜やりそう
• ままゆがいないしラブデスやらないでしょ / 杏がいないしリトルリドルやらないでしょ と思っていました……
  • るーりぃがいない2人に後ろをとられたまま unlimited をやっていたの良かったですね
  • DJぴにゃが出るからそのタイミングでやるかと思っていたが……(リトルリドル)
• ニュージェネにもう少しフォーカスしてほしかった気持ちがある
  • トラパルをDJで少しだけというのは正直物足りなかった気がする
  • とはいえセレンディピティとしての側面というのはそうですね
  • 良かったです
• One Life かっこいいことは知っていたけど，ライブで聞くと想像以上によかった……．
• さっつんはやっぱりすごい……
• 朝井さんには是非ともインディヴィジュアルズの3人揃って「ウチらが、インディヴィジュアルズだッ！」をやってほしい
  • ドームでやると∀NSWERのMV再現になってアツい
• 最後に畳みかけるように今日やるべき曲をぜんぶやっていたなあという感じ
• 5(3)人揃ってよかったね!!!! が大量にあって満腹になった
  • Tulip こんどは5人で歌いたい，を4thのPVで見たあとだったので，……

総評

• ツアーを通じて新曲紹介中心という印象
• DJぴにゃは実際良かったし，もっと掘り下げてほしいという思いも出た
• 地方公演行くとお金がかかる

EVERMORE が流れてきて，あーこのライブも終わってしまうんだなと思い，そしてステージの旗はライブが終わってもそのまま残されており，ほんとうに終わってしまったという気持ち． もう今年はライブに行かないんだなと思うと少し寂しい反面，お金やら時間やらの事情もあるので，ちょっと安心しているという面もあります． アイドルたちも，声優のみなさんについても，文字通りセレンディピティがいっぱいあったと思います．イエスセレンディピティは流石にずるかった．

手元にはMBAしかなくて会場限定CDが取り込めない，もっと言うとおつかいを頼んでいたけど受け取りを忘れていた……．はやく聞きたい．

Neo Beautiful Pain を聞いてほしいと思っていたんですけど，あれはやっぱりライブ映像も見たほうがいいですね．

次回予告

6th LIVE ドーム公演の開催決定おめでとうございます．さすがに全国ツアーじゃないからお金はなんとかなりそう． 先述したように，ライブは最高でしたがまだまだやってほしいことが大量にあるので，来年のライブにも期待しております． 会場に行ければ行きたい，生き残りたい……．

今年の公演エントリまとめ

utgwkk.hateblo.jp

utgwkk.hateblo.jp

utgwkk.hateblo.jp

utgwkk.hateblo.jp

utgwkk.hateblo.jp

こうやって見ると，宮城以外の全ての公演の片方の日程を観ていることが分かります． 宮城もせめてLVぐらい見たほうがよかったという気がしてきましたが，もう終わったことなので，BDで補完しようと思います．

東京日記

インターンの様子をお伝えします．

今日は社会性があるのでテーブルです pic.twitter.com/VeWR1MnJkz

— うたがわきき🔰💊 (@utgwkk) 2017年8月8日

駆け込みでしたが任務完了です pic.twitter.com/fVzCx1w8to

— うたがわきき🔰💊 (@utgwkk) 2017年8月8日

以上です．

夢日記

夢

• 起きたら夜の実家近辺にいる
• 小学校で何やら催しごとがあったようだが，すっかり忘れていたので参加できなかった
• 公園の川を見ていたら身体が浮く
• 実家に戻ってきたら身体が重くなってきて階段を登れなかったが，念じて飛び上がったら登れた
• 朝，父親と喧嘩して父親を踏みつけた (弾力があった)
• 自分のパソコン？ でここが23900年の世界ということを知る
• 窓から飛び降りて自殺しようとしたが，あまりにも感触がリアルだったので躊躇した
• そうこうしているうちに家に戻ったら家が消えていて，近くで作業していた工場のおじさん？ に聞くと，24800年？ になったことを知る．
• Unity は再利用性がある，私たちも……と言っていた．
• 聞いたことのない曲の歌詞を口ずさんでいた．
• そろそろ起きないとやばいかなーと思っていた．
• 夜の町に出たら牛2頭がUFOに連れさられようとしていて，近寄ると自分の身体も浮いてしまった．ここで終了．