Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Find TOP 10 using HiveQL


Copy link to this message
-
Re: Find TOP 10 using HiveQL
Nitin Pawar 2012-07-10, 07:13
i thought you managed to solve this with rank??

On Tue, Jul 10, 2012 at 12:38 PM, Raihan Jamal <[EMAIL PROTECTED]>wrote:

> Problem with that approach is, with LIMIT 10, If I am putting after desc,
> then it will get only 10 rows irrespective of BUYER_ID. But I need
> specifically for each BUYER_ID 10 latest rows.
>
>
>
>
> *Raihan Jamal*
>
>
>
> On Tue, Jul 10, 2012 at 12:03 AM, Abhishek Tiwari <
> [EMAIL PROTECTED]> wrote:
>
>> Raihan,
>>
>> Andes suggests you use 'limit' after 'desc' ie Hive with sort your query
>> results in descending order and then chop off any more than first 10
>> records.
>> However, Hive will still run the full scan on data since it has to find
>> the sorted list of records for you.
>>
>>
>> On Tue, Jul 10, 2012 at 8:36 AM, Raihan Jamal <[EMAIL PROTECTED]>wrote:
>>
>>> This is my below requirement. I need-  *Find `TOP 10` data for each
>>> `BUYER_ID, *So I cannot use LIMIT 10 here in this case.
>>>
>>> This is the below table
>>>
>>>     CREATE TABLE IF NOT EXISTS TestingTable1
>>>     (
>>>     BUYER_ID BIGINT,
>>>     ITEM_ID BIGINT,
>>>     CREATED_TIME STRING
>>>     )
>>>
>>> And this is the below data in the above table-
>>>
>>>     BUYER_ID    |    ITEM_ID   | CREATED_TIME
>>>     ------------+------------------+-----------------------
>>>     1015826235      220003038067        2012-07-09 19:40:21,
>>>     1015826235      300003861266        2012-07-09 18:19:59,
>>>     1015826235      140002997245        2012-07-09 09:23:17,
>>>     1015826235      210002448035        2012-07-09 22:21:11,
>>>     1015826235      260003553381        2012-07-09 07:09:56,
>>>     1015826235      260003553382        2012-07-09 19:40:39,
>>>     1015826235      260003553383        2012-07-09 06:58:47,
>>>     1015826235      260003553384        2012-07-09 07:28:47,
>>>     1015826235      260003553385        2012-07-09 08:48:47,
>>>     1015826235      260003553386        2012-07-09 06:38:47,
>>>     1015826235      260003553387        2012-07-09 05:38:47,
>>>     1015826235      260003553388        2012-07-09 04:55:47,
>>>     1015826235      260003553389        2012-07-09 06:54:37,
>>>     34512201        597245693           2012-07-09 16:20:21,
>>>     34512201        8071787728          2012-07-09 15:19:59,
>>>     34512201        5868222883          2012-07-09 08:23:17,
>>>     34512201        2412180494          2012-07-09 22:21:11,
>>>     34512201        2422054205          2012-07-09 06:09:56,
>>>     34512201        1875744030          2012-07-09 19:40:39,
>>>     34512201        5639158173          2012-07-09 06:58:47,
>>>     34512201        5656232360          2012-07-09 07:28:47,
>>>     34512201        959188449 2012-07-09 08:48:47,
>>>     34512201        4645350592          2012-07-09 06:38:47,
>>>     34512201        5657320532 2012-07-09 05:38:47,
>>>     34512201        290419656539 2012-07-09 04:55:47,
>>>
>>> If you see the above data in the table, there are only two UNIQUE
>>> `BUYER_ID` and corresponding to those I have `ITEM_ID` AND `CREATED_TIME`.
>>> I need only 10 latest record basis on the time for that given day for each
>>> `BUYER_ID`.
>>>
>>> So for this `BUYER_ID` - `34512201` I need 10 latest record basis on
>>> `CREATED_TIME` for that given day only, it means for today's date I need 10
>>> latest record for each `BUYER_ID`.
>>>
>>> And each `BUYER_ID` can have any day's data. But I am specifically
>>> interested for day before  today's data(means yesterday's date always) by
>>> checking at the `CREATED_TIME`
>>>
>>> **Find `TOP 10` data for each `BUYER_ID`. Below is the sample output.**
>>>
>>> Sample Output.
>>>
>>>     BUYER_ID    |    ITEM_ID   | CREATED_TIME
>>>     ------------+------------------+-----------------------
>>>     34512201        2412180494          2012-07-09 22:21:11
>>>     34512201        1875744030          2012-07-09 19:40:39
>>>     34512201        597245693           2012-07-09 16:20:21
>>>     34512201        8071787728          2012-07-09 15:19:59
Nitin Pawar