Sorry forget the group by and sum().. SELECT CST_KEY, sum(AMT) FROM FCT group by CST_KEY ORDER BY 2 DESC
On Sat, Apr 9, 2016 at 2:16 PM, Li Yang <[email protected]> wrote: > Isn't it the same as below? > > SELECT CST_KEY, AMT > FROM FCT > ORDER BY AMT DESC > > On Fri, Apr 8, 2016 at 11:29 AM, hongbin ma <[email protected]> wrote: > >> it's not working for kylin >> >> On Thu, Apr 7, 2016 at 12:51 AM, sdangi <[email protected]> wrote: >> >> > Does Kylin support these analytic functions? >> > >> > I'm hitting into an issue while running this. Works ok on hive. >> > >> > SELECT CST_KEY, AMT, >> > RANK() OVER( ORDER BY AMT DESC) >> > FROM FCT >> > >> > Message: Error while executing SQL "SELECT CST_KEY, AMT, RANK() OVER( >> ORDER >> > BY AMT DESC) FROM TXN_FCT_ORC LIMIT 50000": cannot translate call RANK() >> > OVER (ORDER BY $t2 DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT >> ROW) >> > >> > *EXECUTION PLAN AFTER REWRITE* >> > OLAPToEnumerableConverter >> > EnumerableLimit(fetch=[50000]) >> > EnumerableCalc(expr#0..3=[{inputs}], expr#4=[DENSE_RANK() OVER >> (ORDER >> > BY >> > $t2 DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], >> > CST_KEY=[$t1], >> > AMT=[$t2], EXPR$2=[$t4]) >> > OLAPTableScan(table=[[SCHEMA, FCT]], fields=[[0, 1, 2, 3]]) >> > >> > Caused by: java.lang.RuntimeException: cannot translate call RANK() OVER >> > (ORDER BY $t2 DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:533) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:507) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:472) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:219) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:214) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateList(RexToLixTranslator.java:700) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateProjects(RexToLixTranslator.java:189) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.EnumerableCalc.implement(EnumerableCalc.java:188) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) >> > at >> > >> > >> org.apache.kylin.query.relnode.OLAPRel$JavaImplementor.visitChild(OLAPRel.java:184) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.EnumerableLimit.implement(EnumerableLimit.java:106) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.visitChild(EnumerableRelImplementor.java:97) >> > at >> > >> > >> org.apache.kylin.query.relnode.OLAPRel$JavaImplementor.visitChild(OLAPRel.java:184) >> > at >> > >> > >> org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:108) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102) >> > at >> > >> > >> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92) >> > at >> > >> > >> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1171) >> > at >> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297) >> > at >> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196) >> > at >> > >> > >> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:721) >> > at >> > >> > >> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:588) >> > at >> > >> > >> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:558) >> > at >> > >> > >> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214) >> > at >> > >> > >> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573) >> > at >> > >> > >> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571) >> > at >> > >> > >> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135) >> > >> > >> > -- >> > View this message in context: >> > >> http://apache-kylin.74782.x6.nabble.com/RANK-DENSE-RANK-on-KYLIN-tp4066.html >> > Sent from the Apache Kylin mailing list archive at Nabble.com. >> > >> >> >> >> -- >> Regards, >> >> *Bin Mahone | 马洪宾* >> Apache Kylin: http://kylin.io >> Github: https://github.com/binmahone >> > >
