7つのデータベース 7つの世界 2章 PostgreSQL 2日目

集約関数

My Place をvenues に追加しておく

INSERT INTO venues(name, postal_code, country_code) 
  VALUES ('My Place', '97205', 'us');

events に登録するとき，venue_id を指定する代わりに副問い合わせを使う

INSERT INTO events (title, starts, ends, venue_id)
  VALUES ('Moby', '2012-02-06 21:00:00', '2012-02-06 23:00:00', (
   SELECT venue_id 
   FROM venues
   WHERE name = 'Crystal Ballroom'
  )
);

その他events の行を増やしておく．

INSERT INTO events (title, starts, ends, venue_id)
  VALUES ('Wedding', '2012-02-26 21:00:00', '2012-02-26 23:00:00', 2), ('Dinner with Mam', '2012-02-26 18:00:00', '2012-02-26 20:30:00', 3), ('Valentine''s Day', '2012-02-14 00:00:00', '2012-02-14 23:59:00',NULL);

count

SELECT count(title) 
FROM events 
WHERE title LIKE '%Day%';

 count 
-------
     3
(1 row)

max, min

Crystal Ballroom で開催されるイベントで開催日が最も早いイベントと最も遅いイベントの日時を取得

SELECT min(starts), max(ends) 
FROM events INNER JOIN venues
  ON events.venue_id = venues.venue_id 
WHERE venues.name = 'Crystal Ballroom';

グループ化

特定の列でグループ化

SELECT venue_id, count(*) 
FROM events 
GROUP BY venue_id 
ORDER BY venue_id;

単に重複をなくす場合は

SELECT DISTINCT venue_id FROM events GROUP BY venue_id;

グループ化した結果をフィルタするにはHAVING を使う

SELECT venue_id, count(*) 
FROM events
GROUP BY venue_id 
HAVING count(*) >= 2 AND venue_id IS NOT NULL;

ウィンドウ関数

ウィンドウ関数はグループごとの結果を壊さない

SELECT venue_id, count(*) 
 OVER (PARTITION BY venue_id) 
FROM events
ORDER BY venue_id;

SELECT venue_id, count(*) 
FROM events
GROUP BY venue_id
ORDER BY venue_id;

エラー例

GROUP BYしていないtitleが複数の値を持っていてどちらを表示して良いかわからない．

SELECT title, venue_id, count(*) 
FROM events
GROUP BY venue_id;

ERROR:  column "events.title" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT title, venue_id, count(*) 

PARTITION BYを使うことで集約結果を反復して返す．
ここでは，Wedding とLARP Club に同じcount(*) の結果が使われている．

SELECT venue_id, title, count(*) OVER (PARTITION BY venue_id) FROM events;

トランザクション

処理の途中で問題が発生した際に，処理以前に戻す事が出来る処理．

BEGIN TRANSACTION;
  DELETE FROM events;
ROLLBACK;
SELECT * FROM events;

ストアドプロシージャ

対応するvenueが無い場合，eventの前にvenueを追加する例
（venuesが必要な```cities``のデータが既にある事が前提）

add_event.sql

CREATE OR REPLACE FUNCTION add_event(title text, starts timestamp, ends timestamp, venue text, postal varchar(9), country char(2) )
RETURNS boolean AS $$

DECLARE
  did_insert boolean := false;
  found_cont integer;
  the_venue_id integer;
BEGIN
  SELECT venue_id INTO the_venue_id
  FROM venues v
  WHERE v.postal_code=postal AND v.country_code=country AND v.name ILike venue LIMIT 1;

  IF the_venue_id IS NULL THEN
    INSERT INTO venues (name, postal_code, country_code)
    VALUES (venue, postal, country)
    RETURNING venue_id INTO the_venue_id;

    did_insert := true;
  END IF;

  -- Note: not an "error", as in some programming languages
  RAISE NOTICE 'venue found %', the_venue_id;

  INSERT INTO events (title, starts, ends, venue_id)
  VALUES (title, starts, ends, the_venue_id);

  RETURN did_insert;
END;
$$ LANGUAGE plpgsql;

\i add_event.sql;
SELECT add_event('House Party', '2012-05-03 23:00', '2012-05-04 02:00', 'Run''s House', '97205', 'us');

NOTICE:  venue found 5
 add_event 
-----------
 t
(1 row)

トリガーを引く

データ更新時にログを取るストアドプロシージャの例

CREATE TABLE logs(
  event_id integer,
  old_title varchar(255),
  old_starts timestamp,
  old_ends timestamp,
  logged_at timestamp DEFAULT current_timestamp
);

log_event.sql

CREATE OR REPLACE FUNCTION log_event() RETURNS trigger AS $$
DECLARE
BEGIN
  INSERT INTO logs (event_id, old_title, old_starts, old_ends)
  VALUES (OLD.event_id, OLD.title, OLD.starts, OLD.ends);
  RAISE NOTICE 'Someone just changed event #%', OLD.event_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_events 
  AFTER UPDATE ON events
  FOR EACH ROW EXECUTE PROCEDURE log_event();

UPDATE events 
SET ends='2012-05-04 01:00:00'
WHERE title='House Party';

NOTICE:  Someone just changed event #8
UPDATE 1

SELECT event_id, old_title, old_starts, old_ends, logged_at FROM logs;

 event_id |  old_title  |     old_starts      |      old_ends       |         logged_at          
----------+-------------+---------------------+---------------------+----------------------------
        8 | House Party | 2012-05-03 23:00:00 | 2012-05-04 02:00:00 | 2013-03-25 13:56:57.067047
(1 row)

世界をビューする

複雑なクエリの結果をテーブルのように扱うための道具．

holiday_view_1.sql

CREATE VIEW holidays AS
  SELECT event_id AS holiday_id, title AS name, starts AS date 
  FROM events
  WHERE title Like '%Day%' AND venue_id IS NULL;

SELECT name, to_char(date, 'Month DD, YYY') As date 
FROM holidays 
WHERE date <= '2012-04-01';

      name       |       date        
-----------------+-------------------
 April Fools Day | April     01, 012
 Valentine's Day | February  14, 012
(2 rows)

ビューに新しい列を追加するには元のテーブルに追加する．

ALTER TABLE events 
ADD colors text ARRAY;

CREATE OR REPLACE VIEW holidays AS
  SELECT event_id AS holiday_id, title AS name, starts AS date, colors 
  FROM events
  WHERE title Like '%Day%' AND venue_id IS NULL;

直接ビューから更新することは出来ない

UPDATE holidays SET colors = '{"red", "green"}' where name = 'Christmas Day';

ERROR:  cannot update view "holidays"
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.

学校のルール？

EXPLAIN VERBOSEでVIEW の実行計画を見ると，元のクエリと同じ．

EXPLAIN VERBOSE
  SELECT * 
  FROM holidays;

EXPLAIN VERBOSE
  SELECT event_id AS holiday_id, title AS name, starts AS date, colors 
  FROM events
  WHERE title Like '%Day%' AND venue_id IS NULL;

そこで，元のテーブルを更新するようなルールを用意する．

create_rule_update.sql

CREATE RULE update_holidays AS ON UPDATE TO holidays DO INSTEAD
  UPDATE events
  SET title = NEW.name,
      starts = NEW.date,
      colors = NEW.colors
  WHERE title = OLD.name;

create_rule_insert.sql

CREATE RULE insert_holidays AS ON INSERT TO holidays DO INSTEAD
  INSERT INTO events(title, starts, colors)
  VALUES (NEW.name, NEW.date, NEW.colors);

INSERT INTO holidays (name, date, colors) 
VALUES ('New Year Day', '2013-01-01 00:00:00', '{"white","orange"}');

クロス集計で会いましょう

ピボットテーブルを使うにはcrosstab()関数を使う．
日付の部分集合を返す``extract()```関数があり，これをグループ化に用いる．

SELECT extract(year from starts) as year,
  extract(month from starts) as month, count(*) 
FROM events
GROUP BY year, month;

crosstab()にはrowid, category, valueの3つの列が必要であり，それぞれyear, month, count(*) を対応させる．
各月を列にするため，一時テーブルを用意．

CREATE TEMPORARY TABLE month_count(month INT);
INSERT INTO month_count VALUES (1), (2), (3), (4), (5), (6), (7), (8),(9), (10), (11), (12);

AS 以降で列名の定義が必要．

SELECT * FROM crosstab(
  'SELECT extract(year from starts) as year,
  extract(month from starts) as month, count(*) 
   FROM events
   GROUP BY year, month',
  'SELECT * FROM month_count'
) AS (
  year int,
  jan int, feb int, mar int, apr int, may int, jun int, 
  jul int, aug int, sep int, oct int, nov int, dec int
) ORDER BY YEAR;

2日目の宿題

1. venues テーブルのDELETEを補足して，物理削除せずactiveフラグをFALSEに変える

トリガー関数

logical_delete.sql

CREATE OR REPLACE FUNCTION logical_delete() RETURNS trigger AS $$
DECLARE
BEGIN
  UPDATE venues
  SET active = FALSE
  WHERE name = OLD.name;
  RAISE NOTICE 'Someone just changed venue #%', OLD.venue_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

BEFORE で削除前のトリガーにする

DROP TRIGGER logical_delete ON venues;
CREATE TRIGGER logical_delete
  BEFORE DELETE ON venues
  FOR EACH ROW EXECUTE PROCEDURE logical_delete();

INSERT INTO venues (name, postal_code, country_code) VALUES('My Home', '97205', 'us');
DELETE FROM venues WHERE name = 'My Home';

2. イベントカレンダーのピボット実装にgenerate_series(a, b)を使う

SELECT * FROM crosstab(
  'SELECT extract(year from starts) as year,
          extract(month from starts) as month, count(*) 
   FROM events
   GROUP BY year, month',
  'SELECT generate_series(1, 12)'
) AS (
  year int, 
  jan int, feb int, mar int, apr int, may int, jun int, 
  jul int, aug int, sep int, oct int, nov int, dec int
) ORDER BY YEAR;

3. 月日を表示するピボットテーブルを作る

SELECT * FROM crosstab(
  'SELECT extract(week from starts) as week,
          extract(dow from starts) as dow,
          count(*) 
   FROM events
   WHERE starts >= ''2012-02-01'' AND ''2012-03-01'' > starts
   GROUP BY week, dow
   ORDER BY week, dow',
  'SELECT generate_series(0,6)'
) AS (
  week int,
  sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY WEEK;