Impala query to get sample value of column used in count aggregate function

Question

I have huge data in hive table. PFB sample rows.

Table:

Clid,pid,lid
1 ,1 ,OJA
1 ,2 , KLM
1, 2 , MHK
1 ,2, DNY

I want to write impala query to get count of lid for each clid,pid group along with sample value. I know how to get count but how can I get sample value of lid for each clid, pid group.

Select clid, pid, count(lid) frim t group by clid,pid

What should I add in impala query to get sample lid as shown in below result.

Expected result:

Clid, pid, count, sample lid
1, 1, 1, OJA
1, 2,3,MHK

I have tried using first_val function with over clause but it's giving error.

select clid,pid, count (lid), first_value(lid) over (partition by clid, pid) from t group by clid, pid

Error:

AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): first_value(lid) OVER (PARTITION BY clid,pid)

What error are you receiving? Please include your attempt with the first_val function — ggordon
@{ggordon} I have added my first_val query with error in above post — acg

ggordon ggordon · Accepted Answer · 2021-10-16T17:35:09

You are getting this error because first_value, a window function will produce a value for each row and is not included in your group by clause as a field to group on.

Since there is no explicit ordering on how you retrieve the sample_lid using your first_val function (i.e. including an ORDER BY some_field in the over clause) you may use the MAX function instead in your aggregation to extract a sample_lid eg

select clid,pid, COUNT(lid), MAX(lid) as sample_lid
from t 
group by clid, pid

If you are indeed interest in using the first_value function by some ordering you could modify your query to add an ORDER BY to the over clause and/or use row_number to filter based on the first row.

Let me know if thus works for you.

Impala query to get sample value of column used in count aggregate function

1 Answers