How can I increment the numerical value in my WHERE clause using a loop?-CodePudding

I am currently using the UNION ALL workaround below to calculate old_eps_tfq regression slopes of each ticker based off its corresponding rownum value (see WHERE rownum < x). I am interested to know what the old_eps_tfq is when rownum < 4 then increment 4 by 1 to find out what old_eps_tfq is when rownum < 5, and so on (there are ~20 rownum)

Could I use PL/pgSQL for this?

SELECT * FROM(
    WITH regression_slope AS(
        SELECT
            ROW_NUMBER() OVER ( PARTITION BY ticker ORDER BY earnings_growths_ped) AS rownum,
            *
        FROM "ANALYTICS"."vEARNINGS_GROWTHS"
        --WHERE ticker = 'ACN' 
        ORDER BY ticker )   
    SELECT
        ticker,
        current_period_end_date,
        max(earnings_growths_ped) AS max_earnings_growths_ped, 
        --max(rownum) AS max_rownum, 
        round(regr_slope(old_eps_tfq, rownum)::numeric, 2) AS slope,
        round(regr_intercept(old_eps_tfq, rownum)::numeric, 2) AS y_intercept,
        round(regr_r2(old_eps_tfq, rownum)::numeric, 3) AS r_squared
    FROM regression_slope  
    WHERE rownum < 4
    GROUP BY ticker, current_period_end_date
    ORDER BY ticker asc ) q

UNION ALL 

SELECT * FROM(
    WITH regression_slope AS(
        SELECT
            ROW_NUMBER() OVER ( PARTITION BY ticker ORDER BY earnings_growths_ped) AS rownum,
            *
        FROM "ANALYTICS"."vEARNINGS_GROWTHS"
        --WHERE ticker = 'ACN' 
        ORDER BY ticker )   
    SELECT
        ticker,
        current_period_end_date,
        max(earnings_growths_ped) AS max_earnings_growths_ped, 
        --max(rownum) AS max_rownum, 
        round(regr_slope(old_eps_tfq, rownum)::numeric, 2) AS slope,
        round(regr_intercept(old_eps_tfq, rownum)::numeric, 2) AS y_intercept,
        round(regr_r2(old_eps_tfq, rownum)::numeric, 3) AS r_squared
    FROM regression_slope  
    WHERE rownum < 5
    GROUP BY ticker, current_period_end_date
    ORDER BY ticker asc ) q

Here is my table

CodePudding user response：

The top query SELECT * FROM (...) q sounds like useless. Then you can try this :

WITH regression_slope AS(
    SELECT
        ROW_NUMBER() OVER ( PARTITION BY ticker ORDER BY earnings_growths_ped) AS rownum,
        *
    FROM "ANALYTICS"."vEARNINGS_GROWTHS"
    --WHERE ticker = 'ACN' 
    ORDER BY ticker )   
SELECT
    max,
    ticker,
    current_period_end_date,
    max(earnings_growths_ped) AS max_earnings_growths_ped, 
    --max(rownum) AS max_rownum, 
    round(regr_slope(old_eps_tfq, rownum)::numeric, 2) AS slope,
    round(regr_intercept(old_eps_tfq, rownum)::numeric, 2) AS y_intercept,
    round(regr_r2(old_eps_tfq, rownum)::numeric, 3) AS r_squared
FROM regression_slope
INNER JOIN generate_series(4, 24) AS max -- the range 4 to 24 can be adjusted to the need
   ON rownum < max
GROUP BY max, ticker, current_period_end_date
ORDER BY max asc, ticker asc