복붙노트

[SQL] GROUP BY에서 LIMIT를 사용하면 그룹 별 N 결과를 얻으려면?

SQL

GROUP BY에서 LIMIT를 사용하면 그룹 별 N 결과를 얻으려면?

다음 쿼리 :

SELECT
year, id, rate
FROM h
WHERE year BETWEEN 2000 AND 2009
AND id IN (SELECT rid FROM table2)
GROUP BY id, year
ORDER BY id, rate DESC

수율 :

year    id  rate
2006    p01 8
2003    p01 7.4
2008    p01 6.8
2001    p01 5.9
2007    p01 5.3
2009    p01 4.4
2002    p01 3.9
2004    p01 3.5
2005    p01 2.1
2000    p01 0.8
2001    p02 12.5
2004    p02 12.4
2002    p02 12.2
2003    p02 10.3
2000    p02 8.7
2006    p02 4.6
2007    p02 3.3

내가 원하는 것은 각 ID 만 상위 5 개 결과입니다 :

2006    p01 8
2003    p01 7.4
2008    p01 6.8
2001    p01 5.9
2007    p01 5.3
2001    p02 12.5
2004    p02 12.4
2002    p02 12.2
2003    p02 10.3
2000    p02 8.7

수정처럼 LIMIT의 어떤 종류를 사용하여이 할 수있는 방법이 있나요 그 GROUP BY 내 작품?

해결법

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

    1.당신은 하나의 열, 속도에 의해 ID별로 그룹화 및 정렬에 모든 년을 얻을 GROUP_CONCAT 집계 기능을 사용할 수 있습니다 :

    당신은 하나의 열, 속도에 의해 ID별로 그룹화 및 정렬에 모든 년을 얻을 GROUP_CONCAT 집계 기능을 사용할 수 있습니다 :

    SELECT   id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
FROM     yourtable
GROUP BY id

    결과:

    -----------------------------------------------------------
|  ID | GROUPED_YEAR                                      |
-----------------------------------------------------------
| p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 |
| p02 | 2001,2004,2002,2003,2000,2006,2007                |
-----------------------------------------------------------

    그리고 당신은 예를 들어, 두 번째, 내부의 첫 번째 인수의 위치를 ​​반환 FIND_IN_SET을 사용할 수 있습니다.

    SELECT FIND_IN_SET('2006', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
1

SELECT FIND_IN_SET('2009', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
6

    GROUP_CONCAT 및 FIND_IN_SET의 조합을 사용하고, FIND_IN_SET에 의해 반환 된 위치에 의해 필터링하는 것은, 당신은이 쿼리를 사용할 수있는 모든 ID에 대한 반환 첫 번째 오년 :

    SELECT
  yourtable.*
FROM
  yourtable INNER JOIN (
    SELECT
      id,
      GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
    FROM
      yourtable
    GROUP BY id) group_max
  ON yourtable.id = group_max.id
     AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5
ORDER BY
  yourtable.id, yourtable.year DESC;

    여기 바이올린 참조하십시오.

    둘 이상의 행이 동일한 속도를 가질 수 있다면, 당신은 속도 열 대신 년 열 (속도에 의해 DISTINCT 속도 ORDER) GROUP_CONCAT을 사용하는 것이 좋습니다 있습니다.

    당신이 모든 그룹에 대한 몇 가지 레코드를 선택해야하는 경우이 잘 작동되도록 GROUP_CONCAT에 의해 반환되는 문자열의 최대 길이는 제한됩니다.

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

    2.원래 쿼리는 파생 테이블에 사용자 변수 및 ORDER BY를 사용; 모두 단점의 동작은 보장되지 않습니다. 개정 대답은 다음과 같다.

    원래 쿼리는 파생 테이블에 사용자 변수 및 ORDER BY를 사용; 모두 단점의 동작은 보장되지 않습니다. 개정 대답은 다음과 같다.

    MySQL은 5.x를 당신이 원하는 결과를 달성하기 위해 파티션을 통해 가난한 사람의 순위를 사용할 수 있습니다. 단지 외부는 자체 테이블에 가입하고 각 행에 대해, 그것보다 작은 행의 수를 계산합니다. 위의 경우, 적은 행은 더 빠른 속도로 하나입니다 :

    SELECT t.id, t.rate, t.year, COUNT(l.rate) AS rank
FROM t
LEFT JOIN t AS l ON t.id = l.id AND t.rate < l.rate
GROUP BY t.id, t.rate, t.year
HAVING COUNT(l.rate) < 5
ORDER BY t.id, t.rate DESC, t.year

    데모 및 결과 :

    | id  | rate | year | rank |
|-----|------|------|------|
| p01 |  8.0 | 2006 | 0    |
| p01 |  7.4 | 2003 | 1    |
| p01 |  6.8 | 2008 | 2    |
| p01 |  5.9 | 2001 | 3    |
| p01 |  5.3 | 2007 | 4    |
| p02 | 12.5 | 2001 | 0    |
| p02 | 12.4 | 2004 | 1    |
| p02 | 12.2 | 2002 | 2    |
| p02 | 10.3 | 2003 | 3    |
| p02 |  8.7 | 2000 | 4    |

    참고 그 속도가 예를 들어 관계를 가지고있는 경우 :

    100, 90, 90, 80, 80, 80, 70, 60, 50, 40, ...

    위의 쿼리는 6 개 행을 반환합니다 :

    100, 90, 90, 80, 80, 80

    COUNT (DISTINCT l.rate) <5 HAVING로 변경 8 개 행을 얻을 수 있습니다 :

    100, 90, 90, 80, 80, 80, 70, 60

    또는 변화에 ON t.id = l.id AND (t.rate l.pri_key)) 5 개 행을 얻을 :

     100, 90, 90, 80, 80

    MySQL의 8 년 이상 단지 RANK를 사용, DENSE_RANK 또는 ROW_NUMBER 기능 :

    SELECT *
FROM (
    SELECT *, RANK() OVER (PARTITION BY id ORDER BY rate DESC) AS rnk
    FROM t
) AS x
WHERE rnk <= 5
  3. ==============================

    3.같은 날에 뭔가를

    같은 날에 뭔가를

    SUBSTRING_INDEX(group_concat(col_name order by desired_col_order_name), ',', N)

    완벽하게 작동합니다. 어떤 쿼리를 복잡합니다.

    예를 들어 각 그룹의 상위 1를 얻을 수

    SELECT 
    *
FROM
    yourtable
WHERE
    id IN (SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id
                            ORDER BY rate DESC),
                        ',',
                        1) id
        FROM
            yourtable
        GROUP BY year)
ORDER BY rate DESC;
  4. ==============================

    4.아니, 당신은 LIMIT의 서브 쿼리 임의로 (당신은이 새로운 MySQLs의 제한 정도 할 수 있지만 그룹 당 5 개 결과) 수 없습니다.

    아니, 당신은 LIMIT의 서브 쿼리 임의로 (당신은이 새로운 MySQLs의 제한 정도 할 수 있지만 그룹 당 5 개 결과) 수 없습니다.

    이는 SQL에서 할 사소한없는 GroupWise에 최대 형 쿼리입니다. 이 경우 더 효율적으로 할 수있는 것을 해결하기 위해 여러 가지 방법이 있지만, 일반적으로 상위 N 당신은 유사 이전의 질문에 빌의 대답을보고 싶을 것이다.

    같은 속도로 값이 여러 행이있는 경우 당신은 여전히 ​​후 처리가를 확인하기 위해의 양을 필요로 할 수 있도록이 문제에 대한 대부분의 솔루션과 마찬가지로 5 개 이상의 행을 반환 할 수 있습니다.

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

    5.이 그룹화 동안 다음,이를 제한 값을 순위 합계를 수행하는 하위 쿼리의 일련의 필요

    이 그룹화 동안 다음,이를 제한 값을 순위 합계를 수행하는 하위 쿼리의 일련의 필요

    @Rnk:=0;
@N:=2;
select
  c.id,
  sum(c.val)
from (
select
  b.id,
  b.bal
from (
select   
  if(@last_id=id,@Rnk+1,1) as Rnk,
  a.id,
  a.val,
  @last_id=id,
from (   
select 
  id,
  val 
from list
order by id,val desc) as a) as b
where b.rnk < @N) as c
group by c.id;
  6. ==============================

    6.이 시도:

    이 시도:

    SELECT h.year, h.id, h.rate 
FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:=@index+1, @index:=0) indx 
      FROM (SELECT h.year, h.id, h.rate 
            FROM h
            WHERE h.year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2)
            GROUP BY id, h.year
            ORDER BY id, rate DESC
            ) h, (SELECT @lastid:='', @index:=0) AS a
    ) h 
WHERE h.indx <= 5;
  7. ==============================

    7.

    SELECT year, id, rate
FROM (SELECT
  year, id, rate, row_number() over (partition by id order by rate DESC)
  FROM h
  WHERE year BETWEEN 2000 AND 2009
  AND id IN (SELECT rid FROM table2)
  GROUP BY id, year
  ORDER BY id, rate DESC) as subquery
WHERE row_number <= 5

    하위 쿼리는 쿼리와 거의 동일하다. 만 변경이 추가됩니다

    row_number() over (partition by id order by rate DESC)
  8. ==============================

    8.(오라클의 ROWID 같은) 가상 열을 구축

    (오라클의 ROWID 같은) 가상 열을 구축

    표:

    `
CREATE TABLE `stack` 
(`year` int(11) DEFAULT NULL,
`id` varchar(10) DEFAULT NULL,
`rate` float DEFAULT NULL) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
`

    데이터:

    insert into stack values(2006,'p01',8);
insert into stack values(2001,'p01',5.9);
insert into stack values(2007,'p01',5.3);
insert into stack values(2009,'p01',4.4);
insert into stack values(2001,'p02',12.5);
insert into stack values(2004,'p02',12.4);
insert into stack values(2005,'p01',2.1);
insert into stack values(2000,'p01',0.8);
insert into stack values(2002,'p02',12.2);
insert into stack values(2002,'p01',3.9);
insert into stack values(2004,'p01',3.5);
insert into stack values(2003,'p02',10.3);
insert into stack values(2000,'p02',8.7);
insert into stack values(2006,'p02',4.6);
insert into stack values(2007,'p02',3.3);
insert into stack values(2003,'p01',7.4);
insert into stack values(2008,'p01',6.8);

    이 같은 SQL :

    select t3.year,t3.id,t3.rate 
from (select t1.*, (select count(*) from stack t2 where t1.rate<=t2.rate and t1.id=t2.id) as rownum from stack t1) t3 
where rownum <=3 order by id,rate DESC;

    T3에서 where 절을 삭제하면, 그것은 다음과 같이 표시됩니다 :

    "TOP N 기록"하세요 -> WHERE 절에서 "ROWNUM <= 3"추가 (를 Where 절의 T3);

    "년"을 선택 ->에서 "2000 년 사이와 2009 년"추가 where 절합니다 (여기서 절 T3의);

  9. ==============================

    9.몇 가지 작업을했다,하지만 난 그게 아니라 매우 빨리 우아한 보인다 그대로 내 솔루션 공유에 뭔가있을 거라 생각 했어요.

    몇 가지 작업을했다,하지만 난 그게 아니라 매우 빨리 우아한 보인다 그대로 내 솔루션 공유에 뭔가있을 거라 생각 했어요.

    SELECT h.year, h.id, h.rate 
  FROM (
    SELECT id, 
      SUBSTRING_INDEX(GROUP_CONCAT(CONCAT(id, '-', year) ORDER BY rate DESC), ',' , 5) AS l
      FROM h
      WHERE year BETWEEN 2000 AND 2009
      GROUP BY id
      ORDER BY id
  ) AS h_temp
    LEFT JOIN h ON h.id = h_temp.id 
      AND SUBSTRING_INDEX(h_temp.l, CONCAT(h.id, '-', h.year), 1) != h_temp.l

    이 예는 질문의 목적을 위해 지정되고 다른 유사한 목적을 위해 아주 쉽게 수정 될 수 있습니다.

  10. ==============================

    10.다음 게시물 : SQL : 그룹 별 상위 N 레코드를 선택 하위 쿼리없이 달성의 복잡한 방법을 설명합니다.

    다음 게시물 : SQL : 그룹 별 상위 N 레코드를 선택 하위 쿼리없이 달성의 복잡한 방법을 설명합니다.

    그것은에 의해 여기에서 제공하는 다른 솔루션에 향상 :

    그러나 그것은 꽤하지 않습니다. 달성 될 좋은 솔루션은 윈도우 기능 (일명 분석 기능)이 MySQL을 활성화했다 - 그러나 그들은하지 않습니다. 말했다 게시물에 사용 된 트릭은 종종 "MySQL 용 가난한 사람의 창 함수"로 설명 GROUP_CONCAT를 사용합니다.

  11. ==============================

    11.에서 쿼리 시간을 한 것으로 나와 같은 사람들을 위해. 나는 특정 그룹에 의해 사용 제한 및 다른 어떤 아래를했다.

    에서 쿼리 시간을 한 것으로 나와 같은 사람들을 위해. 나는 특정 그룹에 의해 사용 제한 및 다른 어떤 아래를했다.

    DELIMITER $$
CREATE PROCEDURE count_limit200()
BEGIN
    DECLARE a INT Default 0;
    DECLARE stop_loop INT Default 0;
    DECLARE domain_val VARCHAR(250);
    DECLARE domain_list CURSOR FOR SELECT DISTINCT domain FROM db.one;

    OPEN domain_list;

    SELECT COUNT(DISTINCT(domain)) INTO stop_loop 
    FROM db.one;
    -- BEGIN LOOP
    loop_thru_domains: LOOP
        FETCH domain_list INTO domain_val;
        SET a=a+1;

        INSERT INTO db.two(book,artist,title,title_count,last_updated) 
        SELECT * FROM 
        (
            SELECT book,artist,title,COUNT(ObjectKey) AS titleCount, NOW() 
            FROM db.one 
            WHERE book = domain_val
            GROUP BY artist,title
            ORDER BY book,titleCount DESC
            LIMIT 200
        ) a ON DUPLICATE KEY UPDATE title_count = titleCount, last_updated = NOW();

        IF a = stop_loop THEN
            LEAVE loop_thru_domain;
        END IF;
    END LOOP loop_thru_domain;
END $$

    이 도메인의 목록을 반복하고 다음 각 200의 한계를 삽입

  12. ==============================

    12.이 시도:

    이 시도:

    SET @num := 0, @type := '';
SELECT `year`, `id`, `rate`,
    @num := if(@type = `id`, @num + 1, 1) AS `row_number`,
    @type := `id` AS `dummy`
FROM (
    SELECT *
    FROM `h`
    WHERE (
        `year` BETWEEN '2000' AND '2009'
        AND `id` IN (SELECT `rid` FROM `table2`) AS `temp_rid`
    )
    ORDER BY `id`
) AS `temph`
GROUP BY `year`, `id`, `rate`
HAVING `row_number`<='5'
ORDER BY `id`, `rate DESC;
  13. ==============================

    13.저장 프로 시저 아래 보시기 바랍니다. 난 이미 확인했습니다. 나는 적절한 결과를 얻고 있지만 GROUPBY를 사용하지 않고 있어요.

    저장 프로 시저 아래 보시기 바랍니다. 난 이미 확인했습니다. 나는 적절한 결과를 얻고 있지만 GROUPBY를 사용하지 않고 있어요.

    CREATE DEFINER=`ks_root`@`%` PROCEDURE `first_five_record_per_id`()
BEGIN
DECLARE query_string text;
DECLARE datasource1 varchar(24);
DECLARE done INT DEFAULT 0;
DECLARE tenants varchar(50);
DECLARE cur1 CURSOR FOR SELECT rid FROM demo1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @query_string='';

      OPEN cur1;
      read_loop: LOOP

      FETCH cur1 INTO tenants ;

      IF done THEN
        LEAVE read_loop;
      END IF;

      SET @datasource1 = tenants;
      SET @query_string = concat(@query_string,'(select * from demo  where `id` = ''',@datasource1,''' order by rate desc LIMIT 5) UNION ALL ');

       END LOOP; 
      close cur1;

    SET @query_string  = TRIM(TRAILING 'UNION ALL' FROM TRIM(@query_string));  
  select @query_string;
PREPARE stmt FROM @query_string;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

    14. from https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 관계형 데이터베이스에서 계층 적 데이터를 저장하기위한 옵션은 무엇입니까? [닫은]  (0) 2020.03.05
[SQL] SQL Server의 '피봇'를 사용하여 열을 변환 행  (0) 2020.03.05
[SQL] 어떻게 FULL OUTER MySQL의에 가입 할까?  (0) 2020.03.05
[SQL] 효율적으로 SQL 서버에 열을 행으로 변환  (0) 2020.03.05
[SQL] SQL 주입은 무엇인가? [복제]  (0) 2020.03.05
,

티스토리툴바