You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kylin.apache.org by sh...@apache.org on 2018/05/18 09:53:13 UTC
[kylin] 03/08: KYLIN-3359 add unit test & integration test
This is an automated email from the ASF dual-hosted git repository.
shaofengshi pushed a commit to branch KYLIN-3359
in repository https://gitbox.apache.org/repos/asf/kylin.git
commit 10727c5eae4be8379178a2ceedf3140bb5a032c9
Author: Zhong <nj...@apache.org>
AuthorDate: Mon May 7 20:26:39 2018 +0800
KYLIN-3359 add unit test & integration test
Signed-off-by: shaofengshi <sh...@apache.org>
---
core-metadata/pom.xml | 6 +
.../expression/ExpressionCountDistributorTest.java | 210 +++++++++++++++++++++
.../expression/TupleExpressionSerializerTest.java | 77 ++++++++
.../metadata/expression/TupleExpressionTest.java | 84 +++++++++
.../org/apache/kylin/query/ITKylinQueryTest.java | 4 +
.../resources/query/sql_expression/query01.sql | 22 +++
.../resources/query/sql_expression/query02.sql | 34 ++++
7 files changed, 437 insertions(+)
diff --git a/core-metadata/pom.xml b/core-metadata/pom.xml
index 21aa6ee..791ae86 100644
--- a/core-metadata/pom.xml
+++ b/core-metadata/pom.xml
@@ -111,6 +111,12 @@
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
+ <dependency>
+ <groupId>org.powermock</groupId>
+ <artifactId>powermock-api-mockito</artifactId>
+ <version>${powermock.version}</version>
+ <scope>test</scope>
+ </dependency>
</dependencies>
</project>
diff --git a/core-metadata/src/test/java/org/apache/kylin/metadata/expression/ExpressionCountDistributorTest.java b/core-metadata/src/test/java/org/apache/kylin/metadata/expression/ExpressionCountDistributorTest.java
new file mode 100644
index 0000000..7342475
--- /dev/null
+++ b/core-metadata/src/test/java/org/apache/kylin/metadata/expression/ExpressionCountDistributorTest.java
@@ -0,0 +1,210 @@
+/*
+ * 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.
+*/
+
+package org.apache.kylin.metadata.expression;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.util.List;
+
+import org.apache.kylin.common.util.LocalFileMetadataTestCase;
+import org.apache.kylin.common.util.Pair;
+import org.apache.kylin.metadata.expression.TupleExpression.ExpressionOperatorEnum;
+import org.apache.kylin.metadata.filter.CompareTupleFilter;
+import org.apache.kylin.metadata.filter.IFilterCodeSystem;
+import org.apache.kylin.metadata.filter.TupleFilter;
+import org.apache.kylin.metadata.model.TblColRef;
+import org.apache.kylin.metadata.tuple.IEvaluatableTuple;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.powermock.api.mockito.PowerMockito;
+
+import com.google.common.collect.Lists;
+
+public class ExpressionCountDistributorTest extends LocalFileMetadataTestCase {
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ staticCreateTestMetadata();
+ }
+
+ @AfterClass
+ public static void after() throws Exception {
+ staticCleanupTestMetadata();
+ }
+
+ /**
+ * * 3 10
+ * * | |
+ * * 1*(1+2)*(col+1)*(3+4)+(1+2)*(3+1)*(4+5)-4+5 => 1*(1+2)*(col+1*n)*(3+4)+((1+2)*(3+1)*(4+5)-4+5)*n = 1363
+ * <p>
+ * * + +
+ * * / \ / \
+ * * - 5 - *
+ * * / \ / \ / \
+ * * + 4 + * n 5
+ * * / \ / \ / \
+ * * * * ===> * * n 4
+ * * / \ / \ / \ / \
+ * * * + * + * + * +
+ * * / \ / \ / \ / \ / \ / \ / \ / \
+ * * * + 3 4 + + 4 5 * + 3 4 + + 4 5
+ * * / \ / \ / \ / \ / \ / \ / \ / \
+ * * 1 + col 1 1 2 3 1 1 + col * * * 3 1
+ * * / \ / \ / \ / \ / \
+ * * 1 2 1 2 n 1 n 1 n 2
+ */
+ @Test
+ public void testDistribute1() {
+ NumberTupleExpression n = new NumberTupleExpression(10);
+ ExpressionCountDistributor cntDistributor = new ExpressionCountDistributor(n);
+
+ TupleExpression t0 = new NumberTupleExpression(1);
+ TupleExpression t1 = new NumberTupleExpression(1);
+ TupleExpression t2 = new NumberTupleExpression(2);
+
+ TblColRef c = PowerMockito.mock(TblColRef.class);
+ TupleExpression t3 = new ColumnTupleExpression(c);
+ IEvaluatableTuple evaluatableTuple = PowerMockito.mock(IEvaluatableTuple.class);
+ IFilterCodeSystem filterCodeSystem = PowerMockito.mock(IFilterCodeSystem.class);
+ t3 = PowerMockito.spy(t3);
+ PowerMockito.when(t3.calculate(evaluatableTuple, filterCodeSystem)).thenReturn(new BigDecimal(3));
+
+ TupleExpression t4 = new NumberTupleExpression(1);
+ TupleExpression t5 = new NumberTupleExpression(3);
+ TupleExpression t6 = new NumberTupleExpression(4);
+ TupleExpression t7 = new NumberTupleExpression(1);
+ TupleExpression t8 = new NumberTupleExpression(2);
+ TupleExpression t9 = new NumberTupleExpression(3);
+ TupleExpression t10 = new NumberTupleExpression(1);
+ TupleExpression t11 = new NumberTupleExpression(4);
+ TupleExpression t12 = new NumberTupleExpression(5);
+ TupleExpression t13 = new NumberTupleExpression(4);
+ TupleExpression t14 = new NumberTupleExpression(5);
+
+ TupleExpression b0 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t1, t2));
+
+ TupleExpression b1 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(t0, b0));
+ TupleExpression b2 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t3, t4));
+ TupleExpression b3 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t7, t8));
+ TupleExpression b4 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t9, t10));
+
+ TupleExpression b11 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(b1, b2));
+ TupleExpression b12 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t5, t6));
+ TupleExpression b13 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(b3, b4));
+ TupleExpression b14 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t11, t12));
+
+ TupleExpression b21 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(b11, b12));
+ TupleExpression b22 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(b13, b14));
+
+ TupleExpression b31 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(b21, b22));
+
+ TupleExpression b41 = new BinaryTupleExpression(ExpressionOperatorEnum.MINUS, Lists.newArrayList(b31, t13));
+
+ TupleExpression b51 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(b41, t14));
+
+ TupleExpression ret = b51.accept(cntDistributor);
+ assertTrue(cntDistributor.ifCntSet());
+
+ assertEquals(new BigDecimal(1363), ret.calculate(evaluatableTuple, filterCodeSystem));
+ }
+
+ /**
+ * * 3 10
+ * * | |
+ * * (1+2)*(case when f1 = 'c1' then (1+2)*(col+1)+3 (1+2)*(case when f1 = 'c1' then (1+2)*(col+n*1)+n*3
+ * * when f1 = 'c2' then (2+col)*(2+3)+4 => when f1 = 'c2' then (n*2+col)*(2+3)+n*4
+ * * else 6 else n*6
+ * * end) + col*2 + 1 end) + col*2 + n*1
+ */
+ @Test
+ public void testDistribute2() {
+ NumberTupleExpression n = new NumberTupleExpression(10);
+ ExpressionCountDistributor cntDistributor = new ExpressionCountDistributor(n);
+
+ TupleExpression t1 = new NumberTupleExpression(1);
+ TupleExpression t2 = new NumberTupleExpression(2);
+
+ TupleExpression t3 = new NumberTupleExpression(1);
+ TupleExpression t4 = new NumberTupleExpression(2);
+
+ TblColRef c = PowerMockito.mock(TblColRef.class);
+ TupleExpression t5 = new ColumnTupleExpression(c);
+ IEvaluatableTuple evaluatableTuple = PowerMockito.mock(IEvaluatableTuple.class);
+ IFilterCodeSystem filterCodeSystem = PowerMockito.mock(IFilterCodeSystem.class);
+ t5 = PowerMockito.spy(t5);
+ PowerMockito.when(t5.calculate(evaluatableTuple, filterCodeSystem)).thenReturn(new BigDecimal(3));
+
+ TupleExpression t6 = new NumberTupleExpression(1);
+ TupleExpression t7 = new NumberTupleExpression(3);
+
+ TupleExpression t8 = new NumberTupleExpression(2);
+ TupleExpression t9 = new NumberTupleExpression(2);
+ TupleExpression t10 = new NumberTupleExpression(3);
+ TupleExpression t11 = new NumberTupleExpression(4);
+
+ TupleExpression t12 = new NumberTupleExpression(6);
+
+ TupleExpression t13 = new NumberTupleExpression(2);
+ TupleExpression t14 = new NumberTupleExpression(1);
+
+ TupleExpression b1 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t1, t2));
+ TupleExpression b2 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t3, t4));
+ TupleExpression b3 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t5, t6));
+ TupleExpression b4 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t8, t5));
+ TupleExpression b5 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(t9, t10));
+ TupleExpression b6 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(t5, t13));
+
+ TupleExpression b11 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(b2, b3));
+ TupleExpression b12 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(b4, b5));
+
+ TupleExpression b21 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(b11, t7));
+ TupleExpression b22 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(b12, t11));
+
+ TupleFilter f1 = PowerMockito.mock(CompareTupleFilter.class);
+ TupleFilter f2 = PowerMockito.mock(CompareTupleFilter.class);
+
+ List<Pair<TupleFilter, TupleExpression>> whenList = Lists.newArrayList();
+ whenList.add(new Pair<>(f1, b21));
+ whenList.add(new Pair<>(f2, b22));
+ TupleExpression b31 = new CaseTupleExpression(whenList, t12);
+
+ TupleExpression b41 = new BinaryTupleExpression(ExpressionOperatorEnum.MULTIPLE, Lists.newArrayList(b1, b31));
+
+ TupleExpression b51 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(b41, b6));
+
+ TupleExpression b61 = new BinaryTupleExpression(ExpressionOperatorEnum.PLUS, Lists.newArrayList(b51, t14));
+
+ TupleExpression ret = b61.accept(cntDistributor);
+ assertTrue(cntDistributor.ifCntSet());
+
+ PowerMockito.when(f1.evaluate(evaluatableTuple, filterCodeSystem)).thenReturn(true);
+ assertEquals(new BigDecimal(223), ret.calculate(evaluatableTuple, filterCodeSystem));
+
+ PowerMockito.when(f1.evaluate(evaluatableTuple, filterCodeSystem)).thenReturn(false);
+ PowerMockito.when(f2.evaluate(evaluatableTuple, filterCodeSystem)).thenReturn(true);
+ assertEquals(new BigDecimal(481), ret.calculate(evaluatableTuple, filterCodeSystem));
+
+ PowerMockito.when(f1.evaluate(evaluatableTuple, filterCodeSystem)).thenReturn(false);
+ PowerMockito.when(f2.evaluate(evaluatableTuple, filterCodeSystem)).thenReturn(false);
+ assertEquals(new BigDecimal(196), ret.calculate(evaluatableTuple, filterCodeSystem));
+ }
+}
diff --git a/core-metadata/src/test/java/org/apache/kylin/metadata/expression/TupleExpressionSerializerTest.java b/core-metadata/src/test/java/org/apache/kylin/metadata/expression/TupleExpressionSerializerTest.java
new file mode 100644
index 0000000..9797fa7
--- /dev/null
+++ b/core-metadata/src/test/java/org/apache/kylin/metadata/expression/TupleExpressionSerializerTest.java
@@ -0,0 +1,77 @@
+/*
+ * 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.
+*/
+
+package org.apache.kylin.metadata.expression;
+
+import static org.junit.Assert.assertEquals;
+
+import java.math.BigDecimal;
+
+import org.apache.kylin.common.util.LocalFileMetadataTestCase;
+import org.apache.kylin.common.util.Pair;
+import org.apache.kylin.metadata.filter.ColumnTupleFilter;
+import org.apache.kylin.metadata.filter.CompareTupleFilter;
+import org.apache.kylin.metadata.filter.ConstantTupleFilter;
+import org.apache.kylin.metadata.filter.StringCodeSystem;
+import org.apache.kylin.metadata.filter.TupleFilter;
+import org.apache.kylin.metadata.model.TableDesc;
+import org.apache.kylin.metadata.model.TblColRef;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import com.google.common.collect.Lists;
+
+public class TupleExpressionSerializerTest extends LocalFileMetadataTestCase {
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ staticCreateTestMetadata();
+ }
+
+ @AfterClass
+ public static void after() throws Exception {
+ staticCleanupTestMetadata();
+ }
+
+ private TableDesc t = TableDesc.mockup("T");
+
+ @Test
+ public void testSerialization() {
+ TblColRef colD = TblColRef.mockup(t, 1, "C1", "decimal");
+ TblColRef colM = TblColRef.mockup(t, 2, "C2", "string");
+ BigDecimal value = BigDecimal.valueOf(10L);
+
+ ColumnTupleFilter colFilter = new ColumnTupleFilter(colD);
+ ConstantTupleFilter constFilter = new ConstantTupleFilter("col");
+ CompareTupleFilter compareFilter = new CompareTupleFilter(TupleFilter.FilterOperatorEnum.EQ);
+ compareFilter.addChild(colFilter);
+ compareFilter.addChild(constFilter);
+
+ ColumnTupleExpression colTuple = new ColumnTupleExpression(colM);
+ NumberTupleExpression constTuple = new NumberTupleExpression(value);
+
+ Pair<TupleFilter, TupleExpression> whenEntry = new Pair<TupleFilter, TupleExpression>(compareFilter, colTuple);
+ CaseTupleExpression caseTuple = new CaseTupleExpression(Lists.newArrayList(whenEntry), constTuple);
+
+ byte[] result = TupleExpressionSerializer.serialize(caseTuple, StringCodeSystem.INSTANCE);
+
+ TupleExpression desTuple = TupleExpressionSerializer.deserialize(result, StringCodeSystem.INSTANCE);
+ assertEquals(caseTuple, desTuple);
+ }
+}
diff --git a/core-metadata/src/test/java/org/apache/kylin/metadata/expression/TupleExpressionTest.java b/core-metadata/src/test/java/org/apache/kylin/metadata/expression/TupleExpressionTest.java
new file mode 100644
index 0000000..7617c5a
--- /dev/null
+++ b/core-metadata/src/test/java/org/apache/kylin/metadata/expression/TupleExpressionTest.java
@@ -0,0 +1,84 @@
+/*
+ * 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.
+*/
+
+package org.apache.kylin.metadata.expression;
+
+import static org.junit.Assert.fail;
+
+import java.math.BigDecimal;
+
+import org.apache.kylin.common.util.LocalFileMetadataTestCase;
+import org.apache.kylin.metadata.model.TableDesc;
+import org.apache.kylin.metadata.model.TblColRef;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import com.google.common.collect.Lists;
+
+public class TupleExpressionTest extends LocalFileMetadataTestCase {
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ staticCreateTestMetadata();
+ }
+
+ @AfterClass
+ public static void after() throws Exception {
+ staticCleanupTestMetadata();
+ }
+
+ private TableDesc t = TableDesc.mockup("T");
+
+ @Test
+ public void testBinary() {
+ BigDecimal value1 = BigDecimal.valueOf(10L);
+ BigDecimal value2 = BigDecimal.valueOf(10L);
+ TblColRef col1 = TblColRef.mockup(t, 1, "C1", "decimal");
+ TblColRef col2 = TblColRef.mockup(t, 2, "C2", "decimal");
+
+ NumberTupleExpression constTuple1 = new NumberTupleExpression(value1);
+ NumberTupleExpression constTuple2 = new NumberTupleExpression(value2);
+ ColumnTupleExpression colTuple1 = new ColumnTupleExpression(col1);
+ ColumnTupleExpression colTuple2 = new ColumnTupleExpression(col2);
+
+ BinaryTupleExpression biTuple1 = new BinaryTupleExpression(TupleExpression.ExpressionOperatorEnum.MULTIPLE,
+ Lists.newArrayList(constTuple1, colTuple1));
+ biTuple1.verify();
+
+ BinaryTupleExpression biTuple2 = new BinaryTupleExpression(TupleExpression.ExpressionOperatorEnum.DIVIDE,
+ Lists.newArrayList(constTuple2, colTuple2));
+ try {
+ biTuple2.verify();
+ fail("IllegalArgumentException should be thrown");
+ } catch (IllegalArgumentException e) {
+ }
+
+ biTuple2 = new BinaryTupleExpression(TupleExpression.ExpressionOperatorEnum.DIVIDE,
+ Lists.newArrayList(colTuple2, constTuple2));
+ biTuple2.verify();
+
+ BinaryTupleExpression biTuple = new BinaryTupleExpression(TupleExpression.ExpressionOperatorEnum.MULTIPLE,
+ Lists.<TupleExpression> newArrayList(biTuple1, biTuple2));
+ try {
+ biTuple.verify();
+ fail("IllegalArgumentException should be thrown");
+ } catch (IllegalArgumentException e) {
+ }
+ }
+}
diff --git a/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java b/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java
index e5d3c6b..269c65f 100644
--- a/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java
+++ b/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java
@@ -415,6 +415,10 @@ public class ITKylinQueryTest extends KylinTestBase {
batchExecuteQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_percentile");
}
+ @Test
+ public void testExpressionQuery() throws Exception {
+ batchExecuteQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_expression");
+ }
@Test
public void testValues() throws Exception {
diff --git a/kylin-it/src/test/resources/query/sql_expression/query01.sql b/kylin-it/src/test/resources/query/sql_expression/query01.sql
new file mode 100644
index 0000000..35025ac
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_expression/query01.sql
@@ -0,0 +1,22 @@
+--
+-- 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.
+--
+
+select SLR_SEGMENT_CD, sum(case when LSTG_FORMAT_NAME is null then 0 else 1 end)
+FROM test_kylin_fact
+group by SLR_SEGMENT_CD
+order by SLR_SEGMENT_CD
\ No newline at end of file
diff --git a/kylin-it/src/test/resources/query/sql_expression/query02.sql b/kylin-it/src/test/resources/query/sql_expression/query02.sql
new file mode 100644
index 0000000..a73018b
--- /dev/null
+++ b/kylin-it/src/test/resources/query/sql_expression/query02.sql
@@ -0,0 +1,34 @@
+--
+-- 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.
+--
+
+select LSTG_FORMAT_NAME,
+ sum(price),
+ sum(case
+ when LSTG_FORMAT_NAME = 'ABIN' then 2*price
+ when LSTG_FORMAT_NAME = 'Auction' then (1+2)*price*(2+3)+(2+3)*(3+2)*(4+5)-4+5
+ else 3
+ end)
+FROM test_kylin_fact
+ inner JOIN edw.test_cal_dt as test_cal_dt
+ ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt
+ inner JOIN test_category_groupings
+ ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id
+WHERE SLR_SEGMENT_CD < 16
+group by LSTG_FORMAT_NAME
+having sum((1+2)*price*(2+3)+(2+3)*(3+2)*(4+5)-4+5) > 1800000
+order by LSTG_FORMAT_NAME
\ No newline at end of file
--
To stop receiving notification emails like this one, please contact
shaofengshi@apache.org.