适配实际订单表的SQL月度产品销量查询改写需求

Let's refactor your SQL and Python code to work seamlessly with your actual long-format orders table. The long structure makes this query way simpler—no more messy UNION ALL to pivot columns into rows!

Key Changes Explained: #

• Your original query was built for a wide table where each product was its own column. The actual orders table stores products in a single product column, so we can directly filter and aggregate without pivoting.
• We’ll sum the quantity column (since each row represents an order line item) instead of summing product-specific columns.
• We’ll retain the milk option filter and monthly grouping logic you had before.

Modified SQL Query #

SELECT 
    SUM(quantity) AS total_sales,
    strftime("%m-%Y", orderDate) AS 'month-year'
FROM orders  -- Replace with your actual table name if different
WHERE 
    product = ? 
    AND milkOptions = ?
GROUP BY strftime("%m-%Y", orderDate)
ORDER BY strftime("%Y-%m-%d", orderDate)  -- Optional: sorts months chronologically

Updated Python Code #

import sqlite3

# Get user inputs
product_name = input("Enter product to search (e.g., Espresso): ")
milk_option = input("Enter milk option (e.g., Soya): ")

# Refactored SQL query
sql = """
SELECT 
    SUM(quantity) AS total_sales,
    strftime("%m-%Y", orderDate) AS 'month-year'
FROM orders
WHERE 
    product = ? 
    AND milkOptions = ?
GROUP BY strftime("%m-%Y", orderDate)
ORDER BY strftime("%Y-%m-%d", orderDate)
"""

# Connect to database and execute query
conn = sqlite3.connect("system.db")
cur = conn.cursor()

# Pass both user inputs as parameters (prevents SQL injection!)
monthly_sales = cur.execute(sql, (product_name, milk_option)).fetchall()

# Print formatted results
print(f"\nMonthly sales for *{product_name}* (milk: {milk_option}):")
for sales, month_year in monthly_sales:
    print(f"- {month_year}: {sales} units")

# Clean up database connection
conn.close()

Quick Notes: #

• Table Name: Ensure orders matches the actual name of your long-format order table.
• Security: We’re using parameterized queries (? placeholders) instead of string concatenation to avoid SQL injection risks.
• Sorting: The ORDER BY clause ensures months display in chronological order (not alphabetical, which would misplace "01-2020" after "12-2019").
• Error Handling: For production use, you could add try/except blocks to handle database connection issues or invalid user inputs, but this keeps the core logic clean for your testing needs.

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