使えそうなSQL

TracReport取り込み(というかグラフ化プラグイン)ですが、SQLがどうしてもポイントになってくる。なので、有用なSQL例をどこかで共有できればなぁと思いますが、、、
自分も余りここらへんのノウハウは持っていないので色々と知りたいというのがあります。そのうちShibuya.tracのWikiにまとめてみるか。

ちょっといくつか紹介してみる。

  • 担当チケット数(担当者別表示)を表示する。
    • これは簡単っすね。
SELECT t.owner AS 担当者, count(*) AS "担当チケット件数(総計)"
   FROM ticket t
   GROUP BY t.owner
   ORDER BY "担当チケット件数(総計)" DESC
  • チケットの登録数、クローズ数の累計。
    • これはちょっと複雑になってしまった。ビュー使えばもっとシンプルになりそうだけど、ベタで書くとこんな感じ。
    • 条件にさらにtypeでバグを指定して絞り込めば、バグ収束曲線の出力にも使えそうである。
SELECT a.Month, sum(b.登録数) AS 累計登録数, sum(b.クローズ数) AS 累計クローズ数 FROM

(SELECT tm AS Month, sum(delta1) AS 登録数, sum(delta2) AS クローズ数
 FROM (SELECT id, date(time,'unixepoch') tm, 1 delta1, 0 delta2 FROM ticket
       UNION ALL
       SELECT t.id id, date(tc.time,'unixepoch') tm, 0 delta1, (CASE tc.newvalue WHEN 'closed' THEN 1 ELSE -1 END) delta2
         FROM ticket t
         JOIN ticket_change tc ON t.id=tc.ticket AND tc.field='status' AND (tc.newvalue='closed' or tc.oldvalue='closed'))
 GROUP BY tm) a,

(SELECT tm AS Month, sum(delta1) AS 登録数, sum(delta2) AS クローズ数
 FROM (SELECT id, date(time,'unixepoch') tm, 1 delta1, 0 delta2 FROM ticket
       UNION ALL
       SELECT t.id id, date(tc.time,'unixepoch') tm, 0 delta1, (CASE tc.newvalue WHEN 'closed' THEN 1 ELSE -1 END) delta2
         FROM ticket t
         JOIN ticket_change tc ON t.id=tc.ticket AND tc.field='status' AND (tc.newvalue='closed' or tc.oldvalue='closed'))
 GROUP BY tm) b

WHERE strftime('%s',a.Month) >= strftime('%s',b.Month)
GROUP BY a.Month