基于分组参数筛选最大值：SQLite3事故数据年月维度最高事故星期查询

Alright, let's break down how to solve this problem—getting the day of the week with the most accidents per year-month combination and storing that in a new max_dayofweek table using Python and SQLite3.

Step 1: Craft the SQLite Query #

First, we need to calculate accident counts per year/month/day-of-week, then pick the day(s) with the highest count for each year-month pair. We'll use common table expressions (CTEs) and window functions to make this clean:

WITH accident_counts AS (
    -- First, count accidents for every year-month-day combination
    SELECT 
        Year,
        Month,
        Day_of_Week,
        COUNT(Accident_Index) AS Num_of_Accident
    FROM Accidents
    GROUP BY Year, Month, Day_of_Week
),
ranked_counts AS (
    -- Rank each day in the year-month group by accident count (descending)
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Year, Month ORDER BY Num_of_Accident DESC) AS rn
    FROM accident_counts
)
-- Create the max_dayofweek table and populate it with top-ranked days
SELECT Year, Month, Day_of_Week, Num_of_Accident
INTO max_dayofweek
FROM ranked_counts
WHERE rn = 1
ORDER BY Year ASC, Month ASC;

A quick note on edge cases: If multiple days tie for the most accidents in a month, ROW_NUMBER() will only pick one of them. If you want to keep all tied days, swap ROW_NUMBER() with RANK() instead.

Step 2: Execute the Query with Python #

Now let's wrap this SQL in Python code to run it against your SQLite database:

import sqlite3

# Connect to your SQLite database (replace 'accidents.db' with your actual DB file)
conn = sqlite3.connect('accidents.db')
cursor = conn.cursor()

# Define the SQL query we wrote earlier
create_max_table_query = """
WITH accident_counts AS (
    SELECT 
        Year,
        Month,
        Day_of_Week,
        COUNT(Accident_Index) AS Num_of_Accident
    FROM Accidents
    GROUP BY Year, Month, Day_of_Week
),
ranked_counts AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Year, Month ORDER BY Num_of_Accident DESC) AS rn
    FROM accident_counts
)
SELECT Year, Month, Day_of_Week, Num_of_Accident
INTO max_dayofweek
FROM ranked_counts
WHERE rn = 1
ORDER BY Year ASC, Month ASC;
"""

try:
    # Run the query and save changes
    cursor.execute(create_max_table_query)
    conn.commit()
    print("Success! The max_dayofweek table has been created and populated.")
except sqlite3.Error as e:
    print(f"Oops, hit an error running the SQL: {e}")
finally:
    # Always close the database connection when done
    conn.close()

Quick Checks to Ensure It Works #

• Double-check that your Accidents table has the exact fields: Accident_Index, Year, Month, Day_of_Week (with Day_of_Week values 1-7, where 1 = Sunday).
• If you're using an in-memory database, replace 'accidents.db' with ':memory:' in the connection line.

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