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


## 集約関数

```My Place``` をvenues に追加しておく

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

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

```sql
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 の行を増やしておく．

```sql
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

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

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

### max, min

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

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

### グループ化

特定の列でグループ化

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

単に重複をなくす場合は

```sql
SELECT DISTINCT venue_id FROM events GROUP BY venue_id;
```

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

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

### ウィンドウ関数

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

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

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

エラー例

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

```sql
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(*) の結果が使われている．

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

### トランザクション

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

```sql
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;
```

```sql
\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)
```

### トリガーを引く

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

```sql
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;
```

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

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

```
NOTICE:  Someone just changed event #8
UPDATE 1
```

```sql
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;
```

```sql
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)
```

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

```sql
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;
```

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

```sql
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``` の実行計画を見ると，元のクエリと同じ．

```sql
EXPLAIN VERBOSE
  SELECT * 
  FROM holidays;
```

```sql
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);
```

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

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

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

```sql
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(*)``` を対応させる．
各月を列にするため，一時テーブルを用意．

```sql
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 以降で列名の定義が必要．

```sql
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``` で削除前のトリガーにする

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

```sql
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)```を使う

```sql
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. 月日を表示するピボットテーブルを作る

```sql
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;
```