技术请求：按月份分箱日期，统计去重后的各模型月度告警数

Got it, let's tackle this problem step by step. You need to bin dates into Mon-YY format, count valid alerts per model per bin, and only count the first alert for a customer if multiple alerts trigger within a 7-day window. Below are two practical implementations using SQL (for database-side processing) and Python Pandas (for data analysis workflows).

We'll use window functions to track the time between alerts for each customer, filter out duplicate 7-day alerts, then aggregate by model and date bin.

Step-by-Step Explanation: #

1. Filter valid alerts: First, we keep only records where Score >= Threshold (matches your alert rule).
2. Calculate alert gaps: Use LAG() to get the previous alert date for each customer, then compute the days between alerts.
3. Format date bins: Convert my_dates to Mon-YY format with DATE_FORMAT().
4. Filter duplicate alerts: Keep only the first alert per customer, or alerts that are at least 7 days apart from the previous one.
5. Aggregate results: Count valid alerts per model and date bin, then sort for readability.

WITH ranked_alerts AS (
    SELECT 
        *,
        -- Calculate days since the customer's last alert
        DATEDIFF(my_dates, LAG(my_dates) OVER (PARTITION BY `Customer ID` ORDER BY my_dates)) AS days_since_last_alert,
        -- Format date to Mon-YY (e.g., Aug-17)
        DATE_FORMAT(my_dates, '%b-%y') AS date_bin
    FROM your_table_name
    WHERE Score >= Threshold -- Enforce alert rule
),
valid_alerts AS (
    SELECT *
    FROM ranked_alerts
    -- Keep first alert (no prior alert) or alerts spaced >=7 days apart
    WHERE days_since_last_alert IS NULL OR days_since_last_alert >= 7
)
SELECT 
    Model_name,
    date_bin,
    COUNT(*) AS alert_count
FROM valid_alerts
GROUP BY Model_name, date_bin
ORDER BY Model_name, STR_TO_DATE(date_bin, '%b-%y'); -- Sort dates correctly

Note for PostgreSQL Users:

Replace DATE_FORMAT() with TO_CHAR(my_dates, 'Mon-YY') and DATEDIFF() with (my_dates - LAG(my_dates) OVER (...))::int to get day differences.

This is ideal if you're working with data in a dataframe for analysis or reporting.

Step-by-Step Explanation: #

1. Load and clean data: Convert the my_dates column to datetime format for date calculations.
2. Track alert gaps: Sort records by customer and date, then compute the days between consecutive alerts per customer.
3. Filter valid alerts: Keep only the first alert per customer, or alerts that are at least 7 days apart from the previous one.
4. Create date bins: Format dates to Mon-YY style.
5. Aggregate and sort: Count valid alerts per model and bin, then sort the results.

import pandas as pd

# Load your data (replace with pd.read_csv() or your data source)
sample_data = {
    'Score': [50, 50, 50, 50, 50],
    'Customer ID': [8, 9, 28, 28, 36],
    'my_dates': ['2017-08-05', '2017-12-05', '2017-05-22', '2017-05-26', '2017-06-20'],
    'Threshold': [50, 50, 50, 50, 50],
    'Model_name': ['Mod1', 'Mod1', 'Mod2', 'Mod2', 'Mod2'],
    'is_alert': ['yes', 'yes', 'yes', 'yes', 'yes']
}
df = pd.DataFrame(sample_data)

# Convert date column to datetime
df['my_dates'] = pd.to_datetime(df['my_dates'])

# Sort by customer and date, calculate days since last alert per customer
df = df.sort_values(['Customer ID', 'my_dates'])
df['days_since_last_alert'] = df.groupby('Customer ID')['my_dates'].diff().dt.days

# Filter valid alerts: first alert or alerts >=7 days apart
valid_alerts = df[df['days_since_last_alert'].isna() | (df['days_since_last_alert'] >= 7)]

# Create date bins in Mon-YY format
valid_alerts['date_bin'] = valid_alerts['my_dates'].dt.strftime('%b-%y')

# Aggregate and sort results
final_result = valid_alerts.groupby(['Model_name', 'date_bin'])['Customer ID'].count().reset_index(name='alert_count')
final_result = final_result.sort_values(['Model_name', pd.to_datetime(final_result['date_bin'], format='%b-%y')])

# Print the result
print(final_result)

Sample Output: #

For your provided data, the output will look like this:

(Note: Customer 28's May 26 alert is excluded since it's only 4 days after their May 22 alert)

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