SQL 数据仓库建模与查询优化

group by报错因sql严格模式要求select非聚合字段必须在group by中或加聚合函数；分区表未提速反变慢因查询未带分区键导致全分区扫描；join顺序通常由优化器重排，但驱动表选择不当仍影响性能；物化视图延迟需比对基表与视图的max(updated_at)确认。

为什么 GROUP BY 后加了字段却报错？

常见错误是：在启用 sql_mode=only_full_group_by（mysql 5.7+ 默认）或严格模式的 postgresql 中，SELECT 列表里出现未聚合、也未出现在 GROUP BY 中的字段，直接报错 Expression #1 of SELECT list is not in GROUP BY clause。

这不是 bug，是标准 SQL 的一致性要求。你查的是“每组一条结果”，那所有非聚合列必须明确属于哪一组——要么放进 GROUP BY，要么套进 MAX()/MIN()/ANY_VALUE() 里。

• 别依赖 ANY_VALUE() 滥用，它不保证返回哪一行的值，仅适合你知道组内该字段实际一致（比如订单表里每笔订单只对应一个 customer_id，但按商品分组时误写了它）
• PostgreSQL 更严格：连 ANY_VALUE() 都没有，必须显式 GROUP BY 或用窗口函数替代
• 建模时就该想清楚粒度：如果业务要“每个客户最近一笔订单”，别硬写 GROUP BY customer_id + MAX(order_time)，改用 ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time DESC) 更可靠

分区表没提速，反而变慢？

分区不是银弹。常见场景是按时间字段（如 dt）做范围分区，但查询没带上分区键过滤条件，优化器无法裁剪，就得扫全量分区。

比如表按 dt 分区，但查询写成 WHERE status = 'paid'，哪怕数据只占 1%，也会遍历所有分区文件。

• 必须确保高频查询的 WHERE 条件包含分区字段，且能被优化器识别（避免对分区字段用函数：WHERE DATE(dt) = '2024-01-01' 就会失效）
• 分区数不宜过多：单表超 200 个分区时，元数据开销和查询计划生成时间明显上升，hive/Trino 尤其敏感
• 冷热分离有用，但别为了“看着整齐”而分区：如果 90% 查询都集中在最近 7 天，按天分区合理；如果查询跨度常跨月，按月更稳

JOIN 顺序影响执行计划吗？

在大多数现代 SQL 引擎（Snowflake、BigQuery、Trino、PostgreSQL）里，优化器会重排 JOIN 顺序，你写的先后顺序不决定物理执行流。但有两个关键例外：

• 使用 STRAIGHT_JOIN（MySQL）或 /*+ JOIN_ORDER */ 提示时，强制按书写顺序执行，此时小表放前、大表放后能减少中间结果集
• 左连接链（A LEFT JOIN B LEFT JOIN C）中，若 B 和 C 无关联条件，优化器可能无法下推 C 的过滤条件，导致先膨胀再过滤
• 真正影响性能的是驱动表的选择：如果 JOIN 条件上没索引/统计信息不准/小表没走广播（如 spark 中 broadcast_hashjoin 关闭），引擎可能选错驱动表，这时得靠 EXPLAIN 看 rows 预估是否失真

物化视图更新延迟怎么查？

物化视图（如 Snowflake 的 MATERIALIZED VIEW、PostgreSQL 的 REFRESH MATERIALIZED VIEW CONCURRENTLY）不是实时的。延迟来源往往不在刷新逻辑本身，而在上游变更捕获和调度间隙。

• Snowflake 中，物化视图依赖基表的微分区元数据变更，如果基表是频繁 INSERT ... SELECT 写入，而非批量 copy，微分区合并不及时会导致物化视图“看不见”新数据
• PostgreSQL 物化视图需手动 REFRESH，除非用 pg_cron 定时，否则完全静态；并发刷新虽不锁表，但会阻塞后续刷新请求
• 查延迟最直接方式是比对：物化视图里 MAX(updated_at) 和基表里同字段最大值，差值就是滞后量；别只看刷新任务日志，它只说明“任务跑完了”，不说明“数据对齐了”

建模时最容易被忽略的，是把“查询快”等同于“模型好”。其实慢查询往往是粒度错配、冗余计算、或权限/网络层干扰的结果，而不是模型本身的问题。先确认执行计划里真正耗时的算子，再动表结构。