Forget about the above two queries, it seems that phoenix count() function
has a real problem dealing with large data size.
this 30 day query returns a count of 36076
select count(1) from table t where device ='A' and date >
to_date('2014-05-09','yyyy-MM-dd') and date <
to_date('2014-06-09','yyyy-MM-dd');
while the following query for a smaller day range returns the correct
1303743
select count(1) from table t where device ='A' and date >
to_date('2014-06-01','yyyy-MM-dd') and date <
to_date('2014-06-09','yyyy-MM-dd');
The table has about 70 million rows per day.
On Tue, Jun 10, 2014 at 2:43 PM, Sean Huo <[email protected]> wrote:
> The two queries seems to yield the same results for relatively small query
> size, but diverge when the query size is really big (30*70 millions rows)
>
>
> On Tue, Jun 10, 2014 at 2:07 PM, Gabriel Reid <[email protected]>
> wrote:
>
>> Hi Sean,
>>
>> That doesn't sound right -- any idea which of the queries (if either)
>> is returning the correct results?
>>
>> Any chance you could try this out on a small test table and then post
>> the relevant DDL and literal queries here?
>>
>> - Gabriel
>>
>>
>> On Tue, Jun 10, 2014 at 10:30 PM, Sean Huo <[email protected]> wrote:
>> > These two queries returns vastly different results:
>> >
>> > query1:
>> > select count(1) from t where date > 'certain day' and device ='A';
>> >
>> > query2:
>> >
>> > select device, count(1) from t where date >'certain day' and device ='A'
>> > group by device;
>> >
>> > assuming table t has the composite primary key of
>> > date timestamp, and device varchar.
>> >
>> > What gives?
>> >
>>
>
>
