计算物品借出时长：统计借出次数、单次时长及平均借出天数

Hey there! Let's tackle your problem step by step. You need to calculate average loan days for items, count how many times each itemrecord was loaned, and track the duration of every individual loan. Here's a practical SQL solution tailored to your table structure:

Step 1: Pair Loan and Return Transactions #

First, we need to match each "check-out" transaction with its corresponding "check-in" transaction. I'll assume transtype = 6001 is your loan code—replace this with your actual check-out type if it's different. We'll use a CTE to cleanly link these pairs:

WITH loan_pairs AS (
    SELECT
        l.itemrecord,
        l.transdate AS loan_date,
        r.transdate AS return_date,
        -- Calculate days between loan and return (adjust function for your DB if needed)
        DATEDIFF(day, l.transdate, r.transdate) AS loan_days
    FROM project l
    -- Join to find the matching return for each loan
    JOIN project r
        ON l.itemrecord = r.itemrecord
        AND l.transtype = 6001  -- Replace with your actual check-out transtype
        AND r.transtype = 6002  -- Replace with your actual check-in transtype
        -- Ensure we match each loan to the earliest possible return (avoids duplicate pairs)
        AND r.transnum = (
            SELECT MIN(transnum)
            FROM project
            WHERE itemrecord = l.itemrecord
                AND transtype = 6002
                AND transdate > l.transdate
        )
)

Step 2: Calculate Your Required Stats #

Now we can aggregate the paired data to get the counts, individual loan durations, and averages you need:

-- Stats per itemrecord
SELECT
    itemrecord,
    COUNT(*) AS total_loan_count,
    AVG(loan_days) AS avg_loan_days_per_item,
    -- List all individual loan days (format depends on your database)
    STRING_AGG(CAST(loan_days AS VARCHAR), ', ') AS individual_loan_durations
FROM loan_pairs
GROUP BY itemrecord

-- Add overall average across all items
UNION ALL
SELECT
    'OVERALL_AVERAGE' AS itemrecord,
    COUNT(*) AS total_loan_count,
    AVG(loan_days) AS avg_loan_days_per_item,
    NULL AS individual_loan_durations
FROM loan_pairs;

Quick Adjustments for Your Environment #

• Transtype Codes: Double-check and swap 6001/6002 with your actual check-out/check-in transaction types.
• Unreturned Items: If you want to include items that haven't been returned yet, switch to a LEFT JOIN instead of JOIN, and calculate loan_days as DATEDIFF(day, loan_date, GETDATE()) (SQL Server) or the equivalent for your database to get elapsed days since loan.
• Database-Specific Functions: STRING_AGG works for SQL Server 2017+. For MySQL, use GROUP_CONCAT; for PostgreSQL, use STRING_AGG(loan_days::text, ', ').

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