解决Alasql自定义聚合函数在分组查询中返回Undefined的问题

interface Cat {
  name: string;
  age: number;
  color: string;
}

const cats: Cat[] = [
  { name: 'Mittens', age: 3, color: 'black' },
  { name: 'Whiskers', age: 2, color: 'white' },
  { name: 'Shadow', age: 5, color: 'black' },
  { name: 'Patches', age: 1, color: 'calico' },
  { name: 'Snowball', age: 4, color: 'white' },
];

const sqlBuiltIn = `
  SELECT color, SUM(age) AS totalAge
  FROM ?
  GROUP BY color
`;
alasql(sqlBuiltIn, [cats]).then(result => {
  console.log("使用内置SUM函数的结果:", result);
  // 预期输出: [{ color: 'black', totalAge: 8 }, { color: 'white', totalAge: 6 }, { color: 'calico', totalAge: 1 }]
});

import { sum } from 'lodash';
import alasql from 'alasql'; // 假设alasql已导入

// 错误的自定义聚合函数实现
alasql.fn.mySum = function(xs) {
  // 注意：这里缺少了return语句
  sum(xs);
};

const sqlCustomUDF = `
  SELECT color, mySum(age) AS totalAge
  FROM ?
  GROUP BY color
`;

alasql(sqlCustomUDF, [cats]).then(result => {
  console.log("使用自定义mySum函数（错误实现）的结果:", result);
  // 实际输出可能包含undefined或0，且mySum函数内部的xs会是undefined
}).catch(err => {
  console.error("查询失败:", err);
});

alasql.fn.mySum = function(xs) {
  sum(xs); // lodash的sum函数被调用，但其结果没有被返回
};

import { sum } from 'lodash';
import alasql from 'alasql';

// 正确的自定义聚合函数实现
alasql.fn.mySum = function(xs) {
  // 关键：显式地返回lodash.sum(xs)的结果
  return sum(xs);
};

const sqlCorrectUDF = `
  SELECT color, mySum(age) AS totalAge
  FROM ?
  GROUP BY color
`;

alasql(sqlCorrectUDF, [cats]).then(result => {
  console.log("使用自定义mySum函数（正确实现）的结果:", result);
  // 预期输出: [{ color: 'black', totalAge: 8 }, { color: 'white', totalAge: 6 }, { color: 'calico', totalAge: 1 }]
}).catch(err => {
  console.error("查询失败:", err);
});

import alasql from 'alasql';
import { sum } from 'lodash';

// 定义数据接口
interface Cat {
  name: string;
  age: number;
  color: string;
}

// 示例数据
const cats: Cat[] = [
  { name: 'Mittens', age: 3, color: 'black' },
  { name: 'Whiskers', age: 2, color: 'white' },
  { name: 'Shadow', age: 5, color: 'black' },
  { name: 'Patches', age: 1, color: 'calico' },
  { name: 'Snowball', age: 4, color: 'white' },
];

console.log("原始猫咪数据:", cats);

// 注册正确的自定义聚合函数
alasql.fn.mySum = function(xs: number[]): number {
  // 对于聚合函数，xs会是一个数组，包含当前分组中所有行的相应列值
  console.log(`mySum called with:`, xs); // 调试用途，查看传入的xs
  if (!Array.isArray(xs) || xs.length === 0) {
    return 0; // 处理空数组情况
  }
  return sum(xs); // 显式返回lodash.sum的计算结果
};

// 执行包含自定义聚合函数的查询
const sql = `
  SELECT 
    color, 
    mySum(age) AS totalAgeByColor
  FROM ?
  GROUP BY color
`;

alasql(sql, [cats]).then(result => {
  console.log("\n使用自定义mySum函数进行分组求和的结果:");
  console.log(result);
  /*
  预期输出:
  [
    { color: 'black', totalAgeByColor: 8 },
    { color: 'white', totalAgeByColor: 6 },
    { color: 'calico', totalAgeByColor: 1 }
  ]
  */
}).catch(err => {
  console.error("Alasql查询执行失败:", err);
});

// 也可以测试一个没有分组的查询，看看mySum如何处理
const sqlNoGroup = `SELECT mySum(age) AS totalAge FROM ?`;
alasql(sqlNoGroup, [cats]).then(result => {
  console.log("\n不分组时使用mySum函数进行求和的结果:");
  console.log(result);
  // 预期输出: [{ totalAge: 15 }]
}).catch(err => {
  console.error("Alasql查询执行失败 (无分组):", err);
});