PostgreSQL 9.5下CROSS JOIN语法错误解决及查询适配

报错根因 #

• 语法不兼容：MariaDB 允许为CROSS JOIN附加ON匹配条件的非标准写法，但PostgreSQL严格遵循SQL标准，定义CROSS JOIN为无匹配规则的笛卡尔积连接，不支持携带ON子句，这就是触发syntax error at or near "ON"的直接原因。
• 函数不兼容：MariaDB 内置的LAST_DAY()、DAY()函数在PostgreSQL 9.5中不存在，即使修正了JOIN语法，执行时也会因找不到内置函数报错。
• 分组规则不兼容：PostgreSQL 对GROUP BY的校验远严格于MariaDB，所有未被聚合计算的SELECT字段都必须纳入GROUP BY列表，否则会触发分组规则报错。

适配修改点 #

• 把带ON条件的CROSS JOIN替换为普通内连接（JOIN），保留原有等值匹配逻辑——你的连接逻辑本质是按当月最后一天匹配月度固定成本，属于常规内连接，完全不需要使用CROSS JOIN。
• 用PostgreSQL原生的日期函数替换MariaDB专属函数，保持计算逻辑完全一致：
  • 用date_trunc('month', 日期字段) + interval '1 month' - interval '1 day'替代LAST_DAY(日期字段)，实现取当月最后一天的效果。
  • 用extract(day from 当月最后一天的日期值)替代DAY(LAST_DAY(日期字段))，实现取当月总天数的效果。
• 修正布尔值聚合的写法：PostgreSQL 不会自动将布尔值转换为0/1参与求和计算，需用CASE语句显式标记符合条件的记录值为1，避免聚合计算报错。
• 补全GROUP BY列表，把所有未做聚合的字段纳入分组范围，适配PostgreSQL的严格分组规则。

PostgreSQL 9.5 可运行查询 #

SELECT 
    s.sales_date, 
    s.country, 
    SUM(s.sales_volume) AS sales_volume,
    ROUND(
        CASE 
            WHEN s.country = 'NL' THEN 0
            -- 计算当日非NL国家的总销量
            WHEN SUM(CASE WHEN s.country <> 'NL' THEN s.sales_volume END) OVER (PARTITION BY s.sales_date) > 0
            THEN (
                f.fix_costs / extract(day from (date_trunc('month', s.sales_date) + interval '1 month' - interval '1 day'))
            ) * s.sales_volume 
            / NULLIF(
                SUM(CASE WHEN s.country <> 'NL' THEN s.sales_volume END) OVER (PARTITION BY s.sales_date), 
                0
            )
            -- 非NL国家总销量为0时，平均分摊每日固定成本
            ELSE (
                f.fix_costs / extract(day from (date_trunc('month', s.sales_date) + interval '1 month' - interval '1 day'))
            ) * 1.0 
            / SUM(CASE WHEN s.country <> 'NL' THEN 1 ELSE 0 END) OVER (PARTITION by s.sales_date)
        END
    , 2) AS imputed_fix_costs
FROM sales s
JOIN (
    SELECT 
        date_trunc('month', sales_date) + interval '1 month' - interval '1 day' AS month_ld, 
        SUM(fix_costs) AS fix_costs
    FROM sales
    WHERE country = 'None'
    GROUP BY month_ld
) f
  ON f.month_ld = date_trunc('month', s.sales_date) + interval '1 month' - interval '1 day'
WHERE s.country <> 'None'
GROUP BY s.sales_date, s.country, f.fix_costs;

执行该查询可完全匹配预期的计算结果：

• 1月3日DE、FR按非NL销量占比分摊当日固定成本，NL分摊为0
• 2月15日因非NL销量为0，DE、FR平均分摊当日固定成本，NL分摊为0
• 3月27日DE、FR按非NL销量占比分摊当日固定成本，NL分摊为0

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