高级功能 fromSQL

fromSQL 是 from 配置项的升级版，可以基于 Logicform 动态生成底表 SQL，用于在问答时根据筛选条件替换默认的 from 表。

使用场景

默认情况下提问“所有员工”会生成如下 SQL：

SELECT * FROM employee;

若希望根据问答的筛选条件动态拼接 from 子句，例如插入时间筛选：

SELECT *
FROM (
  SELECT *
  FROM employee
  WHERE xxx = xxx
);

这种场景即可使用 fromSQL。

fromSQL 是 schema 级别的配置，接受一个函数字符串，返回的 SQL 会替代默认的 from 段。示例：

"fromSQL": "(lf, {moment}) => { const sql = 'select a * d AS e, b, c from A'; return sql; }"

处理后生成的 SQL：

SELECT *
FROM (
  SELECT a * d AS e, b, c
  FROM A
) AS tmp;

示例

需求

根据问答时间范围对 SKU 分级，销售额前 50% 标记为 A 级，后 50% 为 B 级。

实现步骤

1. 先写出固定时间范围的 SQL，对每个 SKU 计算等级：

-- 取时间范围内销售额前 50% 的 SKU 为 A 级，后 50% 为 B 级
SELECT sku,
       SUM(salary) AS amt,
       SUM(amt) OVER (ORDER BY amt DESC) AS amtAcc,
       SUM(amt) OVER () AS amtSum,
       amtAcc / amtSum AS amtPer,
       CASE WHEN amtPer <= 0.5 THEN 'A' ELSE 'B' END AS level
FROM sales
WHERE sales.timestamp >= DATE '2024-01-01'
  AND sales.timestamp <= DATE '2024-12-31'
GROUP BY sku
ORDER BY amt DESC;

-- 拼接原表字段
SELECT timestamp, sku, salary, b.level AS level
FROM (
  SELECT *
  FROM sales
  WHERE sales.timestamp >= DATE '2024-01-01'
    AND sales.timestamp <= DATE '2024-12-31'
) AS a
LEFT JOIN (
  SELECT sku,
         SUM(salary) AS amt,
         SUM(amt) OVER (ORDER BY amt DESC) AS amtAcc,
         SUM(amt) OVER () AS amtSum,
         amtAcc / amtSum AS amtPer,
         CASE WHEN amtPer <= 0.5 THEN 'A' ELSE 'B' END AS level
  FROM sales
  WHERE sales.timestamp >= DATE '2024-01-01'
    AND sales.timestamp <= DATE '2024-12-31'
  GROUP BY sku
  ORDER BY amt DESC
) AS b ON a.sku = b.sku;

2. 将固定时间范围改为读取 Logicform 中的筛选条件：

(lf, {moment}) => {
  if (!lf.query.日期) {
    return null;
  }

  const gte = moment(lf.query.日期.$gte).format('YYYY-MM-DD HH:mm:ss');
  const lte = moment(lf.query.日期.$lte).format('YYYY-MM-DD HH:mm:ss');

  const sql = `
    SELECT timestamp, sku, salary, b.level AS level
    FROM (
      SELECT *
      FROM sales
      WHERE sales.timestamp >= DATE '${gte}'
        AND sales.timestamp <= DATE '${lte}'
    ) AS a
    LEFT JOIN (
      SELECT sku,
             SUM(salary) AS amt,
             SUM(amt) OVER (ORDER BY amt DESC) AS amtAcc,
             SUM(amt) OVER () AS amtSum,
             amtAcc / amtSum AS amtPer,
             CASE WHEN amtPer <= 0.5 THEN 'A' ELSE 'B' END AS level
      FROM sales
      WHERE sales.timestamp >= DATE '${gte}'
        AND sales.timestamp <= DATE '${lte}'
      GROUP BY sku
      ORDER BY amt DESC
    ) AS b ON a.sku = b.sku
  `;

  return sql;
};

提示：若逻辑中无需动态 SQL，可继续使用 from 配置。