Get average amount of items in a given time frame from a list (of tuples)

Questions / Help
, ,
1

Hi Everyone,

I (think…) I want a list of items that has been created in the last 30 days, and then get the daily average per person.

I figure I’d probably need a combination of a query and then Enum.reduce / Enum.flat_map to marshall that to get the totals?

The query I’m using at the moment looks like:

Repo.all from i in Issue,
where: i.user_id == ^user_id,
where: i.inserted_at <= ^enddate,
where: i.inserted_at >= ^startdate,
group_by: [i.inserted_at, i.meta],
select: {i.inserted_at, i.meta["viewed_at"]}

which returns

[
  {~U[2021-08-17 19:54:34Z], "2021-08-17T19:54:40.518454Z"},
  {~U[2021-08-17 19:42:56Z], nil},
  {~U[2021-08-17 19:19:13Z], "2021-08-17T19:53:11.901540Z"},
  {~U[2021-08-17 19:47:15Z], "2021-08-17T20:40:01.053899Z"},
  {~U[2021-08-18 19:40:34Z], nil},
  {~U[2021-08-19 20:58:50Z], nil}
]

I’m not sure where to from here – can I do this just with a query? Will reduce or flat_map after getting the data be best? If so which one?

Thanks! :smiley:

Ps: I’m aware that the second date in the tuple is a string that’d need to be parsed to a date, but I figure I’ll tackle that issue later (since I want to get that average as well as part of this calculation)

2 
defmodule Play do
  def cluster_by_datetime_intervals() do
    input = [
      {~U[2021-08-17 19:54:34Z], "2021-08-17T19:54:40.518454Z"},
      {~U[2021-08-17 19:42:56Z], nil},
      {~U[2021-08-17 19:19:13Z], "2021-08-17T19:53:11.901540Z"},
      {~U[2021-08-17 19:47:15Z], "2021-08-17T20:40:01.053899Z"},
      {~U[2021-08-18 19:40:34Z], nil},
      {~U[2021-08-19 20:58:50Z], nil}
    ]

    Enum.group_by(
      input,
      fn {datetime, _data} -> DateTime.to_date(datetime) end,
      fn {_datetime, data} -> data end
    )
  end
end

This yields the following result for me:

%{
  ~D[2021-08-17] => [
    "2021-08-17T19:54:40.518454Z",
    nil,
    "2021-08-17T19:53:11.901540Z",
    "2021-08-17T20:40:01.053899Z"
  ],
  ~D[2021-08-18] => [nil],
  ~D[2021-08-19] => [nil]
}

Which collects all values for the given date. Is that what you need?

1 Like
3

Thanks, I’ll give it a shot :smiley: it’s on my side project and it’s that time again