如何正确使用SQL LAG()函数？修复含语法错误的查询语句

Got it, let's sort out this error for you! The problem here is that window functions like LAG() absolutely need an OVER clause to tell the database how to define the "window" of rows it should look at to fetch the previous day's close price. Without that clause, PostgreSQL (since the error code 42809 is specific to Postgres) has no idea which row counts as the "previous" one.

Why Your Original Query Fails #

You tried using LAG(close) directly in the WHERE clause, but two key issues are at play:

1. LAG() requires an OVER clause to specify grouping (e.g., by stock symbol) and ordering (by date) to correctly pull the prior day's value.
2. Window functions are computed after the WHERE clause runs, so you can't use them directly in filtering conditions—you need to calculate the value first in a subquery or CTE.

Fixed Query #

Assuming your daily_data table includes a symbol column (to group data by individual stocks), here's the corrected version:

SELECT *
FROM (
    SELECT
        *,
        -- Calculate previous day's close for each stock, ordered by date
        LAG(close) OVER (PARTITION BY symbol ORDER BY date) AS prev_close
    FROM "daily_data"
) AS subquery
WHERE date > '2018-01-01'
  -- Use the precomputed prev_close in your condition
  AND (open - prev_close) / prev_close >= 0.4  -- Note: 1.4 would mean a 140% increase, did you mean 40%? Adjust if needed!
  AND volume > 1000000
  AND open > 1;

If You Don't Have a symbol Column #

If this table only tracks data for a single stock, you can remove the PARTITION BY clause:

SELECT *
FROM (
    SELECT
        *,
        LAG(close) OVER (ORDER BY date) AS prev_close
    FROM "daily_data"
) AS subquery
WHERE date > '2018-01-01'
  AND (open - prev_close) / prev_close >= 0.4
  AND volume > 1000000
  AND open > 1;

Quick Note on the Percentage Condition #

I noticed your original condition uses >=1.4—that would mean the open price is 140% higher than the previous close (a 240% total value). If you meant a 40% increase (open is 1.4x the prior close), that condition is correct, but if you meant a 140% increase, you'd want >=2.4. Just double-check that to match your intended logic!

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