トップページに戻る
DSP受験記
記事(OTN)
記事(CodeZine)
MSDN C#
@IT C#入門
2006年 2月16日から2007年 7月28日までのブログ
2007年 8月 1日から2007年10月28日までのブログ
2007年10月20日から2008年 5月 8日までのブログ
2008年 5月22日から2008年 9月 3日までのブログ
2008年 9月 4日から2009年 2月 4日までのブログ
2009年 2月 7日から2009年 5月20日までのブログ
2009年 5月22日から2009年 7月13日までのブログ
2009年 7月15日から2009年11月11日までのブログ
2009年11月14日から2010年 3月13日までのブログ
2010年 3月15日から2010年 7月21日までのブログ
2010年 7月24日から2011年 3月29日までのブログ
2011年 3月30日から2012年 1月11日までのブログ
2012年 1月18日から2014年 3月18日までのブログ
2014年 3月21日から2014年11月15日までのブログ
2014年11月22日から2015年 3月 1日までのブログ
2015年 3月15日から2015年 5月30日までのブログ
2015年 6月 7日から2018年 4月24日までのブログ
2018年 7月21日から2021年 2月19日までのブログ
最新のブログ
●2009年11月14日(土)
PostgreSQL8.4新機能 window関数のリハーサルをしておきました。
機会があれば、来年のpgConで、「PostgreSQL8.4新機能 再帰with句」もやりたいですねぇ
US-OTNとかOTN-Japanでよくみかける問題を、難易度の昇順に、再帰with句で解いていくような
●2009年11月16日(月)
PostgreSQL8.4新機能 window関数のタイムスケジュール
13:20-13:25 1. window関数とは
13:20-13:25 2. select文の件数取得
13:20-13:25 3. except allとcount(*) over()
13:25-13:30 4. 最大値の行の取得
13:25-13:30 5. 順位を付ける
13:25-13:30 6. 最大値の行の取得(ソートキーが複数)
13:30-13:35 7. 前後の行の値
13:30-13:35 8. 累計
13:30-13:35 9. First_ValueとLast_Valueとnth_Value
13:35-13:40 10. array_agg関数で配列型にまとめる
13:35-13:40 11. 移動累計(2行前からカレント行までの累計)
13:35-13:40 12. 全称肯定,全称否定,存在肯定,存在否定
13:40-13:45 13. 最頻値(モード)
13:40-13:45 14. 最小の空き番号の取得 (2人旅人算)
13:40-13:45 15. 連続範囲の最小値と最大値 (3人旅人算)
13:45-13:50 16. 次の入社日を求める
13:45-13:50 17. count(distinct Val) over(partition by ID)
13:45-13:50 18. count(distinct Val) over(order by sortKey)
13:50-13:55 19. sum(Val) Keep(Dense_Rank Last order by sortKey) over(partition by ID)
13:50-13:55 20. sum(Val) over(order by sortKey range between current row and unbounded following)
13:50-13:55 21. Rows 2 Precedingなcount(*)とminとmax
13:55-14:00 22. Range 2 Precedingなcount(*)とmax
13:55-14:00 23. Last_Value関数のignore nulls(entire)
13:55-14:00 24. Last_Value関数のignore nulls(until)
14:00-14:05 25. Lag関数とLead関数のignore nulls
14:00-14:05 26. update文でwindow関数の値に更新
14:05-14:10 予備
学習は、繰り返し学ぶのが重要と言われますので、
教材をhtml形式で、事前にも事後にもWebで公開する、
予習復習を前提とした勉強会とします :-)
●2009年11月18日(水)
英語 US-OTN --- I like recursive with clause
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracle Database 11g新機能
Oracle11gR2の日本語のマニュアルが公開されましたね。
「再帰with句」と「階層問い合わせ」を使い分ける修行中です。
再帰with句
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
INSERT文のIGNORE_ROW_ON_DUPKEY_INDEXヒント
INSERT INTO TARGET...SELECT...FROM SOURCEでは、
挿入する行の一意キーが既存の行と衝突することがあります。
IGNORE_ROW_ON_DUPKEY_INDEXにより、警告なしに衝突を無視して、衝突のない行を挿入できます。
●2009年11月20日(金)
PgCon2009の1日目メモ
・稲葉実行委員長の挨拶
・片岡理事長のユーザ会に関する講演
・PostgreSQLの同期アプリケーション
・昼ごはん
・SuperSQL
・ASPサービスの事例紹介
・PostgreSQL適用事例
・ECクラウド上での大規模PostgreSQL適用事例
・基幹システムにPostgreSQL
・レセプションで夕食
では、明日の、私の講演の準備をして早めに寝るとします。
●2009年11月21日(土)
PgCon2009の2日目メモ
・Hinemos
・PostgreSQLのXML機能
LetsPostgresの記事でXMLの勉強をしようと思います。
・新原さんの講演
プレゼンうまかったですね。
・PostgreSQLのしくみ分科会の笠原さんの講演
DBAと思われる方々の熱気が熱かった
・私の講演
100人ぐらいの方に御清聴いただきました。
4月のOracleOpenWorldのときよりも、うまくできたと思います。(慣れてきたので、最初しか緊張しなかったし)
質疑応答での原田さんへのパスは狙ってました ;-)
講演後に、木村さんと原田さんと名刺交換
・講師控え室で、昼ごはん
・windows対応への挑戦
・SE-PostgreSQL
・Writable CTE
・PostgreSQLユーザグループ
・ライトニングトーク
・稲葉実行委員長の挨拶
・記念撮影
2日間の感想としては、PostgreSQLコミュニティのパワーを知りました。
来年のPgConでは、再帰クエリでの講演に立候補したいと思います :-)
********************************************************************************
私の講演での反省点
・htmlからDoPDFで変換したPDFを、PDFの編集ツールで適度に拡大したほうが見やすい
フリーのPDF編集ツールを探しておく。 fontタグを使うほうが良さそうだ 2009-11-25
●2009年11月23日(月)
技術者のための プレゼン上達塾のPDFを読みつつPgCon2009の総括をしてますが、
OTNセミナー オンデマンド コンテンツ(無料) からダイセミの内容がmp3とwmvでダウンロード可能になりませんかねぇ
●2009年11月25日(水)
PgCon2009も終わりましたし、1週間ぐらいは、SQLから少し離れて、C#と英語を黙々とやろうと思います。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
44のアンチパターンに学ぶ DBシステム
買ってこよう
●2009年11月26日(木)
珍しくOTNで質問してみました。
evaluated order of Pivot and UnPivot in select statement
PivotもUnPivotも、from句の一部扱いと考えていいようだ。
PivotやUnPivotした結果に表別名を付けれますので
select * from dual
unpivot(vals1 for key1 in(dummy,dummy,dummy,dummy,dummy))
unpivot(vals2 for key2 in(key1,key1))
pivot(max(key2) for vals2 in('DUMMY' as newDummy))
pivot(max(newDummy) for vals1 in('DUMMY' as newDummy2)) a
Join dual b
on a.newDummy2 is null;
NEWDUMMY2 DUMMY
--------- ------
X null
●2009年11月27日(金)
表21-1 分析関数およびその使用目的
ウィンドウ
累積集計および変動集計を計算する。SUM、AVG、MIN、MAX、COUNT、
VARIANCE、STDDEV、FIRST_VALUE、LAST_VALUEおよび新しい統計関数とともに動作する。
DISTINCTキーワードはMAXおよびMINを除くウィンドウ関数ではサポートされない。
レポート
市場占有率などのシェアを計算する。
SUM、AVG、MIN、MAX、COUNT（DISTINCT付き/なし）、
VARIANCE、STDDEV、RATIO_TO_REPORTおよび新しい統計関数とともに動作する。
集計モードでDISTINCTキーワードがサポートされるレポート関数では、DISTINCTを使用できる。
select count(distinct Val) over(partition by PID) は、OK
(レポート関数なのでcount関数でdistinctオプションが使える)
select count(distinct Val) over(partition by PID order by SK) は、NG
(ウィンドウ関数なのでcount関数でdistinctオプションが使えない)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracle11gR1を自宅PCにインストールしました。
デフォルトデータベースではなく、メモリを節約したら快適に動きました :-)
10gR2のbinフォルダから、11gR1のbinフォルダに、SQLPlusW.exe関連のファイルをコピーしたら、
SQLPlusW.exeも動きました :-)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
UnPivotとPivotについて勉強中
まずは、脳内のイメージを構築するとします。
UnPivot(列値を表示する列名 for 元列の識別値を表示する列名 in(元列1,元列2,元列3))
Pivot(集約関数 for 集約条件列 in(集約条件値1 as 集約後列名1,
集約条件値2 as 集約後列名2,
集約条件値3 as 集約後列名3))
Pivotでは、暗黙のgroup byが実行されるらしい。
10-278 unpivotとpivot
10-284 UnPivotして連番付与
●2009年12月6日(日)
Oracle11gR2のwindows版(32ビット)が出たら試したいSQL
--■■■ 件数制限付の再帰with句1 ■■■
with rec(Val,recCnt) as(
select 1,count(*) over() from dict
union all
select Val+1,recCnt+count(*) over()
from rec
where recCnt+RowNum < 9999)
select Val,count(*),sum(count(*)) over()
from rec
group by Val
order by Val;
うまく動いたら
幅優先探索での件数制限付の再帰with句と
深さ優先探索での件数制限付の再帰with句も作る。
--■■■ 件数制限付の再帰with句2 (PostgreSQLのLimit句で再帰with句の件数制限もどき) ■■■
with rec(Val) as(
select 1 from dict
union all
select Val+1 from rec)
select count(*) from rec
where RowNum < 99
/*and Val = 2*/;
--■■■ interVal型のSumを求める ■■■
create table sumInter(Val) as
select interVal '10' minute from dual union
select interVal '20' minute from dual union
select interVal '30' minute from dual;
with work(Rn,Val,recCnt) as(
select RowNum,Val,Count(*) over() from sumInter),
rec(Rn,Val,recCnt) as(
select Rn,Val,recCnt
from work
where Rn = 1
union all
select b.Rn,a.Val+b.Val,a.recCnt
from rec a,work b
where a.Rn+1 = b.Rn)
select * from rec
where Rn = recCnt;
--■■■ OracleSQLパズルの更新 ■■■
10-218 wmsys.wm_concatにListAgg関数での実験を追加
8-53 nth_value関数を模倣をOracle11gR2の結果に変更
10-308 Lag関数とLead関数のignore nullsを模倣をOracle11gR2の結果に変更
--■■■ unionによる重複排除で無限再帰を回避できるか？ ■■■
-- PostgreSQL8.4のSQL
postgres=# with recursive rec(Val) as(
postgres(# values(1),
postgres(# (1)
postgres(# union
postgres(# select Val from rec)
postgres-# select*from rec;
val
-----
1
with rec(Val) as(
select 1 from dict
union
select 1 from rec)
select*from rec;
--■■■ connect_by_isLeafを模倣 ■■■
alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
create table gra(ID,nextID) as
select 1,3 from dual union
select 3,5 from dual union
select 5,0 from dual union
select 7,9 from dual union
select 9,0 from dual;
select ID,nextID,connect_by_IsLeaf as IsLeaf
from gra
start with ID in(1,7)
connect by prior nextID = ID;
with rec(ID,nextID,LV) as(
select ID,nextID,1
from gra
where ID in(1,7)
union all
select b.ID,b.nextID,a.LV+1
from rec a,gra b
where a.nextID = b.ID)
SEARCH DEPTH FIRST BY ID SET seq
select ID,nextID,
case when LV < Lead(LV) over (order by seq)
then 0 else 1 end as IsLeaf
from rec;
--■■■ 再帰with句のサンプル ■■■
1 Level擬似列を模倣
2 sys_connect_by_path関数を模倣
3 order siblings byを模倣
4 connect_by_IsLeafを模倣
5 connect by nocycleを模倣
6 ナイト巡回問題 -- 深さ優先探索で解が3つで枝切りを行う解も用意(可能？？？)
7 最短距離問題
8 最小全域木問題
9 総積を求める
10 その他いろいろ
●2009年12月7日(月)
英語 US-OTN Pivotよりもmaxとdecodeのほうがシンプルっぽい
with tab as(
select 1 col1, 'test1' col2 from dual union all
select 2 col1, 'test2' col2 from dual union all
select 3 col1, 'test3' col2 from dual union all
select 4 col1, 'test4' col2 from dual union all
select 5 col1, 'test5' col2 from dual union all
select 6 col1, 'test6' col2 from dual)
select *
from (select floor((col1-1)/2) as GID,mod(col1,2) as md,
col1,col2
from tab)
Pivot(max(col1) as ID,max(Col2) as name for md in(1,0))
order by GID;
GID 1_ID 1_NAM 0_ID 0_NAM
--- ---- ----- ---- -----
0 1 test1 2 test2
1 3 test3 4 test4
2 5 test5 6 test6
ピボット操作
ピボット列には不定的な式を含めることはできません。
式に対するピボットが必要な場合、ビュー内でその式に別名を与えてからPIVOT操作を行います。
この場合は、Pivotよりもmaxとdecodeのほうがシンプルっぽいですねぇ
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
再帰with句に関連する話
Depth first search と Breadth first search
Iterative deepening (反復深化法)
Iterative broadening (反復拡幅法)
●2009年12月8日(火)
7.8. WITH問い合わせ
複数キーの時は、サロゲートキーを使うよりも、行コンストラクタの結果の配列にしたほうが良さそうだ・・・
あるcycleを認識するために検査しなくてはならない１つ以上のフィールドが必要とされる一般的な場合、
行の配列を使用します。例えば、f1 と f2 のフィールドを比較する必要があるときは次のようにします。
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1,
ARRAY[ROW(g.f1, g.f2)],false
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1,
path || ROW(g.f1, g.f2),
ROW(g.f1, g.f2) = ANY(path)
FROM graph g, search_graph sg
WHERE g.id = sg.link AND NOT cycle)
SELECT * FROM search_graph;
これにより、行コンストラクタとarray_agg関数と組み合わせれば、複数列でも幻のcontains述語を代用できますね。
PostgreSQLパズル 9-57 集合が等しい組み合わせを求める(複数列版)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN --- DB2 allows rows 1 following
DB2の分析関数のSyntaxに関連した話。Oracle11gR2で拡張されたのでしょうかねぇ？
英語 US-OTN --- Hahaha I like multi columns in predicate B-)
マルチカラムin述語を積極的に使おう。という話
マルチカラムin述語は、select文をネストしてwhere句で条件指定するだけのようなケースで特に力を発揮します。
マルチカラムin述語の脳内のイメージは、exists(select マルチカラム intersect サブクエリ)です。
マルチカラム指定は、PostgreSQL8.4新機能 window関数でも活躍してくれました :-)
select ID,sortKey1,sortKey2,extraCol
from multiSortKey a
where (sortKey1,sortKey2) = (select b.sortKey1,b.sortKey2
from multiSortKey b
where b.ID = a.ID
order by b.sortKey1 desc,b.sortKey2 desc Limit 1)
order by ID,extraCol;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN Query to find out rows deleted from a table
再帰with句は慣れてきました :-)
WITH recursive t(Val) AS (
values(1),(2),(4),(5),(6),(12)),
rec(Val,LeadVal) as(
select Val+1,LeadVal
from (select Val,Lead(Val) over(order by Val) as LeadVal
from t) tmp
where Val+1 < LeadVal
union all
select Val+1,LeadVal
from rec
where Val+1 < LeadVal)
select Val from rec;
val
-----
3
7
8
9
10
11
●2009年12月11日(金)
OTN --- 列に対する量化
■■■■■■■■■■■■■■■■■■■■■■■■■■■
DB2の分析関数の使用例の1と26を作っておきました。
●2009年12月16日(水)
あなたのプレゼンは何故つまらないのか？
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN --- Sorting based on count of rows
「model句 VS 再帰with句」なスレッド
US-OTNでwindows(32ビット版)のOracle11gR2がリリースされて
OTN-JapanがOracle11gR2のマニュアルの日本語版を公開するのは、来年でしょうかねぇ
●2009年12月19日(土)
英語 US-OTN --- add previous numer with current numebr
再帰with句で F0=0 , F1=1 , Fn = F(n-1) + F(n-2)
を漸化式とした数列(フィボナッチ数列)を求める。
with recursive rec(SumVal,PreSum) as(
select 1,0
union all
select PreSum+SumVal,SumVal
from rec
where SumVal < 21)
select*from rec;
SumVal | PreSum
--------+--------
1 | 0
1 | 1
2 | 1
3 | 2
5 | 3
8 | 5
13 | 8
21 | 13
●2009年12月21日(月)
英語 US-OTN --- 日付型をソートキーにした旅人算の感覚
英語 US-OTN --- Hahaha I have used sense of Tabibitosan B-)
Using Aketi's "Sense of Tabibitosan" に笑った :-)
英語 US-OTN --- Using the tabibitosan method:
Using the tabibitosan method: に笑った :-)
英語 US-OTN --- I am glad that "sense of tabibitosan" is used
中学受験の算数って？: 旅人算
この知識を習うとき、「ぐんぐん２人が離れていく」という感覚的な理解、線分図上での理解、数式的な理解が必要です。
旅人算の感覚を取得するには、図表付きで解説している、このサイトがオススメです。
今月は旅人算 - 学びの場.com
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
「ハートで話そう！マジカル英語塾」年末年始の再放送予定
2009年12月30日(水) 午前 6:40〜 7:00 Unit12 (12/16)
午後 0:40〜 1:00 Unit 9 (11/25)
午後11:10〜11:30 Unit10 (12/ 2)
2010年1月6日(水) 午前 6:40〜 7:00 Unit11 (12/ 9)
●2009年12月31日(木)
2009年の総括と、なるべく客観的な2010年の目標
*********************************************************************
勉強会関連
2009年は、講師デビューをしました。
OracleOpenWorldのアンカンファレンスで講師をさせていただいて、
PostgreSQLカンファレンス2009でも講師をさせていただきました。
2010年はClubDB2でも講師をさせていただくことになりました。
勉強会のネタは、SQLの分析関数が主力ですが、
次の主力は再帰with句ですねぇ
分析関数関連で、頻出で奥が深い、旅人算の感覚と全称肯定命題だけでも
50分ぐらいなら余裕で話せそうですが
*********************************************************************
記事執筆関連
CodeZineでの記事執筆は、
OracleのSQLの記事が4本で、
PostgreSQLのSQLの記事が5本で、
MySQLのSQLの記事が2本で、
正規表現の記事が1本
*********************************************************************
英語関連
TOEIC600点への道は、2010年以降に持越しです :-)
最近は、US-OTNのスレッドをわりと読めるようになったので、
英語のレベルが一皮むけたと思ってます。
2010年の目標は、自宅にある英語の本の読破です。
*********************************************************************
C#関連
2009年から本格的に始めたC#です。
2010年の目標は、自宅にあるC#の本の読破と
C#のサンプル集サイトを立ち上げることと、
ペースト君とスレッドビューワのC#版の作成です。
●2010年1月4日(月)
Software Design 2010年1月号
10日(日)か11日(月)に読んでくる
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
OTN セミナー オンデマンド コンテンツ
おお！ wmv形式の動画がダウンロード可能になってる！
気になるところは標準で見て、他は2倍速で見るとよさげですね :-)
●2010年1月5日(火)
Oracle Database 11g リリース2 (11.2) ドキュメント
Oracle11gR2の日本語版マニュアルがダウンロード可能になりましたね。
US-OTNのようにマニュアルを全部まとめたzipファイルもダウンロード可能です。
Oracle11gR2のSQLの新機能の紹介です。
(2009年9月2日のブログのエントリの焼き増しですが)
**************************************************************************************************
・INSERT文のIGNORE_ROW_ON_DUPKEY_INDEXヒント
マニュアル --- 1.2.2.4 INSERT文のIGNORE_ROW_ON_DUPKEY_INDEXヒント
INSERT INTO TARGET...SELECT...FROM SOURCEでは、
挿入する行の一意キーが既存の行と衝突することがあります。
IGNORE_ROW_ON_DUPKEY_INDEXにより、警告なしに衝突を無視して、衝突のない行を挿入できます。
**************************************************************************************************
・Lag関数とLead関数のignore nulls
DB2 V9.5で使えるLead関数とLag関数でのIGNORE NULLS optionですね。
マニュアル --- Lag
マニュアル --- Lead
OracleSQLパズル 10-308 Lag関数とLead関数のignore nullsを模倣
**************************************************************************************************
・ListAgg関数
アンドキュメントなwmsys.wm_concatやsys.straggでしたが、ついにマニュアルに載りました。
wmsys.wm_concatやsys.straggと同等の機能を持つかは不明ですが
マニュアル --- ListAgg
Laurent Schneider --- stragg in 11gR2
OracleSQLパズル 10-218 wmsys.wm_concat
wmsys.wm_concat関数では、keepが使えるのですが
ListAgg関数では、keepは使えないようですね。 (2010-01-05 実機では未確認)
select wmsys.wm_concat(Val) as conStr1,
wmsys.wm_concat(Val) Keep(Dense_Rank Last order by sortkey) as conStr2
from (select 1 as sortkey,'aaa' as Val from dual union all
select 2 ,'bbb' from dual union all
select 3 ,'ccc' from dual union all
select 3 ,'ddd' from dual);
conStr1 conStr2
--------------- --------
aaa,bbb,ccc,ddd ccc,ddd
**************************************************************************************************
・nth_Value関数
PostgreSQL8.4で使えるNth_Value関数ですね。
Row_Number関数の値が2の行の値を求めるなどが容易になりそうです。
マニュアル --- nth_Value
OracleSQLパズル 8-53 nth_value関数を模倣
**************************************************************************************************
・再帰with句
PostgreSQL8.4やDB2 V9.5やSQLServer2005で使える再帰with句ですね。
行を再帰的に作成したい時などに便利です。model句と使い分けるとよさそうですね。
親子関係やグラフ構造のデータに対するselect文では、
従来からあるstart with句とconnect by句の組み合わせと使い分けるとよさそうですね。
経路の総和や総積が欲しいとか、経路で枝切りしたいとかなら再帰with句のほうが向いてるはずです。
マニュアル --- subquery_factoring_clause
マニュアル --- 再帰的副問合せのファクタリング例
PostgreSQLパズル 再帰with句01 OracleのLevel擬似列を模倣
DB2 SQLパズル 再帰with句01 OracleのLevel擬似列を模倣
●2010年1月9日(土)
distinctで重複を排除してから、分析関数の結果でソートする方法。
使い道はなさそうですが :-)
create table OLAPTest(ID,Val) as
select 1,3 from dual union all
select 1,3 from dual union all
select 1,3 from dual union all
select 1,3 from dual union all
select 1,3 from dual union all
select 2,4 from dual union all
select 2,4 from dual;
select distinct ID,Val
from OLAPTest
order by sum(Val) over(partition by ID);
ID Val
-- ---
1 3
2 4
●2010年1月20日(水)
英語 US-OTN --- I like recursive with clause
最近多い
再帰with句 VS model句 どっちのSQLがシンプルか
という戦い
再帰with句 VS 階層問い合わせ どっちのSQLがシンプルか
という戦いも、最近多いですねぇ
ところでwindows(32ビット)版のOracle11gR2のダウンロードはいつから可能になるのでしょう？
●2010年1月27日(水)
OTNJ --- UnPivotで明示的なソート
order by句で明示的にソートしようとしたら、昔からある、連番表とのクロスジョインもいいかもね :-) といった話
英語 US-OTN --- 計算列を使ったPivot
Pivotと、group byとmaxとdecodeは使い分けようという話
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SQLServer会議室 --- 品番ごとで、累計が最初に負数になった行を取得
PostgreSQL8.4で解いてみた :-)
PostgreSQLでは、distinct onが使えるので、
window関数のmin関数を使って、累計が負数の中でも最小のソートキーを求めなくてもいいです。
create table runSumT(品番,順,入庫数,出庫数) as
values('B01', 1, 10, 0),
('B01', 2, 0, 8),
('B01', 3, 0, 4),
('B01', 4, 0, 3),
('B01', 5, 0, 2),
('B02', 6, 6, 0),
('B02', 7, 0, 5),
('B02', 8, 0, 1),
('B02', 9, 0, 6),
('B02',10, 0, 2),
('B03',11, 0, 4),
('B03',12,100, 0),
('B03',13, 0,10);
select distinct on(品番) 品番,runSum
from (select 品番,順,入庫数,出庫数,
Sum(入庫数-出庫数) over(partition by 品番 order by 順) as runSum
from runSumT) a
where runSum < 0
order by 品番,順;
品番 | runsum
------+--------
B01 | -2
B02 | -6
B03 | -4
@IT会議室 --- SQLServer2008で分析関数のsum関数のorder by指定の代用
SQLServer2008なら再帰SQLでもいいかもしれない・・・
自己結合な相関サブクエリで分析関数のSum関数のorder by指定を代用するよりも、
再帰SQLのほうが実行計画がいいらしい・・・
しかもこの場合は、累計が負数になった時点で枝切りできますし
PostgreSQL8.4で書いてみた :-)
with recursive tmp(品番,rn,入庫数,出庫数) as(
select 品番,Row_Number() over(partition by 品番 order by 順),入庫数,出庫数
from runSumT),
rec(品番,rn,数) as(
select 品番,rn,入庫数-出庫数
from tmp
where rn=1
union all
select a.品番,b.rn,a.数+b.入庫数-b.出庫数
from rec a,tmp b
where a.品番 = b.品番
and a.rn+1=b.rn
and a.数 >= 0 /*累計が負数になった時点で枝切り*/ )
select*from rec
where 数 < 0
order by 品番;
品番 | rn | 数
------+----+----
B01 | 3 | -2
B02 | 4 | -6
B03 | 1 | -4
SQLServer2008で、再帰SQLを使わない方法でもできますが、こっちのほうが分かりやすいかも
select 品番,runsum
from (select 品番,runsum,順,
min(順) over(partition by 品番) as min順
from (select 品番,順,
(select sum(b.入庫数-b.出庫数)
from runSumT b
where b.品番=a.品番
and b.順 <= a.順) as runsum
from runSumT a) c
where runSum < 0) c
where 順 = min順;
品番 | runsum
------+--------
B01 | -2
B02 | -6
B03 | -4
●2010年2月3日(水)
英語 US-OTN --- リセット機能付の累計
model句 VS 再帰with句 ですが、この場合のシンプルさは互角ですかねぇ
●2010年2月6日(土)
Postgresqlでn日後の日付を動的に取得する方法 - yamazのRails日記 - Rubyist
with tmp(end_at,num_of_days) as(
values(current_timestamp,1),
(current_timestamp,2),
(current_timestamp,3))
select end_at + num_of_days * InterVal '1 day'
from tmp;
?column?
----------------------------
2010-02-07 14:45:23.968+09
2010-02-08 14:45:23.968+09
2010-02-09 14:45:23.968+09
OracleでもInterVal型での掛け算は、可能だったりする :-)
たいていは、NumToDsInterVal関数を使いますが・・・
select sysdate,sysdate+3*InterVal '1' day as "After3days"
from dual;
SYSDATE After3da
-------- --------
10-02-06 10-02-09
●2010年2月10日(水)
英語 US-OTN --- 旅人算メソッド
必ず進む旅人でもパーティション切りがある少し珍しいパターン
●2010年2月13日(土)
PostgreSQLパズル 再帰with句10 数独を解く
DB2 SQLパズル 再帰with句10 数独を解く
たまには、OverLay関数のことも思い出してあげてください :-)
postgres=# select
postgres-# overlay('abcde' placing 'X' from 1) as tes1,
postgres-# overlay('abcde' placing 'X' from 3) as tes2;
tes1 | tes2
-------+-------
Xbcde | abXde
PostgreSQLでは、OverLay関数
DB2では、OverLay関数
SQLServerでは、stuff関数
MySQLでは、Insert関数
C#では、Insertメソッド "abcde".Insert(1, "XX"); //aXXbcde
ですが、
Oracle11gR2には存在しないので、
substr関数で前後をつなぐか
RegExp_Replace関数を使うしかないようだ
*************************************************************************************
C#のサンプル集 8-8 数独
C#でも解いてみた。
Stackジェネリックが強力だ・・・
PL/SQL7 数独
PL/SQLでも解いてみた。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
select文を楽しむSQLパズルゲームとしての、Oracle11gR2とPostgreSQL8.4のselect文の機能比較
Oracleにない機能を最も多く実装してるのは、PostgreSQLですねぇ
OracleSQLパズルの9章「Joe Celko氏の記事のSQLパズル」をPostgreSQLに移植しようか検討中。
*************************************************************************************
Oracle11gR2にあって、PostgreSQL8.4にない機能
・分析関数のRowsとかRangeでの細かい指定
・分析関数のcount関数でのdistinct指定
・Lag関数,Lead関数,First_Value関数,Last_Value関数,Nth_Value関数のignore nulls
・Nth_Value関数のfrom Last
・model句
・PivotとUnPivot
・rollup,cube,grouping sets,grouping,grouping_ID
・再帰with句でのcycle指定,search指定
・Median関数,stats_mode関数,ListAgg関数,nvl2関数
・Partitioned Outer Join
・集約関数や分析関数でのKeep指定
・RowNum擬似列,RowID
・階層問い合わせ
・any述語,all述語での複数式との比較
・表別名の省略
・集約関数のネスト
*************************************************************************************
PostgreSQL8.4にあって、Oracle11gR2にない機能
・distinct on指定
・array_agg関数
・bool_and関数,bool_or関数
・is not false述語,is not true述語
・select文の列値としての論理型,配列型
・Limit句とoffSet指定
・except allとintersect all
・values構文
・generate_series関数
・行コンストラクタ
・OverLay関数,age関数
・sum関数でのInterVal型の合計取得
・正規表現での肯定先読みと否定先読み
・group by句でのselect句での列別名の使用
・from句の省略
・select句での相関サブクエリでのインラインビューのwhere句での大本の列の使用
*************************************************************************************
PostgreSQLでもOracleのように、複数列in述語での複数式指定ができるようだ。
行コンストラクタとみなされてそうですが
postgres=# select (1,2) in((5,6),(5,9),(7,5),(1,2));
?column?
----------
t
●2010年2月15日(月)
Oracle Database 11g リリース2 (11.2) ドキュメント
Oracle11gR2のマニュアルのフォルダの対応表
不要なマニュアルを削除すればgrepの速度が上がります。
誰か、完全版作ってください :-)
\server.112\b56299 『Oracle Database SQL言語リファレンス』
\server.112\b56309 『Oracle Databaseデータ・ウェアハウス・ガイド』
\server.112\b56300 『Oracle Database SQL言語クイック・リファレンス』
\appdev.112\b56260 『Oracle Database PL/SQL言語リファレンス』
\appdev.112\b56262 『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』
●2010年2月22日(月)
英語 Assign group value based on lag value change
英語 Is it possible to add a row number that increases based on a column?
検索case式とLag関数でwillSumを求めて、sum関数でwillSumの累計を求めるSQL。
旅人算の感覚が使えないケースです。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Using a bit of Aketi's "Tabibitosan-method" ;)
旅人算メソッドがask tomに進出したようだ :-)
PostgreSQL8.4で解いてみた :-)
with tmp as(
select 1 as Val union
select 2 union
select 3 union
select 5 union
select 7 union
select 8 union
select 9 union
select 11)
select Val,
case when Val = max(Val) over(partition by dis)
then min(Val) over(partition by dis) end as s,
case when Val = max(Val) over(partition by dis)
and count(*) over(partition by dis) > 1
then Val end as E
from (select Val,Val-Row_Number() over(order by Val) dis
from tmp) a;
val | s | e
-----+----+---
1 | |
2 | |
3 | 1 | 3
5 | 5 |
7 | |
8 | |
9 | 7 | 9
11 | 11 |
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Oracleの階層問い合わせ5
PostgreSQLの分析関数の衝撃6
Oracleのmodel句1
PostgreSQLの再帰SQL1
PostgreSQLの分析関数の衝撃7
Oracleの階層問い合わせ6
一般公開していただきました。
●2010年2月26日(金)
Oracle11gR2にもPostgreSQL8.4にもない、DB2 V9.7のSQLの機能
・分析関数を使ったUpdatableView,DeletableView
・fetch first 行数 rows onlyを使ったUpdatableView,DeletableView
・分析関数でのorder by ソートキー rows 2 Following
・分析関数でのorder by ソートキー rows Unbounded Following
●2010年3月1日(月)
エチカの鏡で参考になったので買ってきた。
脳に悪い７つの習慣
第１章 脳に悪い習慣１―「興味がない」と物事を避けることが多い；
第２章 脳に悪い習慣２―「嫌だ」「疲れた」とグチを言う；
第３章 脳に悪い習慣３―言われたことをコツコツやる；
第４章 脳に悪い習慣４―常に効率を考えている；
第５章 脳に悪い習慣５―やりたくないのに、我慢して勉強する；
第６章 脳に悪い習慣６―スポーツや絵などの趣味がない；
第７章 脳に悪い習慣７―めったに人をほめない
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 US-OTN median関数でdistinctオプション
median関数でdistinctオプションが使えないとは知らなかった
●2010年3月2日(火)
SQL Server2005再帰クエリについて - 質問・相談ならMSN相談箱
PostgreSQL8.4で解いてみました :-)
with recursive tmp(氏名,上司の氏名) as(
values('田中',Null),
('山田','田中'),
('佐藤','山田'),
('高田','山田')),
rec(root氏名,氏名,上司の氏名,LV) as(
select 氏名,氏名,NULL,1
from tmp
union all
select a.root氏名,b.氏名,b.上司の氏名,LV+1
from rec a,tmp b
where a.氏名 = b.上司の氏名)
select root氏名,nullif(氏名,root氏名) as 部下の氏名
from (select count(*) over(partition by root氏名) as cnt,
root氏名,氏名,LV
from rec) a
where cnt = 1 or LV > 1
order by cnt desc,root氏名;
root氏名 | 部下の氏名
----------+------------
田中 | 佐藤
田中 | 山田
田中 | 高田
山田 | 高田
山田 | 佐藤
佐藤 | null
高田 | null
Oracleの階層問い合わせでも作ってみました。
with tmp as(
select '田中' as 氏名,Null as 上司の氏名 from dual union
select '山田','田中' from dual union
select '佐藤','山田' from dual union
select '高田','山田' from dual)
select root氏名,部下の氏名
from (select connect_by_root 氏名 as root氏名,
case when Level > 1 then 氏名 end as 部下の氏名
from tmp
where connect_by_IsLeaf = 1 or Level > 1
connect by prior 氏名 = 上司の氏名)
order by count(*) over(partition by root氏名) desc,root氏名;
●2010年3月11日(木)
オライリーの新刊
アルゴリズムクイックリファレンス
正規表現クックブック
両方買おうと思います。原著は多分この2冊
Algorithms in a Nutshell
Regular Expressions Cookbook
Regular Expressions Cookbookは、まだ読み終わってない・・・
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 Rounding time to nearest hourly quarter
NumToDsInterVal関数を使ってみた :-)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
英語 String to Row: Delimiter as part of the value
declare
leftKakko pls_Integer :=0;
rightKakko pls_Integer :=0;
str varchar2(400) := q'['My column',LPAD(TRIM(my_column),4,'0'),10,10000]';
willOut varchar2(400) := null;
begin
for I in 1..length(str) Loop
if substr(str,I,1) = ',' and leftKakko = rightKakko then
DBMS_Output.Put_Line(willOut);
willOut := null;
elsif substr(str,I,1) = ')' then
rightKakko := rightKakko+1;
willOut := willOut || ')';
elsif substr(str,I,1) = '(' then
leftKakko := leftKakko+1;
willOut := willOut || '(';
else
willOut := willOut || substr(str,I,1);
end if;
end Loop;
DBMS_Output.Put_Line(willOut);
end;
/
'My column'
LPAD(TRIM(my_column),4,'0')
10
10000
Oracleの正規表現でも、
鬼車の田中哲スペシャルとか(詳説正規表現3版の472ページ)
.NETの平衡グループ(詳説正規表現3版の431ページ)
が使えるといいですねぇ
先読みが使えるPostgreSQLの正規表現なら、
先読みで行末までに開き括弧と閉じ括弧の合計が偶数個か調べてもよさそうですが
正規表現パズル 4-10 aが偶数個でbが奇数個の文字列
OracleMaster
TOEIC
執筆記事
オライリー
翔泳社SHOP
正規表現
ブール代数
DB2
PostgreSQL
C#