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

前回紹介したさきたま古墳のそばに、古代蓮の里という公園（行田市の天然記念物であり市の花である「古代蓮（行田蓮）」をシンボルとする公園）があり、そこに行った時の写真です。6月下旬から8月上旬にかけて42種類12万株の花蓮を見ることができるということですが、花弁の数が少ない原始的な形態を持つ行田蓮は、約1400～3000年前の蓮であると言われています。 午前中、正午くらいまでが見ごろのようですよ！ 何か神秘的な雰囲気もあり、結構きれいですね。

＜本日の題材＞ 外部結合について（SQL Server 編） 前回実施した外部結合の内容を、SQL Server2012でも試してみましたので、報告します。

EMP表(従業員表)とDEPT表(部署表)をDEPTNOを条件として外部結合する例です。

１．左外部結合： LEFT [OUTER] JOIN の例 （[OUTER]は省略可能）

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.EMP E LEFT OUTER JOIN dbo.DEPT D ON E.deptno = D.deptno ORDER BY E.empno;

２．右外部結合： RIGHT [OUTER] JOIN の例

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.DEPT D RIGHT OUTER JOIN dbo.EMP E ON D.deptno = E.deptno ORDER BY E.empno;

３．完全外部結合： FULL [OUTER] JOIN

SELECT E.empno, E.ename, E.job, D.dname, D.deptno FROM dbo.EMP E FULL OUTER JOIN dbo.DEPT D ON E.deptno = D.deptno ORDER BY E.empno;

SQL Server2012でも同様な内容を確認しました。ちなみに、SQL Server2000の頃は、外部結合は *= という演算子を使っていましたが、データベースの互換性レベル(モード)が90以上ではこの演算子はサポートされなくなっていますね。 Oracleも昔は (+) というのを使用していましたが。。。

今日は以上まで

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

映画「のぼうの城」で有名な忍城のある埼玉県行田市にあります、さきたま古墳に行ってきました。前方後円墳8基と円墳1基の5～7世紀ごろの大型古墳が残る、全国有数の大型古墳群です。写真は、直径105m、高さ約19mの日本最大の円墳である丸墓山古墳の頂上から見た稲荷山古墳です。

ところで、ここのところ、大手通信教育の会社の個人情報漏えい事件、マレーシア航空機の撃墜事件、中国の食品会社の使用期限切れ食肉使用問題、イスラエルとパレスチナの紛争など、様々な事件や問題が世界中で起きていることを感じます。

個人情報漏洩の件では、私も以前、協力会社のＳＥとして、ある大手会社の個人情報を扱う立場にいたこともありましたが、そこでは定期的に協力会社の社員にも個人情報に関する教育やＷＥＢでのテストなどを実施して、危機意識を高める努力をしていたこともありますし、何か起きたときに真っ先に疑われるのが協力会社のＳＥということも感じていたので、とても注意して作業をしていました。

2003年に成立し、2005年から施工された個人情報保護法が、ビッグデータの利活用に関連して来年には改正される方向ですが、今回の件も考慮したかたちでの対応が必要と思われますね。

うちの子供もまさに今回問題となった通信教育を受けていて、名簿を買ったといわれる企業から急にＤＭが届いたので、何で急に？と不思議に思ったのを覚えています。
しかし、成人式が近い子供の振袖の宣伝やら、高校卒業が近い子供への各種専門学校からのＤＭ、その他塾や通信教育の宣伝、本当にたくさんのＤＭが日々送られてきますね。どこの会社も頑張っているのはわかるのですが。
ときに、ＤＭを包んでいるプラスチックゴミの分別など、手間がかかるなと感じてしまう私でした。

＜本日の題材＞
外部結合について

SELECT文で、複数のテーブルを結合して結果を出す場合に、ある条件で、両方のテーブルに一致する値を持つレコードだけでなく、一方のテーブルにしかないレコードも一緒に表示させたいケースも結構あります。今回は、外部結合についてちょこっと再確認してみます。

外部結合の種類と構文：
１．左外部結合： LEFT [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA LEFT [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

左側の表(テーブルA)の内容を全て抽出し、右側の表(テーブルB)は　　条件で一致するレコードのみを表示します。

２．右外部結合： RIGHT [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA RIGHT [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

右側の表(テーブルB)の内容を全て抽出し、左側の表(テーブルA)は　　条件で一致するレコードのみを表示します。

３．完全外部結合： FULL [OUTER] JOIN
SELECT .., .., ..
FROM テーブルA FULL [OUTER] JOIN テーブルB
ON テーブルA.列名 = テーブルB.列名

左側の表(テーブルA)、右側の表(テーブルB)とも条件に一致しない　　レコードも含めてすべて表示します。

例）よくある例としては、EMP表(従業員表)とDEPT表(部署表)があって、DEPTNOという部署コードが2つのテーブルをジョインするキーとなっている場合で、従業員と所属部署の一覧を見たいというときに外部結合を使用する例です。このとき、新しい従業員を雇ったのでEMP表にデータを登録するのですが、所属部署は設定せず、後から決めるというときに、その時点での一覧を見ると通常の結合（内部結合）では部署コードのない新入社員が抽出されないため、以下のように外部結合で抽出します。

新入社員の登録：
INSERT INTO EMP(empno,ename,hiredate) VALUES(8000,'SATOU','2014-07-20');

従業員と所属部署の一覧：
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM emp E
LEFT OUTER JOIN dept D ON E.deptno = D.deptno
ORDER BY E.empno;

上記は左外部結合ですが、これを右外部結合で表すと、
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM dept D
RIGHT OUTER JOIN emp E ON D.deptno = E.deptno
ORDER BY E.empno;

さらに、完全外部結合で表すと、DEPT表には存在するが、EMP表ではその部署コードを持つ従業員が存在しない部署も表示されます。
SELECT E.empno, E.ename, E.job, D.dname, D.deptno
FROM emp E
FULL OUTER JOIN dept D ON E.deptno = D.deptno
ORDER BY E.empno;

今回はOracleを中心に確認しました。
今日は以上まで

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

娘が高校の遠足でディズニーランドに行ってきました。
同じ日に複数の学校から遠足で学生が来て、人も多かったみたいですが、楽しかったようです。

5月は、開発案件の関係で忙しく、緊張した日々が続きました。まだ一部残っている部分がありますが、最後まで気を抜かず、お客様に喜んでもらえるよう頑張らねば！

開発で使用しているツールはdbSheetといい、ExcelやAccessを使いながらWebシステムを構築できるツールです。なかなかおもしろいですよ。
http://www.newcom07.jp/dbsheetclient/index.html

今日は、少し話題を変えて我が家の様子などを紹介してみたいと思います。

仕事が遅くなって家に帰ると、妻がご飯を用意して待ってくれています。疲れて寝てるときもあったり、ときに遅くまで出かけてる時もありますが。。。

妻は、話をし始めたら止まらなくなるとてもおしゃべりタイプで、いろいろとその日の出来事などをおもしろおかしく教えてくれます。仕事のことでいっぱいだった頭も、いろいろな妻の話で気分転換されることが多く、内心はとても助かっています。

仕事で心配なことでもときに話してみると、結構冷静に、また前向きに元気づけてくれます。私よりも多分に気が強く、男勝りな部分もあるので、結構力強く背中を押してくれるので、ありがたい。ただ、機嫌が悪くなるとなかなか手が付けられなくなる部分もあるのが困りものですが。（ハイ～～）

また、子供たちも上は専門学校生、下は小学3年生と、なかなか幅が広く人数も多いのですが、それぞれに性格が違って、なかなかおもしろいです。
何も言わなくても一生懸命に勉強を頑張って、成績表をうれしそうに見せてくれる子もいれば、勉強はきらいだと、なかなかやる気が出ないけれど、遊ぶことについてはいろいろと知恵を使って新しい遊びを考えたりする子とか。。。

皆をまっすぐに育てるのが親の責任なんでしょうが、なかなか大変ですね。

ITコーディネータの資格の更新も6月末まで（毎年5月末までだったのが、今年は事情があって6月末まで）なので、遅れないように更新手続きもしないと！

ながながと痴話話につきあって頂き、ありがとうございました。
今日は以上まで

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

お久しぶりです。

＜本日の題材＞
比較関数について

データベースの違いによって、あるDBでは使用できる関数が、別のDBでは使用できないケースがあります。その一例として、複数項目の最大値・最小値を取得するSQLを、今回は見てみたいと思います。

データベースがOracleの場合には、GREATEST関数・LEAST関数というのがあります。

例）金額に関する3つの項目の中から最大値を求める。具体的には

item_no | priceA | priceB | priceC |
-----------------------------------------------------
item01  |  6000 |  2000 |  3000
item02  |  1000 |  7500 |  4000
item03  |  1500 |  3000 |  8000

というテーブルがあった場合に、以下のような結果を出したいとします。

item_no | 最大値 | 最小値
------------------------------------------
item01  |  6000 |  2000
item02  |  7500 |  1000
item03  |  8000 |  1500

SQL> SELECT item_no
, GREATEST( priceA, priceB, priceC ) AS 最大値
, LEAST( priceA, priceB, priceC ) AS 最小値
FROM テーブル名;

これは、MySQLでも同様の関数があります。
　GREATEST()、LEAST()

上記をSQL Serverで行おうとすると、同様の関数がないため、下記のようなSQLを実行するかたちになります。

SELECT  item_no, MAX(price1) AS 最大値, MIN(price2) AS 最小値 FROM
(           SELECT item_no, priceA AS price1, priceA AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceB AS price1, priceB AS price2 FROM テーブル名
UNION ALL SELECT item_no, priceC AS price1, priceC AS price2 FROM テーブル名
) AS TBL
GROUP BY item_no
ORDER BY item_no;

今日は以上まで

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

春先に京都に行った時の金閣寺の写真です。せっかく撮っていたのでアップします。

金閣寺は正式には鹿苑寺といい、写真の金閣（舎利殿）を含む鹿苑寺が世界文化遺産として登録されています。現在の金閣は、三島由紀夫の小説金閣寺にもあるように、1950年の失火で国宝であった当時の金閣が全焼した後、1955年に再建されたものです。
再建にあたっては焼失直前の姿ではなく創建時の姿を再現するとの方針が採られ、現在のような金色に光る豪華なかたちになったとのこと。
ただ、国宝や重要文化財がこのような人為的な火災や、災害などで損失してしまうことは本当に残念です。文部科学省でもこのような文化財の総合防災対策などリスク管理についてはいろいろと検討・対策を取られているようです。

実は、最近仕事も忙しくなり、少々疲れ気味。久しぶりの投稿です。
体調管理も含め、リスク管理は本当に重要ですね！

＜本日の題材＞
日付関数についての続き（年齢計算）

システムでは、生年月日をデータベースの項目に持つテーブルが存在するケースが非常に多いですね。日付の計算に関連して、今回は年齢の計算について見てみたいと思います。

データベースがOracleであれ、SQL Server であれ、年齢計算においては、以下の2つの内容を確認することになるかと思います。
１）生年月日と基準日(通常は今日)の「年」の差
２）生年月日と基準日(通常は今日)の「月日」の大小を比較し、基準日が誕生日に達してなければ -1

SQLとしてはいろいろなやり方があると思いますが、以下に例を挙げます。

Oracle：
SELECT
EXTRACT(YEAR FROM 基準日) - EXTRACT(YEAR FROM 誕生日) -CASE WHEN TO_CHAR(基準日, 'MMDD') < TO_CHAR(誕生日, 'MMDD') THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

SQL Server：
SELECT YEAR(基準日) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , 基準日, 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

※YEAR(基準日) - YEAR(生年月日) は、DATEDIFF(YEAR,生年月日,GETDATE())でも同じ

例）
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, YEAR(GETDATE()) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , getdate(), 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

また、簡単に計算する方法として、日付型(yyyymmdd)を8桁数値に変換後に10000で割って小数点を切り捨てる方法もよく使われるようです。

Oracle：
SELECT TRUNC((TO_CHAR(基準日,'YYYYMMDD') - TO_CHAR(生年月日, 'YYYYMMDD')) /10000, 0) AS 年齢 FROM テーブル名;

※TRUNC関数は切り捨てで、第2引数が少数第何位での切り捨てかを示す。

SQL Server：
SELECT
ROUND((CONVERT(INT,CONVERT(VARCHAR(8),基準日,112))-CONVERT(INT,CONVERT(VARCHAR(8),生年月日,112))/10000,0,1) AS 年齢
FROM テーブル名;

※ROUND関数の第3引数が0以外の場合は切り捨てになります。また、FLOOR関数を使用すると、引数に対して、それ以下で最も大きい整数値を取るので同じことになります。
※閏年生まれの方も、3月1日時点できちんと年齢が上がります。

例）
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, ROUND((CONVERT(INT,CONVERT(VARCHAR,getdate(),112))-CONVERT(INT,CONVERT(VARCHAR,生年月日,112)))/10000,0,1) AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

結果は最初の処理と同じ。

注意）日本の法律（年齢計算に関する法律）上は、年齢というのは誕生日の前日で加算されるもののため、計算結果の利用目的によっては上記のような計算に1日加算するなど調整が必要になります。年齢を元に何らかの計算するような場合、年齢計算を法律に則って行うのか、一般慣習に基づいて行っていいのかよく調査してから計算式を決定する必要があります。

★また、EXCELからデータをアクセスできる環境であれば、EXCELのほうで年齢計算をできる（DATEDIF関数）ので、EXCEL側で年齢を表示させることが簡単にできます。

【書式】
DATEDIF(開始日,終了日,単位)
【単位の引数】
"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の日数
"MD" 開始日から終了日までの日数
"YM" 開始日から終了日までの月数
"YD" 開始日から終了日までの日数

今日は以上まで

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

先週末の雨と風で桜が結構散ってしまいましたね。これはその前に撮った家の近くの庁舎にある桜の写真です。

４月に入り、子供たちもいよいよ始業式、入学式の時期になりました。わが家も子供たちが学校へ行く準備で慌ただしくしている朝の日々が戻ってきました。

朝になって、子供たちからあれがない、これがない、これを準備して！とか言われると本当に大変ですよね。前の日の夜に次の日の準備をしっかりさせるように躾けることが大事だとつくづく感じます。

＜本日の題材＞ 日付関数についての続き

前回、日付型関数について少し話をしましたが、日付の計算などについて今回は見てみたいと思います。

①    何日後・何日前など、日にちを足し引きする場合：
Oracle：
　DATE型の項目に対して、足し引きしたい日数を単純にプラス 、マイナスすればよい。

SQL Server：
  DATEADD関数　(構文：DATEADD(datepartパラメータ, number, date) )でパラメータに dayを使用する。

※追記）
Oracleと同様に、足し引きしたい日数をプラスマイナスも可能です。ただし、Oracleの場合は、日数だけでなく、時間・分・秒なども 1/24 、1/24/60、1/24/60/60 などを足し引きすることで計算が可能ですが、SQL Serverでは日数までです。

例）購入日(DATE型)の7日後、7日前
Oracle：
    SELECT 購入日
　　　　　　,　購入日+7  AS 購入日の7日後
　　　　　　,　購入日-7  AS 購入日の7日前
      FROM テーブル名;

購入日が ’2014-03-25’ だった場合、結果は以下のようになる：
　　　購入日         　　　購入日の7日後    購入日の7日前
　　　-----------------      -------------------     --------------------
　　　14-03-25       　　14-04-01          　14-03-18

SQL Server：
    SELECT  購入日
　　　　　　,  DATEADD(day, 7, 購入日)  AS 購入日の7日後
　　　　　　,  DATEADD(day, -7, 購入日)  AS 購入日の7日前
      FROM テーブル名;

※Oracleと同様の、+7、-7 などの計算も可能です。

 
②    何か月後・何か月前など、月数を足し引きする場合：
Oracle：
ADD_MONTHS関数(構文：ADD_MONTHS (date型項目, integer) )。

SQL Server：
  DATEADD関数(構文：DATEADD(datepartパラメータ, number, date型項目) )でパラメータに month を使用する。

例）購入日(DATE型)の月末日の１ヶ月後、１ヶ月前
Oracle：
  SELECT   LAST_DAY(購入日)  AS 購入日の月末日
　,  LAST_DAY(ADD_MONTHS(購入日,1))  AS 購入日の翌月末日
　,  LAST_DAY(ADD_MONTHS(購入日,-1))  AS 購入日の前月末日 　FROM テーブル名;

   購入日が ’2014-03-25’ だった場合、結果は以下のようになる：
　    購入日の月末日     購入日の翌月末日     購入日の前月末日
       --------------------      ----------------------         -----------------
       14-03-31                  14-04-30                        14-02-28

SQL Server (2012)：
    SELECT      EOMONTH(購入日)  AS 購入日の月末日
  ,  EOMONTH(DATEADD(month, 1,購入日))  AS 購入日の翌月末日
  ,  EOMONTH(DATEADD(month, -1,購入日))  AS 購入日の前月末日     FROM テーブル名;

※ただし、上記の月末日を取得する EOMONTH関数は、SQL Server2012から使える機能です。それ以前の場合には、翌月１日から１日引いた日付を取得するかたちが一般的ですね。いろいろな書き方があると思いますが、例えば以下のような文で今月の月末日を抽出できます。

SELECT DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,+1,GETDATE())) + '-' + DATENAME(MONTH,DATEADD(MONTH,+1,GETDATE())) + '-' + '01') as 当月末

★前回と同様、EXCELからデータをアクセスできる環境であれば、EXCELのほうで月末日を取得する関数（EOMONTH関数）があるので、EXCEL側で月末日を表示させることが簡単にできます。

今日は以上まで

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

会社の近くで撮った写真、桜が見事に咲きました！
遠方に見えるのは、さいたま新都心のビル群

WindowsXPのサポート期限切れが迫ってきました。4月9日です。Office2003も同一日でサポートが切れますね。

先日(3/6~7)、東京駅のすぐ隣のＪＰタワーで行われたSecurity Days 2014でもそのことを取り上げて、セキュリティに注意を促すセッションを行うところも結構ありました。日本MSの発表では、XPとWindows8.1ではウィルス感染率に21倍もの差があるとか。。。

昨年の11月の調査時点で、サポート終了予定から約1年が経過した2015年3月時点でも、18.2％のPCがWindows XPで稼動予定という矢野経済研究所の調査報告(http://enterprisezine.jp/article/detail/5403)があり、総務省の2013/11/22の発表では自治体でも26万6千台が期限切れになるという予想報告や、最近でも個人でXPを使用している人がまだ4人に一人という調査結果を出しているところもあります。

問題は、サポートが切れることで、新しく発見された脆弱性に対してXPについてはセキュリティパッチが提供されなくなるため、その脆弱性を狙った攻撃があればそれによる被害が発生してしまう危険性が高いということです。最近は標的型攻撃とよばれるサイバー攻撃が非常に増えてきているということなので、それに対応した防御も十分考える必要が出てきていますね。

＜本日の題材＞
日付関数について

前回、日付型にどういうものがあるかという話をしましたが、日付型項目の値を表現したり計算したりする場合に、関数を使用するケースが随所に出てきます。その関数についても、OracleとSQL Serverでは違いがあるので、整理しておくと便利かと思います。

まず、よく使うのがシステム日付。

Oracle：SYSDATE
SQL Server：GETDATE()

実際にSQLでシステム日付を確認するのは、
Oracleでは、
SELECT SYSDATE FROM dual;
SQL Serverでは、
SELECT GETDATE();

そして、日付型項目を日本語の日付表現に直して表すとき、西暦の4桁の年月日までか、年月までか、年だけの表記にするか、それとも時刻まですべて表現するのかなど、いろいろとシステムによって項目ごとに使い分ける場合があると思います。

今回は、この日付のフォーマットに関して少し上げてみたいと思います。

Oracleの場合は、TO_CHAR関数を使用して、フォーマットを設定しますが、SQL Serverの場合は、CONVERT関数を利用することが多いと思います。

例）
①yyyy/mm/dd という表示にしたい
Oracle：
SELECT TO_CHAR(日付項目, ‘YYYY/MM/DD’) FROM テーブル名;
SQL Server：
SELECT CONVERT(VARCHAR, 日付項目, 111) FROM テーブル名;

②yyyy-mm-dd hh:mi:ss というように日付+時刻(24時間表記)で表示したい
Oracle：
SELECT TO_CHAR(日付項目, ‘YYYY-MM-DD HH24:MI:SS’) FROM テーブル名;

SQL Server：
SELECT CONVERT(VARCHAR, 日付項目, 120) FROM テーブル名;

結果はこんな感じ：
2014-03-31 14:46:39

③和暦で表示したい
Oracle：
SELECT TO_CHAR(日付項目, 'EEYY"年"mm"月"dd"日"', 'nls_calendar = ''Japanese Imperial''') 日付 FROM テーブル名;

結果はこんな感じ：
日付
----------------------------
平成26年3月31日

※Oracleの書式について
E ： 元号の略語（大正:T、昭和:S、平成:H）
EE： 元号（大正、昭和、平成）

和暦変換する場合は'nls_calendar = ''Japanese Imperial'''で「nls_calendarオプション」のカレンダを指定するかたちになります。

SQL Server：
SQL Serverでは、実は和暦に変換する関数は持っていないため、SQLを駆使してこれを実現するケースが多いと思います。例えば元号を表すためには

SELECT
Case when 日付項目 > '19890107' then '平成'
when日付項目 > '19261224' then '昭和'
when日付項目 > '19120729' then '大正'
when日付項目 > '18680124' then '明治'
else 'その他' end 年号
というようなかたちでSQLで設定が必要です。

ただし、例えば結果をEXCELで表示するというように、EXCELからデータをアクセスできる環境であれば、EXCELのほうで日付データに対して和暦の表示を行うことができるので、SQL Serverのデータでも、簡単に和暦に変換することが可能になります。

EXCELはいろいろと関数を持っているので、使えば結構便利ですよね。

なお、SQL ServerのCONVERT関数についての仕様やパラメータについては、以下のサイトで確認できます。
http://msdn.microsoft.com/ja-jp/library/ms187928.aspx

今日は以上まで