[SQL] PostgreSQL의 그룹화 LIMIT : 각 그룹의 첫 번째 N 행을 보여?

PostgreSQL의 그룹화 LIMIT : 각 그룹의 첫 번째 N 행을 보여?

나는 사용자 정의 열을 기준으로 정렬 된 각 그룹의 첫 번째 N 행을해야합니다.

주어진 다음의 표 :

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I 유사한 결과 즉 각 SECTION_ID 대해 (이름 순서)는 제 2 개 행을 필요

 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

나는 PostgreSQL을 8.3.5을 사용하고 있습니다.

해결법

1. ==============================

   1.새로운 솔루션 (PostgreSQL의 8.4)

   새로운 솔루션 (PostgreSQL의 8.4)

   SELECT
     * 
   FROM (
     SELECT
       ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS r,
       t.*
     FROM
       xxx t) x
   WHERE
     x.r <= 2;
   

2. ==============================

   2.v9.3 때문에 당신은 가입 횡 방향 할 수

   v9.3 때문에 당신은 가입 횡 방향 할 수

   select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
   join lateral (
       select * from t t_inner
       where t_inner.section_id = t_outer.section_id
       order by t_inner.name
       limit 2
   ) t_top on true
   order by t_outer.section_id;
   

   그것은 빠를 수 있지만, 물론, 당신은 당신의 데이터와 사용 사례에 특히 성능을 테스트해야합니다.

3. ==============================

   3.여기에 또 다른 솔루션 (PostgreSQL을 <= 8.3)이다.

   여기에 또 다른 솔루션 (PostgreSQL을 <= 8.3)이다.

   SELECT
     *
   FROM
     xxx a
   WHERE (
     SELECT
       COUNT(*)
     FROM
       xxx
     WHERE
       section_id = a.section_id
     AND
       name <= a.name
   ) <= 2
   

4. ==============================

   4.

   SELECT  x.*
   FROM    (
           SELECT  section_id,
                   COALESCE
                   (
                   (
                   SELECT  xi
                   FROM    xxx xi
                   WHERE   xi.section_id = xo.section_id
                   ORDER BY
                           name, id
                   OFFSET 1 LIMIT 1
                   ),
                   (
                   SELECT  xi
                   FROM    xxx xi
                   WHERE   xi.section_id = xo.section_id
                   ORDER BY 
                           name DESC, id DESC
                   LIMIT 1
                   )
                   ) AS mlast
           FROM    (
                   SELECT  DISTINCT section_id
                   FROM    xxx
                   ) xo
           ) xoo
   JOIN    xxx x
   ON      x.section_id = xoo.section_id
           AND (x.name, x.id) <= ((mlast).name, (mlast).id)
   

5. ==============================

   5.

           -- ranking without WINDOW functions
   -- EXPLAIN ANALYZE
   WITH rnk AS (
           SELECT x1.id
           , COUNT(x2.id) AS rnk
           FROM xxx x1
           LEFT JOIN xxx x2 ON x1.section_id = x2.section_id AND x2.name <= x1.name
           GROUP BY x1.id
           )
   SELECT this.*
   FROM xxx this
   JOIN rnk ON rnk.id = this.id
   WHERE rnk.rnk <=2
   ORDER BY this.section_id, rnk.rnk
           ;
   
           -- The same without using a CTE
   -- EXPLAIN ANALYZE
   SELECT this.*
   FROM xxx this
   JOIN ( SELECT x1.id
           , COUNT(x2.id) AS rnk
           FROM xxx x1
           LEFT JOIN xxx x2 ON x1.section_id = x2.section_id AND x2.name <= x1.name
           GROUP BY x1.id
           ) rnk
   ON rnk.id = this.id
   WHERE rnk.rnk <=2
   ORDER BY this.section_id, rnk.rnk
           ;
   

from https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group by cc-by-sa and MIT license