如何用JS实现SQL的GROUP BY、SUM及COUNT(DISTINCT)处理JSON数组

问题原因 #

原有代码的核心问题是没有对同分组下的user_id做去重判断，每次新增同分组条目就直接对计数加1，导致重复用户也被统计，同时首次创建分组时没有计入当前条目的user_id，也会带来计数偏差。

修正后代码 #

let merged = [
    { user_id: 2, year: 2021, month: 'July', gross_subscription_amount: 650, gross_redemption_amount: 0, gender: 'M', state: '01', age: '18', number_of_investors: 0 },
    { user_id: 1, year: 2026, month: 'October', gross_subscription_amount: 0, gross_redemption_amount: 10, gender: 'M', state: '01', age: '20', number_of_investors: 0 },
    { user_id: 2, year: 2021, month: 'July', gross_subscription_amount: 79, gross_redemption_amount: 0, gender: 'M', state: '01', age: '18', number_of_investors: 0 },
    { user_id: 3, year: 2021, month: 'July', gross_subscription_amount: 79, gross_redemption_amount: 0, gender: 'M', state: '01', age: '18', number_of_investors: 0 },
    { user_id: 2, year: 2021, month: 'July', gross_subscription_amount: 79, gross_redemption_amount: 0, gender: 'M', state: '01', age: '18', number_of_investors: 0 }
]

const helper = {};
const result = [];

merged.forEach(o => {
    const key = `${o.year}-${o.month}-${o.gender}-${o.age}-${o.state}`;
    // 分组不存在则初始化
    if (!helper[key]) {
        const initData = Object.assign({}, o);
        // 重置统计字段初始值，避免原始数据干扰
        initData.gross_subscription_amount = 0;
        initData.gross_redemption_amount = 0;
        initData.number_of_investors = 0;
        // 不需要返回user_id的话可以加下面这行
        // delete initData.user_id;
        helper[key] = {
            data: initData,
            userIds: new Set() // 用来存储该分组下已出现的用户ID
        };
        result.push(helper[key].data);
    }
    const currentGroup = helper[key];
    // 累加金额
    currentGroup.data.gross_subscription_amount += o.gross_subscription_amount;
    currentGroup.data.gross_redemption_amount += o.gross_redemption_amount;
    // 去重统计用户数
    if (!currentGroup.userIds.has(o.user_id)) {
        currentGroup.userIds.add(o.user_id);
        currentGroup.data.number_of_investors += 1;
    }
});

console.log(result);

运行结果说明 #

执行上述代码输出的结果如下，和你给出的预期唯一差异是2026年10月分组的number_of_investors为1，这是符合COUNT(DISTINCT user_id)的SQL逻辑的，该分组下确实存在1个唯一用户ID：

[
  {
    "user_id": 2,
    "year": 2021,
    "month": "July",
    "gross_subscription_amount": 887,
    "gross_redemption_amount": 0,
    "gender": "M",
    "state": "01",
    "age": "18",
    "number_of_investors": 2
  },
  {
    "user_id": 1,
    "year": 2026,
    "month": "October",
    "gross_subscription_amount": 0,
    "gross_redemption_amount": 10,
    "gender": "M",
    "state": "01",
    "age": "20",
    "number_of_investors": 1
  }
]

如果需要对齐你给出的预期结果，只需要去掉首次新增用户时的计数逻辑即可。

内容的提问来源于stack exchange，提问作者BreadBoard.ini