请求编写Oracle SQL查询：计算日交易占月总交易百分比

Got it, let's break down your requirement and build the right query for you. First, I'll assume you have a transaction table (let's call it transactions) with the core fields you mentioned: transaction_date, transaction_type, product, plus any other transaction details you need to display.

I'll cover two common scenarios based on your needs:

1. Grouped by Date, Transaction Type, and Product #

If you want to see daily counts per transaction type and product, along with how that daily count compares to the monthly total for that same type/product combination, use this query:

SELECT
    TRUNC(t.transaction_date) AS transaction_day,
    t.transaction_type,
    t.product,
    COUNT(*) AS daily_transaction_count,
    SUM(COUNT(*)) OVER (
        PARTITION BY 
            TRUNC(t.transaction_date, 'MONTH'), 
            t.transaction_type, 
            t.product
    ) AS monthly_transaction_count,
    ROUND(
        (COUNT(*) / SUM(COUNT(*)) OVER (
            PARTITION BY 
                TRUNC(t.transaction_date, 'MONTH'), 
                t.transaction_type, 
                t.product
        )) * 100, 
        2
    ) AS percentage_of_monthly_total
FROM
    transactions t
GROUP BY
    TRUNC(t.transaction_date),
    t.transaction_type,
    t.product
ORDER BY
    transaction_day,
    transaction_type,
    product;

How this works: #

• TRUNC(t.transaction_date): Truncates the timestamp to just the date (so all transactions on the same day are grouped together).
• COUNT(*): Counts the number of transactions for that day, type, and product.
• The window function SUM(COUNT(*)) OVER (...): Calculates the total number of transactions for the same month, transaction type, and product across all days in the month. This avoids needing separate subqueries or joins to get the monthly total.
• ROUND(..., 2): Formats the percentage to 2 decimal places for readability (adjust the number if you need more/less precision).

2. Detailed Transaction View with Daily/Monthly Totals #

If you want to display every individual transaction along with the daily total transactions and monthly total transactions (and the percentage), use this approach:

SELECT
    t.transaction_date,
    t.transaction_type,
    t.product,
    t.amount, -- Add any other transaction fields you need here
    daily.daily_count,
    monthly.monthly_count,
    ROUND(
        (daily.daily_count / monthly.monthly_count) * 100, 
        2
    ) AS percentage_of_monthly_total
FROM
    transactions t
-- Join to get daily transaction counts
JOIN (
    SELECT
        TRUNC(transaction_date) AS transaction_day,
        COUNT(*) AS daily_count,
        TRUNC(transaction_date, 'MONTH') AS transaction_month
    FROM
        transactions
    GROUP BY
        TRUNC(transaction_date),
        TRUNC(transaction_date, 'MONTH')
) daily ON TRUNC(t.transaction_date) = daily.transaction_day
-- Join to get monthly transaction counts
JOIN (
    SELECT
        TRUNC(transaction_date, 'MONTH') AS transaction_month,
        COUNT(*) AS monthly_count
    FROM
        transactions
    GROUP BY
        TRUNC(transaction_date, 'MONTH')
) monthly ON daily.transaction_month = monthly.transaction_month
ORDER BY
    t.transaction_date;

Key Notes: #

• Performance: If your table is large, make sure you have an index on transaction_date—this will speed up the TRUNC operations and joins.
• Year Handling: TRUNC(transaction_date, 'MONTH') includes the year, so January 2023 and January 2024 will be treated as separate months (no overlap).
• Precision: Adjust the ROUND function's second parameter if you want whole numbers (use 0) or more decimal places.

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