基于Oracle使用PARTITION BY实现值分组的SQL查询咨询

Hey there! Let's break down how to use Oracle's PARTITION BY clause to query your testd table effectively. First, a quick note: your last INSERT statement is cut off, but I'll work with the valid sample data you provided.

What is PARTITION BY? #

PARTITION BY is part of Oracle's analytical functions—it splits your dataset into logical groups (partitions) based on a column (or columns), then lets you perform calculations within each group while keeping all individual rows in your result set. This is key difference from GROUP BY, which collapses each group into a single row.

Example 1: Add Group-Level Metrics to Each Row #

If you want to see every record alongside details about its group (like how many total entries exist for that Source_id), use an aggregate function with OVER (PARTITION BY ...):

SELECT
    Source_id,
    job_status,
    -- Count total records per Source_id
    COUNT(*) OVER (PARTITION BY Source_id) AS total_records,
    -- Get the last job_status in the group (ordered alphabetically)
    LAST_VALUE(job_status) OVER (PARTITION BY Source_id ORDER BY job_status ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_status
FROM testd;

Result:
Each row will show its Source_id, job_status, the total number of rows for that Source_id, and the alphabetically last status in the group. For ABC, every row will have total_records = 3.

Example 2: Number Rows Within Each Group #

Use ROW_NUMBER() (or RANK(), DENSE_RANK()) to assign a unique number to each row within its Source_id partition. This is handy for prioritizing entries:

SELECT
    Source_id,
    job_status,
    ROW_NUMBER() OVER (PARTITION BY Source_id ORDER BY job_status DESC) AS status_priority
FROM testd;

Result:
For ABC, the finished status will get status_priority = 1, failed = 2, canceled = 3 (since we ordered descending). You could use this to filter only the highest-priority status per source, for example.

Example 3: Aggregate Statuses into a Single String #

If you want to combine all job_status values for each Source_id into a comma-separated list (while leveraging PARTITION BY), use LISTAGG as an analytical function:

SELECT DISTINCT
    Source_id,
    LISTAGG(job_status, ', ') WITHIN GROUP (ORDER BY job_status) 
        OVER (PARTITION BY Source_id) AS all_statuses
FROM testd;

Result:
You'll get one row per Source_id, with a column like all_statuses = 'canceled, failed, finished' for ABC. The DISTINCT ensures we don't duplicate rows for each status entry.

Quick Recap: PARTITION BY vs GROUP BY #

• GROUP BY: Reduces each group to one row (you lose individual record details)
• PARTITION BY: Keeps all individual rows, adding group-level calculations as extra columns

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