如何在SQL中基于列比较预入库船期与实际入库船期？

Hey there! Let's walk through exactly how to compare your preliminary inbound ship dates (prelim_inbound ship date) and actual inbound ship dates (inbound ship date), count each scenario's frequency, and determine if manufacturers are shipping early, on time, or late. I'll cover the most common tools you might be using—pick the one that fits your workflow:

Before diving into code/formulas, let's map the comparisons to shipping statuses clearly:

• prelim_inbound ship date > inbound ship date: Manufacturer shipped early (actual date is earlier than planned)
• prelim_inbound ship date = inbound ship date: Manufacturer shipped on time
• prelim_inbound ship date < inbound ship date: Manufacturer shipped late (actual date is later than planned)

1. Using Excel #

This is straightforward with built-in formulas and pivot tables:

• First, confirm both date columns are formatted as Date (not text!)—this prevents weird comparison errors.
• Add an auxiliary column (e.g., named Shipment Status) with this nested IF formula (adjust column letters to match your data):

  =IF(A2>B2,"提前发货",IF(A2=B2,"准时发货","延迟发货"))
  

  Note: A = prelim date column, B = actual date column
• To count frequencies, use COUNTIF for each status:

  =COUNTIF(C:C,"提前发货") // Counts early shipments
  =COUNTIF(C:C,"准时发货") // Counts on-time shipments
  =COUNTIF(C:C,"延迟发货") // Counts late shipments
  

• For a more visual breakdown, use a Pivot Table: Drag the Shipment Status column to both the "Rows" and "Values" areas—Excel will auto-calculate the counts for you.

2. Using SQL #

If your data lives in a database, use a CASE statement to categorize statuses, then group to count frequencies:

SELECT
  CASE
    WHEN prelim_ship_date > actual_ship_date THEN '提前发货'
    WHEN prelim_ship_date = actual_ship_date THEN '准时发货'
    ELSE '延迟发货'
  END AS shipment_status,
  COUNT(*) AS frequency
FROM shipments -- Replace with your table name
GROUP BY shipment_status;

Pro tip: If your date fields include time stamps, use a date-truncation function (like DATE(prelim_ship_date) in MySQL or CAST(prelim_ship_date AS DATE) in SQL Server) to ignore time differences when comparing dates.

3. Using Python (Pandas) #

For data analysis workflows, Pandas makes this quick and scalable:
First, make sure your date columns are parsed as datetime objects:

import pandas as pd
import numpy as np

# Load your data (adjust path/input method as needed)
df = pd.read_csv("your_shipment_data.csv")

# Convert columns to datetime
df['prelim_inbound_ship_date'] = pd.to_datetime(df['prelim_inbound_ship_date'])
df['inbound_ship_date'] = pd.to_datetime(df['inbound_ship_date'])

Then add a status column using np.select (cleaner than nested IFs):

df['shipment_status'] = np.select(
    [
        df['prelim_inbound_ship_date'] > df['inbound_ship_date'],
        df['prelim_inbound_ship_date'] == df['inbound_ship_date']
    ],
    [
        '提前发货',
        '准时发货'
    ],
    default='延迟发货'
)

Finally, count the frequencies with value_counts():

status_counts = df['shipment_status'].value_counts()
print(status_counts)
# Optional: Plot the results for visualization
status_counts.plot(kind='bar', title='Manufacturer Shipment Status Frequency')

Quick Notes to Avoid Mistakes #

• Always double-check that your date columns are in a consistent format (no text mixed with dates!).
• If dealing with time zones, normalize all dates to the same timezone before comparing—otherwise, you might get incorrect status labels.

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