16.20 - 例: GeoSequenceFromRows - Teradata Vantage NewSQL Engine

Teradata Vantage™ 地理空間データ型

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1181-162K-JPN
CREATE TABLE TripRowInput(
       trip_id INTEGER NOT NULL,
       pi_AMP INTEGER NOT NULL,
       flag INTEGER NOT NULL,
       pcount INTEGER NOT NULL,
       ppi# INTEGER NOT NULL,
       seq INTEGER,
       x FLOAT,
       y FLOAT,
       t TIMESTAMP,
       link_id NUMERIC(18),
       speed FLOAT,
       accel FLOAT,
       heading FLOAT)
PRIMARY INDEX(trip_id)
PARTITION BY ppi#;

INSERT INTO TripRowInput(100, 200, 300, 400, 500, 600, 1.11, 2.11, 
'2014-01-07 13:29:49.200000', 12112121, 150.2, 110.1, 120);
   
  CREATE TABLE TempFromRowsInput(
      trip_id INTEGER NOT NULL,
      pi_AMP INTEGER NOT NULL,
      flag INTEGER NOT NULL,
      pcount INTEGER NOT NULL,
      ppi# INTEGER NOT NULL,
      seq INTEGER,
      x FLOAT,
      y FLOAT,
      t TIMESTAMP,
      link_id NUMERIC(18),
      speed FLOAT,
      accel FLOAT,
      heading FLOAT)
   PRIMARY INDEX(trip_id)
   PARTITION BY ppi#;
   INSERT INTO TempFromRowsInput
   SELECT trip_id, pi_AMP, flag,
      COUNT(*) OVER (PARTITION BY pi_AMP, trip_id ORDER BY trip_id)
         AS pcount,
      SUM (flag) OVER (PARTITION BY pi_AMP ORDER BY trip_id, flag
         DESC ROWS UNBOUNDED PRECEDING) AS ppi#,
      seq, x, y, t, link_id, speed, accel, heading
      FROM (
         SELECT trip_id,
            HASHAMP(HASHBUCKET(HASHROW(trip_id))) AS pi_AMP,
            CASE WHEN trip_id = MAX(trip_id) OVER
               (PARTITION BY pi_AMP ORDER BY trip_id
                  ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
               THEN 0
               ELSE 1
            END AS flag,
            seq, x, y, t, link_id, speed, accel, heading
         FROM TripRowInput) d
      QUALIFY ppi# > 0 and ppi# <= 64000;

   CREATE TABLE trips AS (
      SELECT T.trip_id, R.geom
      FROM (
         SELECT trip_id, pcount, seq, x, y, t, link_id, speed, accel,
            heading, flag
         FROM TempFromRowsInput) T,
         TABLE (GeoSequenceFromRows(T.trip_id, T.pcount, T.seq,
            T.x, T.y, T.t, T.link_id, T.speed, T.accel, T.heading,
            NULL, NULL, NULL, NULL, NULL, NULL, NULL)) R
      WHERE R.out_key = T.trip_id
      QUALIFY( COUNT(*) OVER (PARTITION BY T.trip_id
         ORDER BY T.trip_id ROWS UNBOUNDED PRECEDING) = 1 )
   ) WITH DATA;