Zoho Analytics中按月份关联两表统计比值的SQL问题排查

嘿，我来帮你排查下这个SQL的问题～你的关联查询结果不对，核心是踩了两个典型的坑：

• 笛卡尔积导致数据重复计算：你直接用B."modified" = A."Call Date"把两个表按天关联，这会让同一天的每条Demo记录和每条customertype=0的用户记录两两配对。比如某天有2条Demo、3条符合条件的用户记录，原查询会生成6条关联记录，统计出来的TotalDemos会变成6（实际是2）、TotalTrials变成6（实际是3），结果完全失真。
• 日期匹配粒度错配：你要的是按月统计，但原查询是按天匹配日期，这会漏掉那些「一个表当月有数据但某天没数据」的月份，而且分组时同时按Period和Period1也完全没必要——这俩本来就是同一个月份维度。

正确的解决思路 #

先分别对两个表做独立的月度统计（就像你单独查询时的逻辑，这个是对的！），然后把这两个统计好的月度结果按月份关联，最后计算比值。这样既不会出现重复计算，也能保证所有有数据的月份都被覆盖。

修正后的SQL代码 #

WITH monthly_demos AS (
    SELECT 
        absmonth("Call Date") as Period, 
        COUNT("Call Type") as TotalDemos
    FROM "customer_calls"
    WHERE "Call Type" = 'Demo'
    GROUP BY Period
),
monthly_trials AS (
    SELECT 
        absmonth("modified") as Period, 
        SUM(CASE WHEN "customertype" = 0 THEN 1 ELSE 0 END) AS TotalTrials
    FROM "users"
    GROUP BY Period
)
SELECT 
    COALESCE(d.Period, t.Period) AS Period,
    COALESCE(d.TotalDemos, 0) AS TotalDemos,
    COALESCE(t.TotalTrials, 0) AS TotalTrials,
    -- 处理分母为0的情况，避免报错
    CASE WHEN COALESCE(t.TotalTrials, 0) = 0 THEN 0 ELSE COALESCE(d.TotalDemos, 0) * 1.0 / t.TotalTrials END AS DemoToTrialRatio
FROM monthly_demos d
FULL OUTER JOIN monthly_trials t ON d.Period = t.Period
ORDER BY Period;

代码细节说明 #

1. CTE子查询：用WITH把两个表的月度统计拆成独立模块，和你单独查询的逻辑完全一致，保证基础统计数据准确。
2. 全外连接（FULL OUTER JOIN）：不管是只有Demo数据的月份，还是只有用户数据的月份，都会被包含在结果里，不会丢失数据。
3. COALESCE函数：把空值替换成0，避免计算时出现null值影响结果展示。
4. 比值容错处理：通过CASE语句判断分母是否为0，防止出现除以0的报错——毕竟有时候某个月份可能没有符合条件的用户记录。

再给你补个反例理解原问题 #

假设1月有：

• 2条Demo记录（分别在1月1日、1月2日）
• 3条customertype=0的用户记录（都在1月1日）

原查询会生成2*3=6条关联记录，统计出来的TotalDemos是6（实际应该是2），TotalTrials是6（实际应该是3），比值变成2，而真实比值应该是2/3≈0.666，完全不对。而先统计月度数据再关联的方式，就会得到正确的2和3，比值也准确。

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