CODEWITHSUNDEEP
sqlpostgresqlsql-update
Tim Berti
Tim Berti

Reputation: 67

PostgreSQL: efficiently update table with lag()

I have a table with annual revenues of about 20000 companies for 10 years (so ~200000 rows). I want to add to each row the revenue growth compared to 3 years ago. What i did is this:

WITH tmp AS (   
    SELECT
       ticker,
       time,
       revenue,
       LAG(revenue, 3) OVER w revenue_3
    FROM companies_analytics
    WINDOW w AS (
       PARTITION BY ticker
           ORDER BY time
    )
) 
UPDATE companies_analytics
SET
    revenue_growth_3y = tmp.revenue / NULLIF(tmp.revenue_3, 0)
FROM tmp
;

But this query is taking much longer than other queries with similar workloads. Is there a more efficient way to do this?

Upvotes: 0

Views: 161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

You are missing condition connecting the table being updated to tmp. Assuming that ticker/time is unique, you can use:

UPDATE companies_analytics ca
    SET revenue_growth_3y = tmp.revenue / NULLIF(tmp.revenue_3, 0)
    FROM tmp
    WHERE tmp.ticker = ca.ticker AND tmp.time = CA.time;

Note, however, that that is still updating basically all the rows, and that is also an expensive operation.

Upvotes: 1

Related Questions