| -- @@@ START COPYRIGHT @@@ |
| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, |
| -- software distributed under the License is distributed on an |
| -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| -- KIND, either express or implied. See the License for the |
| -- specific language governing permissions and limitations |
| -- under the License. |
| -- |
| -- @@@ END COPYRIGHT @@@ |
| |
| ?section ddl |
| -- CREATE database |
| drop table t023t1; |
| drop table t023t2; |
| drop table t023t3; |
| |
| drop table t023t4; |
| drop table t023t5; |
| drop table t023t6; |
| drop table t023t7; |
| |
| log LOG023 clear; |
| |
| create table t023t1 ( |
| a int not null , |
| b int not null , |
| c int not null , |
| d int not null , |
| primary key(a) ); |
| |
| create table t023t2 ( |
| a int not null , |
| b int not null , |
| c int not null , |
| d int not null , |
| primary key(a) ); |
| |
| create table t023t3 ( |
| a int not null , |
| b int not null , |
| c int not null , |
| d int not null , |
| primary key(a) ); |
| |
| |
| create table t023t4 ( |
| sk int not null , |
| numRows int not null , |
| a0 int not null , |
| a1 int not null , |
| a2 int not null , |
| a3 int not null , |
| primary key(sk) ); |
| |
| create table t023t5 ( |
| sk int not null , |
| numRows int not null , |
| b0 int not null , |
| b1 int not null , |
| b2 int not null , |
| b3 int not null , |
| b4 int not null , |
| b5 int not null , |
| b6 int not null , |
| b7 int not null , |
| primary key(sk) ); |
| |
| create table t023t6 ( |
| sk int not null , |
| numRows int not null , |
| c0 int not null , |
| c1 int not null , |
| c2 int not null , |
| c3 int not null , |
| c4 int not null , |
| c5 int not null , |
| c6 int not null , |
| c7 int not null , |
| primary key(sk) ); |
| |
| create table t023t7 ( |
| sk int not null , |
| numRows int not null , |
| d0 int not null , |
| d1 int not null , |
| d2 int not null , |
| d3 int not null , |
| d4 int not null , |
| d5 int not null , |
| d6 int not null , |
| d7 int not null , |
| d8 int not null , |
| d9 int not null , |
| d10 int not null , |
| d11 int not null , |
| d12 int not null , |
| d13 int not null , |
| d14 int not null , |
| d15 int not null , |
| primary key(sk) ); |
| |
| ?section dml |
| |
| -- POPULATE TABLES |
| |
| insert into t023t1 values |
| (0, 0, 0, 0), |
| (1, 10, 100, 1000), |
| (2, 20, 200, 2000), |
| (3, 30, 300, 3000), |
| (4, 40, 400, 4000), |
| (5, 50, 500, 5000), |
| (6, 60, 600, 6000), |
| (7, 70, 700, 7000), |
| (8, 80, 800, 8000), |
| (9, 90, 900, 9000); |
| |
| insert into t023t2 ( |
| select a * 10 + c1, c1, c1 * 10, c1 * 100 |
| from t023t1 |
| transpose 0,1,2,3,4,5,6,7,8,9 as c1 |
| key by k1); |
| |
| insert into t023t3 ( |
| select a * 10 + c1, c1, c + c1, d + (c1 * 10) |
| from t023t2 |
| transpose 0,1,2,3,4,5,6,7,8,9 as c1 |
| key by k1); |
| |
| |
| insert into t023t4 values (0, 4, 0, 1, 2, 3), |
| (4, 4, 4, 5, 6, 7), |
| (8, 4, 8, 9, 10, 11), |
| (12, 4, 12, 13, 14, 15), |
| (16, 4, 16, 17, 18, 19), |
| (20, 4, 20, 21, 22, 23), |
| (24, 4, 24, 25, 26, 27), |
| (28, 4, 28, 29, 30, 31), |
| (32, 4, 32, 33, 34, 35); |
| |
| insert into t023t5 values |
| (0, 8, 100, 101, 102, 103, 104, 105, 106, 107), |
| (8, 8, 108, 109, 110, 111, 112, 113, 114, 115), |
| (16, 8, 116, 117, 118, 119, 120, 121, 122, 123), |
| (24, 8, 124, 125, 126, 127, 128, 129, 130, 131), |
| (32, 4, 132, 133, 134, 135, -1, -1, -1, -1); |
| |
| insert into t023t6 values |
| (0, 8, 200, 201, 202, 203, 204, 205, 206, 207), |
| (8, 8, 208, 209, 210, 211, 212, 213, 214, 215), |
| (16, 8, 216, 217, 218, 219, 220, 221, 222, 223), |
| (24, 8, 224, 225, 226, 227, 228, 229, 230, 231), |
| (32, 4, 232, 233, 234, 235, -1, -1, -1, -1); |
| |
| insert into t023t7 values |
| (0, 16, 300, 301, 302, 303, 304, 305, 306, 307, |
| 308, 309, 310, 311, 312, 313, 314, 315), |
| (16, 16, 316, 317, 318, 319, 320, 321, 322, 323, |
| 324, 325, 326, 327, 328, 329, 330, 331), |
| (32, 4, 332, 333, 334, 335, -1, -1, -1, -1, |
| -1,-1,-1,-1,-1,-1,-1,-1); |
| |
| -- SOME BASIC QUERIES USING TRANSPOSE. |
| |
| select * |
| from t023t1 |
| transpose a,b,c,d as c1 |
| key by k1; |
| |
| select * |
| from t023t1 |
| transpose a+b,b+c,c+d,d+a as c1 |
| key by k1; |
| |
| select * |
| from t023t1 |
| transpose a,b,c,d as c1 |
| key by k1 |
| transpose c1, k1 as c2 |
| key by k2; |
| |
| select * |
| from t023t1 |
| transpose a,b,c,d as c1 |
| 1 + a, 2 + b, 3 + c, 4 + d as c2 |
| key by k1; |
| |
| |
| -- All combinations of legal clauses with transpose. |
| -- |
| -- from_clause |
| -- [where_clause] |
| -- [transpose_clause]+ |
| -- [groupby_clause] |
| -- [having_clause] |
| -- |
| |
| select * |
| from t023t1 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2; |
| |
| select sum(a) |
| from t023t1 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2 |
| having sum(a) > 10; |
| |
| select sum(a) |
| from t023t1 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2 |
| group by k1, k2; |
| |
| select sum(a) |
| from t023t1 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2 |
| group by k1, k2 |
| having sum(a) > 10; |
| |
| select * |
| from t023t1 |
| where a > 5 |
| and b < 80 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2; |
| |
| select sum(a) |
| from t023t1 |
| where a > 5 |
| and b < 80 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2 |
| having sum(a) > 10; |
| |
| select sum(a) |
| from t023t1 |
| where a > 5 |
| and b < 80 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2 |
| group by k1, k2; |
| |
| select sum(a) |
| from t023t1 |
| where a > 5 |
| and b < 80 |
| transpose a, b, c, d as c1 |
| 1, 2, 3, 4 as c2 |
| key by k1 |
| transpose d, c, b, a as c3 |
| key by k2 |
| group by k1, k2 |
| having sum(a) > 10; |
| |
| |
| -- Subqueries within the Transpose expressions. |
| |
| select * |
| from t023t1 |
| transpose a,b,c,d as c1 |
| (select sum(a) from t023t2), |
| (select sum(b) from t023t2), |
| (select sum(c) from t023t2), |
| (select sum(d) from t023t2) as c2 |
| key by k1; |
| |
| -- Queries typical of DataMining application |
| -- Calculate multiple crosstables, trunctating the IV's |
| -- |
| select ctid, iv, b, count(*) |
| from t023t3 |
| transpose Cast(a/100 as int), |
| c - (Cast(c/10 as int) * 10), |
| d - (Cast(d/3 as int) * 3) as iv |
| key by ctid |
| group by ctid, iv, b; |
| |
| -- Calculate each crosstable independently. |
| |
| select ctid, iv, b, count(*) |
| from t023t3 |
| transpose Cast(a/100 as int) as iv |
| key by ctid |
| group by ctid, iv, b; |
| |
| select ctid, iv, b, count(*) |
| from t023t3 |
| transpose c - (Cast(c/10 as int) * 10) as iv |
| key by ctid |
| group by ctid, iv, b; |
| |
| select ctid, iv, b, count(*) |
| from t023t3 |
| transpose d - (Cast(d/3 as int) * 3) as iv |
| key by ctid |
| group by ctid, iv, b; |
| |
| |
| -- Query typical of a continuous crosstable. |
| |
| select ctid, b, Cast(count(*) as int), |
| Cast(avg(iv) as int), |
| Cast(variance(iv) as int), |
| Cast(stddev(iv) as int) |
| from t023t3 |
| transpose a, c, b as iv |
| key by ctid |
| group by ctid, b |
| order by ctid, b; |
| |
| select ctid, b, count(*) |
| from t023t3 |
| transpose a, c, b as iv |
| key by ctid |
| group by ctid, b |
| order by ctid, b; |
| |
| -- A transpose within a subquery with correlated values |
| -- within the transpose expressions. |
| |
| select t1.a, t2.b |
| from t023t1 t1, t023t2 t2 |
| where t1.a = t2.a |
| and t1.c < (select sum(c1) |
| from t023t3 t3 |
| where t3.c < t2.d |
| transpose t1.a, t2.b as c1 |
| key by k1); |
| |
| -- This query could create a VEG between t1.k1 and t2.k2 |
| -- but, for now, VEG's are not created between ValueIDUnion nodes. |
| -- To enable VEGRefs for ValueIDUnion nodes, change ValueIdUnion:: |
| -- normalizeNode() to replace with a VegRef (if possible). |
| -- To allow ValueIdUnion nodes in a VEG, alter isAColumnReference() |
| -- to include ITM_VALUEIDUNION. |
| -- |
| select * |
| from (select k1,c1 |
| from t023t1 |
| transpose a,b,c as c1 |
| key by k1) as t1, |
| (select k2,c2 |
| from t023t1 |
| transpose c,b,a as c2 |
| key by k2) as t2 |
| where t1.k1 = t2.k2 |
| and t1.k1 < 3; |
| |
| -- This is the type of query that may be generated when scanning a packed |
| -- vertically partitioned table. Here we simulate four VP's with the tables |
| -- t023t4, t023t5, t023t6, and t023t7. Each of the tables contains the data |
| -- for one column of the 'base' table. The VP's are also packed with the |
| -- packing factors of 4, 8, 8, and 16 for the VP's respectively. The following |
| -- query first unpacks each VP to a packing factor of 4 then joins the |
| -- results to form a packed (non-VP) table. This result is then unpacked to |
| -- a packing factor of 1 (non-packed). Unpacking is accomplished through use |
| -- of the Transpose operator. |
| |
| select a,b,c,d |
| from |
| (select (skp + (k - 1)) as skp2, numRowsp2, a,b,c, d |
| from |
| (select VP1.skp, VP1.numRowsp, ap0, bp0, cp0, dp0, |
| ap1, bp1, cp1, dp1, |
| ap2, bp2, cp2, dp2, |
| ap3, bp3, cp3, dp3 |
| from (select sk as skp, numRows as numRowsp, |
| a0 as ap0, a1 as ap1, a2 as ap2, a3 as ap3 |
| from t023t4) as VP1, |
| (select sk + ((k - 1) * 4) as skp, numRowsp, bp0, bp1, bp2, bp3 |
| from t023t5 |
| transpose (case when numRows > 4 then 4 else numRows end, |
| b0,b1,b2,b3), |
| (case when numRows > 4 then numRows - 4 else 0 end, |
| b4,b5,b6,b7) |
| as (numRowsp, bp0,bp1,bp2,bp3) |
| key by k) as VP2, |
| (select sk + ((k - 1) * 4) as skp, numRowsp, cp0, cp1, cp2, cp3 |
| from t023t6 |
| transpose (case when numRows > 4 then 4 else numRows end, |
| c0,c1,c2,c3), |
| (case when numRows > 4 then numRows - 4 else 0 end, |
| c4,c5,c6,c7) |
| as (numRowsp, cp0,cp1,cp2,cp3) |
| key by k) as VP3, |
| (select sk + ((k - 1) * 4) as skp, numRowsp, dp0, dp1, dp2, dp3 |
| from t023t7 |
| transpose (case when numRows > 4 then 4 else numRows end, |
| d0,d1,d2,d3), |
| (case when numRows > 8 then 4 else |
| case when numRows > 4 then numRows - 4 else 0 end end, |
| d4,d5,d6,d7), |
| (case when numRows > 12 then 4 else |
| case when numRows > 8 then numRows - 8 else 0 end end, |
| d8,d9,d10,d11), |
| (case when numRows > 12 then numRows - 12 else 0 end, |
| d12,d13,d14,d15) |
| as (numRowsp, dp0,dp1,dp2,dp3) |
| key by k) as VP4 |
| |
| where VP1.skp = VP2.skp |
| and VP2.skp = VP3.skp |
| and VP3.skp = VP4.skp |
| ) as P1 |
| transpose (case when numRowsp > 0 then 1 else 0 end, |
| ap0, bp0, cp0, dp0), |
| (case when numRowsp > 1 then 1 else 0 end, |
| ap1, bp1, cp1, dp1), |
| (case when numRowsp > 2 then 1 else 0 end, |
| ap2, bp2, cp2, dp2), |
| (case when numRowsp > 3 then 1 else 0 end, |
| ap3, bp3, cp3, dp3) as (numRowsp2, a,b,c,d) |
| key by k) as P2 |
| where numRowsp2 = 1; |
| ; |
| |
| ?section clnup |
| -- CLEANUP database |
| drop table t023t1; |
| drop table t023t2; |
| drop table t023t3; |
| |
| drop table t023t4; |
| drop table t023t5; |
| drop table t023t6; |
| drop table t023t7; |
| |
| log; |
| |
| |
| |
