格式化清晰易读的SQL查询

问题描述

我正在编写一些带有多个子查询和许多连接的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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！