sql - 标记表每个分区中前 90% 的行

Question

我有一张表格，如下所示。我想派生一列 Flag ，以便每个分区的前 90% 的行将具有 TypeA ，其余 10%行中将有 TypeB 作为其中的标志。

+------+----+
| 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 |
+------+----+

期望的结果

+------+----+-------+
| 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 |
+------+----+-------+

任何帮助将不胜感激。

SQL Fiddle

Answer 1

一种方法是进行显式计数:

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