ITコーディネータのシュウです。

最近、鳥の写真を撮るのに凝り始めたという知人の方から、おもしろい写真が撮れたということで頂いたものです。結構大きめの鳥ということすが、名前まではわかりませんでした。月をバックに鳥がうまく収まっていますよね。

今日は、普段の生活について気の向くままに書いてみたいと思います。だんだん年を取ってくると、何かと健康が気になりますし、毎年行っている健康診断でも、昔は問題なかったところが、少しずつ正常でない数値になってきていることがあります。
今年もまた、健康診断の時期がやってきましたが、結果を見るのが少し怖いというか、何もなければうれしいけど、何かあると落ち込んでしまいますよね。

私は見た目は太っていないけれども、運動不足もあって、内臓脂肪が割と高めのタイプなんですが、結果が送られてくると、妻がそれを細かくチェック。「あなた、コレステロールの値がまた上がってるわよ！　間食で甘いものとかまたたくさん食べてるんじゃないの！」と厳しい追及が！！
しかし、そういう妻のほうが、結構見た目からもわかるほど太ってきているのも事実なんですが～、そこを言うとこじれてしまいそうなので、。。。

子供もまだまだ育ちざかりなので、健康にも十分気を付けないといけないですよね。

さて、しばらく思いつくままに書いてきましたが、１つだけSQLについてトピックを上げてみたいと思います。

＜本日の題材＞
スリープ処理

以前、ORACLEでのストアドプロシージャの処理がとても長くかかってしまうために、あるツールのほうでタイムアウトのエラーが起きてしまうということがお客様のほうで発生したことがあり、それを確認するためにどうしたらよいか？と調べてみたところ、ORACLEには、処理を待機させるパッケージが用意されているのを知り、試してみました。

PL/SQLのDBMS_LOCK パッケージに含まれる、DBMS_LOCK.SLEEPです。DBMS_LOCKパッケージは、その名の如く、特定モードのロックを要求したり、別のプロシージャ内で識別できる一意の名前をロックに付けたり、ロック・モードの変更およびロックの解放を行うことができるというものとのことですが、その中に、SLEEPプロシージャというものがあって、パラメータで指定した秒数だけスリープ（処理を待機）させることができます。

SCOTTユーザで実行してみます。
20秒スリープさせたいので、

execute DBMS_LOCK.SLEEP(20);

 このパッケージはロックに関連するものであるので、PUBLICロールに実行権限は含まれていないため、別途実行権限を与えてあげないとエラーになってしまいます。

DBA権限のあるユーザで権限を付与します。

CONNECT / AS SYSDBA
GRANT EXECUTE ON DBMS_LOCK TO SCOTT;

 再度、SCOTTユーザでログインして実行してみます。

なお、SQLの実行時間を計測して表示したいので、TIMINGシステム変数をONにします。

SET TIMING ON
execute DBMS_LOCK.SLEEP(20);

 

実行すると、今度はエラーは出ず、たしかに処理を待機して、20秒後に結果が返ってきました。

<<SQL Serverでは>>
上記をSQL Serverでやろうとすると、WAITFOR DELAY というコマンドがあることがわかりました。試してみます。

同様に、20秒待機させたいので、’00:00:20’ をパラメータで指定します。

WAITFOR DELAY ’00:00:20’

 たしかに、20秒ほどしたら、結果が返ってきました。

 Oracleのように、処理時間を表示させたいので、開始時刻と終了時刻をPRINTすることにします。

 PRINT '開始時刻：' + convert(nvarchar, getdate(), 114);
WAITFOR DELAY '00:00:20';
PRINT '終了時刻：' + convert(nvarchar, getdate(), 114);

 処理の開始時刻と終了時刻が表示されて、20秒ほどスリープしていたことがわかりました。

 今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

新年あけましておめでとうございます。
写真は、今まで何回か写真をアップしている、加須はなさき水上公園の池のところを撮った写真です。池の水面に木々が写ってとてもきれいです。何気なく見ている景色も、見る角度や季節、時刻などが変わると、全然違うように感じることがありますね。

さて、新しい1年が出発しました。また１つ年を取ってしまったという思いもありますが、今年こそはやってやる！という夢と気概を持って、何事にも挑戦して行ければと思います。
本年も、よろしくお願いいたします。

＜本日の題材＞
横に並んだ項目を縦の行データに変換

以前、「複数行のデータを集計して横展開」という題名で、複数行の縦に並んだデータを、横に項目を並べて表示させるためのSQLを取り上げましたが、その反対に、最初から横に項目を並べて登録されているデータを、複数行の縦に並んだデータに変換するやり方については取り上げていませんでした。
実際のシステム開発において、そういうケースにも時折出会うことがあるため、今回一度取り上げてみたいと思います。

様々なシステムにおいて、縦には商品であったり、勘定科目であったり、そのシステムで管理したいデータを並べ、横の列に、例えば4月～翌年3月までの各月を項目として持たせて、年度毎の一覧を表示／修正させるようなケースがあると思います。そのときに、データベースのテーブル自体の列に、4月、5月、…12月、..3月というように、表示に合わせて、各月の項目を持たせるという設計になる場合もあります。
このようにして登録された横に並んだ各列のデータを、今度はひと月ずつのデータとして複数行に分けて処理したいという場合に、どのようなSQLにすればよいのか？　以下に例を示します。

例）
商品ごとの各月の売上実績を登録するテーブルを以下のように定義、作成します。

CREATE TABLE dbo.商品売上(
  商品CD     VARCHAR(20)
, 年度       VARCHAR(4)
, 売上4月    DECIMAL(12)
, 売上5月    DECIMAL(12)
, 売上6月    DECIMAL(12)
, 売上7月    DECIMAL(12)
, 売上8月    DECIMAL(12)
, 売上9月    DECIMAL(12)
, 売上10月   DECIMAL(12)
, 売上11月   DECIMAL(12)
, 売上12月   DECIMAL(12)
, 売上1月    DECIMAL(12)
, 売上2月    DECIMAL(12)
, 売上3月    DECIMAL(12)
, CONSTRAINT PK_商品売上 PRIMARY KEY (商品CD, 年度))
;

データを以下のように登録します。

INSERT INTO dbo.商品売上('A0001', 2015, 495280, 503400, 485400, 534800, 521300, 494600, 538200, 482600, 546820, 483240, 452600, 517800);
INSERT INTO dbo.商品売上('B0001', 2015, 213300, 246400, 220480, 253100, 262300, 247200, 251800, 236700, 262400, 254300, 223800, 248900);
INSERT INTO dbo.商品売上('C0021', 2015, 165800, 139200, 181040, 166400, 176300, 168300, 192400, 168000, 201400, 187600, 176500, 194700);

このテーブルから、2015年度の商品CD毎の売上を普通に抽出すると

DECLARE
@年度 VARCHAR(4) = 2015

SELECT * FROM dbo.商品売上
 WHERE 年度 = @年度
 ORDER BY 商品CD;

 このデータを、商品CD毎、一月毎の売上データとして、各月のデータを複数行に分けて抽出する場合、例えば以下のようにします。

DECLARE
@年度 VARCHAR(4) = 2015

SELECT SU.商品CD, SU.月, SU.売上
  FROM
       (SELECT 商品CD, 4 AS 月, ISNULL(売上4月,0) AS 売上, 1 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 5 AS 月, ISNULL(売上5月,0) AS 売上, 2 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 6 AS 月, ISNULL(売上6月,0) AS 売上, 3 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 7 AS 月, ISNULL(売上7月,0) AS 売上, 4 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 8 AS 月, ISNULL(売上8月,0) AS 売上, 5 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 9 AS 月, ISNULL(売上9月,0) AS 売上, 6 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 10 AS 月,ISNULL(売上10月,0) AS 売上, 7 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 11 AS 月,ISNULL(売上11月,0) AS 売上, 8 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 12 AS 月,ISNULL(売上12月,0) AS 売上, 9 表示順 FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 1 AS 月, ISNULL(売上1月,0) AS 売上, 10 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 2 AS 月, ISNULL(売上2月,0) AS 売上, 11 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
  UNION SELECT 商品CD, 3 AS 月, ISNULL(売上3月,0) AS 売上, 12 表示順  FROM dbo.商品売上 WHERE 年度 = @年度
       ) SU
 ORDER BY 商品CD, 表示順;

 各月のデータを抽出し、UNIONで結合したものを1つのテーブルのようにみなして、そこからデータを抽出するというかたちです。
※結果を4月から順に上から並べたいので、表示順という項目をつけてみました。

上記はORACLEでも同様に行うことができます。

SELECT SU.商品CD, SU.月, SU.売上
  FROM
       (SELECT 商品CD, 4 AS 月, NVL(売上4月,0) AS 売上, 1 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 5 AS 月, NVL(売上5月,0) AS 売上, 2 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 6 AS 月, NVL(売上6月,0) AS 売上, 3 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 7 AS 月, NVL(売上7月,0) AS 売上, 4 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 8 AS 月, NVL(売上8月,0) AS 売上, 5 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 9 AS 月, NVL(売上9月,0) AS 売上, 6 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 10 AS 月,NVL(売上10月,0) AS 売上, 7 表示順 FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 11 AS 月,NVL(売上11月,0) AS 売上, 8 表示順 FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 12 AS 月,NVL(売上12月,0) AS 売上, 9 表示順 FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 1 AS 月, NVL(売上1月,0) AS 売上, 10 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 2 AS 月, NVL(売上2月,0) AS 売上, 11 表示順  FROM 商品売上 WHERE 年度 = '2015'
  UNION SELECT 商品CD, 3 AS 月, NVL(売上3月,0) AS 売上, 12 表示順  FROM 商品売上 WHERE 年度 = '2015'
       ) SU
 ORDER BY 商品CD, 表示順;

SQL Serverのときと同様の結果が表示されるのが確認できました。

今日は以上まで

にほんブログ村