Help with query

[Software Dev <st...@gmail.com>]

We have a table with user entered queries, their IP. How could we
write a query that will count and order queries by their count having
a unique IP count > X. For example if we had the same IP enter the
same query Y times we wouldnlt want to include this in the final
result unless there have been X-Y other IP's that searched for that
query.

Is this perhaps better suited fro Pig?

Thanks

RE: Help with query

[Ricardo Birmele <Ri...@microsoft.com>]

One quick suggestion…if you modify the query to this:

SELECT COUNT(DISTINCT ip) AS IP_count, IP, query_string
FROM myTable
GROUP BY IP, query_string
ORDER BY count DESC;

…then you should see each IP along with its count.
FWIW,
__Birm

Ricardo Birmele, CISSP
Senior Security Technologist
Microsoft IT Security Operations
• | •••• | •

[Microsoft Logo]

From: Petter von Dolwitz (Hem) [mailto:petter.von.dolwitz@gmail.com]
Sent: Thursday, 5 June, 2014 01:33
To: user@hive.apache.org
Subject: Re: Help with query


I tried to answer this earlier. Maybe the mail got suck somewhere. I'll try again.

SELECT COUNT(DISTINCT ip) AS count, query_string
FROM myTable
GROUP BY query_string
ORDER BY count DESC;

This should give you one row per asked query. Rows are ordered by number of unique IPs that asked the query. Most popular first.

Br,
Petter

2014-05-30 1:29 GMT+02:00 Software Dev <st...@gmail.com>>:
We have a table with user entered queries, their IP. How could we
write a query that will count and order queries by their count having
a unique IP count > X. For example if we had the same IP enter the
same query Y times we wouldnlt want to include this in the final
result unless there have been X-Y other IP's that searched for that
query.

Is this perhaps better suited fro Pig?

Thanks

Re: Help with query

["Petter von Dolwitz (Hem)" <pe...@gmail.com>]

I tried to answer this earlier. Maybe the mail got suck somewhere. I'll try
again.

SELECT COUNT(DISTINCT ip) AS count, query_string
FROM myTable
GROUP BY query_string
ORDER BY count DESC;

This should give you one row per asked query. Rows are ordered by number of
unique IPs that asked the query. Most popular first.

Br,
Petter


2014-05-30 1:29 GMT+02:00 Software Dev <st...@gmail.com>:

> We have a table with user entered queries, their IP. How could we
> write a query that will count and order queries by their count having
> a unique IP count > X. For example if we had the same IP enter the
> same query Y times we wouldnlt want to include this in the final
> result unless there have been X-Y other IP's that searched for that
> query.
>
> Is this perhaps better suited fro Pig?
>
> Thanks
>

Re: Help with query

[Nitin Pawar <ni...@gmail.com>]

can you put your example with sample data and expected result output?


On Fri, May 30, 2014 at 4:59 AM, Software Dev <st...@gmail.com>
wrote:

> We have a table with user entered queries, their IP. How could we
> write a query that will count and order queries by their count having
> a unique IP count > X. For example if we had the same IP enter the
> same query Y times we wouldnlt want to include this in the final
> result unless there have been X-Y other IP's that searched for that
> query.
>
> Is this perhaps better suited fro Pig?
>
> Thanks
>



-- 
Nitin Pawar