小浣熊办公助手：数据海洋中的智能导航者

# Query to calculate the total sales revenue of iPhones in the autumn of 2023 by regionsales_revenue_query = """SELECT u.registration_location, SUM(o.total_price) as total_revenueFROM Orders oJOIN Users u ON o.user_id = u.user_idWHERE o.product_id IN ({}) AND o.order_date BETWEEN "{}" AND "{}"GROUP BY u.registration_locationORDER BY total_revenue DESC""".format(",".join(map(str, iphone_product_ids)), start_date_2023, end_date_2023)
sales_revenue_by_region = cursor.execute(sales_revenue_query).fetchall()sales_revenue_by_region

# Query to calculate the total sales revenue of iPhones from the beginning of 2023 to autumn 2023 by regionsales_revenue_beginning_query = """SELECT u.registration_location, SUM(o.total_price) as total_revenue_beginningFROM Orders oJOIN Users u ON o.user_id = u.user_idWHERE o.product_id IN ({}) AND o.order_date BETWEEN "{}" AND "{}"GROUP BY u.registration_location""".format(",".join(map(str, iphone_product_ids)), "2023-01-01", start_date_2023)
sales_revenue_beginning = cursor.execute(sales_revenue_beginning_query).fetchall()
# Convert the results to a dictionary for easier lookupsales_revenue_beginning_dict = {row[0]: row[1] for row in sales_revenue_beginning}
# Calculate the growth rategrowth_rates = []for region, revenue in sales_revenue_by_region:    beginning_revenue = sales_revenue_beginning_dict.get(region, 0)    if beginning_revenue == 0:  # avoid division by zero        growth_rate = 0    else:        growth_rate = ((revenue - beginning_revenue) / beginning_revenue) * 100    growth_rates.append((region, growth_rate))
growth_rates

# Step 1: Calculate monthly sales for each product in 2023# (This has already been done previously, so we can reuse the data)
# Step 2: Use the average of the last 2 months to predict December salesdecember_predictions = {}for product, sales_data in monthly_sales_by_product.items():    if len(sales_data) >= 2:        last_2_months_avg = sum([sales for _, sales in sales_data[-2:]]) / 2        december_predictions[product] = last_2_months_avg
# Plot the December predictionsproduct_names, december_sales = zip(*december_predictions.items())
plt.figure(figsize=(10, 6))plt.bar(product_names, december_sales, color='mediumseagreen')plt.title('2023年12月各商品预测销售总额')plt.xlabel('商品名称')plt.ylabel('预测销售总额')plt.xticks(rotation=45, ha='right')plt.tight_layout()plt.show()