山下寛人オフィシャルブログ

オイシックス株式会社 執行役員 システム本部長 山下寛人の公式ブログです。

SQLite tips

tracのレポートを作るのにいろいろ調べたこと。

カウントして率を出したい場合はreal型にキャストする必要があります。

round(cast(sum(c.value) as real)/cast(count(id) as real),4)*100

さらにパーセントの文字をくっつけたい場合はtextにキャストしなければできませんでした。ネット上のサンプルでは数値にそのまま||で連結したら自動的に文字列になるというものもありましたがなぜ違うのかは不明です。

cast(
  round(cast(sum(c.value) as real)/cast(count(id) as real),4)*100 as text
)||'%' as 回答率

unix timeから日付に変換

date(time,'unixepoch','localtime')

ハイフン区切りの日付と比較できます。

date(time,'unixepoch','localtime')>='2015-08-01'

スラッシュ区切りの日付をハイフン区切りに変換。replace関数もあるはずですがなぜかエラーになったのでsubstrでやってみました。

substr(tlimit.value,1,4)||'-'||
substr(tlimit.value,6,2)||'-'||
substr(tlimit.value,9,2)

現在の日付

date('now','localtime')

週でまとめる
unix日付ベースで1週間=1*60*60*24*7秒で割って切り捨ててまた掛けて秒に戻して日付に変換します。週の始まりの曜日を調整するために60*60*24*日数を引いたり足したりしています。

date(
  '1970-01-01',
  cast(
    cast(
      (time+60*60*24*3)/60/60/24/7 as int
    )*60*60*24*7-60*60*24*3 as string
  )||'seconds'
)

timeがunixtimeになっていて、そのtimeの日付を含む週の月曜に変換します。これでグループ化すると週ごとにまとまります。