SQLのLambda関数...考えてみましょう

記事の内容、つまりその名前が意味するもの。



さらに、著者はこれが彼の観点からなぜ必要なのかを説明し、SUBJは単なるファッショナブルなテクノロジーではなく、「二重に必要なビジネス-快適で便利なもの」であることも伝えます。



何人かの才能ある人々がどのように何かをするのか（プログラミング言語、なぜそうではないのか）、彼らが解決しようとしている問題と彼らが自分で設定したタスクを正確に知っているのを見るのは常に興味深い。 また、自分で作成をテストします。 宇宙の調和の維持を最前線に置いている巨大委員会の記念碑的な創造物と比較しないでください。



たとえば、 FORTRANとPL / 1の運命を比較してください。 このPL / 1について誰が今覚えているでしょう。



この観点から、たとえばAWKは非常に成功しています。 ドラゴンブックの著者の一人である彼のタイトルAはアルフレッド・アホであり、WはFortran-77に手を携えたピーター・ワインバーガーであり、Kはブライアン・カーニガンであり、彼は彼なしでいると言う価値があります。 この言語は、プロセス間のパイプでオンザフライのテキストストリームを処理することを目的としています。



言語はタイプレスです（ これは完全に真実ではありません ）、構文はCに非常に似ており、フィルタリング機能、連想配列、ストリームの開始/終了のイベント、改行イベントがあります...



作成者は、この言語にインタプリタをインストールする必要がなく、常にUNIXライクなシステムに存在し、Windowsでは実行可能ファイルをコピーするだけで十分であるという事実にも感銘を受けています。 ただし、そうではありません。



その過程で、著者はかなり頻繁にSQL + AWKバンドルを使用する必要があり、そのためです。 SQLは、データフローを制御するために設計された最初の宣言型言語のままです。 集計関数の形式でクエリ実行のコンテキストを操作する機会は非常に限られています。



たとえば、SQLを使用して2次元のヒストグラムを作成する方法は？

--   100 x 100 SELECT count(), round(x, -2) AS cx, round(y, -2) AS cy FROM samples GROUP BY cx, xy
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ただし、GROUP BYの使用は並べ替えを意味し、1億行（またはそれ以上）の行がある場合、それは安易な喜びではありません。

要約：SQLプロセッサーは、GROUP BYの後のグループ数を推定できない場合、ソートに頼ります。 ただし、計算値によるグループ化は（多くの場合）まさにそのケースです。



したがって、次のようなことをする必要があります。

 psql -t -q -c 'select x, y from samples' | gawk -f mk_hist2d.awk
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

mk_hist2d.awkは、連想配列に統計を蓄積し、作業の完了時に統計を表示します

 # mk_hist2d.awk { bucket[int($2*0.01), int($3*0.01)]+=$1; } END { for (i=0; i < 500; i++) for (j=0; j < 500; j++) { if ((i, j) in bucket) print i*100." "j*100." "bucket[i, j]; else print i*100." "j*100." 0"; } }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ただし、1つあります。サーバーから作業マシンに完全なデータストリームを送信する必要がありますが、それほど安くはありません。



SQLクエリの実行中に、並べ替えに頼らずに統計を蓄積するために、何らかの方法で楽しいものと便利なものを組み合わせることができますか？ はい、たとえば、カスタム集計関数を使用します。

カスタム集計関数

Subjはさまざまなシステムに存在し、どこでも独自の方法で行われます。

1. PostgreSQL ドキュメントはこちらです。 詳細はこちら 。
   
   これは、最大口座残高が計算される場所です。
   
   そして、これはブール列の詳細を計算する例です-trueまたはfalse。
   
   
   
   このように見えます-
   
   
   
   

    CREATE AGGREGATE mode(boolean) ( SFUNC = mode_bool_state, STYPE = INT[], FINALFUNC = mode_bool_final, INITCOND = '{0,0}' );
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   ここで、 SFUNCはストリーム内のすべての行に対して呼び出される関数です。
   
   その最初の引数はSTYPE型です。
   
   
   
   FINALFUNCは、計算を完了し、集計の値を返すために使用されます。
   
   INITCOND-最初の引数として渡される内部状態の初期値（ STYPE ）の初期化。
   
   関数はCで記述できることを考えると（内部状態では、リクエストを閉じると自動的に解放されるメモリを使用できることを意味します）、これは非常に強力なツールです。 その使用の範囲外では、まだ行かなければなりません。
2. MS SQL
   
   以前 （2000年）、このオブジェクトを使用して集計を行うには、ActiveXオブジェクトを作成する要求の前に必要でした。
   
   現在 （2016以降）、これはCLR環境で行われています。 カスタム関数を作成し、 アセンブリを作成して登録する必要があります。 その後、 集計を作成できます。
   
   幾何平均の計算と文字列のマージの例 ：追加のパラメーターと中間状態を保存するためのユーザー定義型を使用します。
3. オラクル
   
   Oracleでは、これはODCIAggregate Data Cartridge （インターフェース）を使用して行われます。
   
   独自の集計を作成するには、4つのメソッドを実装するカスタムタイプを記述する必要があります
   
   -初期化（ODCIAggregateInitialize）、静的、目的の型のインスタンスを作成し、パラメーターを介して返す必要があります
   
   -データの各行で呼び出される反復（ODCIAggregateIterate）
   
   -merge（ODCIAggregateMerge）、並行して実行される集約をマージするために使用
   
   -終了（ODCIAggregateTerminate）-結果出力
   
   例： 1、2、3、4 。
4. DB2
   
   DB2でカスタム集計を使用する明示的な方法はありません。
   
   ただし、標準関数（MAXではあるが）をユーザー定義型（Javaの場合）にスリップし、システムにフォームのクエリを実行させることができます。
   
   
   
   

    CREATE TYPE Complex AS ( real DOUBLE, i DOUBLE ) … CREATE TABLE complexNumbers ( id INTEGER NOT NULL PRIMARY KEY, number Complex ) … SELECT sum..real, sum..i FROM ( SELECT GetAggrResult(MAX(BuildComplexSum(number))) FROM complexNumbers ) AS t(sum)
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

これらすべてのシステムで注目すべきことは何ですか？

• 何らかの方法で、データベースにオブジェクトを作成する必要があります。 AGGREGATEまたはTYPEになります。 少なくとも、適切な権利が必要です。 そして、彼の膝にいくつかの数字を追加したいだけです。
  
  
• C、C＃、Javaなど、別の言語で何かを書く必要があるかもしれません。
  
  システムに書き込まれた内容を統合するには、再度、権限が必要です。 私はただ...
  
  
• 初期化の難しさ。 異なるバスケットサイズのヒストグラムを読みたいとします。 簡単に思えます-集合体（PostgreSQL）とビジネス全体を宣言するときに、目的のINITCONDを示します。 ただし、バスケットのサイズごとに、独自の集計が必要になります。この場合も、権利が必要です。
  
  
  
  ここでは、ダーティトリックに頼って、初期化行（フォワード）とデータからユニオンプロセッサをスリップし、コンストラクタではなく、最初の行を受け取ったときにコンテキストを構築できます。
  
  
• それでも、説明されている制限があっても、カスタム集計を使用すると、何でも計算できます。
  
  
• 少なくともPostgreSQLで集計を並列化できることが重要であり、Oracle（Enterprise Edition）がこれを実行できます。 このためには、中間状態をシリアル化/非シリアル化する方法を学習し、異なるストリームから受信した状態をフリーズする必要があります。

ウィンドウ関数

ウィンドウ関数はSQL：2003標準に登場しました。 現時点では、上記のすべてのシステムでサポートされています。 本質的に、ウィンドウ関数はユニットを使用した作業の拡張です。 そして、もちろん、カスタム集計関数もウィンドウコンテキストで機能します。



拡張子はこれです。 SQL：2003以前は、集計関数は特定のウィンドウで機能しました。これは、GROUP BY式のフィールド値の組み合わせに対応する結果セット全体またはその一部のいずれかでした。 ユーザーはこのウィンドウを自由に操作できるようになりました。



違いは、ウィンドウを使用して計算された値が別の列の出力に追加され、集計関数を使用してストリーム全体を折りたたむ必要がないことです。 そのため、1つの要求で、それぞれ独自のコンテキスト（ウィンドウ）で複数のウィンドウ集合を使用できます。 以前はいくつかの集約関数がありましたが、それらはすべて1つのウィンドウで機能しました。



大きなストローク

• 以上（）
  
  ウィンドウは結果セット全体です。 クエリ ' select count（1）from Samples 'が169を返すとします。この場合、 ' select count（1）over（）from Samples 'を実行すると、169回169回書き込まれた列が得られます。
• オーバー（パーティションバイ）
  
  これはGROUP BYの類似物であり、値の組み合わせごとに、集計関数が実行されるウィンドウが作成されます。 Samplesテーブルで、1つの整数列がvalであり、データが1〜169の数字であるとします。
  
  次に、 サンプルからクエリ ' select count（1）over（partition by（12 + val）/ 13） 'は、値13が169回書き込まれた列を返します。
  
  
• オーバー（ORDER BY）
  
  PARTITION BYと組み合わせることができ、カーソルの間にウィンドウのサイズを動的に変更できます。この場合、ウィンドウはグループの先頭から現在のカーソル位置まで拡張されます。 その結果、グループは集計列で同じ値を取得せず、独自の値を取得します。 累積金額の計算に便利です。 クエリ結果
  
  'Select sum（val）over（order by val）from Samples 'は、n番目の要素に1からnまでの自然数の合計が含まれる列になります。
• オーバー（行）
  
  カーソル位置またはORDER BY範囲の開始/終了から開始して、ウィンドウフレームを定義できます。
  
  
  
  たとえば、「 ... ROWS 1 PRECEDING ... 」は、ウィンドウが現在の行とその前の1で構成されることを意味します。 A ... ... 1 FOLLOWINGと2 FOLLOWING ... 'の間-ROWSは、カーソルの直後の2行で構成されています。
  
  
  
  このモードのCURRENT ROWは、現在のカーソル位置を示します。 たとえば、「現在の行と未バインドのフォローの間 」は、現在の行から範囲の終わりまでを意味します。
• オーバー（範囲）
  
  ここでCURRENT ROWは、ウィンドウの先頭としてORDER BYからの範囲の始まりを意味し、ウィンドウの終わりとしてORDER BYの範囲の最後の行を意味するという点でROWSと異なります。

異なるシステムでウィンドウ関数を使用するための構文はわずかに異なります。



上記を要約すると、SQLのさまざまなレポートの構築を分析した開発者が最も一般的なケースを強調し、構文で厳密に具体化したという少し苦痛な気持ちが残っています。

レコードを返す関数

集計/ウィンドウ関数の出力では、結果の各行は、着信データストリームの特定の範囲の行に対応します。 人生では、そのような通信は常に存在するとは限りません。



たとえば、共分散行列10X10を作成する必要があります（ このためには、672X672が必要になります）。 これは 1つのパスで実行できます。このため、10個の数値パラメーターを使用して作成した集計関数を実行します。 彼女の作業の結果は、10個の値からなる10行のレコードセットです。各マトリックス要素は、入力ストリームのすべての行を参照します（いくつあるかに関係なく）。



たとえば、PostgreSQlでは、関数から2次元配列を返すことができます（例： 'ARRAY [[1,2]、[3,4]'）。 または、マトリックスを行にシリアル化します。



良いことですが、このアプローチで受け入れられるフレームワークで結果のサイズを維持することは常に可能とは限りません。





最後に結果全体が同時に取得されるため、共分散行列はまだ入力ストリームと出力ストリーム間の非同期化の非常に良い例ではありません。 ソースデータストリームを処理/圧縮したいとします。 同時に

• 大量のデータがあり、それらはインデックスなしの「ヒープ」にあります。実際、それらは単に「迅速に」ディスクに書き込まれました。
• 比較的少数の異なるカテゴリに分類する必要があります
• カテゴリ内で、時間間隔の平均、平均のみ、測定数と分散のみを保存
• これらすべてを迅速に行う必要があります

オプションは何ですか？

1. SQL内では、時間間隔/カテゴリでソートする必要がありますが、これは最後のポイントと矛盾します。
2. データが既に時間でソートされており（実際には保証されていない）、この事実をSQLプロセッサに伝えることができる場合は、ウィンドウ関数と1つのパスで行うことができます。
3. これをすべて行う別のアプリケーションを作成します。 PL / SQL、またはより多くの場合、C / C ++で多くのデータがあることを考えると。
4. レコードを返す関数。 おそらく彼らは私たちを助けることができます。

A.4。の詳細 これには、一時テーブルとパイプライン関数の2つのメカニズムがあります。

1. コンベア機能。
   
   このメカニズムはOracle（2001年9i以降）に登場し、レコードセットを返す関数がデータを蓄積せず、必要に応じて計算できるようにします（パイプ経由で接続された2つのプロセスのstdoutとstdinの同期に似ています）。
   
   つまり パイプライン関数の結果は、この関数を終了する前に処理を開始する場合があります。 このためには、関数の定義で言うだけで十分です
   
   
   
   

     FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED IS …
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   本体に結果行を登録します
   
   
   
   

    LOOP … out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); … END LOOP;
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   その結果、
   
   
   
   

    SELECT * FROM TABLE( refcur_pkg.f_trans( CURSOR(SELECT * FROM employees WHERE department_id = 60)));
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   パイプライン関数がある場合、カスタム集計は必要ありません。
   
   
   
   オラクル、ブラボー！
   
   
   
   少し前（2014）、パイプライン関数はDB2（IBM i 7.1 TR9、i 7.2 TR1）にも登場しました。
2. 一時テーブル。
   
   そもそも、 MS SQLでもPostgreSQLでも、集約関数からカーソルを返すことは明らかに不可能です。
   
   
   
   さて、パイプライン関数と同様に、カーソルをパラメーターとして取得し、処理し、一時テーブルに追加して、カーソルを戻しましょう。
   
   
   
   ただし、MS SQLでは、パラメータによってストアドプロシージャにカーソルを渡すことはできません ;プロシージャでカーソルを作成し、出力を通じてパラメータを返すことのみが可能です。 PostgreSQL についても同じことが言えます。
   
   
   
   さて、大丈夫、カーソルを開いて減算し、値を処理し、結果を計算し、一時テーブルに追加して、カーソルをレンダリングします。
   
   
   
   または、さらに簡単に、クエリ結果を1つの一時テーブルに追加して処理し、カーソルを介して別の一時テーブルに結果を返します。
   
   
   
   何と言ってもいい。 まず、そして最も重要なことは、カーソルを介したデータの読み取りは、ストリームでの処理よりも遅いことです。 次に、なぜSQLプロセッサが必要なのか、カーソルを使用してテーブルを読み取り、手で一時テーブルを作成し、ループ内に結合ロジックを作成しましょう... C / C ++でのアセンブラ挿入のようです。

したがって、レコードセットを返す関数に関する質問を検討した結果、結論に達しました。

• ここでは、カスタム集計はあまり役に立ちません。
• いずれにせよ、データベースにいくつかのオブジェクトを作成する必要があります。 関数であれ一時テーブルであれ。 少なくとも、適切な権利が必要です。 そして、いくつかの数字を処理したいだけです。
  
  
• それにもかかわらず、説明されている制限があっても、あまりエレガントではない場合がありますが、この方法を使用して問題を解決できます。

他に何

実際、問題を解決する機会がすでにある場合、著者には他に何が必要ですか？

実際、チューリングマシンは何でも計算できますが、それは非常に速くなく、あまり便利ではありません。



次のように要件を策定します。

1. 残り（選択、投影など）と同等に使用できる関係演算子である必要があります。
2. あるデータストリームを別のデータストリームに変換する演算子でなければなりません
3. 入力ストリームと出力ストリームの間に同期はありません
4. 演算子宣言は、出力ストリームの構造を定義します
5. 演算子は、動的に初期化する機能を持っています（関数の形式で、より正確にはその本体で、演算子の定義で直接指定されます）
6. 関数の形式のデストラクタ（...）
7. 入力ストリームから新しい行が取得されるたびに呼び出される関数（...）
8. オペレーターには実行コンテキストがあります-作業に必要なユーザー定義の変数および/またはコレクションのセット
9. この演算子を実行するには、データベースオブジェクトを作成する必要はありません。追加の権限は必要ありません
10. 仕事に必要なものはすべて、1つの場所、1つの言語で定義されます。

むかしむかし、著者は、実装されたTTM /チュートリアルDのサブセットの自作プロセッサを拡張する演算子を作成しました。 現在、SQLについても同じ考え方が提案されています。



警告する価値があります。ここでSQLが終了し、即興が始まります。 構文は元のもののままであり、最終的には任意の構文糖が存在する可能性がありますが、これは本質を変更しません。



したがって、 噛む演算子は

1. 出力フィールドとそのタイプのリストを含むヘッダー。
   
   各出力（および入力）フィールドはローカル変数です。
   
   例： “ chew {“ var1” float、“ var2” integer}”は、出力ストリームに浮動小数点と整数の2つの列があることを意味します
2. ボディ-現時点でのイベントのコールバックのリスト-ストリームの開始、ストリームの終了、ライン。 構文上、関数はPL / SQLに近いです。 事前定義関数__interrupt （）はPIPEに類似しており、出力列に対応する変数から値を取得し、出力ストリームに配置します。 出力ストリームのバッファがオーバーフローすると、ハンドラーの作業が一時停止し、ストリームの受信側の作業が開始されます。
   
   例：「フック」init」{var1：= 0; var2：= -1; } "

例を表示する最も簡単な方法。

• 集計関数SUMの類似物。
  
  
  
  

   --  'select sum(val) from samples' -- select * from samples chew {“sum(val)” float} --    hook “init” { “sum(val)” := 0; --      } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; end if; } hook “finit” { call __interrupt(); --  PIPE }
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  
  
  かさばっていますが、これは単なる例です。
  
  いくつかの数字を追加するためにCプログラムを作成する必要はありません。
• SUM + AVG
  
  
  
  

   --  'select sum(val), avg(val) from samples' -- select * from samples chew { “sum(val)” float, “avg(val)” float --       } hook “init” { “sum(val)” := 0; “avg(val)” := 0; var num integer; num := 0; --    ,       } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then “avg(val)” := “sum(val)” / num; end if; call __interrupt(); }
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

  
  
  ここでは、合計が1回しか発生しないという事実に注目します。
• SUM + GROUP BY
  
  
  
  

   --  'select sum(val) from samples group by type' -- select * from --     ( samples val, type from samples order by type ) chew { “sum(val)” float } hook “init” { “sum(val)” := 0; var gtype integer; gtype := NULL; var num integer; --   num := 0; } hook “row” { if (gtype <> “type”) then __interrupt(); “gtype” := type; "sum(val)" := 0; num := 0; end if; if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then call __interrupt(); end if; }
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

• ROW_NUMBER（）OVER（）
  
  
  
  

   -- select row_number() over() as num, * from samples -- select * from samples chew { “num” integer, * --        --   '* except val1, ...valX',   TTM } hook “init” { num := 0; } hook “row” { num := num + 1; call __interrupt(); }
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

このアプローチが通常の方法で根本的に達成できない結果をもたらす例を提供することは可能ですか？ それらがあります。



データがほとんどソートされている場合があります。 完全に並べ替えられている場合もありますが、確実にはわかりません。



上記の例（データストリーム圧縮）で、データはさまざまなソースからのものであり、さまざまな理由でわずかに混在する可能性があるとします。 つまりタイムスタンプT1を持つ1つのソースからの行は、時間T2を持つ別のソースからの行の後にデータベースに存在できますが、T1 <T2です。



T1とT2の差が特定の（わずかな）定数を決して超えないことを保証したとしても、ここで（従来の方法で）ソートせずにはできません。



ただし、提案されたアプローチを使用すると、入力ストリームがバッファリングされ、現在の時間間隔のデータが処理されるのは、入力が間隔の右境界までに少なくとも一定の定数を超えるタイムスタンプを持つ行を受信した後のみです。



ここには非常に重要なポイントがあります。



データがほとんどソートされていることを知っているのは私たちだけです。



私たちだけがその定数の値を知っています。



この定数は、この問題にのみ特徴的であり、おそらくこの実験にのみ特徴的です。

そして、このハックを自分の責任で使用して、ソートを回避します。



タスクに関する標準的な知識は、SQLプロセッサに伝えるための標準的な方法では存在せず、想像することは困難です。



また、ラムダ関数を使用すると、SQLプロセッサに、必要な場所で必要なことを正確に実行させる普遍的な方法が提供されます。

おわりに

提案された設計は、実装するのがそれほど難しくないようです。



いずれの場合でも、有効なPL / SQLを使用します。



アイデア自体はシンプルで直感的で、新しいエンティティを言語に追加しません。



これは単一のユニットであり、必要に応じて、集約関数とウィンドウ関数GROUP BYを置き換えます。



従来のSQLプロセッサでは方法がない場合に、ソートなしで実行できるメカニズム。



しかし、最も重要なのは、データを使用して最も必要な方法で、好きなことを自由に行えるメカニズムです。



PS：記事の準備に参加してくれたDorofei Proleskovskyに感謝します。