I'm trying to group things by week (Sun - Sat). First, the trunc(`date_column`,'ww') is not that useful as the first day of the week is based on Jan 1 of the year. I tried to use weekofyear() but notice the following behaviour: select weekofyear('2013-12-29') -- result: 52, ok select weekofyear('2013-12-30') -- result: 1?? select weekofyear('2013-12-31') -- result: 1?? select weekofyear('2014-12-31') -- result: 1?? select weekofyear(‘2015-12-31’) -- result: 53 , ok select weekofyear(‘2016-12-31’) -- result: 52 , ok select weekofyear(‘2017-12-31’) -- result: 52, ok select weekofyear(‘2018-12-31’) -- result: 1 ? is this a bug?
... View more
I have a query as simple as this in Impala: with query1 as (
select user_id, cast (rand() as float) as random1
from master_table where dt='2015-12-17'
select * from query1
0 A60028682230369691 0.00047147460281848907
1 A60028682230362251 0.58958953619003296
2 A60028682230777191 0.44319009780883789 But when I add a where clause to compare value with the random1 column it doesn't work. For example: select * from query1 where random1 < 0.2 Result: returns all rows select * from query1 where random1 < 0 Result: returns 0 row (which is correct) Tried all kinds of variations, including - cast as float everywhere - multiplying the rand() by 1000 and cast as int - do the comparison and store true/false in the column in query1, and then select with where clause random1=true none of the above can give me a portion of the data set. Am I missing something for this simple query? It's not about the randomness of the rand() but the use of the rand() result in a where clause. Thanks.
... View more