如何在Partition查询中判断列值一致性并筛选指定数据

Let's break down your requirements and implement them using SQL window functions—this is exactly the right tool for partition-based grouping and filtering like this.

First, let's recap your data and needs clearly:

• We need to group rows by category and id
• Filter two sets of rows based on indicator values within each group
• Ensure we retain the correct b_name for every matching row

Step 1: Calculate Group-Level Statistics #

First, we'll use a CTE (Common Table Expression) to compute key stats for each group. This makes it easy to quickly identify if indicator values are all the same, mixed, all 'Y', or all 'N'.

WITH group_indicator_stats AS (
    SELECT
        b_name,
        category,
        amount,
        id,
        indicator,
        -- Count distinct indicators (1 = all same, 2 = mixed values)
        COUNT(DISTINCT indicator) OVER (PARTITION BY category, id) AS distinct_ind_count,
        -- Label the group type based on indicator values
        CASE 
            WHEN MAX(indicator) OVER (PARTITION BY category, id) = 'Y' 
                 AND MIN(indicator) OVER (PARTITION BY category, id) = 'Y' 
                 THEN 'ALL_Y'
            WHEN MAX(indicator) OVER (PARTITION BY category, id) = 'N' 
                 AND MIN(indicator) OVER (PARTITION BY category, id) = 'N' 
                 THEN 'ALL_N'
            ELSE 'MIXED' 
        END AS ind_group_type
    FROM your_table_name -- Replace with your actual table name
)

Step 2: Fetch First Result Set (Mixed or All 'N' Groups) #

Your first requirement is to get rows from groups where indicator values are either mixed or all 'N'. Using our precomputed CTE, this is straightforward:

-- First Result Set: Mixed indicator groups + All 'N' groups
SELECT b_name, category, amount, id
FROM group_indicator_stats
WHERE ind_group_type IN ('MIXED', 'ALL_N')
ORDER BY id, category, b_name;

This returns exactly the output you provided:

+---------+----------+--------+-------+
|b_name   | category | amount | id    |
+---------+----------+--------+-------+
|BUCKET1  | CAT1     | 211.8  | 21006 |
|BUCKET2  | CAT1     | 275.88 | 21006 |
|BUCKET1  | CAT3     | 180.6  | 21011 |
|BUCKET2  | CAT3     | 180.36 | 21011 |
+---------+----------+--------+-------+

Step 3: Fetch Second Result Set (Fixing the Typo in Your Example) #

You noted your second result example has a typo—since the example shows a row from CAT2 (which is all 'Y'), but your description mentions "mixed indicators or all 'N' indicators", I'll cover both the intended logical options:

Option A: Mixed + All 'Y' Groups

If your actual need is mixed groups plus all 'Y' groups (likely what the typo example was aiming for):

-- Second Result Set Option A: Mixed indicator groups + All 'Y' groups
SELECT b_name, category, amount, id
FROM group_indicator_stats
WHERE ind_group_type IN ('MIXED', 'ALL_Y')
ORDER BY id, category, b_name;

This returns:

+---------+----------+--------+-------+
|b_name   | category | amount | id    |
+---------+----------+--------+-------+
|BUCKET1  | CAT1     | 211.8  | 21006 |
|BUCKET2  | CAT1     | 275.88 | 21006 |
|BUCKET1  | CAT2     | 265.4  | 21008 |
|BUCKET2  | CAT2     | 289.8  | 21008 |
+---------+----------+--------+-------+

Option B: Only All 'Y' Groups

If you just need rows from all-'Y' groups:

-- Second Result Set Option B: Only All 'Y' groups
SELECT b_name, category, amount, id
FROM group_indicator_stats
WHERE ind_group_type = 'ALL_Y'
ORDER BY id, category, b_name;

This returns:

+---------+----------+--------+-------+
|b_name   | category | amount | id    |
+---------+----------+--------+-------+
|BUCKET1  | CAT2     | 265.4  | 21008 |
|BUCKET2  | CAT2     | 289.8  | 21008 |
+---------+----------+--------+-------+

Key Details #

• PARTITION BY category, id: Groups rows by your specified columns so we analyze each group independently.
• COUNT(DISTINCT indicator): Instantly tells us if the group has mixed values (count=2) or all identical values (count=1).
• MAX(indicator) + MIN(indicator): Since 'Y' and 'N' are string values, comparing max and min lets us confirm if all values in the group are the same, and what that value is.

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