如何优化含重复子查询的SQL语句，避免代码冗余？

Great catch on that redundant code—duplicating logic like that not only makes your query harder to maintain (if you need to update the check later, you have to do it twice!) but can also lead to subtle bugs if the two versions drift out of sync. Since you’re using PostgreSQL (I can tell from the sys_period, sys_time(), and range operator &&), there are a few clean ways to fix this. Let’s go through the best options:

1. Use a LATERAL Join (Most Efficient for Row-Level Logic) #

PostgreSQL's LATERAL join lets you run a subquery that references columns from the outer query, and you can reuse its results multiple times. This is perfect for your scenario because it computes the eligibility check once per row, then lets you use that result in both the SELECT and WHERE clauses:

SELECT 
  usage_check.is_eligible AS usage,
  acc.usage_range,
  acc.invoice,
  acc.invoice_range,
  o.*
FROM "order" o
LEFT JOIN period prd ON prd.id = o.period_id
LEFT JOIN accounting_ready() acc ON TRUE
LEFT JOIN LATERAL (
  SELECT 
    acc.usage 
      AND EXISTS (
        SELECT * 
        FROM order_bt prev_order 
        WHERE sys_period @> sys_time() 
          AND prev_order.id = o.id 
          AND prev_order.app_period && acc.usage_range
      ) AS is_eligible
) AS usage_check ON TRUE
WHERE usage_check.is_eligible OR acc.invoice;

The LATERAL subquery runs alongside your core joins, calculating the is_eligible value once. No more duplicate code, and you only run the accounting_ready() function and joins once—keeping things efficient.

2. Nest the Query in a Subquery (Familiar and Straightforward) #

If you’re not comfortable with LATERAL, wrapping your core logic in a subquery works just as well. Compute the eligibility check once in the inner query, then reference it in the outer SELECT and WHERE:

SELECT 
  is_eligible_for_usage AS usage,
  usage_range,
  invoice,
  invoice_range,
  o.*
FROM (
  SELECT 
    o.*,
    acc.usage_range,
    acc.invoice,
    acc.invoice_range,
    acc.usage 
      AND EXISTS (
        SELECT * 
        FROM order_bt prev_order 
        WHERE sys_period @> sys_time() 
          AND prev_order.id = o.id 
          AND prev_order.app_period && acc.usage_range
      ) AS is_eligible_for_usage
  FROM "order" o
  LEFT JOIN period prd ON prd.id = o.period_id
  LEFT JOIN accounting_ready() acc ON TRUE
) AS core_query
WHERE is_eligible_for_usage OR invoice;

This approach is easy to read and maintain—you define all your calculated columns in one place, then use them as needed in the outer query.

3. Use a CTE (Common Table Expression) for Readability #

CTEs are great for breaking down complex queries into modular parts. You can define the eligibility check in a CTE, then join back to it to reuse the result:

WITH order_eligibility AS (
  SELECT 
    o.id,
    acc.usage 
      AND EXISTS (
        SELECT * 
        FROM order_bt prev_order 
        WHERE sys_period @> sys_time() 
          AND prev_order.id = o.id 
          AND prev_order.app_period && acc.usage_range
      ) AS is_eligible_for_usage,
    acc.usage_range,
    acc.invoice,
    acc.invoice_range
  FROM "order" o
  LEFT JOIN period prd ON prd.id = o.period_id
  LEFT JOIN accounting_ready() acc ON TRUE
)
SELECT 
  is_eligible_for_usage AS usage,
  usage_range,
  invoice,
  invoice_range,
  o.*
FROM "order" o
JOIN order_eligibility e ON e.id = o.id
WHERE e.is_eligible_for_usage OR e.invoice;

CTEs make your query’s intent clear at a glance, though note that in some cases PostgreSQL might optimize CTEs differently than subqueries or LATERAL joins—test with your dataset if performance is critical.

Final Note #

All three methods eliminate the redundant code, but the LATERAL join is usually the most efficient here since it avoids re-running joins or function calls. Pick the one that fits your team’s familiarity and your query’s performance needs!

内容的提问来源于stack exchange，提问作者Eugen Konkov