●2007年8月1日
MySQLでメジアンを求める方法

create table medianTest(Val int);
insert into medianTest values( 0);
insert into medianTest values(10);
insert into medianTest values(10);
insert into medianTest values(20);
insert into medianTest values(80);
insert into medianTest values(90);
insert into medianTest values(999);
insert into medianTest values(999);
insert into medianTest values(999);
insert into medianTest values(999);
insert into medianTest values(999);
commit;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLクックブックより

select avg(val)
from (select e.val
        from medianTest e,medianTest d
      group by e.val
      having sum(case when e.val = d.val then 1 else 0 end)
          >= abs(sum(sign(e.val-d.val)))) dummy;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
HAVING句の力より

select avg(Val)
from (SELECT T1.val
        FROM medianTest T1, medianTest T2
      GROUP BY T1.val
      HAVING SUM(CASE WHEN T2.val >= T1.val THEN 1 ELSE 0 END) >= COUNT(*) / 2
         AND SUM(CASE WHEN T2.val <= T1.val THEN 1 ELSE 0 END) >= COUNT(*) / 2) dummy;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
2-3-6 メジアン(中央値)を取得より

こうゆうのは無理
そもそも limit句では、
order by Val limit 5+5
などの計算式すら許可していないようです。

-- select avg(Val)
-- from (select Val
--       from medianTest a,(select count(*) as RecordCount from medianTest) b
--       order by Val limit ceil(RecordCount/2)-1 offset -mod(RecordCount,2)+2);

ちなみに、offset -mod(RecordCount,2)+2は、
y=ax+b として
(x,y) = (0,2),(1,1)
を満たすaとbを求めた結果を使ってます。

連立方程式
2=a*0+b
1=a*1+b
を解いて
(a,b) = (-1,2)
y= -x+2 (x=0,1)
を求めたということです。

●2007年8月5日
考察してみた。
論理と証明
数学の論理

9-48 集合で考える
max(case when 条件A then 1 else 0 end) = 1 ⇔ 集合に、条件Aを満たす要素が少なくとも1つ存在する
max(case when 条件A then 0 else 1 end) = 1 ⇔ 集合に、条件Aを満たさない要素が少なくとも1つ存在する

min(case when 条件A then 1 else 0 end) = 1 ⇔ 集合に、条件Aを満たさない要素が1つもない
                                           ⇔ 集合の要素は、全て条件Aを満たす

min(case when 条件A then 0 else 1 end) = 1 ⇔ 集合に、条件Aを満たす要素が1つもない
                                           ⇔ 集合の要素は、全て条件Aを満たさない

●2007年8月7日
空集合を考慮したSQLを作ってみた。

9-27 集合の包含関係を調べる
9-43 集合(重複要素を許可)の包含関係を調べる

日本の数学サイト巡りをしましたが、
集合が等しいことと同値と思われる。

(Ａ⊂Ｂ、または、Ｂ⊃Ａが成立する)
かつ
集合Ａと集合Ｂの要素数が等しい

(A⊂Bまたは、B⊃A)
かつ
n(A) = n(B)

は、見つからなかったですね。
英語の数学サイト巡りもしてみようと思います。

反例があって同値じゃなかったりして

●2007年8月15日
I used RANGE UNBOUNDED FOLLOWING. But syntax error
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
The short form of this is RANGE UNBOUNDED FOLLOWING, which can also be used.

which can also be used (和訳すると、省略形もまた使用できる)
なんて追加されてる・・・(修正されると思いますが)

エピソード記憶で英語が強化されるのは、ありがたいですね。

●2007年8月17日
It has been a while.
でお久しぶりです。という意味らしい。

いや、英語のメールを書いてまして、
Nice to meet you.は、知ってましたが
It has been a while.は今日知りました。

●2007年8月20日
Oracle Databaseデータ・ウェアハウス・ガイド 10gリリース2（10.2）

●2007年8月21日
CodeZineに、分析関数の衝撃(後編)を入稿しました。
次回作は、分析関数の衝撃(完結編)か、正規表現の問題集にする予定です。

記事で扱ったDB2のSQLで、
Oracleだと文法エラーですが、(I used RANGE UNBOUNDED FOLLOWING. But syntax error)
DB2では、単なるfollowing指定が使えるのを知りました。

OLAP 関数

select sortKey,Val,
max(Val) over(order by sortKey Range Unbounded Following) as MaxVal1,
max(Val) over(order by sortKey Rows 2 following) as MaxVal2
from (select 10 as sortKey,90 as Val from sysibm.sysdummy1
union select 20 as sortKey,50 as Val from sysibm.sysdummy1
union select 30 as sortKey,70 as Val from sysibm.sysdummy1
union select 40 as sortKey,30 as Val from sysibm.sysdummy1
union select 50 as sortKey,95 as Val from sysibm.sysdummy1
union select 60 as sortKey,98 as Val from sysibm.sysdummy1) dummy

sortKey  Val  MaxVal1  MaxVal2
-------  ---  -------  -------
     10   90       98       90
     20   50       98       70
     30   70       98       95
     40   30       98       98
     50   95       98       98
     60   98       98       98

●2007年8月23日
US-OTNの優良スレ

COMPUTE count w/ distinct
GROUP BY LAST 5 WEEK
View data in one row

●2007年8月24日
OverLaps述語は、隠し機能として実装されてるらしい

Between & And Clarification
SQL support for Time periods and handling history tables, etc.

d1をコアタイム開始時間
d2をコアタイム終了時間
d3を出社時間
d4を退社時間
とおいて

今日のコアタイム終了前に、出社して (d3 <= d2)
今日のコアタイム開始後に、退社した (d1 <= d4)
ならば、今日のコアタイムに存在したという発想で代用できますけどね。

10-142 命題成立時のブール代数の同値変形

等号を含む含まない等の判定の細かい仕様は不明。(マニュアルにのってないでしょうし)

SQL> select d1,d2,d3,d4,
  2  case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
  3  case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS2"
  4  from (select Least(d1,d2) as d1,
  5        greatest(d1,d2) as d2,
  6        Least(d3,d4) as d3,
  7        greatest(d3,d4) as d4
  8        from(select
  9             trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
 10             trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
 11             trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
 12             trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
 13             from all_catalog
 14             where RowNum <= 50));

D1      ■D2      ■D3      ■D4      ■OVERLAPS1■OVERLAPS2
--------■--------■--------■--------■---------■---------
07-09-03■07-11-30■07-08-14■07-08-21■        0■        0
07-06-28■07-08-17■07-05-24■07-08-10■        1■        1
07-06-20■07-10-10■07-07-23■07-11-26■        1■        1
07-05-30■07-09-18■07-06-09■07-08-15■        1■        1
07-09-26■07-10-17■07-05-21■07-11-07■        1■        1
07-07-17■07-08-17■07-08-08■07-09-16■        1■        1
07-07-05■07-11-11■07-08-05■07-08-18■        1■        1
07-07-14■07-11-17■07-06-03■07-11-26■        1■        1
07-07-05■07-08-20■07-07-28■07-11-23■        1■        1
07-11-04■07-11-22■07-07-21■07-08-19■        0■        0
07-10-04■07-11-03■07-05-22■07-08-13■        0■        0
07-06-02■07-07-19■07-06-13■07-08-28■        1■        1
07-06-09■07-08-18■07-09-19■07-10-06■        0■        0
07-07-29■07-11-21■07-05-23■07-07-04■        0■        0
07-06-03■07-08-01■07-07-05■07-08-23■        1■        1
07-07-29■07-09-19■07-06-17■07-06-17■        0■        0
07-08-18■07-09-29■07-08-12■07-09-27■        1■        1
07-07-22■07-10-30■07-05-21■07-07-01■        0■        0
07-06-18■07-10-18■07-06-26■07-10-21■        1■        1
07-07-15■07-07-28■07-11-10■07-11-24■        0■        0
07-07-10■07-11-16■07-07-14■07-08-05■        1■        1
07-06-10■07-07-28■07-09-08■07-11-23■        0■        0
07-06-26■07-09-13■07-07-25■07-10-12■        1■        1
07-09-05■07-11-06■07-10-09■07-11-15■        1■        1
07-07-14■07-10-05■07-07-29■07-09-24■        1■        1
07-11-10■07-11-13■07-05-18■07-08-24■        0■        0
07-05-20■07-09-24■07-08-19■07-09-29■        1■        1
07-08-24■07-09-26■07-06-05■07-11-20■        1■        1
07-05-22■07-05-28■07-05-18■07-11-10■        1■        1
07-07-29■07-08-18■07-08-23■07-10-25■        0■        0
07-08-30■07-09-18■07-06-15■07-06-20■        0■        0
07-08-16■07-12-01■07-07-20■07-08-05■        0■        0

D1      ■D2      ■D3      ■D4      ■OVERLAPS1■OVERLAPS2
--------■--------■--------■--------■---------■---------
07-07-18■07-09-15■07-05-19■07-10-04■        1■        1
07-09-20■07-11-20■07-05-22■07-10-03■        1■        1
07-10-06■07-10-30■07-09-16■07-11-04■        1■        1
07-07-13■07-09-24■07-08-08■07-11-14■        1■        1
07-08-19■07-08-23■07-07-13■07-12-01■        1■        1
07-11-25■07-11-28■07-06-20■07-11-28■        1■        1
07-08-23■07-10-20■07-05-29■07-06-07■        0■        0
07-06-15■07-06-16■07-05-29■07-11-05■        1■        1
07-06-13■07-07-01■07-09-28■07-10-08■        0■        0
07-05-28■07-08-03■07-09-19■07-11-05■        0■        0
07-10-16■07-11-29■07-09-15■07-12-01■        1■        1
07-09-22■07-10-15■07-07-27■07-11-26■        1■        1
07-08-09■07-10-16■07-09-28■07-09-29■        1■        1
07-09-17■07-10-06■07-07-03■07-11-01■        1■        1
07-05-21■07-10-30■07-07-26■07-11-04■        1■        1
07-05-20■07-08-29■07-06-16■07-10-23■        1■        1
07-06-07■07-09-13■07-07-14■07-09-10■        1■        1
07-08-31■07-10-20■07-06-28■07-07-17■        0■        0

50行が選択されました。


PL/SQLでxorなんて隠し機能もあるらしい

SQL> begin
  2      if xor(false,false) then
  3          DBMS_Output.Put_Line('aaaa');
  4      end if;
  5      if xor(false,true) then
  6          DBMS_Output.Put_Line('bbbb');
  7      end if;
  8      if xor(true,false) then
  9          DBMS_Output.Put_Line('cccc');
 10      end if;
 11      if xor(true,true) then
 12          DBMS_Output.Put_Line('dddd');
 13      end if;
 14  end;
 15  /
bbbb
cccc

PL/SQLプロシージャが正常に完了しました。

●2007年8月28日
全称命題と存在命題について勉強してみました。
哲学みたいで、難しい・・・
以下メモ書き

述語論理学
数学の論理

4つの公式
¬(∀X:P(X)) = ∃X:¬P(X)
¬(∃X:P(X)) = ∀X:¬P(X)
∀X:P(X) = ¬(∃X:¬P(X))
∃X:P(X) = ¬(∀X:¬P(X))

∀X:P(X) は　P(1) and P(2) and P(3) and P(4) and P(5) and …を意味する。
∃X:P(X) は　P(1) or P(2) or P(3) or P(4) or P(5) or …を意味する。

これとドモルガンの法則を使って、
¬(∀X:P(X))
= ¬(P(1) and P(2) and P(3) and P(4) and P(5))
= ¬P(1) or ¬P(2) or ¬P(3) or ¬P(4) or ¬P(5)
= ∃X:¬P(X)
とすれば脳内で同値変形できる。

高校数学の
A、B、Cが全て0でない
の余事象は、A、B、Cの少なくとも1つが0である。
とか
命題と、その命題の対偶が同値
の感覚に近い。

∃X:P(X)がSQLのexists述語にあたる。
¬(∃X:P(X))は、not existsで代用できる。

partitionを切って、もしくはgroup化しての
max(case when 条件P then 1 else 0 end) = 1 が∃X:P(X)に相当
min(case when 条件P then 1 else 0 end) = 1 が∀X:P(X)に相当
max(case when 条件P then 0 else 1 end) = 1 が∃X:¬P(X)に相当
min(case when 条件P then 0 else 1 end) = 1 が∀X:¬P(X)に相当

●2007年8月30日
partitionを切って、もしくはgroup化しての
max(case when 条件P then 1 else 0 end) = 1 が∃X:P(X)に相当
min(case when 条件P then 1 else 0 end) = 1 が∀X:P(X)に相当
max(case when 条件P then 0 else 1 end) = 1 が∃X:¬P(X)に相当
min(case when 条件P then 0 else 1 end) = 1 が∀X:¬P(X)に相当

をUS-OTNで発表してみました。
Newbie: Simple select with 'IN ALL'

数学やSQLやプログラム技術は、
大勢の目に触れ、大勢に使われることにより
洗練されるのです。

●2007年9月7日
US-OTNのforumでは、
postsが50で銀メダル、150で金メダルの称号となるのですが、
なかなか金メダルになれないですね。
今は、97postsなので、残り50postsぐらいか

How to "where any of(b,c,d,e,f,g,h..) is null" with SQL(not PL/SQL)
文法をミスった・・・
these function  retuensではなく
these functions retuen

例外はありますが、
SかVのどちらかに必ずsが付くと覚えるといいと最近知りました。

●2007年9月12日
Analytic functions - help needed
distinctオプションとorder byを使った分析関数を代用する方法についてのスレッド
最近US-OTNのスレッドが読めるだけの英語力がついたようです。

●2007年9月14日
Select only records with same Id
全称命題と存在命題をまたしても発表してみた。
しかし、alikeの使い方が間違ってる・・・

alikeは「〜は似ている」という意味の叙述用法しかなく、
限定用法(名詞を修飾する用法)はないそうです。

×This thread deals alike question.
×alike threads

てか形容詞の用法にも制限があったのか・・・

●2007年9月18日
SQLPlusのSQLPROMPTコマンドの備忘録

SQL> SET SQLP "_date> "
07-09-18> SET SQLP "_USER> "
TEST> SET SQLP "&_USER.@&_CONNECT_IDENTIFIER> "
TEST@orcl>

●2007年9月19日
CodeZineで分析関数の衝撃（後編）が公開されましたが、
次は、数学と分析関数を組み合わせた完結編を予定してます。
次の次は、正規表現の問題集を予定

●2007年9月25日
プログラマなら人月なんかさっさと超えろ
>人月評価なんかさっさと超えてしまえ。
>あなたは「１人月」じゃない。
>「こいつは何人月分では計算できない。必須だ」と思わせるんだ。

ドラゴンボールでスカウターが壊れる感覚ですね。

●2007年9月26日
2-3-29 グループごとのモード(最頻値)を取得
2-3-30 グループごとのメジアン(中央値)を取得
2-3-31 標準SQLでメジアン(中央値)を取得

今、モードとメジアンが熱い。

2007年8月1日のブログにある、
自己結合とグループ化を使ってメジアンを求めるクエリは難しいですね。
なかなか、脳内でイメージできないです。
偶数と奇数の、場合分けもあって難しい・・・

●2007年9月27日
All Elementary Mathematics
素晴らしい数学サイトを発見
英語のレベルがちょうどいいです。

●2007年9月28日
insert-selectでwithが使えるらしい。

create table TestTable (Col number);

insert into TestTable
with WorkView as (select 10 from dual
            union select 20 from dual)
select count(*) from WorkView,WorkView;

●2007年9月29日
Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives
使い道なさそうですが

SQL> select ColA,ColB,
  2  case when SYS_OP_MAP_NONNULL(ColA) = SYS_OP_MAP_NONNULL(ColB) then 1 else 0 end as method1,
  3  decode(ColA,ColB,1,0) as method2
  4  from (select 1 as ColA,1 as ColB from dual
  5  union select 1,null from dual
  6  union select null,1 from dual
  7  union select null,null from dual);

     COLA■     COLB■  METHOD1■  METHOD2
---------■---------■---------■---------
        1■        1■        1■        1
        1■ヌル     ■        0■        0
ヌル     ■        1■        0■        0
ヌル     ■ヌル     ■        1■        1

●2007年10月1日
Merging consecutive rows of a table
151postsで金メダルになってしまった。

●2007年10月2日
6-1 連続した重複行を1行に置換
6-2 出現回数が2回目以降なら削除その1
6-3 出現回数が2回目以降なら削除その2

正規表現パズルに新ジャンルとして、
連続した置換編
を作ってみた。

●2007年10月3日
OTN 階層問合せによるデータの取得
8-18 dense_Rankとrangeの組み合わせ
8-19 12進数変換とrangeの組み合わせ

今、rangeが熱い

●2007年10月4日
分析関数で各行の値が使える箇所

--■■■LagとLeadの、第2引数と第3引数■■■
select ID,Seek,
Lag(ID,Seek,spare)  over(order by ID) as LagID,
Lead(ID,Seek,spare) over(order by ID) as LeadID
from (select 1 as ID,1 as Seek,99 as spare from dual
union select 2,1,88 from dual
union select 3,2,77 from dual
union select 4,1,66 from dual
union select 5,3,55 from dual
union select 6,1,44 from dual
union select 7,2,33 from dual)
order by ID;

ID■Seek■LagID■LeadID
--■----■-----■------
 1■   1■   99■     2
 2■   1■    1■     3
 3■   2■    1■     5
 4■   1■    3■     5
 5■   3■    2■    55
 6■   1■    5■     7
 7■   2■    5■    33

--■■■following指定とpreceding指定■■■
select ID,Seek,
max(ID) over(order by ID
              rows between current row
                       and Seek following) as MaxID
from (select 1 as ID,1 as Seek from dual
union select 2,1 from dual
union select 3,2 from dual
union select 4,1 from dual
union select 5,3 from dual
union select 6,1 from dual
union select 7,2 from dual)
order by ID;

ID■Seek■MaxID
--■----■-----
 1■   1■    2
 2■   1■    3
 3■   2■    5
 4■   1■    5
 5■   3■    7
 6■   1■    7
 7■   2■    7


OTN 月間売り上げを作成する SQL
このスレに投稿したクエリで両方使ってたりする。

●2007年10月5日
ジョー・セルコ『SQLパズル 第2版』
SQLパズル 第2版
伝説の本がついに復活

ちなみにパズルという単語の意味は、日本語と英語で意味が違ってたりする。
SQLパズルというより
SQL難問集といったほうがしっくりくるかもしれない。

SQLパズル 第1版を読んだことがありますが、
数学を使うのが多かったですね。

●2007年10月9日
select max date from a group of columns of type date

●2007年10月10日
旅人算の感覚を使った別解を追加
7-50 前後の値で分岐
9-52 最大のリージョンを求める(境界なし)
9-54 最大のリージョンを求める(境界あり)
10-130 ignore nullsをsum関数で代用

このサイトの旅人の画像を、脳内でイメージすると分かりやすいでしょう。
今月は旅人算 - 学びの場.com

Group by preserving the order
US-OTNで発表してみましたが、通じる英語かは不明
そもそも旅人算とか植木算とか日暦算とかって
英語でどういうんでしょうか？

●2007年10月11日
Parsing Data

OracleマスターBronze SQL基礎の黒本によると
代替引用符演算子と呼ぶらしいですね。
忘れてたのでメモ

col c1 for a30
col new_c1 for a10
col new_c1_regex1 for a15
col new_c1_regex2 for a15

WITH t AS (SELECT q'['CITY';'ZIP';'COUNTRY']' c1 FROM dual
UNION SELECT q'[;;'CITY';'ZIP';'COUNTRY']' FROM dual
UNION SELECT q'[;;;'COUNTRY']'FROM dual)
SELECT c1
, SUBSTR(c1, INSTR(c1, ';', -1, 1) + 1) new_c1
, REGEXP_REPLACE(c1, '^.*;') new_c1_regex1
, REGEXP_substr(c1 , '[^;]*$') new_c1_regex2
FROM t;

●2007年10月14日
Completion of data series by analytical function
10gの新機能で、Partitioned Outer Join
なんてあったらしい。

Partitioned Outer Joinを使う別解を、追加しときました。
3-34 Partitioned Outer Join
7-70 クロスジョインして分析関数
7-75 全ての年月の値を出力
10-109 ANSI構文でクロスジョイン
10-170 クロスジョインで組み合わせを列挙