无嵌套子查询时如何在SuiteQL中聚合数据并计算毛利率

我有一张名为transactions的表，结构及数据如下：

我希望按location和account对amount求和，将每个account的结果放在单独列中，预期结果如下：

通常我会通过自连接子查询实现，SQL语句如下：

SELECT cogs.location,
    SUM(cogs.amount) AS 'cogs_total',
    sales.sales_total
FROM transactions cogs
LEFT JOIN (
    SELECT location,
        SUM(amount) AS 'sales_total'
    FROM transactions
    WHERE account = 'sales'
    GROUP BY location
) sales ON sales.location = cogs.location
WHERE cogs.account = 'cogs'
GROUP BY location;

但我使用的Netsuite SuiteTalk REST API仅支持受限的SuiteQL语法（Oracle SQL子集），不允许此类子查询JOIN。

实际业务中，表包含更多字段，结构及数据如下：

预期结果需包含按location、department聚合的cogs_total、sales_total及毛利率，格式如下：

毛利率百分比 = ((收入 - COGS) / 收入) * 100

需要找到无需子查询JOIN的替代方案，实现上述多维度聚合及毛利率计算需求。

可以使用条件聚合（CASE WHEN配合SUM）来实现，这是SuiteQL完全支持的语法，不需要子查询或JOIN操作。

针对实际业务场景的SQL语句如下：

SELECT 
    location AS "location（地点）",
    department AS "department（部门）",
    SUM(CASE WHEN account = 'cogs' THEN amount ELSE 0 END) AS "cogs_total",
    SUM(CASE WHEN account = 'sales' THEN amount ELSE 0 END) AS "sales_total",
    -- 计算毛利率，处理sales_total为0的情况避免除以0错误
    CASE 
        WHEN SUM(CASE WHEN account = 'sales' THEN amount ELSE 0 END) = 0 THEN 'N/A'
        ELSE ROUND(
            ((SUM(CASE WHEN account = 'sales' THEN amount ELSE 0 END) - SUM(CASE WHEN account = 'cogs' THEN amount ELSE 0 END)) 
            / SUM(CASE WHEN account = 'sales' THEN amount ELSE 0 END)) * 100, 
            0
        ) || '%' 
    END AS "gross_margin（毛利率）"
FROM transactions
GROUP BY location, department
ORDER BY location, department;

语法说明 #

1. 条件聚合：通过CASE WHEN判断每条记录的account类型，只对符合条件的amount进行求和，直接在同一查询中生成cogs_total和sales_total列，无需拆分查询再JOIN。
2. 毛利率计算：
   • 先判断sales_total是否为0，避免出现除以0的错误，此时返回N/A（可根据业务需求调整显示内容）。
   • 用sales_total减去cogs_total得到毛利，再除以sales_total并乘以100得到百分比，通过ROUND函数保留整数位，最后拼接%符号完成格式转换。
3. 分组与排序：按location和department分组聚合，确保每个维度组合的结果唯一，排序后结果更易读。

结果验证 #

执行上述SQL后，将得到与预期一致的聚合结果：每个location+department组合的成本、收入求和准确，毛利率按公式计算并格式化显示。

内容的提问来源于stack exchange，提问作者Adam Richard