skipping and merging similar data ecto elixir

Question

I have some records in the database and I am getting them as.

  def get_all_records_for_sim(sim_number) do
    SimLogs
    |> where(number: ^sim_number)
    |> order_by(asc: :datetime)
    |> Repo.all
    |> IO.inspect
  end

as a result, I get this kind of data,

 %EdgeCommander.ThreeScraper.SimLogs{__meta__: #Ecto.Schema.Metadata<:loaded, "sim_logs">,
  addon: "60GB Broadband", allowance: "61,440.00 MB",
  datetime: ~N[2017-10-12 10:39:00.839670], id: 795, name: "User 6 Sim",
  number: "0860100421", volume_used: "0.00 MB"},
 %EdgeCommander.ThreeScraper.SimLogs{__meta__: #Ecto.Schema.Metadata<:loaded, "sim_logs">,
  addon: "60GB Broadband", allowance: "61,440.00 MB",
  datetime: ~N[2017-10-12 11:02:20.296758], id: 815, name: "User 6 Sim",
  number: "0860100421", volume_used: "0.00 MB"},
 %EdgeCommander.ThreeScraper.SimLogs{__meta__: #Ecto.Schema.Metadata<:loaded, "sim_logs">,
  addon: "60GB Broadband", allowance: "61,440.00 MB",
  datetime: ~N[2017-10-13 05:30:25.800565], id: 837, name: "User 6 Sim",
  number: "0860100421", volume_used: "0.00 MB"},
 %EdgeCommander.ThreeScraper.SimLogs{__meta__: #Ecto.Schema.Metadata<:loaded, "sim_logs">,
  addon: "60GB Broadband", allowance: "61,440.00 MB",
  datetime: ~N[2017-10-16 05:24:04.536224], id: 859, name: "User 6 Sim",
  number: "0860100421", volume_used: "0.00 MB"},
 %EdgeCommander.ThreeScraper.SimLogs{__meta__: #Ecto.Schema.Metadata<:loaded, "sim_logs">,
  addon: "60GB Broadband", allowance: "61,440.00 MB",
  datetime: ~N[2017-10-16 12:28:21.565377], id: 881, name: "User 6 Sim",
  number: "0860100421", volume_used: "43.09 MB"},
 %EdgeCommander.ThreeScraper.SimLogs{__meta__: #Ecto.Schema.Metadata<:loaded, "sim_logs">,
  addon: "60GB Broadband", allowance: "61,440.00 MB",
  datetime: ~N[2017-10-17 05:03:49.866221], id: 903, name: "User 6 Sim",
  number: "0860100421", volume_used: "43.09 MB"}]

there are many duplicates are coming from it, which is not exactly the same but if you look at DateTime, the first part of the date is same as 2017-10-12 and 2017-10-16, In above example, I am processing that data to furthermore to create a chart line as

chartjs_data =
  sim_number
  |> get_all_records_for_sim()
  |> Enum.map(fn(one_record) ->
    {current_in_number, _} = one_record |> get_volume_used() |> String.replace(",", "") |> Float.parse()
    {allowance_in_number, _} = one_record |> get_allowance() |> String.replace(",", "") |> Float.parse()

    %{
      datetime: "#{shift_datetime(one_record.datetime)}",
      percentage_used: (current_in_number / allowance_in_number * 100) |> Float.round(3)
    }
  end)

which results in an array of such objects {percentage_used: 0, datetime: "2017-10-10 05:03:49"}, My issue is, I want to merge the same dates, for example, if there are 7 records of 2017-10-12, then make them one, and as the other half of the object is based on volume_used(ecto resulting query), so get the mean of all those 7 record's volume_used and make it one and create object as {percentage_used: MEAN_OF_ALL_7_RECORDS, datetime: "2017-10-12"}..

there are multiple ways of doing that, But I am looking if its possible in ecto, or on the server side only in any efficient way? any help would be appreciable.

UPDATE: Or maybe in if its possible in jquery as to process this data,

[{percentage_used: 0, datetime: "2017-10-10 05:03:49"}
{percentage_used: 0, datetime: "2017-10-10 17:13:38"}
{percentage_used: 0, datetime: "2017-10-11 04:39:32"}
{percentage_used: 0, datetime: "2017-10-11 12:50:42"}
{percentage_used: 0, datetime: "2017-10-12 06:31:22"}
{percentage_used: 0, datetime: "2017-10-12 09:21:08"}
{percentage_used: 0, datetime: "2017-10-12 09:34:33"}
{percentage_used: 0, datetime: "2017-10-12 10:17:00"}
{percentage_used: 0, datetime: "2017-10-12 10:39:00"}
{percentage_used: 0, datetime: "2017-10-12 11:02:20"}]

and making something like

[{percentage_used: 0, datetime: "2017-10-10"}
{percentage_used: 0, datetime: "2017-10-11"}
{percentage_used: 0, datetime: "2017-10-11"}]

any solution would be appreciable. thanks

Answer 1

You can use DISTINCT ON date_trunc('day', datetime) in the query to return only one record per day from the database:

SimLogs
|> where(number: ^sim_number)
|> distinct([s], fragment("date_trunc('day', ?)", s.datetime))
|> order_by(asc: :datetime)
|> Repo.all

To fetch the last record per day, you can to modify the order_by:

|> order_by([s], [asc: fragment("date_trunc('day', ?)", s.datetime), desc: s.datetime])