Flag top 90% of rows in each partition of table

user_10535439 0
Viewed 70

I have a table as below. I want to derive a column Flag such that approximately Top 90% of rows for each partition will have TypeA and remaining 10% of rows will have TypeB as a flag in it.

+------+----+
| City | id |
+------+----+
| A    | 1A |
| A    | 2A |
| A    | 3A |
| A    | 4A |
| A    | 5A |
| B    | 1B |
| B    | 2B |
| B    | 3B |
| B    | 4B |
| B    | 5B |
| B    | 6B |
| D    | 1D |
| D    | 2D |
| D    | 3D |
| D    | 4D |
| D    | 5D |
| D    | 6D |
| D    | 7D |
| D    | 8D |
+------+----+

Desired Result

+------+----+-------+
| City | id | Flag  |
+------+----+-------+
| A    | 1A | TypeA |
| A    | 2A | TypeA |
| A    | 3A | TypeA |
| A    | 4A | TypeA | // Approximately Top 90% of rows for City A: Flag Type A
| A    | 5A | TypeB | // Approximately below 10% of rows for City A: Flag Type B
| B    | 1B | TypeA |
| B    | 2B | TypeA |
| B    | 3B | TypeA |
| B    | 4B | TypeA |// Approximately Top 90% of rows for City B: Flag Type A
| B    | 5B | TypeB |// Approximately below 10% of rows for City B: Flag Type B
| B    | 6B | TypeB |
| D    | 1D | TypeA |
| D    | 2D | TypeA |
| D    | 3D | TypeA |
| D    | 4D | TypeA |
| D    | 5D | TypeA |
| D    | 6D | TypeA |
| D    | 7D | TypeA |
| D    | 8D | TypeB |
+------+----+-------+

Any help will be really appreciated.

SQL Fiddle

sql-server sql
3 Answers

One method is to do explicit counting:

select t.*,
       (case when row_number() over (partition by city order by id) <=
                  0.9 * count(*) over (partition by city)
             then 'TypeA'
             else 'TypeB'
        end) as flag
from t
user_11440350

Here is one option, using COUNT as an analytic function:

SELECT
    City,
    id,
    CASE WHEN COUNT(*) OVER (PARTITION BY City ORDER BY id) /
        COUNT(*) OVER (PARTITION BY City) <= 0.9
         THEN 'TypeA'
         ELSE 'TypeB' END AS Flag
FROM yourTable
ORDER BY
    City,
    Id;

enter image description here

Demo

The first call to COUNT computes the number of elements in each city partition, up to the current row, as ordered by the Id. Then, we normalize that by the total number of records for each city, and compare this to 0.9 to decide which flag to assign.

user_18632290

SQL Server has a percent_rank() window function for calculating the number you want directly, without needing to do it yourself:

SELECT City, id
     , CASE
        WHEN percent_rank() OVER (PARTITION BY City ORDER BY id) <= 0.9 THEN 'TypeA'
        ELSE 'TypeB'
       END AS Flag
FROM table1
ORDER BY City, id;

Fiddle.

user_99521960
Related