17.00 - 17.05 - Javaの実装 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL外部ルーチン プログラミング

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
Publication ID
B035-1147-170K-JPN
Language
日本語 (日本)
import com.teradata.fnc.*;
import java.io.*;
import java.sql.*;

class agr_storage implements Serializable{
   double count;
   double x_sq;
   double x_sum;

   public agr_storage(double a, double b, double c){
      count =a;
      x_sq =b;
      x_sum = c;
   }
}

public class UDFExample {
   static boolean debug = true;

   public static Double STD_DEV(Phase phase,
                                Context[] context,
                                double x) throws SQLException
   {

   try {
      agr_storage s1 =null;
      agr_storage s2 =null;

      /* The aggregate storage only hold valid data in the
       * AGR_DETAIL, AGR_COMBINE and AGR_FINAL phases. */	
      if(phase.getPhase()>Phase.AGR_INIT&&
         phase.getPhase()<Phase.AGR_NODATA){
         s1 = (agr_storage)context[0].getObject(1);

         /* switch to determine the aggregation phase */
         switch (phase.getPhase())
         {			
            /* This phase is executed once per group and */
            /* allocates and initializes intermediate    */
            /* aggregate storage                         */
            case Phase.AGR_INIT:
               if (debug) System.err.println("Phase: AGR_INIT");

               /* Get storage for intermediate aggregate values.*/
               s1 = new agr_storage(0,0,0);
               context[0].initCtx(s1);

               /*********************************************/
               /* Fall through to detail phase, because the */
               /* AGR_INIT phase passes in the first set of */
               /* values for the group.                     */
               /*********************************************/

            /* This phase is executed once for each selected    */
            /* row to aggregate. One copy will run on each AMP. */
            case Phase.AGR_DETAIL:
               if (debug) System.err.println("Phase: AGR_DETAIL");
               s1.count++;
               s1.x_sq +=x*x;
               s1.x_sum += x;
               break;

            /* This phase combines the results of ALL */
            /* individual AMPs for each group.        */
            case Phase.AGR_COMBINE:
               if (debug) System.err.println("Phase: AGR_COMBINE");

               /* This is the only phase where Context.getObject(2) */
               /* has a valid value.                                */
               s2=(agr_storage)context[0].getObject(2);
               s1.count += s2.count;
               s1.x_sq += s2.x_sq;
               s1.x_sum += s2.x_sum;				
               break;

            /* This phase returns the final result. */
            /* It is called once for each group.    */
            case Phase.AGR_FINAL:
               if (debug) System.err.println("Phase: AGR_FINAL");
               double term2 = s1.x_sum/s1.count;
               double variance = s1.x_sq/s1.count - term2*term2;

               /* Adjust for deviations close to zero */
               if (Math.abs(variance) < 1.0e-14)
                  variance = 0.0;
               return new Double(Math.sqrt(variance));

            case Phase.AGR_NODATA:
               if (debug) System.err.println("Phase: AGR_NODATA");

               /* return null if no data */
               return null;

            default:
               /* If it gets here there must be an error     */
               /* because this function does not accept any  */
               /* other phase options                        */
               throw new SQLException("Invalid Phase", "38U05");
         }
         context[0].setObject( 1, s1 );

      } catch(IOException ex) {
         ex.printStackTrace();
      } catch(ClassNotFoundException e){
         e.printStackTrace();
      }
      return null;
   }

   public static Double fastSTD_DEV(Phase phase,
                                    Context[] context,
                                    double x) throws SQLException
   {
      /*The intermediate storage is a byte array of 3 doubles*/
      double count=0;
      double x_sq=0;
      double x_sum=0;

      try {
         ByteBuffer s1 =null;
         ByteBuffer s2 =null;

         /* The aggregate storage only hold valid data in the */
         /* AGR_DETAIL, AGR_COMBINE and AGR_FINAL phases.     */
         if (phase.getPhase() > Phase.AGR_INIT &&
            phase.getPhase() < Phase.AGR_NODATA) {
            s1 = ByteBuffer.wrap(context[0].getBytes(1));
            count = s1.getDouble();
            x_sq = s1.getDouble();
            x_sum = s1.getDouble();
         }

         /* switch to determine the aggregation phase */
         switch (phase.getPhase())
         {			

            /* This phase is executed once per group and */
            /* allocates and initializes intermediate    */
            /* aggregate storage                         */
            case Phase.AGR_INIT:
               if (debug) System.err.println("Phase: AGR_INIT");
               /* Get storage for intermediate aggregate values.*/
               context[0].initCtx(24);
               /*********************************************/
               /* Fall through to detail phase, because the */
               /* AGR_INIT phase passes in the first set of */
               /* values for the group.                     */
               /*********************************************/

            /* This phase is executed once for each selected */
            /* row to aggregate. One copy will run on each AMP. */
            case Phase.AGR_DETAIL:
               if (debug) System.err.println("Phase: AGR_DETAIL");
               s1 = ByteBuffer.allocate(24);
               count++;
               x_sq +=x*x;
               x_sum += x;
               s1.putDouble(count);
               s1.putDouble(x_sq);
               s1.putDouble(x_sum);
               break;

            /* This phase combines the results of ALL */
            /* individual AMPs for each group.        */
            case Phase.AGR_COMBINE:
               if (debug) System.err.println("Phase: AGR_COMBINE");
               /*This is the only phase where Context.getBytes(2) */
               /* has a valid value.                              */
               s2 =ByteBuffer.wrap(context[0].getBytes(2));
               count += s2.getDouble();
               x_sq += s2.getDouble();
               x_sum += s2.getDouble();				
               break;

            /* This phase returns the final result. */
            /* It is called once for eeach group. */
            case Phase.AGR_FINAL:
            {
               if (debug) System.err.println("Phase: AGR_FINAL");
               double term2 = x_sum/count;
               double variance = x_sq/count - term2*term2;

               /* Adjust for deviations close to zero */
               if (Math.abs(variance) < 1.0e-14)
                  variance = 0.0;
               return new Double(Math.sqrt(variance));
            }
            case Phase.AGR_NODATA:
               if (debug) System.err.println("Phase: AGR_NODATA");
               /* return null if no data */
               return null;
            default:
               /* If it gets here there must be an error    */
               /* because this function does not accept any */
               /* other phase options                       */
               throw new SQLException("Invalid Phase", "38U05");
         }
         context[0].setBytes( 1, s1.array() );
      } catch(IOException ex){
         ex.printStackTrace();
      } catch(ClassNotFoundException e){
         e.printStackTrace();
      }
      return null;/*will be ignored*/
   }

}