●2010年3月15日(月)
英語 US-OTN --- Distinct combination of Data

●2010年3月20日(土)
Club DB2の3月26日(金)の19:00-21:00の、「第98回 【SQL上級編】 DB2の分析関数の使用例」で、
私が講師をさせていただくことになりました。

配布資料と、講演で使う資料を作成して
別館とまとめページにアップロードしておきました。

80分で28項目なので、1項目あたり3分のペースなので、
OracleOpenWorldの分析関数とmodel句と同様に予習復習を前提とした勉強会ということにします :-)

*********************************************************************
タイムスケジュールを作ってみた。

OOW2009は、30分で20項目で、30/20 = 1分半で、
PgCon2009も、50分で26項目で、50/26 = 1分半でしたので、
今回は、30秒も多い :-)

・19:00-19:20  Club DB2より、開始の挨拶
・19:20-19:22  1. 分析関数とは
・19:22-19:24  2. select文の件数取得
・19:24-19:26  3. except allとcount(*) over()
・19:26-19:28  4. 最大値の行の取得
・19:28-19:30  5. 順位を付ける
・19:30-19:32  6. 最大値の行の取得(ソートキーが複数)
・19:32-19:34  7. 前後の値
・19:34-19:36  8. 累計
・19:36-19:38  9. 移動累計
・19:38-19:40 10. First_ValueとLast_Value
・19:40-19:42 11. First_ValueとLast_Value(IGNORE NULLS)
・19:42-19:44 12. 全称肯定,全称否定,存在肯定,存在否定
・19:44-19:46 13. 最頻値(モード)
・19:46-19:48 14. 連続範囲の最小値と最大値 (2人旅人算)
・19:48-19:50 15. 連続範囲の最小値と最大値 (3人旅人算)
・19:50-19:52 16. 次の入社日を求める
・19:52-19:54 17. 次の次の入社日を求める
・19:54-19:56 18. 2日前の値を求める
・19:56-19:58 19. update文で分析関数の値に更新
・19:58-20:00 20. delete文で重複行を削除
・休憩10分
・20:10-20:12 21. count(distinct Val) over(partition by ID)
・20:12-20:14 22. count(distinct Val) over(order by sortKey)
・20:14-20:16 23. sum(Val) Keep(Dense_Rank Last
・20:16-20:18 24. Range '10' minute Preceding
・20:18-20:20 25. Range '1' month Preceding
・20:20-20:22 26. nth_Value
・20:24-20:26 27. ListAggとwmsys.wm_concat
・20:26-20:28 28. 中央値(メジアン)
・20:28-20:40 予備
・20:40-20:50 質疑応答
・20:50-21:00 Club DB2より、終了の挨拶

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
勉強会関連
技術者のための プレゼン上達塾
Oracle OpenWorldの「裏メニュー」アンカンファレンスとは − ＠IT

勉強会を楽しむなら発表しよう！ | Shin x blog
5分で絶対に分かるテクニカルトーク − ＠IT自分戦略研究所
勉強会に勉強だけをしに来るヤツは素人
レポート：このカンファレンスは伝説になる−「勉強会カンファレンス2009」開催｜gihyo.jp

エンジニアの勉強法について
IT業界で楽しく仕事をするための10カ条

京都の地より、ＩＴ勉強会を再考してみる #2

●2010年3月25日(木)
OTNJ --- 経路の各レベルの値をPivot
再帰with句と階層問い合わせは、使い分けようという話。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
では明日、IBM箱崎事業所で会いましょう :-)

twitterでも数ヶ月ぶりにつぶやいてみた

●2010年3月27日(土)
10-331 可変個のカンマ区切りの数字でソート

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Club DB2の3月26日(金)の19:00-21:00の、「第98回 【SQL上級編】 DB2の分析関数の使用例」は、
無事に終了しました。どうもありがとうございました。

少し話しましたが、DB2の分析関数編に続いて、
第二段、DB2の再帰SQL編もあるかもしれません。

再帰SQLは、まだ慣れてないので、あるとしても、半年以上先ですが

●2010年3月30日(火)
まとめページを作るのが面白いので、
SQLServerの分析関数の使用例なんてページを作ってみました。
1部と2部で違った面白さがあります。

●2010年3月31日(水)
英語 US-OTN --- I have used "Boolean algebra in some Rows" B-)
存在肯定命題の真偽で論理演算

英語 US-OTN --- I like recursive with clause
再帰with句で行を作成

●2010年4月8日(木)
Oracle11gR2 windows32ビット版

再帰with句で非再帰項と再帰項のdate型の列同士の型チェックが異様に厳しいといった話
dump関数の結果が同じなのに、なぜか明示的にcast関数を使わないといけないようだ
US-OTN --- ORA-01790 from 11gR2 Recursive with clause
データタイプ（内部形式） - オラクル・Oracleをマスターするための基本と仕組み

多分、内部的に、Typeが13から12に変更されて、
なおかつ、再帰with句のunion allでは、Type12と13では、型が違うと判定してますね。
なのでcast関数でTypeを12に明示的に変換する必要があるようです。

create table typeT as select date '2010-04-08' dayc from dual;
col c1 for a40
col c2 for a40

select dump(dayc) as c1,dump(date '2010-04-08') as c2 from typeT;

C1                               C2
-------------------------------  -------------------------------
Typ=12 Len=7: 120,110,4,8,1,1,1  Typ=13 Len=8: 218,7,4,8,0,0,0,0


select dump(date '2010-04-08') as c1,
       dump(cast(date '2010-04-08' as date)) as c2 from typeT;

C1                               C2
-------------------------------  -------------------------------
Typ=13 Len=8: 218,7,4,8,0,0,0,0  Typ=12 Len=7: 120,110,4,8,1,1,1

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
US-OTN --- wrong result of 11gR2 Recursive with clause

Oracle11gR2では、select句にdate型やtimestamp型がある再帰with句は、まともに動作しないようですねぇ

Recursive SQL giving ORA-01790 - Stack Overflow

●2010年4月17日(土)
wmsys.wm_concatとListAgg関数の違い

・ListAgg関数では、keep指定が使えない
・ListAgg関数では、distinctオプションが使えない
・ListAgg関数では、分析関数として使ったときにover句でのorder byが指定できない

SQL> select ListAgg(distinct ENAME) withIn group(order by ENAME) as ConcatVal
  2    from scott.emp;
select ListAgg(distinct ENAME) withIn group(order by ENAME) as ConcatVal
       *
行1でエラーが発生しました。:
ORA-30482: この機能にDISTINCTオプションは使用できません

SQL> select ListAgg(ENAME) withIn group(order by ENAME) over(order by ENAME desc) as ConcatVal
  2    from scott.emp;
select ListAgg(ENAME) withIn group(order by ENAME) over(order by ENAME desc) as ConcatVal
                                                        *
行1でエラーが発生しました。:
ORA-30487: ここでORDER BYは使用できません。

●2010年4月18日(日)
US-OTN --- finding discontiguous numbers

with STORE_TRACKING as(
SELECT 1 id, 'STORE X' store, 100 tracking FROM DUAL union
SELECT 2, 'STORE X', 101 FROM DUAL union
SELECT 3, 'STORE X', 102 FROM DUAL union
SELECT 4, 'STORE X', 103 FROM DUAL union
SELECT 5, 'STORE X', 104 FROM DUAL union
SELECT 6, 'STORE X', 105 FROM DUAL union
SELECT 7, 'STORE X', 107 FROM DUAL union
SELECT 8, 'STORE X', 108 FROM DUAL union
SELECT 9, 'STORE X', 109 FROM DUAL union
SELECT 10, 'STORE X', 110 FROM DUAL union
SELECT 11, 'STORE X', 112 FROM DUAL union
SELECT 12, 'STORE X', 113 FROM DUAL union
SELECT 13, 'STORE X', 114 FROM DUAL union
SELECT 14, 'STORE X', 115 FROM DUAL union
SELECT 15, 'STORE Y', 901 FROM DUAL union
SELECT 16, 'STORE Y', 902 FROM DUAL union
SELECT 17, 'STORE Y', 903 FROM DUAL union
SELECT 18, 'STORE Y', 905 FROM DUAL union
SELECT 19, 'STORE Y', 906 FROM DUAL union
SELECT 20, 'STORE Y', 907 FROM DUAL union
SELECT 21, 'STORE Y', 908 FROM DUAL union
SELECT 22, 'STORE Y', 909 FROM DUAL union
SELECT 23, 'STORE Y', 910 FROM DUAL)
select store,
case grouping(dis) when 1 then 'Total'
     else to_char(min(tracking)) end as "Min",
case grouping(dis) when 1 then count(*)
     else max(tracking) end as "Max"
from (select store,tracking,
       tracking
      -Row_Number() over(partition by store order by ID) as dis
      from STORE_TRACKING)
group by store,rollup(dis);

STORE    Min    Max
-------  -----  ---
STORE X  100    105
STORE X  107    110
STORE X  112    115
STORE X  Total   14
STORE Y  901    903
STORE Y  905    910
STORE Y  Total    9

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
正規表現クックブックを買ってきました。寝る前の読書に最適です。

●2010年4月19日(月)
Oracle11gR2新機能 insert文でのIGNORE_ROW_ON_DUPKEY_INDEXヒント

SQL> create table dupTest(Val number(8) primary key);
表が作成されました。

SQL> insert into dupTest values(1);
1行が作成されました。

SQL> insert into dupTest values(3);
1行が作成されました。

SQL> insert into dupTest values(5);
1行が作成されました。

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(dupTest(Val)) */ into dupTest values(2);
1行が作成されました。

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(dupTest(Val)) */ into dupTest values(3);
0行が作成されました。

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(dupTest(Val)) */ into dupTest
  2  select RowNum from dict where rowNum <=10;
6行が作成されました。

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(dupTest(Val)) */ into dupTest
  2  select RowNum from dict where rowNum <=10;
0行が作成されました。

●2010年4月21日(水)
英語 US-OTN --- I like scalar subQuery

●2010年4月22日(金)
MSDN --- SQLServer2005でListAggもどき

PostgreSQL8.4で解いてみました :-)

with recursive work(ID,Code,Data) as(
values( 1, 'a', 'AA'),
      ( 2, 'a', 'BBBB'),
      ( 3, 'b', 'CCC'),
      ( 4, 'a', 'D'),
      ( 5, 'a', 'EEEE'),
      ( 6, 'b', 'FF'),
      ( 7, 'b', 'GG'),
      ( 8, 'a', 'H'),
      ( 9, 'c', 'II'),
      (10, 'd', 'JJ')),
tmp(ID,Code,Data,rn,recCnt) as(
select ID,Code,Data,Row_Number() over(partition by Code order by ID),
count(*) over(partition by Code)
  from work),
rec(KeyCol,Code,rn,DATACOL,recCnt) as(
select ID,Code,rn,Data,recCnt
  from tmp
 where rn=1
union all
select a.KeyCol,a.Code,b.rn,a.DATACOL || b.Data,a.recCnt
  from rec a,tmp b
 where a.Code=b.Code
   and a.rn+1=b.rn)
select *
  from rec
 where rn=recCnt
order by Code;

 keycol | code | rn |   datacol    | reccnt
--------+------+----+--------------+--------
      1 | a    |  5 | AABBBBDEEEEH |      5
      3 | b    |  3 | CCCFFGG      |      3
      9 | c    |  1 | II           |      1
     10 | d    |  1 | JJ           |      1

●2010年4月24日(土)
英語 US-OTN --- regular expression hangs

Javaで有名な強欲な量指定子は、
*ではなく+であることを度忘れしてた。

^a{0,2}*$
ではなく
^a{0,2}+$
ならJavaの正規表現としては正しいです。
Oracle11gR2や.NET2008の正規表現では、強欲な量指定子をサポートしてませんので、ダメですが。

そもそも、aが偶数個か調べるのであれば
^(a{2})*$
でいいのですが

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN --- My homepage mentions similar problem

US-OTN --- In tuning,I guess that It is basic to decrease TableScans.
Left Joinでminusをも兼ねさせてTableScanを減らそう。という話

英語 US-OTN --- Select Query in Want unique time. 
10-320 ユニークな時間にインクリメントしてselect

●2010年4月26日(月)
Ustream/Twitter時代の勉強会・講演会の注意点 - Vox

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
JavaScriptの絵本
基礎から学ぶOracle SQLチューニング
買ってきました。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN --- I like ListAgg :D

●2010年4月27日(火)
アルゴリズムクイックリファレンス
買ってきました。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLserverでコマンドプロンプトでSQLを実行する方法。
C:\>sqlcmd -S localhost\SQLEXPRESS

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN --- 再帰SQLで日付の補完
Oracleのフォーラムなのに何故かPostgreSQLが出てくるという不思議 :-)

英語 US-OTN --- missing numbersを求める
再帰with句の木を脳内でイメージするのに慣れてきました。

英語 US-OTN --- I like emulating Outer Union
3-38 Outer Union (外和集合演算)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
インフラエンジニアに成る:ネットワーク監視の夜勤を考える
夜早く寝る７つのコツ。
夜中のパソコンは、睡眠の質を低下させるので、自戒として

●2010年4月29日(木)
SETOF関数のあれこれ - 象と戯れ

postgres=# with work(Val) as(
postgres(# values(6),(7))
postgres-# select Val,generate_series(1,3)
postgres-#   from work;
 val | generate_series
-----+-----------------
   6 |               1
   6 |               2
   6 |               3
   7 |               1
   7 |               2
   7 |               3
(6 rows)

postgres=# select generate_series(1,3),generate_series(10,13);
 generate_series | generate_series
-----------------+-----------------
               1 |              10
               2 |              11
               3 |              12
               1 |              13
               2 |              10
               3 |              11
               1 |              12
               2 |              13
               3 |              10
               1 |              11
               2 |              12
               3 |              13
(12 rows)

9.22. 集合を返す関数
34.4.7. 集合を返すSQL関数

SQL関数がSETOF sometypeを返すよう宣言されている場合、
関数の最後の問い合わせは最後まで実行され、各出力行は結果集合の要素として返されます。

この機能は通常、関数をFROM句内で呼び出す時に使用されます。

現在、集合を返す関数は問い合わせの選択リスト内でも呼び出すことができます。
問い合わせ自身によって生成する各行に対し、集合を返す関数が呼び出され、
関数の結果集合の各要素に対して出力行が生成されます。
ただし、この機能は現在では推奨されておらず、今後のリリースでは削除される可能性があります。

そうかselect句でのgenerate_seriesは、非推奨ですか。
from句でgenerate_series関数の引数に、固定値かスカラーサブクエリを指定しなければならないとすると
下記のようなのは、実質使えないですね。

create table geneT1(ID,StartV,EndV) as(
values(111,1,5),
      (222,3,5),
      (333,7,9));

select ID,generate_series(StartV,EndV) as Val
  from geneT1;

 ID  | Val
-----+-----
 111 |   1
 111 |   2
 111 |   3
 111 |   4
 111 |   5
 222 |   3
 222 |   4
 222 |   5
 333 |   7
 333 |   8
 333 |   9

そこで再帰SQLの出番となるわけです :-)

with recursive rec(ID,StartV,EndV) as(
select ID,StartV,EndV from geneT1
union all
select ID,StartV+1,EndV from rec
where StartV+1 <= EndV)
select ID,StartV from rec order by ID,StartV;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
US-OTN --- 再帰with句で[^*]+で行に分割
PostgreSQLでは、regexp_split_to_table関数がありますが、
IDごとのようなケースでは、再帰SQLを使うか
select句でregexp_split_to_table関数を使うような非推奨なケースになるようですねぇ

OracleSQLパズル 10-25 1行を複数行に変換
PostgreSQLパズル 10-25 1行を複数行に変換

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
日付ごとの件数を求める。

with recursive work(Val) as(
values(date '2010-06-01'),
      (date '2010-06-01'),
      (date '2010-06-03'),
      (date '2010-06-03'),
      (date '2010-06-03'),
      (date '2010-06-04')),
rec(Val) as(
values(date '2010-06-01')
union all
select Val+1
  from rec
 where Val+1 <= date '2010-06-05')
select Val,(select count(*) from work b
             where b.Val=a.Val) as cnt
  from rec a;

    val     | cnt
------------+-----
 2010-06-01 |   2
 2010-06-02 |   0
 2010-06-03 |   3
 2010-06-04 |   1
 2010-06-05 |   0

generate_seriesを使ったほうがよさげですが

●2010年5月1日(土)
英語 US-OTN --- 階層問い合わせで組み合わせ問題
階層問い合わせと再帰with句のいいとこ取りなSQLで笑った :-)

col subPath for a20

with colours as
(select 1 id, 'red'   colour from dual union all
 select 2 id, 'blue'  colour from dual union all
 select 3 id, 'green' colour from dual),
rec(path,subPath,LV,maxLV) as(
select path,RegExp_Substr(path,'[^,]+',1,1),1,maxLV
from (select sys_connect_by_path(to_char(ID),',') as path,Level as maxLV
        from colours
      connect by prior id < id)
union all
select path,RegExp_Substr(path,'[^,]+',1,LV+1),LV+1,maxLV
  from rec
 where LV < maxLV)
select dense_rank() over(order by path) as PID,subPath
  from rec order by PID,LV;

PID  subPath
---  -------
  1  1
  2  1
  2  2
  3  1
  3  2
  3  3
  4  1
  4  3
  5  2
  6  2
  6  3
  7  3

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
10-324 範囲を過不足なく埋めるかのチェック
10-325 最小上界な行がなければ、最大下界を取得その1
10-326 最小上界な行がなければ、最大下界を取得その2

●2010年5月4日(火)
PostgreSQL9.0beta版のwindows版をインストールしました。
Installer version Version 9.0.0 Beta1

PostgreSQL9.0 のSQLの新機能

postgres=# select version();
                            version
----------------------------------------------------------------
 PostgreSQL 9.0beta1, compiled by Visual C++ build 1500, 32-bit

*********************************************************************************
PostgreSQL 9.0 の新機能 --- ウィンドウ関数と集約関数の強化
ROWS n PRECEDING/FOLLOWINGが追加されました。

これで移動累計がバッチリ :-)

with work(sortKey,Val) as(
values(1, 10),
      (3, 20),
      (5, 60),
      (7,100),
      (9,200))
select sortKey,Val,
sum(Val) over(order by sortKey rows 2 preceding) as moveSum1,
array_agg(Val) over(order by sortKey rows 2 preceding) as beseOfSum
  from work;

 sortKey | Val | moveSum1 |  beseOfSum
---------+-----+----------+--------------
       1 |  10 |       10 | {10}
       3 |  20 |       30 | {10,20}
       5 |  60 |       90 | {10,20,60}
       7 | 100 |      180 | {20,60,100}
       9 | 200 |      360 | {60,100,200}

*********************************************************************************
rows betweenも使用可能に

select Val,
array_agg(Val) over(order by Val rows between 2 preceding and 1 following) as agg1,
array_agg(Val) over(order by Val rows between current row and 1 following) as agg2,
array_agg(Val) over(order by Val rows between 1 following and unBounded following) as agg3
  from generate_series(1,9) as work(Val);

 val |   agg1    | agg2  |       agg3
-----+-----------+-------+-------------------
   1 | {1,2}     | {1,2} | {2,3,4,5,6,7,8,9}
   2 | {1,2,3}   | {2,3} | {3,4,5,6,7,8,9}
   3 | {1,2,3,4} | {3,4} | {4,5,6,7,8,9}
   4 | {2,3,4,5} | {4,5} | {5,6,7,8,9}
   5 | {3,4,5,6} | {5,6} | {6,7,8,9}
   6 | {4,5,6,7} | {6,7} | {7,8,9}
   7 | {5,6,7,8} | {7,8} | {8,9}
   8 | {6,7,8,9} | {8,9} | {9}
   9 | {7,8,9}   | {9}   | null

*********************************************************************************
PostgreSQL 9.0 の新機能 --- ウィンドウ関数と集約関数の強化
集約関数のarray_agg関数でorder byを指定可能になりました。

with tmp(Val) as(
values('a'),('b'),('c'),('d'),('e'))
select array_agg(Val order by Val) as ArrayAgg1,
       array_agg(Val order by Val desc) as ArrayAgg2
  from tmp;

  arrayagg1  |  arrayagg2
-------------+-------------
 {a,b,c,d,e} | {e,d,c,b,a}

PostgreSQLパズル 9-56 集合が等しい組み合わせを求める

これでcontains述語もどきが、
    array_agg(Val1) @> array_agg(Val2)
and array_agg(Val1) <@ array_agg(Val2)
ではなく、
array_agg(Val1 order by Val1) = array_agg(Val2 order by Val2)
で実現できるようになり、重複を許可した集合でのcontains述語もどきが可能になりましたね。

window関数のarray_agg関数でorder byを指定すると、文法エラーになります。

select array_agg(123 order by 456) over();
ERROR:  aggregate ORDER BY is not implemented for window functions

*********************************************************************************
PostgreSQL 9.0 のその他の新機能 --- string_agg 関数
string_agg関数が追加されました。
array_agg関数と同様に、集約関数のstring_agg関数であれば、order byも指定可能のようです。

with tmp(ID,Val) as(
values(1,'a'),
      (1,'b'),
      (1,'c'),
      (2,'d'),
      (2,'e'))
select ID,
string_agg(Val order by Val desc) as StrAgg1,
string_agg(Val,'+' order by Val desc) as StrAgg2,
string_agg(string_agg(Val,'+' order by Val),'---') over() as StrAgg3
  from tmp
group by ID;

 id | stragg1 | stragg2 |   stragg3
----+---------+---------+-------------
  1 | cba     | c+b+a   | a+b+c---d+e
  2 | ed      | e+d     | a+b+c---d+e

window関数のstring_agg関数でもorder byを指定すると、文法エラーになります。
select string_agg('aaa' order by 456) over();
ERROR:  aggregate ORDER BY is not implemented for window functions

●2010年5月5日(水)
英語 US-OTN --- getting values from a specific row in a table
英語 US-OTN --- In tuning,It is basic to decrease tableScans. :D

英語 US-OTN --- 最大下界な年月のsumを求める
dense_rankとrange指定のsum関数の組み合わせ
8-55 次の入社日を求めるのアレンジ問題1

●2010年5月7日(金)
第103回 達人がみんなにわかるように話す！データベース徹底指南（ゲスト講師：ミックさん）
5/28 Club DB2 に講師として参加します - ミックのブログ 〜君につけ届け〜
これは、参加したいですねぇ

達人に学ぶ SQL徹底指南書の問題を、DB2 V9.7の分析関数とか再帰SQLを使って、
華麗に解く勉強会なんてのも面白そうですねぇ

●2010年5月8日(土)
英語 US-OTN --- Update table from other table HAVING NO PRIMARY KEY CONSTRAINT
merge文のon句では、括弧が必要なのを度忘れしてしまった :-)

マニュアル --- Merge

●2010年5月12日(水)
英語 US-OTN --- 1年以上前かを判定
分析関数のrange指定でのInterVal型の使用例

英語 US-OTN --- 複数列をUnPivot
UnPivotの構文は、たまに忘れる

●2010年5月15日(土)
・OracleOpenWorld2009Tokyoの"分析関数とmodel句"
・PostgreSQLConference2009JapanのPostgreSQL8.4新機能 window関数
・ClubDB2第98回の【SQL上級編】 DB2の分析関数の使用例
に続く、予習復習を前提とした勉強会の第4段のまとめページを開設しました。
DB2でSQL徹底指南書の問題を華麗に解く

google検索 --- 予習復習を前提

●2010年5月16日(日)
@IT会議室 --- SQLServerの分析関数の使用例
OracleSQLパズル 4-23 model句でnullの行を詰める
model句の使いどころかもしれませんねぇ

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PostgreSQLのarray_agg関数の使い方

with work(sortKey,Val) as(
values(1,'a'),
      (3,'a'),
      (5,'b'),
      (7,'c'),
      (8,'a'),
      (9,'d'))
select sortKey,Val,
(select count(distinct b.Val)
   from unnest(a.Vals) as b(Val)) as discnt
from (select sortKey,Val,
      array_agg(Val) over(order by sortKey) as Vals
      from work) a;

 sortkey | val | discnt
---------+-----+--------
       1 | a   |      1
       3 | a   |      1
       5 | b   |      2
       7 | c   |      3
       8 | a   |      3
       9 | d   |      4

マニュアル --- unnest(anyarray)

●2010年5月17日(月)
11g R2 for Windows キャンペーン│日本オラクル
これは凄い

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLServerの分析関数の使用例 --- 11. update文で分析関数の値に更新
SQLServerは、with句で分析関数を使って、UpdatableViewとかDeletableViewを定義できるようだ

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
MSDN --- 文字列から数字を取得

●2010年5月19日(水)
10-327 最大下界の行でupdate

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracle 10gの新機能「Partitioned Outer Join」(外部結合で行の補完)が一般公開されました。
Partitioned Outer Joinの脳内のイメージは、1年前に作ったものです。

●2010年5月20日(木)
ClubDB2のSQL上級編2の打ち合わせをしてきました。

ClubDB2で開催予定の勉強会です。
SQL徹底指南書1

●2010年5月22日(土)
英語 US-OTN --- rows unbounded precedingな累計

●2010年5月25日(火)
ClubDB2 SQL上級編2のまとめページを作成中
勉強会の講師になって、まとめページを作ったり、リハーサルしたりが、一番勉強になるという不思議 :-)

●2010年5月28日(金)
英語 US-OTN --- 階層問い合わせでノードの塊ごとにID付与1
英語 US-OTN --- 階層問い合わせでノードの塊ごとにID付与2
再帰with句よりも階層問い合わせを使うほうが、脳内で無向グラフをイメージしやすいですねぇ

階層問い合わせのnocycle指定は、再帰with句のcycle句より記述がシンプルですし、
階層問い合わせのconnect_by_rootは、再帰with句だとselect句の列を増やして模倣しなければいけませんし

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第103回 達人がみんなにわかるように話す！データベース徹底指南
に参加してきました。

数学の話とかRDBの歴史の話が興味深かったです。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracleのmodel句2 (行の補完)が一般公開されました。
再帰with句は、便利ですねぇ。

●2010年6月1日(火)
英語 US-OTN --- 再帰with句でカンマ区切りの文字列を行に分割
英語 US-OTN --- 再帰with句で日付型の補完

●2010年6月3日(木)
44のアンチパターンに学ぶ DBシステム
データベース実践講義
ASP.NET の絵本

買ってくる予定の本

●2010年6月4日(金)
英語 US-OTN --- I like Boolean algebra
in述語を使った全称否定命題

英語 MSDN --- Hehehe I have used PostgreSQL9.0
再帰SQLで、次の月がなかったら補完。
再帰SQLの木を脳内でイメージするのは、慣れました。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracleの階層問い合わせ7 (複雑な枝切り)が一般公開されました。
枝切りは、便利ですねぇ。

●2010年6月7日(月)
OracleSQLパズル 10-328 連続した3日のセットを出力できるだけ出力
分析関数と階層問い合わせでの枝切りが面白かった。

●2010年6月8日(火)
等結合と外部結合を条件に応じて使い分け
tbl1とtbl2をそれぞれnoカラムで結合しております。
そこでtbl1のjカラムが0なら等結合、
tbl1のjカラムが1なら外部結合としたいのですが
何かいい方法はないでしょうか。

from句の次にwhere句が評価されるのを利用してみました。
ついでにブール代数の公式
  _
A+A*B = A+B
も使ってみました。

with tbl1(no,moji,j) as(
select 1,'あ',0 from dual union all
select 2,'い',0 from dual union all
select 3,'う',1 from dual union all
select 4,'え',1 from dual),
tbl2(no) as(
select 1 from dual union all
select 4 from dual)
select *
  from tbl1 a Left Join tbl2 b
    on a.no = b.no
 where j=1 or b.no is not null;

NO  MO  J    NO
--  --  -  ----
 1  あ  0     1
 4  え  1     4
 3  う  1  null

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
まとめページを準備中
DB2でSQL徹底指南書の問題を華麗に解くパート

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
OTNJ --- case式で短絡and

MSDNJ --- OracleのLast_Value(ignore nulls)もどき

英語 US-OTN --- 可変個のドット区切りの16進数でソート

●2010年6月10日(木)
英語 US-OTN --- showing all months using outer join
再帰with句でdate型がある場合は、型が違うエラーになるケースが多いようだ。
例として、select句にdate型の列のみしかないケースは、ダメっぽい

英語 US-OTN --- 複数コードマスタを混ぜたテーブルとのJoin
結構見かけるケースですね。

●2010年6月12日(土)
US-OTN --- summarize records by date range
分析関数のrange指定の使いどころですねぇ
OracleSQLパズル 9-3 開始日と終了日をまとめる

●2010年6月13日(日)
プログラマのためのSQL第2版の70ページの分析関数を使った解

with TimeSheets(staD,endD) as(
select date '1998-01-01', date '1998-01-03' from dual union
select date '1998-01-05', date '1998-01-08' from dual union
select date '1998-01-06', date '1998-01-10' from dual union
select date '1998-01-20', date '1998-01-25' from dual union
select date '1998-01-18', date '1998-01-23' from dual union
select date '1998-02-01', date '1998-02-05' from dual union
select date '1998-02-03', date '1998-02-08' from dual union
select date '1998-02-07', date '1998-02-11' from dual)
select min(staD) as staD,max(endD) as endD
from (select staD,endD,
      sum(willSum) over(order by staD) as GID
      from (select staD,max(endD) as endD,
            case when staD >=max(max(endD)) over(order by staD
                                                 range between unbounded preceding
                                                           and 1 preceding)
                 then 1 else 0 end as willSum
            from TimeSheets
            group by staD))
group by GID
order by GID;

staD      endD
--------  --------
98-01-01  98-01-03
98-01-05  98-01-10
98-01-18  98-01-25
98-02-01  98-02-11

staDに重複がないならば、最初のグループ化は不要になります。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQL ゼロからはじめるデータベース操作
ミックさんの新刊

●2010年6月15日(火)
英語 US-OTN --- 再帰with句で欠番を全て求める

●2010年6月18日(金)
OracleのSQLのアンチパターンの問題集1を一般公開していただきました。
DBマガジン2009/05月号の特集であったアンチパターンを意識してます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
US-OTN --- Let us discussion at wmsys.wm_concat VS ListAgg
wmsys.wm_concat関数とListAgg関数の違いについて

●2010年6月22日(火)
英語 US-OTN --- Hehe case expression uses short-circuit evaluation

●2010年6月24日(木)
英語 US-OTN --- query reg removing logical dup rows
英語 US-OTN --- 再帰with句で文字列を繰り返しreplace
英語 US-OTN --- How to return a ResultSet with additional records that do not exist?

●2010年6月29日(火)
英語 US-OTN --- Regarding Sys_Connect_By_Path
再帰with句で、階層問い合わせのsys_connect_by_path関数の逆につなげるバージョンを模倣

reverse関数(隠し関数)を2回使う方法を思いつかなかった
反対の反対は賛成なのだ。(バカボンのパパ)

col revPath for a20

with tmp(ID,NextID) as(
select 'abc','def' from dual union
select 'def','ghi' from dual union
select 'ghi','jkl' from dual)
select reverse(sys_connect_by_path(reverse(ID),'/')) as revPath
  from tmp
 where connect_by_IsLeaf = 1
start with ID = 'abc'
connect by prior NextID = ID;

revPath
-------------
ghi/def/abc/

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN --- Row sum in PIVOT query
Pivotと代替案は、使い分けようという話

●2010年6月30日(水)
simple-talk(英語)
導師の記事

英語 MSDN --- Select parent ids that have exact match on child values (no more no less)
厳密な関係除算もどき

英語 MSDN --- How to select rows that add up to a certain value?
ナップサック問題もどき

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
10-330 Siteごと,全Site,全Site(Japanは除く)で集計

●2010年7月3日(土)
買ってきた
SQL ゼロからはじめるデータベース操作
旅人算―中学入試問題をわかりやすくマンガで攻略
はじめての新TOEICテスト全パート教本

●2010年7月4日(日)
データベーススペシャリストの試験を受けようと思います。

数年前に買った、情報処理教科書テクニカルエンジニア[データベース]2005年度と、
データベーススペシャリスト完全教本を買って勉強しようと思います。

過去問は、情報処理推進機構からダウンロードしておいたので、まずは学習計画を作るとしよう。

●2010年7月5日(月)
US-OTN ---  in this case,RegExp_SubStr 6th parameter is really useful
RegExp_SubStr関数の第6引数が強力な件
正規表現を使った検索での、肯定先読みと肯定戻り読みを、ある程度は代用できそうですね

select*from dba_source where OWNER='SYS';
をspoしてRegExp_Substr関数の第5引数のデフォルト値を調べたところnullでした。

SHIFT the Oracle --- RegExp_SubStr
マニュアル --- RegExp_SubStr

●2010年7月6日(火)
英語 US-OTN --- Introduction to regular expressions part4
英語 US-OTN --- I like grouping sets

●2010年7月7日(水)
予習復習を前提とした勉強会の第4段DB2でSQL徹底指南書の問題を華麗に解くパート1のタイムスケジュール
リハーサルは、日曜にしておいたので、後は、技術者のための プレゼン上達塾をよく読んでおきます。

19:00-19:20  Club DB2より、開始の挨拶やLTなど

第1部 自己結合の使い方,HAVING句の力
19:20-19:30 1. 重複行を削除する (32ページ)
19:30-19:40 2. 部分的に不一致なキーの検索 (34ページ)
19:40-19:50 3. 最頻値を求める (67ページ)
19:50-20:00 特別企画 IBMの方が語る OLAP関数を使ったときのアクセスプランの改善
休憩10分
20:10-20:18 4. メジアンを求める (70ページ)
20:18-20:26 5. 直近と比較 (108ページ)

第2部 SQLで集合演算
20:26-20:34 6. 集合の相等性チェック (124ページ)
20:34-20:42 7. 関係除算 (129ページ)
20:42-20:50 8. 等しい部分集合を見つける (132ページ)

20:50-20:55 質疑応答
20:55-21:00 Club DB2より、終了の挨拶

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
勉強会関連
技術者のための プレゼン上達塾
Oracle OpenWorldの「裏メニュー」アンカンファレンスとは − ＠IT

勉強会を楽しむなら発表しよう！ | Shin x blog
5分で絶対に分かるテクニカルトーク − ＠IT自分戦略研究所
勉強会に勉強だけをしに来るヤツは素人
レポート：このカンファレンスは伝説になる−「勉強会カンファレンス2009」開催｜gihyo.jp

エンジニアの勉強法について
IT業界で楽しく仕事をするための10カ条

京都の地より、ＩＴ勉強会を再考してみる #2

●2010年7月8日(木)
US-OTN --- wrong result of 11gR2 Recursive with clause part2
US-OTN --- wrong result of 11gR2 Recursive with clause part3
ボランティアでしつこくバグ報告してみる。

●2010年7月12日(月)
英語 US-OTN --- I like Boolean algebra
英語 US-OTN --- I like Regex
英語 US-OTN --- I like case expression

●2010年7月15日(木)
英語 US-OTN --- 文字列を縦文字に変換
再帰with句の有効な使い方と、multiSetの使用例

US-OTN --- 深さ優探索順で(レベルによって条件を変えて)ソート
深さ優先探索でのソートキーとして、sys_connect_by_path関数を使用

英語 US-OTN --- Summarise the results based on start and end dates
旅人算メソッドのライバルソリューション

英語 US-OTN --- I have researched RegExp_Replace VS RegExp_Substr
RegExp_Substr関数の第6引数を使うよりも、
RegExp_Replace関数で行頭から行末までマッチさせたほうが分かりやすい気がしてきた。

英語 US-OTN --- In this case,alterNative Pivot solution is useful than pivot
PivotとPivotの代替案の使い分け

英語 US-OTN --- Since Oracle11gR2 we can use this funny solution
Lag関数のignore nullsオプション

英語 US-OTN --- Hierarchical Hell - traversing up the tree?
相関サブクエリで階層問い合わせ

●2010年7月17日(土)
XMLQueryの使用例

select table_name,
       xmlquery (('count(ora:view("' || table_name || '"))') returning content) cnt
  from user_tables;

●2010年7月21日(水)
gihyo.jp --- スタートダッシュで一気に作り上げる

●2010年7月22日(木)
ClubDB2 第108回 【SQL上級編2】 DB2でSQL徹底指南書の問題を華麗に解く - ClubDB2 Cafe
7/23のCLUB DB2

ライトニングトークが岡口さんとミックさん。
そして、「OLAP関数の使用によるアクセスプランの改善」について語っていただくのが安田さん。
と超豪華な布陣となってますね :-)

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN --- リセット機能付の累計の計算
再帰with句とmodel句の比較