Sql: "select ADX,GEOID,SUM(IMPS) as imps,SUM(CLKS) as clks,SUM(CONS) as cons,SUM(IMP_CLOSINGPRICE)+SUM(CLK_CLOSINGPRICE) as cost,COUNT(DISTINCT IMP_DEVICEID) as imp_uv,COUNT(DISTINCT CLK_DEVICEID) as clk_uv from EXT_MID_EVENT_JOIN where "DATE">=DATE'2016-01-01' and "DATE"<DATE'2016-01-08' group by ADX,GEOID order by cost limit 100" The following error information appeared: Error while executing SQL "select ADX,GEOID,SUM(IMPS) as imps,SUM(CLKS) as clks,SUM(CONS) as cons,SUM(IMP_CLOSINGPRICE)+SUM(CLK_CLOSINGPRICE) as cost,COUNT(DISTINCT IMP_DEVICEID) as imp_uv,COUNT(DISTINCT CLK_DEVICEID) as clk_uv from EXT_MID_EVENT_JOIN where "DATE">=DATE'2016-01-01' and "DATE"<DATE'2016-01-08' group by ADX,GEOID order by cost limit 100": For input string: "1451606400000" Is it DATE'2016-01-01' returned a string, however DATE is a integer type, the type is not match?
张天生 <[email protected]>于2016年8月9日周二 下午6:45写道： > Ok, i will try it. > > ShaoFeng Shi <[email protected]>于2016年8月9日周二 下午6:36写道： > >> The "date" encoding is better than "dic" encoding as it doesn't need >> storage cost. >> >> Did you try this (explicitly let SQL parser know it is a Date value): where >> "DATE">=DATE'2016-01-01' >> >> 2016-08-09 17:38 GMT+08:00 张天生 <[email protected]>: >> >>> I found the answer, it because DATE dimension rowkey encode is date. >>> When executes "select APPID, SUM(IMPS) as imps from EXT_MID_EVENT_JOIN >>> where "DATE">=1462060800000 and "DATE"<1462147200000 group by APPID order >>> by imps desc limit 100", it returned right results. Btw, whether the date >>> rowkey encode is better performace than dictionary encode? >>> >>> 张天生 <[email protected]>于2016年8月9日周二 下午3:55写道： >>> >>>> I built a cube, it has 2 dimesions : CAMPAIGNID, DATE, and has 2 >>>> measures: count(1), topN(100). TOP-N is grouped by APPID and order by/sum >>>> by IMPS measure. When i queried: select APPID, SUM(IMPS) as imps from >>>> EXT_MID_EVENT_JOIN where "DATE">='2016-01-01' and "DATE"<'2016-01-08' group >>>> by APPID order by imps desc limit 100, it returned 0 records. Abviously >>>> there are a lot of records. Can someone explain why it always returned 0 >>>> records? >>>> >>> >> >> >> -- >> Best regards, >> >> Shaofeng Shi >> >>
