私がOTNに投稿したクエリの中で、 面白かったクエリをまとめてみました。
■■■植木算で件数を計算してRowNum指定■■■ OTN-Japan ROW_NUMBER()で実行速度が遅い SQLの結果件数の最大が事前に分かる場合は、 RowNum指定でCountStopさせることによって速くなる可能性があるらしいので、 植木算で件数を計算してRowNum指定してます。
■■■SQL99構文での結合■■■ OTN-Japan 穴埋めしてデータを取得するクエリ SQL99構文を使って結合させると クエリがシンプルになることがあります。
■■■LTrimで高速化■■■ OTN-Japan 文字列検査：英文字、数字以外 LTrimとtranslate の内部的なアルゴリズムを考えると面白いかも
■■■IPの前ゼロを消去■■■ OTN-Japan IPアドレスの前ゼロを消去 正規表現で先読みと戻り読みがないのは、つらいものですね でも後方参照で代用できる場合があります。
■■■Last関数■■■ OTN-Japan 最大値の合計を取得 Last関数の使用例
■■■逆ソート■■■ OTN-Japan ある並びで重複カラムレコードを削除したい 情報処理試験のアルゴリズムで出てきた？逆ソートを order by句のascとdescを入れ替えてOracleで実現
■■■階層問い合わせは深さ優先探索らしい■■■ OTN-Japan 階層問合せの末端について Oracle8.1.7で根を取得する方法 マニュアルの細かいところまで読まれてるんですねぇ
■■■対数を使った総積取得■■■ OTN-Japan 集計の積について プログラマのためのSQL の321ページにのってたSQLが元ネタ 数学の対数法則を使ってます。
■■■ブール代数での分配法則■■■ OTN-Japan 条件指定をしたSQL文について 条件式をブール代数を使って同値変形します。 ブール代数の分配法則 数の計算の分配法則にない、 ブール代数の分配法則を使うのが面白いです。 A+B*C = (A+B)*(A+C)
■■■分析関数でのignore nulls■■■ OTN-Japan 行の差分を計算したい Oracle10gの新機能である First_ValueとLast_Valueでのignore nullsオプションは case式と組み合わせることができます。 使用例 3-16 case式とignore nullsその1
■■■単調増加での累計■■■ OTN-Japan 値の合計が100以上になるまでデータを取得 負数があるかどうか(単調増加かどうか)でクエリが変わりますが 単調増加ならではのクエリが面白い(昔解いた数学の積分の問題でこんなのがあったような？)
■■■ソートキーを多分岐■■■ OTN-Japan DECODEを使用したORDER BYの分岐 分析関数はorder by句でも使えるので case式と組み合わせてソートキーを多分岐させることができます。
■■■最大値をフラグオン■■■ OTN-Japan 同一値を持つレコードのうち一レコードだけにフラグ 別解の多いSQLは面白いものです。 大きく分けて 相関サブクエリを使う方法と、 非相関サブクエリを使う方法がありますね。
■■■集合の一致を調べる■■■ OTN-Japan 会計年度の第何週を求める 集合の講座（基本編）（１） 数学の集合演算の公式をSQLで使えたりします。 SQLの結果が一致するか調べるのに使えます。
■■■10進数を2進数に変換■■■ OTN-Japan 10進数を2進数に変換 情報処理技術者試験の方法をOracleに移植 移植できるものですね。
■■■移動平均を求める■■■ OTN-Japan 移動平均を求めるクエリ ushitakiさんの真似してSQLクイズを投稿してみた OracleSQLパズルのアクセス解析で SQL 移動平均 で検索してくる人が多かったので作ったMySQLとPostgreSQL用のSQLが元ネタ
■■■条件式の同値変形■■■ OTN-Japan 条件式の同値変形 OracleSQLクイズのアレンジ問題 私が投稿したSQLクイズの2問目
★★★US-OTN編★★★ ■■■仮想テーブルに対する熱い説明■■■ ignore X in group by blushadowさんの仮想テーブルに対する熱い説明が勉強になります。 補足 仮想テーブル
■■■MySQLのGroup_Concat関数をOracleで実装■■■ how to display empno in a single row instead of displaying it vertically OracleエースのXMLQueryを使った方法をヒントに XMLAggとXMLElementで実装してみました 補足 stragg in 10gR2
■■■誕生日から年齢を求める■■■ How old are you? Laurent Schneiderさんの誕生日から年齢を求めるクエリ 2月29日に生まれた人の誕生日が、 閏年は、2月29日で、 平年は、3月1日になることを考慮されてます trunc(months_between(誕生日,現在日付)/12) だと 2月29日が誕生日の人の、平年の2月28日の年齢が正しくないんですね 補足 誕生日から年齢を算出する - オラクル・Oracle SQL 関数リファレンス 3-11 指定期間内に誕生日を迎える人を取得 3-12 指定した年月時点での年齢を取得
■■■パスカルの三角形■■■ Pascal's triangle or binomial coefficients パスカルの三角形をSQLで書こうというスレッド 補足 10-24 パスカルの三角形
★★★番外編★★★ ■■■アルゴリズムのオーダー通りにいかなかった・・・■■■ OTN-Japan 最新のactionを一度で抽出 最大値が存在するレコードを取得するクエリでの考察 アルゴリズムのオーダー通りにいかなかった・・・ と思いきや Laurent Schneiderさんの考察をみるに 多少はアルゴリズムのオーダーの影響をうけるみたいです。 KEEP DENSE_RANK versus ROW_NUMBER() SQL> select count(*) from emp a,emp,emp,emp,emp,emp,emp; COUNT(*) --------- 105413504 SQL> set timi on SQL> SQL> with emp2 as (select a.* from emp a,emp,emp,emp,emp,emp,emp) 2 select max(ename) keep (dense_rank first order by sal desc,empno) ename, 3 deptno,max(sal) as sal 4 from emp2 group by deptno; ENAME■DEPTNO■ SAL -----■------■---- KING ■ 10■5000 SCOTT■ 20■3000 BLAKE■ 30■2850 経過: 00:01:30.18 SQL> with emp2 as (select a.* from emp a,emp,emp,emp,emp,emp,emp) 2 select ename,deptno,sal 3 from (select ename,deptno,sal, 4 row_number() over (partition by deptno order by sal desc,empno) as r 5 from emp2) 6 where r=1; ENAME■DEPTNO■ SAL -----■------■---- KING ■ 10■5000 SCOTT■ 20■3000 BLAKE■ 30■2850 経過: 00:01:33.45
■■■group byでソートされないことに対するoracleエースの熱い検証■■■ OTN-Japan distinctがorder by 無しでソートする Oracleエースの2人の検証が熱いです。