Home Create rolling array_agg column over 10-day period
Reply: 1

Create rolling array_agg column over 10-day period

AlexanderHughes
1#
AlexanderHughes Published in 2018-02-13 20:35:02Z

I am trying to do create a column with a rolling array agg over a 10 day period.

I table a table with:

row_id | date_end    | user_id | item_id
1186     '2017-11-06'  3908805   158
59801    '2017-11-16'  3249668   157
59800    '2017-11-16'  3249668   158
59802    '2017-11-16'  3249668   158
59799    '2017-11-16'  3249668   157
61678    '2017-11-17'  3249668   158
61679    '2017-11-17'  3249668   157

I want to create a rolling aggregate array (array_agg) of the item_id with respect to upcoming 10-day window.

Currently I have tried:

select a.row_id, a.date_end, a.user_id, array_agg(b.item_id)
from table1 as a
join table1 as b on a.row_id = b.row_id
and b.date_end between (a.date_end - interval '10 days') and a.date_end
group by a.row_id, a.date_end, a.user_id
order by a.row_id, a.date_end

but this is not aggregating the item_id into an array. Only appends a single int value.

Sql fiddle here: http://sqlfiddle.com/#!17/696ed/4/0

I am using PostgreSQL 9.6

clinomaniac
2#
clinomaniac Reply to 2018-02-13 22:24:12Z

Assuming the groupings you need, this should get you the results you need:

select a.date_end, a.user_id, array_agg(b.item_id)
from table1 as a
join table1 as b on a.row_id = b.row_id
and b.date_end between (a.date_end - interval '10 days') and a.date_end
group by a.date_end, a.user_id
order by a.date_end

You need to GROUP BY on the columns that you need aggregated. If you group by all the columns then you will not get the aggregation.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.315043 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO