将表格功能链接在一起

假设我想执行一个SQL语句,它可以分解为简单的“步骤”,就像在ETL中一样。以下是最基本的示例:

-- step1
SELECT * FROM Sales JOIN Other WHERE Country='US'

-- step2 
SELECT * FROM NewTable Join (SELECT id, age FROM <previous1> GROUP BY Id) x USING (id)

-- step3
SELECT * FROM OtherTable WHERE someField in (SELECT field FROM <previous2>)

-- and 10 more steps...

我想我可以用以下方法来实现这个“步骤”概念:

WITH first_step AS (
        SELECT * FROM Sales WHERE Country='US'),
     second_step AS (
        SELECT * FROM NewTable JOIN (SELECT id, age FROM first_step GROUP BY Id) x USING (id)),
     third_step AS (
        SELECT * FROM OtherTable WHERE someField in (SELECT field FROM second_step)),
    ...

这似乎是一种可读性和可扩展性更强的方法,而不仅仅是将所有内容都放入查询中,我从经验中知道,经过一定数量的步骤后,查询几乎不可能维护甚至理解:

SELECT * FROM OtherTable WHERE someField in (SELECT field FROM (
    SELECT * FROM NewTable Join (SELECT id, age FROM (
        SELECT * FROM Sales JOIN Other WHERE Country='US'
    ) GROUP BY Id) x USING (id)
)

这是最好的选择吗?或者,在不将中间结果具体化到表的情况下(如果步骤之间有很多数据,这可能需要一段时间),如何最好地组织它?