代码之家 › 专栏 › 技术社区 › Junaid Farooq

跳过和合并相似数据的外显灵丹妙药

ecto chart.js elixir jquery

Junaid Farooq · 技术社区 · 7 年前

我在数据库中有一些记录,我正在将它们作为。

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

因此,我得到了这种数据,

 %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"}]

它有许多重复,这并不完全相同,但如果你看 DateTime 2017-10-12 和 2017-10-16 ,在上面的示例中,我正在处理该数据,以进一步创建一条图表线,如下所示:

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)

这就产生了一系列这样的对象 {percentage_used: 0, datetime: "2017-10-10 05:03:49"} ,我的问题是,我想合并相同的日期,例如,如果有7条 2017-10-12 ,然后使它们成为一个,因为对象的另一半是基于 volume_used (ecto结果查询),因此得到所有这7个记录的平均值 使用的volume_ {percentage_used: MEAN_OF_ALL_7_RECORDS, datetime: "2017-10-12"} ..

[{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"}]

做一些类似的事情

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

任何解决方案都是值得赞赏的。谢谢

1 回复 | 直到 7 年前

Dogbert 7 年前

您可以使用 DISTINCT ON date_trunc('day', datetime) 在每天仅从数据库返回一条记录的查询中:

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

要每天提取最后一条记录,您可以修改 order_by :

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