Is it possible to reduce the duplication in this simple query?

Question

I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string concatenation from the language I'm using to generate the long query but I think there must be a way to do this with plain SQL?

select
  x + y as a,
  x + z as b,
  (x + y) / (x + z) as c
from
  integers
order by
  z + (x + y) / (x + z) desc
limit
  10;

What I'd like:

select
  x + y as a,
  x + z as b,
  a / b as c
from
  integers
order by
  z + c desc
limit
  10;
-- Fails with: Query Error: error: column "a" does not exist

Sample table:

create table integers as
select x, y, z
from
  generate_series(1, 10) x,
  generate_series(x, 10) y,
  generate_series(y, 10) z;

Fiddle: https://www.db-fiddle.com/f/u3cVh7QsafWNE6ELWhvBc1/0

score 1 · Accepted Answer · answered Mar 07 '21 at 00:54

Two levels of derived tables (subquery or CTE) to avoid all repeated calculations:

SELECT a, b, c
FROM  (
   SELECT z, a, b, a / b AS c
   FROM (
      SELECT z
           , x + y AS a
           , x + z AS b
      FROM   integers
      ) i
   ) i
ORDER  BY  z + c DESC
LIMIT  10;

With simple expressions, it won't matter for performance.
What might matter with repeating more expensive expressions is that all involved functions have (correct!) volatility STABLE or IMMUTABLE. See:

Can declaring function volatility IMMUTABLE harm performance?

Is it possible to reduce the duplication in this simple query?

1 Answers1