Oracle SQL查询求助：按日统计ITEM_NUMBER数量并获取当月及上月数据

Alright, let's fix and optimize your query to meet the requirement of daily distinct item counts for the current and previous month—including every date in those months, even if no items were created that day. Here's how to do it properly:

Option 1: Based on Current System Date (SYSDATE) #

This will generate stats for the previous month and current month relative to today's date. We first create a date range covering every day in those two months, then left join it with your item table to get counts (including 0 for dates with no items).

WITH date_range AS (
    -- Generate all dates from the first day of last month to the last day of this month
    SELECT 
        TRUNC(SYSDATE, 'MM') - INTERVAL '1' MONTH + LEVEL - 1 AS stat_date
    FROM dual
    CONNECT BY LEVEL <= (TRUNC(SYSDATE, 'MM') + INTERVAL '1' MONTH - 1) - (TRUNC(SYSDATE, 'MM') - INTERVAL '1' MONTH) + 1
)
SELECT 
    dr.stat_date,
    COUNT(DISTINCT e.item_number) AS daily_item_count
FROM date_range dr
LEFT JOIN EGP_SYSTEM_ITEMS_B e 
    ON TRUNC(e.creation_date) = dr.stat_date
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

Key Notes for This Option: #

• The date_range CTE uses Oracle's CONNECT BY to generate a continuous sequence of dates. No need for a separate date dimension table (though using one is better for very large datasets).
• TRUNC(e.creation_date) removes the time component from the creation date, ensuring we group by full calendar days.
• The LEFT JOIN ensures every date in our range appears in the result, even if there are no matching items (those will show 0 for daily_item_count).

Option 2: Based on the Latest Month in CREATION_DATE #

If you want the stats to be relative to the most recent month present in your CREATION_DATE field (instead of today's date), adjust the date range to use the max CREATION_DATE from your table:

WITH date_range AS (
    -- Get the latest month from CREATION_DATE, then generate dates for that month and the one before it
    SELECT 
        TRUNC(max_creation_month, 'MM') - INTERVAL '1' MONTH + LEVEL - 1 AS stat_date
    FROM (
        SELECT MAX(creation_date) AS max_creation_month 
        FROM EGP_SYSTEM_ITEMS_B
    )
    CONNECT BY LEVEL <= (TRUNC(max_creation_month, 'MM') + INTERVAL '1' MONTH - 1) - (TRUNC(max_creation_month, 'MM') - INTERVAL '1' MONTH) + 1
)
SELECT 
    dr.stat_date,
    COUNT(DISTINCT e.item_number) AS daily_item_count
FROM date_range dr
LEFT JOIN EGP_SYSTEM_ITEMS_B e 
    ON TRUNC(e.creation_date) = dr.stat_date
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

Optimization Tip #

If your EGP_SYSTEM_ITEMS_B table is large, the TRUNC(e.creation_date) condition might not use existing indexes. To speed up the query significantly, create a function-based index:

CREATE INDEX idx_egp_creation_date_trunc ON EGP_SYSTEM_ITEMS_B (TRUNC(creation_date));

This index will help Oracle quickly find all items created on a specific day without running full table scans.

Why Your Original Query Didn't Work #

Your original query select distinct count(item_number), creation_date from EGP_SYSTEM_ITEMS_B has two critical issues:

1. You're using an aggregate function (COUNT()) alongside a non-aggregated column (creation_date) without a GROUP BY clause—this will throw an error in standard SQL.
2. Even with GROUP BY creation_date, you'd only get dates where items were created, missing days with zero counts. The date range + left join approach fixes this gap by ensuring every date in the target months is included.

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