SQL拥有，但采用Javascript方式

Question

所以我在javascript中使用javascript来转换SQL查询。

下面的SQL语句列出了每个国家/地区的客户数量仅包括拥有5个以上客户的国家：

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

请看下面数组的JS对象和他的预期输出。

//my array
[
{
customerID: 1,
CustomerName: "Alfreds Futterkiste",
contactName: "Maria Anders",
country: "Germany",
},
{
customerID: 2,
CustomerName: "Ana Trujillo Emparedados y helados",
contactName: "Ana Trujillo",
country: "Mexico",
},
{
customerID: 3,
CustomerName: " Antonio Moreno Taquería",
contactName: "Antonio Mereno Jr.",
country: "Mexico",
},
];
//expected output
[
{
country: "Mexico",
count: 2
},
{
country: "Germany",
count: 1
},
]

我们可以使用lodash或任何库来解决这个问题

Answer 1

我们可以使用Array.reduce来获得所需的结果，这可以通过创建按国家/地区划分的客户地图来计算每个客户的数量。

一旦我们有了这个，我们就可以使用Object.values将映射转换回数组。

如果我们希望对count属性进行筛选，那么我们可以对结果数据进行Array.filter。

Lodash有一个groupBy函数，它可以做同样的事情。

const data = [{
customerID: 1,
CustomerName: "Alfreds Futterkiste",
contactName: "Maria Anders",
country: "Germany",
},
{
customerID: 2,
CustomerName: "Ana Trujillo Emparedados y helados",
contactName: "Ana Trujillo",
country: "Mexico",
},
{
customerID: 3,
CustomerName: " Antonio Moreno Taquería",
contactName: "Antonio Mereno Jr.",
country: "Mexico",
},
];
const result = Object.values(data.reduce((map, customer) => {
if (!map[customer.country]) {
map[customer.country] = {
country: customer.country,
count: 0
}
}
map[customer.country].count++;
return map;
}, {}));

console.log("All results: ", result);
const countThreshold = 2;
console.log("Filtered by count (" + countThreshold + "): ", result.filter(r => r.count >= countThreshold));

Answer 2

根据您的需求尝试这个简单的解决方案。

var arr = [{
customerID: 1,
CustomerName: "Alfreds Futterkiste",
contactName: "Maria Anders",
country: "Germany",
},
{
customerID: 2,
CustomerName: "Ana Trujillo Emparedados y helados",
contactName: "Ana Trujillo",
country: "Mexico",
},
{
customerID: 3,
CustomerName: " Antonio Moreno Taquería",
contactName: "Antonio Mereno Jr.",
country: "Mexico",
},
];
var countryCount = {};
arr.forEach(element => {
countryCount[element.country] = countryCount[element.country] ? ++countryCount[element.country] : 1;
});

var output = Object.keys(countryCount).map(key => ({
country: key,
count: countryCount[key]
}));
console.log(output);