Retool中同一层级count结果汇总及占比计算需求

问题背景 #

需要按totalattempts分组统计订单尝试数据，同时计算每组的allattempts（该尝试次数对应的订单总数）和successfulattempts（其中成功的订单数）在总订单数中的占比，但Retool不支持窗口函数，无法直接用COUNT(totalattempts)/SUM(COUNT(totalattempts)) OVER()计算占比。

原查询代码：

select totalattempts, count(totalattempts) allattempts, count(case when success>0 then totalattempts else null end) successfulattempts
    from ( 

            select *, case when success> 0 then attemptspresuccess+1 else attemptspresuccess end totalattempts
                from (select orderid, count(orderid) attemptspresuccess, count(case when recoveredPaymentId is not null then recoveredPaymentId end ) success from (
                        select orderid, recoveredPaymentId
                            from errors
                            where platform = 'woo'
                        ) alitable
                group by orderid) minitable ) finaltable
group by totalattempts
order by totalattempts asc

解决方案 #

方案1：在SQL中通过预计算总计数关联查询 #

核心思路是先单独计算总订单数，再将其与原分组查询结果关联，通过除法计算占比。如果Retool支持CTE（WITH子句），可以用更清晰的写法：

WITH total_orders AS (
    -- 计算woo平台的总订单数
    SELECT COUNT(DISTINCT orderid) AS total_count
    FROM errors
    WHERE platform = 'woo'
),
grouped_data AS (
    -- 原分组查询逻辑
    select totalattempts, 
           count(totalattempts) allattempts, 
           count(case when success>0 then totalattempts else null end) successfulattempts
    from ( 
            select *, case when success> 0 then attemptspresuccess+1 else attemptspresuccess end totalattempts
            from (select orderid, 
                         count(orderid) attemptspresuccess, 
                         count(case when recoveredPaymentId is not null then recoveredPaymentId end ) success 
                  from (
                          select orderid, recoveredPaymentId
                          from errors
                          where platform = 'woo'
                      ) alitable
            group by orderid) minitable ) finaltable
    group by totalattempts
)
-- 关联总计数计算占比，保留4位小数可按需调整
SELECT 
    gd.totalattempts,
    gd.allattempts,
    gd.successfulattempts,
    ROUND(CAST(gd.allattempts AS DECIMAL) / tc.total_count, 4) AS all_attempts_ratio,
    ROUND(CAST(gd.successfulattempts AS DECIMAL) / tc.total_count, 4) AS successful_ratio
FROM grouped_data gd, total_orders tc
ORDER BY gd.totalattempts asc;

如果Retool不支持CTE，改用子查询嵌套写法：

SELECT 
    gd.totalattempts,
    gd.allattempts,
    gd.successfulattempts,
    ROUND(CAST(gd.allattempts AS DECIMAL) / tc.total_count, 4) AS all_attempts_ratio,
    ROUND(CAST(gd.successfulattempts AS DECIMAL) / tc.total_count, 4) AS successful_ratio
FROM (
    -- 原分组查询逻辑
    select totalattempts, 
           count(totalattempts) allattempts, 
           count(case when success>0 then totalattempts else null end) successfulattempts
    from ( 
            select *, case when success> 0 then attemptspresuccess+1 else attemptspresuccess end totalattempts
            from (select orderid, 
                         count(orderid) attemptspresuccess, 
                         count(case when recoveredPaymentId is not null then recoveredPaymentId end ) success 
                  from (
                          select orderid, recoveredPaymentId
                          from errors
                          where platform = 'woo'
                      ) alitable
            group by orderid) minitable ) finaltable
    group by totalattempts
) gd,
(
    -- 计算总订单数
    SELECT COUNT(DISTINCT orderid) AS total_count
    FROM errors
    WHERE platform = 'woo'
) tc
ORDER BY gd.totalattempts asc;

方案2：在Retool前端计算占比 #

无需修改SQL，直接在Retool的表格组件中添加自定义列实现占比计算：

1. 运行原SQL查询，将结果绑定到表格组件
2. 在表格中添加自定义列（如all_attempts_ratio），公式填写：

   {{ currentRow.allattempts / table1.data.reduce((sum, row) => sum + row.allattempts, 0) }}
   

3. 同理添加successful_ratio列，公式为：

   {{ currentRow.successfulattempts / table1.data.reduce((sum, row) => sum + row.successfulattempts, 0) }}
   

4. 对自定义列设置格式为百分比，按需调整小数位数

这种方法更轻量化，适合不想修改SQL的场景。

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