group-by

SQL select nth member of group

by

SELECT a.class, ( SELECT b.age FROM users b WHERE b.class = a.class ORDER BY age LIMIT 1,1 ) as age FROM users a GROUP BY a.class Would get the 2nd youngest in each class. If you wanted the 10th youngest, you’d do LIMIT 9,1 and if you wanted the 10th oldest, you’d do ORDER BY … Read more

group by date aggregate function in postgresql

by

At the moment it is unclear what you want Postgres to return. You say it should order by persons.updated_at but you do not retrieve that field from the database. I think, what you want to do is: SELECT date(updated_at), count(updated_at) as total_count FROM “persons” WHERE (“persons”.”updated_at” BETWEEN ‘2012-10-17 00:00:00.000000’ AND ‘2012-11-07 12:25:04.082224’) GROUP BY date(updated_at) … Read more

Pandas groupby and aggregation output should include all the original columns (including the ones not aggregated on)

by

agg with a dict of functions Create a dict of functions and pass it to agg. You’ll also need as_index=False to prevent the group columns from becoming the index in your output. f = {‘NET_AMT’: ‘sum’, ‘QTY_SOLD’: ‘sum’, ‘UPC_DSC’: ‘first’} df.groupby([‘month’, ‘UPC_ID’], as_index=False).agg(f) month UPC_ID UPC_DSC NET_AMT QTY_SOLD 0 2017.02 111 desc1 10 2 1 … Read more

Aggregation over Partition in pandas

by

You can use pandas transform() method for within group aggregations like “OVER(partition by …)” in SQL: import pandas as pd import numpy as np #create dataframe with sample data df = pd.DataFrame({‘group’:[‘A’,’A’,’A’,’B’,’B’,’B’],’value’:[1,2,3,4,5,6]}) #calculate AVG(value) OVER (PARTITION BY group) df[‘mean_value’] = df.groupby(‘group’).value.transform(np.mean) df: group value mean_value A 1 2 A 2 2 A 3 2 B … Read more