Conversation

Notices

1. zunda ? nはおまけ :green_dango: (zundan@mastodon.zunda.ninja@mastodon.zunda.ninja)'s status on Friday, 28-Dec-2018 11:20:15 JST zunda ? nはおまけ :green_dango:

   突然ですが弊インスタンスは午前中にコーラとレッドブルを飲み夕方にビールを飲むことがわかりました。健全だw

   > SELECT
   EXTRACT(HOUR FROM created_at AT TIME ZONE 'UTC-10') as hour,
   SUM(CASE WHEN text LIKE '%ビール%' THEN 1 ELSE 0 END) AS beer,
   SUM(CASE WHEN text LIKE '%コーラ%' THEN 1 ELSE 0 END) AS coke,
   SUM(CASE WHEN text LIKE '%レッドブル%'
   OR text LIKE '%RedBull%' THEN 1 ELSE 0 END) AS redbull,
   COUNT(1) as total
   FROM statuses WHERE account_id=1
   GROUP BY EXTRACT(HOUR FROM created_at AT TIME ZONE 'UTC-10');

   • Asmodeus :dumpling: ? repeated this.
   • zunda ? nはおまけ :green_dango: (zundan@mastodon.zunda.ninja@mastodon.zunda.ninja)'s status on Friday, 28-Dec-2018 11:21:32 JST zunda ? nはおまけ :green_dango:

     in reply to

     グラフはpsqlの出力をtsvに編集しておいてGnuplotで。

     reset
     set xrange [-0.5:23.5]
     set xtics 3
     set yrange [0:40]
     set y2range [0:1600]
     set style data boxes
     set style fill solid
     set boxwidth 0.2
     set xlabel "Hour (UTC-10)"
     set ytics (0)
     plot 'beer.dat' using 1:5 tit 'Total' axes x1y2 with lines,\
     'beer.dat' using ($1-0.2):3 tit 'Coke',\
     'beer.dat' using ($1+0.0):4 tit 'RedBull',\
     'beer.dat' using ($1+0.2):2 tit 'Beer'
     set term push;set term png small; set output 'beer.png'
     replot
     unset output; set term pop