映射和压缩SQL查询结果以创建分组数据结构

Question

我有一个来自SQL.query(Repo...)的数据结构:

IO.inspect results.columns:

["tour_id", "name", "year", "num_shows", "num_cities", "num_festivals", "num_countries"]

和实际行，IO.inspect results.rows:

[
[5, "tour", 2022, 13, 10, 4, 2],
[1, "asia", 2018, 4, 4, 3, 3],
[2, "north america", 2018, 39, 17, 2, 2],
[3, "europe", 2018, 13, 12, 9, 10],
[4, "las vegas shows", 2018, 3, 1, 0, 1]
]

我正在努力做一些像Enum.zip的结果将是:

[
[ tour_id: 5, name: "tour", year: 2022, num_shows: 13, ....],
[ tour_id: 1, name: "asia", year: 2018, num_shows: 4, ....],
]

最后，我想做一个基于year的group_by:

2018: [
[ tour_id: 1, name: "asia", year: 2018, num_shows: 4, ....],
[ tour_id: 3, name: "europe", year: 2018, num_shows: 13, ....],
],
2022: [ ... ]

或者，无论有人建议如何对其进行格式化。我认为将行和列转换为关键字列表就可以了，然后我可以做group_by。

谢谢

Answer 1

如果你想要一个列名的关键字列表->第一步是将列名转换为原子:

columns =
["tour_id", "name", "year", "num_shows", "num_cities", "num_festivals", "num_countries"]
|> Enum.map(&String.to_atom/1)

然后您可以映射行并将每个行与列压缩:

rows = [
[5, "tour", 2022, 13, 10, 4, 2],
[1, "asia", 2018, 4, 4, 3, 3],
[2, "north america", 2018, 39, 17, 2, 2],
[3, "europe", 2018, 13, 12, 9, 10],
[4, "las vegas shows", 2018, 3, 1, 0, 1]
]
zipped = for row <- rows, do: Enum.zip(columns, row)
IO.inspect(zipped)

[
[
tour_id: 5,
name: "tour",
year: 2022,
num_shows: 13,
num_cities: 10,
num_festivals: 4,
num_countries: 2
],
[
tour_id: 1,
name: "asia",
year: 2018,
num_shows: 4,
num_cities: 4,
num_festivals: 3,
num_countries: 3
],
[
tour_id: 2,
name: "north america",
year: 2018,
num_shows: 39,
num_cities: 17,
num_festivals: 2,
num_countries: 2
],
[
tour_id: 3,
name: "europe",
year: 2018,
num_shows: 13,
num_cities: 12,
num_festivals: 9,
num_countries: 10
],
[
tour_id: 4,
name: "las vegas shows",
year: 2018,
num_shows: 3,
num_cities: 1,
num_festivals: 0,
num_countries: 1
]
]

最后按年份分组:

IO.inspect(Enum.group_by(zipped, fn row -> row[:year] end))

%{
2018 => [
[
tour_id: 1,
name: "asia",
year: 2018,
num_shows: 4,
num_cities: 4,
num_festivals: 3,
num_countries: 3
],
[
tour_id: 2,
name: "north america",
year: 2018,
num_shows: 39,
num_cities: 17,
num_festivals: 2,
num_countries: 2
],
[
tour_id: 3,
name: "europe",
year: 2018,
num_shows: 13,
num_cities: 12,
num_festivals: 9,
num_countries: 10
],
[
tour_id: 4,
name: "las vegas shows",
year: 2018,
num_shows: 3,
num_cities: 1,
num_festivals: 0,
num_countries: 1
]
],
2022 => [
[
tour_id: 5,
name: "tour",
year: 2022,
num_shows: 13,
num_cities: 10,
num_festivals: 4,
num_countries: 2
]
]
}