aws sp simulator

sp_simulator.py

def get_df_sp(sp_uncovered_map, sp_committed):
    sp_result = []
    for hour, rows in sp_uncovered_map.items():
        remaining_sp_committed = sp_committed
        for row in rows:
            # 현재 usage_type에서 최대로 커버할 수 있는 amount ->  ? * sp_rate = remaining_sp_committed 가 되는 ? 또는 해당 usage_type amount의 최댓값
            # 즉 min(remaining_sp_committed/sp_rate, usage_amount)가 현재 usage_type이 쓸수있는 최대 amount
            covered_amount = min(remaining_sp_committed / row['sp_rate'], row['usage_amount'])
            uncovered_amount = max(0, row['usage_amount'] - covered_amount)
            covered_cost = covered_amount * row['sp_rate']
            uncovered_cost = uncovered_amount * row['unblended_rate']
            remaining_sp_committed -= covered_cost
            sp_result += [{
                'hour': hour,
                'usage_type': row['usage_type'],
                'sp_rate': row['sp_rate'],
                'unblended_rate': row['unblended_rate'],
                'usage_amount': row['usage_amount'],
                'covered_cost': covered_cost,
                'uncovered_cost': uncovered_cost
            }]
    df_sp = pd.DataFrame(sp_result)
    df_sp['covered_cost_cumsum'] = df_sp.groupby('hour').covered_cost.cumsum()
    df_sp['uncovered_cost_cumsum'] = df_sp.groupby('hour').uncovered_cost.cumsum()
    df_sp['recurred_cost_cumsum'] = sp_committed - df_sp['covered_cost_cumsum']
    return df_sp

def get_df_sp_cumsum(df_sp):
    df_sp_cumsum = df_sp[df_sp.hour != df_sp.hour.shift(-1)][['hour', 'covered_cost_cumsum', 'uncovered_cost_cumsum', 'recurred_cost_cumsum']]
    df_sp_cumsum.loc[(df_sp_cumsum.hour.dt.hour % 6)!=0, 'hour'] = ''
    return df_sp_cumsum
  

stmt = dedent(f'''
    SELECT
        DATE_TRUNC('HOUR', USAGESTARTDATE) DATE_HOUR, USAGETYPE, UNBLENDEDRATE, SUM(USAGEAMOUNT) USAGEAMOUNT
    FROM
        AWS_COST.PUBLIC.AWS_BILLING
    WHERE
        (USAGESTARTDATE BETWEEN DATEADD(DAY, -37, CURRENT_DATE()) AND DATEADD(DAY, -7, CURRENT_DATE())) AND
        OPERATION IN ('RunInstances', 'Invoke', 'FargateTask') AND
        PRODUCTCODE IN ('AmazonEC2', 'AmazonECS', 'AWSLambda') AND
        LINEITEMTYPE = 'Usage' AND
        NOT (USAGETYPE LIKE ANY ('%Storage%', '%Provisioned%', '%SpotUsage%', '%Request%'))
    GROUP BY
        DATE_HOUR, USAGETYPE, UNBLENDEDRATE
    ORDER BY
        DATE_HOUR, USAGETYPE, UNBLENDEDRATE
''')
query = conn.cursor().execute(stmt)
rows = query.fetchall()

sp_uncovered_map = defaultdict(list)
for date_hour, usage_type, unblended_rate, usage_amount in rows:
    sp_uncovered_map[date_hour] += [{
        'usage_type': usage_type,
        'usage_amount': usage_amount,
        'unblended_rate': unblended_rate,
        'sp_rate': sp_rate_map.get(usage_type, unblended_rate)
    }]
for key in sp_uncovered_map:
    sp_uncovered_map[key] = sorted(sp_uncovered_map[key], key=lambda _: _['sp_rate'])
    


prev_sp_cost_sum = float('inf')

df_sp_base = get_df_sp(sp_uncovered_map, 0)
df_sp_cumsum_base = get_df_sp_cumsum(df_sp_base)
sp_cost_sum_base = (df_sp_cumsum_base.covered_cost_cumsum.sum() + df_sp_cumsum_base.uncovered_cost_cumsum.sum() + df_sp_cumsum_base.recurred_cost_cumsum.sum())

for sp_committed in range(1, 20):
    df_sp = get_df_sp(sp_uncovered_map, sp_committed)
    df_sp_cumsum = get_df_sp_cumsum(df_sp)
    sp_cost_sum = (df_sp_cumsum.covered_cost_cumsum.sum() + df_sp_cumsum.uncovered_cost_cumsum.sum() + df_sp_cumsum.recurred_cost_cumsum.sum())

    print(f'${sp_committed}/hour 추가 구매했다면, 지난 30일간 {str(round(sp_cost_sum - sp_cost_sum_base,2)).replace("-","-$")}')

    if prev_sp_cost_sum < sp_cost_sum:
        break
    prev_sp_cost_sum = sp_cost_sum

print()
print(f"${sp_committed-1}/hour 추가 구매했을때가 제일 비용 절감 효과가 컷음")


sp_committed = ??
df_sp = get_df_sp(sp_uncovered_map, sp_committed)
df_sp_cumsum = get_df_sp_cumsum(df_sp)
sp_cost_sum = (df_sp_cumsum.covered_cost_cumsum.sum() + df_sp_cumsum.uncovered_cost_cumsum.sum() + df_sp_cumsum.recurred_cost_cumsum.sum())