ID의 순서를 유지하면서 하위 카테고리의 순위 지정

ID의 순서를 유지하면서 하위 카테고리의 순위 지정

다음 테이블이 있습니다.

+----+------+------+
| id | cat1 | cat2 |
+----+------+------+
|  1 | A    | foo  |
|  2 | A    | foo  |
|  3 | A    | bar  |
|  4 | B    | sci  |
|  5 | B    | ble  |
|  6 | B    | ble  |
+----+------+------+

하위 카테고리(cat2)의 순위를 매기고 싶습니다.

원하는 결과:

+----+------+------+------+
| id | cat1 | cat2 | res  |
+----+------+------+------+
|  1 | A    | foo  |    1 |
|  2 | A    | foo  |    1 |
|  3 | A    | bar  |    2 |
|  4 | B    | sci  |    1 |
|  5 | B    | ble  |    2 |
|  6 | B    | ble  |    2 |
+----+------+------+------+

DENSE_RANK를 PARTITION BY와 함께 사용하여 다음과 같은 결과를 얻습니다.

+----+------+------+------+
| id | cat1 | cat2 | res  |
+----+------+------+------+
|  1 | A    | foo  |    2 |
|  2 | A    | foo  |    2 |
|  3 | A    | bar  |    1 |
|  4 | B    | sci  |    2 |
|  5 | B    | ble  |    1 |
|  6 | B    | ble  |    1 |
+----+------+------+------+

문:

SELECT DENSE_RANK() OVER (PARTITION BY cat1 ORDER BY cat2 asc) as res, t.* 
  FROM mytable t 
 ORDER BY id;

보시다시피, 제가 유일하게 놓치고 있는 것은 결과의 순서입니다.현재 순위는 cat2의 알파벳 순서를 기준으로 합니다.하지만 id의 순서를 유지하고 싶습니다(원하는 결과 참조).단순히 내 DENSE_RANK의 ORDER BY를 변경하는 것만으로는 효과가 없습니다.

WITH cte AS ( SELECT MIN(id) id, cat1, cat2
              FROM test
              GROUP BY cat1, cat2 )
SELECT t1.id,
       t1.cat1,
       t1.cat2,
       DENSE_RANK() OVER (PARTITION BY t1.cat1 ORDER BY t2.id asc) as res
FROM test t1 
JOIN cte t2 USING (cat1, cat2)
ORDER BY t1.id;

만지작거리다

물론 CTE는 하위 쿼리로 변환될 수 있습니다.

사용할 수 있습니다.

LAG() OVER (PARTITION BY .. ORDER BY ..)그리고.SUM() OVER (PARTITION BY .. ORDER BY ..)

분석 함수 조합:

WITH t2 AS
(
SELECT LAG(cat2,1) OVER (PARTITION BY cat1 ORDER BY id) lg,
       t.* 
  FROM mytable t 
 ORDER BY id
)
SELECT id, cat1, cat2,
       SUM(CASE WHEN lg=cat2 THEN 0 ELSE 1 END) OVER (PARTITION BY cat1 ORDER BY id) as res
  FROM t2

Demo

언급URL : https://stackoverflow.com/questions/59480152/ranking-subcategories-while-keeping-order-of-id