「SQLパズル」を DB2/400 で学習中です

SQLパズル 第2版 プログラミングが変わる書き方/考え方」という本を買ってみました。

SQL についてすでにそれなりの(かなりの)知識があることが前提とされています。
各章それぞれ中身が濃いので急いで読み飛ばせるような本ではなく、副題の「プログラミングが変わる書き方/考え方」というのを実感しながらじっくり読み進める本ですね。子供がもらった大好きなお菓子を大切にゆっくりゆっくり食べるような、そんなかんじですこしずつ、何回も取り出して読むような本のような気がします。「ローマは一日にしてならず」といったところでしょうか。

すぐに即効で役に立つ、というわけではありませんが、SQL の考え方を理解する上での練習問題/応用問題がつまっています。

いくつか実験しながら内容を見てみた結果を載せてみます。当然、解説は本を見ていただくか、さらに訳者の方によるサポートページがありますのでそちらをあわせて見ていただくか、してください。


パズル 1

データベースは DB2/400 を使用しました。

いきなり以下の箇所がエラーになったので、ちょっとあせったのですが

CHECK ((DAYS(end_date) - DAYS(start_date)) = INTERVAL '359' DAY))

そもそも DB2 (i5/OS版というだけではなく、Linux/Windows版のDB2 も含めて) では INTERVAL という書き方がサポートされていないようで、

CHECK ((DAYS(end_date) - DAYS(start_date)) = 359))

と書きかえることで無事動作しました。

日付の計算は、以下のような SQL で検算できます。

SELECT (DAYS('1995-09-30') - DAYS('1994-10-01')) FROM SYSIBM.SYSDUMMY1;

翻訳をされた方が開設されている『SQLパズル 第2版』サポートページに載っていた例を、上記の点を修正して動かしてみたところ、ちゃんと動きました。(まぁ当り前と言えば当り前なのですが ...... )

なんか、ちゃんと動いて結果が出てくるだけで楽しいです。

パズル 2

勢いに乗ってパズル2 をさらにやってみました。

やはり INTERVAL 関係の修正が必要でした。自分で忘れないように、あくまで自分の復習の参照用に、変更が必要だった SQL は全体を載せておきます。
当り前のことですが、載っている SQL を動かして満足してしまうだけでは何も身につきません。実行して、結果を確かめながら理解し、納得することが大事なんですね。

DELETE FROM Personnel 
 WHERE emp_id = 
   (SELECT A1.emp_id 
      FROM Absenteeism AS A1 
     WHERE A1.emp_id = Personnel.emp_id 
       AND absent_date 
           BETWEEN DATE(CURRENT_TIMESTAMP - 365 DAYS) 
               AND DATE(CURRENT_TIMESTAMP) 
     GROUP BY A1.emp_id 
    HAVING SUM(severity_points) >= 40);
CREATE TABLE Absenteeism 
(emp_id INTEGER NOT NULL 
        REFERENCES Personnel (emp_id) 
        ON DELETE CASCADE, 
 absent_date DATE NOT NULL, 
 reason_code CHAR(40) NOT NULL 
             REFERENCES ExcuseList (reason_code), 
 severity_points INTEGER NOT NULL 
             CHECK (severity_points BETWEEN 1 AND 4), 
 PRIMARY KEY (emp_id, absent_date));
UPDATE Absenteeism AS A1 
   SET severity_points = 0, 
       reason_code = 'long term illness' 
 WHERE EXISTS 
   (SELECT * 
      FROM Absenteeism AS A2 
     WHERE A1.emp_id = A2.emp_id 
       AND (A2.absent_date + 1 DAY) = A1.absent_date);
SELECT A.emp_id, 
       SUM(A.severity_points) AS absentism_score 
  FROM Absenteeism AS A, Calendar AS C 
 WHERE C.cal_date = A.absent_date 
   AND A.absent_date 
       BETWEEN DATE(CURRENT_TIMESTAMP - 365 DAYS) 
           AND DATE(CURRENT_TIMESTAMP) 
   AND C.date_type = 'work' 
 GROUP BY emp_id 
HAVING SUM(severity_points) >= 40;
SELECT A.emp_id, 
       SUM(A.severity_points) AS absentism_score 
  FROM Absenteeism AS A, Calendar AS C 
 WHERE C.cal_date = A.absent_date 
   AND A.absent_date 
       BETWEEN DATE(CURRENT_TIMESTAMP - 1 YEAR) 
           AND DATE(CURRENT_TIMESTAMP) 
   AND C.date_type = 'work' 
 GROUP BY emp_id 
HAVING SUM(severity_points) >= 40;

パズル 37

INTERVAL の修正の仕方はこれでいいのかなと確かめたくなり、サポートページに「INTERVAL 定数を使う問題は、「2.欠勤」、「37.移動平均」、「45.ペパロニピザ」などを参照」とあったので、続けてパズル37 をやってみました。

UPDATE Samples
SET moving_avg
    = (SELECT AVG(S1.load)
         FROM Samples AS S1
        WHERE S1.sample_time
              IN (Samples.sample_time,
                 (Samples.sample_time - 15 MINUTE),
                 (Samples.sample_time - 30 MINUTE),
                 (Samples.sample_time - 45 MINUTE)));
UPDATE Samples
   SET moving_avg
       = (SELECT AVG(S1.load)
            FROM Samples AS S1
           WHERE S1.sample_time
                 BETWEEN (Samples.sample_time - 1 HOUR)
                     AND Samples.sample_time);

パズル 37 のその4 のSQL は DB2/400 で AVG() OVER がサポートされていないためそのままでは実行できませんでした。

SELECT *
  FROM (SELECT sample_time,
        AVG(load) OVER (ORDER BY sample_time ASC ROWS 4 PRECEDING)
          FROM Samples)
 WHERE EXTRACT(MINUTE FROM sample_time) = 00;

パズル 37 のその4 のView は元のテーブルをどう作っていいのかわからずとりあえず先送りにしています ......

CREATE VIEW DailyTimeSlots (slot_timestamp)
AS SELECT CURRENT_DATE + CAST(tick AS MINUTES)
     FROM ClockTicks;

パズル 45

次にパズル 45 の結果です。

SELECT cust_id, '0-30 days = ' AS age, SUM(pizza_amt)
  FROM FriendsOfPepperoni
 WHERE bill_date BETWEEN (CURRENT_DATE - 30 DAY)
   AND CURRENT_DATE
 GROUP BY cust_id
UNION ALL
SELECT cust_id, '31-60 days = ' AS age, SUM(pizza_amt)
  FROM FriendsOfPepperoni
 WHERE bill_date BETWEEN (CURRENT_DATE - 60 DAY)
   AND (CURRENT_DATE - 31 DAY)
 GROUP BY cust_id
UNION ALL
SELECT cust_id, '61-90 days = ' AS age, SUM(pizza_amt)
  FROM FriendsOfPepperoni
 WHERE bill_date BETWEEN (CURRENT_DATE - 90 DAY)
   AND (CURRENT_DATE - 61 DAY)
 GROUP BY cust_id
UNION ALL
SELECT cust_id, '90+ days = ' AS age, SUM(pizza_amt)
  FROM FriendsOfPepperoni
 WHERE bill_date < CURRENT_DATE - 90 DAY
 GROUP BY cust_id
 ORDER BY cust_id, age;
SELECT cust_id, 
       SUM(CASE WHEN bill_date BETWEEN date(CURRENT_DATE - 30 DAYs) 
                                   AND date(CURRENT_DATE) 
                THEN pizza_amt ELSE 0.00 END) AS age1, 
       SUM(CASE WHEN bill_date BETWEEN date(CURRENT_DATE - 60 DAYs) 
                                   AND date(CURRENT_DATE - 31 DAYs) 
                THEN pizza_amt ELSE 0.00 END) AS age2, 
       SUM(CASE WHEN bill_date BETWEEN date(CURRENT_DATE - 90 DAYs) 
                                   AND date(CURRENT_DATE - 61 DAYs) 
                THEN pizza_amt ELSE 0.00 END) AS age3, 
       SUM(CASE WHEN bill_date < date(CURRENT_DATE - 91 DAYs) 
                THEN pizza_amt ELSE 0.00 END) AS age4 
  FROM FriendsofPepperoni 

 GROUP BY cust_id 
 ORDER BY cust_id; 

WITH 句での VALUES がどうも使えないらしく、今書き換え方を考え中です ......

WITH ReportRanges(day_count, start_cnt, end_cnt)
AS (VALUES ('under Thirty days', 00, 30),
   ('Sixty days', 31, 60),
   ('Ninty days', 61, 90))
SELECT F1.cust_id, COALESCE(R1.day_count, 'Over Ninety days'),
       SUM(pizza_amt)
  FROM FriendsOfPepperoni AS F1
       LEFT OUTER JOIN
       ReportRanges AS R1
       ON F1.bill_date
          BETWEEN CURRENT_DATE - end_cnt
              AND CURRENT_DATE - start_cnt
 GROUP BY F1.cust_id, R1.day_count
 ORDER BY F1.cust_id, R1.day_count;

… というのが V5R4 までの状況だったのですが …

DB2 for IBM i V6R1 になって WITH 句での VALUES が使えるようになり、最初の方でもふれている日付関係の変更をあわせて行うことで実行できるようになりました。

WITH ReportRanges(day_count, start_cnt, end_cnt)
AS (VALUES ('under Thirty days', 00, 30),
   ('Sixty days', 31, 60),
   ('Ninty days', 61, 90))
SELECT F1.cust_id, COALESCE(R1.day_count, 'Over Ninety days'),
       SUM(pizza_amt)
  FROM FriendsOfPepperoni AS F1
       LEFT OUTER JOIN
       ReportRanges AS R1
       ON F1.bill_date
          BETWEEN CURRENT_DATE - (end_cnt)days
              AND CURRENT_DATE - (start_cnt)days
 GROUP BY F1.cust_id, R1.day_count
 ORDER BY F1.cust_id, R1.day_count;

日数には days をつけるのがポイントですね。
↓が実行結果になります。

[Top Pageに戻る]

Ads by TOK2