格式化清晰可读的 SQL 查询 [英] Formatting Clear and readable SQL queries

问题描述

我正在编写一些带有多个子查询和大量连接的 SQL 查询，无论是在子查询内部还是来自子查询的结果表.

I'm writing some SQL queries with several subqueries and lots of joins everywhere, both inside the subquery and the resulting table from the subquery.

我们没有使用视图，所以这是不可能的.

We're not using views so that's out of the question.

写完之后，我看着它，挠头想知道它在做什么，因为我无法理解它.

After writing it I'm looking at it and scratching my head wondering what it's even doing cause I can't follow it.

你使用什么样的格式来试图清理这样的烂摊子?也许是缩进?

What kind of formatting do you use to make an attempt to clean up such a mess? Indents perhaps?

推荐答案

对于大型查询，我倾向于非常依赖使用 WITH 的命名结果集.这允许预先定义结果集，并使主查询更简单.命名结果集也可能有助于提高查询计划的效率，例如postgres 将结果集存储在临时表中.

With large queries I tend to rely a lot on named result sets using WITH. This allows to define the result set beforehand and it makes the main query simpler. Named results sets may help to make the query plan more efficient as well e.g. postgres stores the result set in a temporary table.

示例:

WITH 
  cubed_data AS (
     SELECT 
        dimension1_id,
        dimension2_id,
        dimension3_id,
        measure_id,
        SUM(value) value
     FROM
        source_data
     GROUP BY
        CUBE(dimension1, dimension2, dimension3),
        measure
  ), 
  dimension1_label AS(
     SELECT 
        dimension1_id,
        dimension1_label
     FROM 
        labels 
     WHERE 
        object = 'dimension1'
  ), ...
SELECT 
  *
FROM  
  cubed_data
  JOIN dimension1_label USING (dimension1_id)
  JOIN dimension2_label USING (dimension2_id)
  JOIN dimension3_label USING (dimension3_id)
  JOIN measure_label USING (measure_id)

这个例子有点做作，但我希望它显示出与内联子查询相比清晰度的提高.当我为 OLAP 使用准备数据时，命名结果集对我很有帮助.如果您有/想要创建递归查询，命名结果集也是必须的.

The example is a bit contrived but I hope it shows the increase in clarity compared to inline subqueries. Named result sets have been a great help for me when I've been preparing data for OLAP use. Named results sets are also must if you have/want to create recursive queries.

WITH 至少适用于当前版本的 Postgres、Oracle 和 SQL Server

WITH works at least on current versions of Postgres, Oracle and SQL Server

这篇关于格式化清晰可读的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！