如何用Snowflake SQL关联无连接条件的两张表生成指定分摊报表？

我有一张日历表dim_calendar，通过以下SQL计算主计算所需的DAILY_RATIO值：

select 
  (DATE_PART(DAY, CURRENT_DATE()) - 1) / (MAX(BUSINESS_DAY_COUNT)) as DAILY_RATIO 
from reporting_db.public.dim_calendar 
where year = DATE_PART(YEAR, CURRENT_DATE()) 
  and MONTH_NUMBER = DATE_PART(MONTH, CURRENT_DATE());

当前该比率为0.086957。

同时，我通过以下SQL从FORECAST表计算总额（以FORECAST_TOTAL_SALES_$为例）：

select 
  FORECAST_DATE, 
  SUM(FORECAST_TOTAL_SALES_$) 
from REPORTING_DB.PUBLIC.FORECAST 
group by 1
having FORECAST_DATE = CURRENT_DATE() - (DATE_PART(DAY, CURRENT_DATE())-1);

查询结果如下：

我需要生成如下格式的SQL输出报表，供业务用户每日运行核对数据：

但我无法将这两张无连接条件的表结合生成该报表，需要将上述单独的SQL语句整合为一个查询。

可以通过CTE（公共表表达式）分别计算每日比率和预测总额，再通过CROSS JOIN关联两个无连接条件的单结果集，最后将多列指标转成行结构并格式化输出。以下是整合后的SQL：

WITH daily_ratio AS (
    SELECT 
        ROUND((DATE_PART(DAY, CURRENT_DATE()) - 1) / MAX(BUSINESS_DAY_COUNT), 5) AS DAILY_RATIO
    FROM reporting_db.public.dim_calendar 
    WHERE year = DATE_PART(YEAR, CURRENT_DATE()) 
      AND MONTH_NUMBER = DATE_PART(MONTH, CURRENT_DATE())
),
forecast_totals AS (
    SELECT
        SUM(FORECAST_TOTAL_SALES_$) AS SALES_TOTAL,
        SUM(FORECAST_TOTAL_MARGIN_$) AS MARGIN_TOTAL,
        SUM(FORECAST_EXTEND_NET_WGHT_LB) AS WEIGHT_TOTAL,
        SUM(FORECAST_TOTAL_COST_$) AS COST_TOTAL
    FROM REPORTING_DB.PUBLIC.FORECAST 
    WHERE FORECAST_DATE = CURRENT_DATE() - (DATE_PART(DAY, CURRENT_DATE()) - 1)
),
report_rows AS (
    -- 销售总额行
    SELECT
        'FORECAST_TOTAL_SALES_$' AS metric,
        SALES_TOTAL AS monthly_total,
        dr.DAILY_RATIO,
        ROUND(SALES_TOTAL * dr.DAILY_RATIO, 2) AS pro_rated
    FROM forecast_totals ft
    CROSS JOIN daily_ratio dr
    UNION ALL
    -- 利润总额行
    SELECT
        'FORECAST_TOTAL_MARGIN_$' AS metric,
        MARGIN_TOTAL AS monthly_total,
        dr.DAILY_RATIO,
        ROUND(MARGIN_TOTAL * dr.DAILY_RATIO, 2) AS pro_rated
    FROM forecast_totals ft
    CROSS JOIN daily_ratio dr
    UNION ALL
    -- 净重行
    SELECT
        'FORECAST_EXTEND_NET_WGHT_LB' AS metric,
        WEIGHT_TOTAL AS monthly_total,
        dr.DAILY_RATIO,
        ROUND(WEIGHT_TOTAL * dr.DAILY_RATIO, 2) AS pro_rated
    FROM forecast_totals ft
    CROSS JOIN daily_ratio dr
    UNION ALL
    -- 总成本行
    SELECT
        'FORECAST_TOTAL_COST_$' AS metric,
        COST_TOTAL AS monthly_total,
        dr.DAILY_RATIO,
        ROUND(COST_TOTAL * dr.DAILY_RATIO, 2) AS pro_rated
    FROM forecast_totals ft
    CROSS JOIN daily_ratio dr
    UNION ALL
    -- 空行
    SELECT '', NULL, NULL, NULL FROM forecast_totals
    UNION ALL
    SELECT '', NULL, NULL, NULL FROM forecast_totals
    UNION ALL
    -- 利润率计算行
    SELECT
        'Total Margin / Total Sales' AS metric,
        NULL AS monthly_total,
        NULL AS DAILY_RATIO,
        ROUND(MARGIN_TOTAL / SALES_TOTAL, 9) AS pro_rated
    FROM forecast_totals ft
)
SELECT
    metric AS "",
    CASE WHEN monthly_total IS NOT NULL THEN TO_CHAR(monthly_total, 'FM999,999,999.00') ELSE '' END AS "August Total",
    CASE WHEN DAILY_RATIO IS NOT NULL THEN TO_CHAR(DAILY_RATIO, 'FM9.00000') ELSE '' END AS "Daily Ratio",
    CASE WHEN pro_rated IS NOT NULL THEN TO_CHAR(pro_rated, 'FM999,999,999.00') ELSE '' END AS "Pro-rated"
FROM report_rows;

核心逻辑说明 #

1. daily_ratio CTE：计算当月的每日分摊比率，保留5位小数匹配需求格式。
2. forecast_totals CTE：一次性汇总所有需要的预测指标，避免重复扫描FORECAST表。
3. report_rows CTE：用UNION ALL将多列指标转换为行结构，通过CROSS JOIN关联每日比率（两个CTE均为单条结果，不会产生笛卡尔积），同时插入空行和利润率计算行。
4. 最终查询：用TO_CHAR函数格式化数字为带千分位的字符串，完全匹配业务报表的显示要求。

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