PostgreSQL：如何用最近4行收入的AVG值与比率列相乘预测收入

Alright, let's walk through how to calculate those future revenue predictions based on your data step by step.

First, let's confirm the requirements: We need to calculate the average of the most recent 4 valid revenue values (from 03-30 to 04-02), then multiply that average by the ratio value for each future date (04-03 onwards) to get predicted revenue.

Step 1: Calculate the Average of Recent Valid Revenue #

First, sum up the 4 valid revenue figures:
918264 + 981247 + 957353 + 926274 = 3,783,138

Then divide by 4 to get the average:
3,783,138 ÷ 4 = 945,784.5

Step 2: Compute Predicted Revenue for Future Dates #

Multiply the average we just found by each future date's ratio:

• 04-03-18: 945784.5 × 1.05 = 993,073.725 (we can round this to 993,074 for practical use)
• 04-04-18: 945784.5 × 0.97 = 917,410.965 (rounded to 917,411)
• 04-05-18: 945784.5 × 1.23 = 1,163,314.935 (rounded to 1,163,315)

Final Results Table #

Bonus: Automate the Calculation #

If you want to do this programmatically, here's how you can do it with Python (using pandas):

import pandas as pd

# Create the initial dataframe
data = {
    'date': ['03-30-18', '03-31-18', '04-01-18', '04-02-18', '04-03-18', '04-04-18', '04-05-18'],
    'ratio': [1.2, 0.94, 1.1, 0.99, 1.05, 0.97, 1.23],
    'revenue': [918264, 981247, 957353, 926274, None, None, None]
}
df = pd.DataFrame(data)

# Calculate average of the last 4 valid revenues
avg_rev = df['revenue'].dropna().tail(4).mean()

# Generate predicted revenue for future dates
df['predicted_revenue'] = df.apply(
    lambda row: round(row['ratio'] * avg_rev) if pd.isna(row['revenue']) else None,
    axis=1
)

# Print the result
print(df)

Or in Excel, you can use these formulas:

• Average calculation: =AVERAGE(C2:C5) (assuming revenue values are in cells C2 to C5)
• Predicted revenue for 04-03-18: =$C$6*B6 (where C6 is the cell with the average, B6 is the ratio for that date)

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