그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

programing

그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

bestcode 2022. 11. 27. 11:35

그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

다음 쿼리:

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

GROUP BY 내에서 기능하는 LIMIT와 같은 수식어를 사용하여 이를 수행할 수 있는 방법이 있습니까?

GROUP_CONCAT 집계 함수를 사용하여 모든 연도를 다음과 같이 그룹화된 단일 열로 가져올 수 있습니다.id주문자rate:

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_SETfind_in_set에 의해 반환된 위치에 따라 필터링하면 모든 ID에 대해 처음 5년만 반환하는 다음 쿼리를 사용할 수 있습니다.

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;

여기 바이올린 좀 봐주세요.

둘 이상의 행이 동일한 환율을 가질 수 있는 경우 다음을 사용하는 것을 고려해야 합니다.GROUP_CONCAT(DISTINCT rate ORDER BY rate)에서rate컬럼 대신year기둥.

에서 반환되는 문자열의 최대 길이GROUP_CONCAT는 한정되어 있기 때문에, 그룹 마다 몇개의 레코드를 선택할 필요가 있는 경우, 이 기능은 유효합니다.

그룹당 상위 n개의 행을 찾습니다.이 답변은 OP와 다른 예제 데이터를 사용한 일반적인 솔루션을 제공합니다.

MySQL 8 이후에서는 상위5의 정확한 정의에 따라 또는 함수를 사용할 수 있습니다.아래는 이러한 함수에 의해 생성되는 수치입니다.value내림차순으로 정렬되어 있습니다.넥타이 처리 방법에 주의해 주세요.

pkid	카티드	가치	row_number	순위	dense_rank
1	p01	100	*1	*1	*1
2	p01	90	*2	*2	*2
3	p01	90	*3	*2	*2
4	p01	80	*4	*4	*3
5	p01	80	*5	*4	*3
6	p01	80	6	*4	*3
7	p01	70	7	7	*4
8	p01	60	8	8	*5
9	p01	50	9	9	6
10	p01	40	10	10	7

기능을 선택하면 다음과 같이 사용합니다.

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC) AS n
    FROM t
) AS x
WHERE n <= 5

DB <>Fiddle

MySQL 5.x에서는 원하는 결과를 얻기 위해 poor man의 랭크 오버 파티션을 사용할 수 있습니다.외부 테이블은 그 자체와 결합하고 각 행은 그 앞의 행의 수를 셀 수 있습니다(예: 이전 행은 더 높은 값을 가질 수 있습니다).

다음과 같은 결과가 나타납니다.RANK기능:

SELECT t.pkid, t.catid, t.value, COUNT(b.value) + 1 AS rank
FROM t
LEFT JOIN t AS b ON b.catid = t.catid AND b.value > t.value
GROUP BY t.pkid, t.catid, t.value
HAVING COUNT(b.value) + 1 <= 5
ORDER BY t.catid, t.value DESC, t.pkid

다음과 같은 결과를 얻으려면 다음과 같이 변경하십시오.DENSE_RANK기능:

COUNT(DISTINCT b.value)

또는 다음과 같은 결과를 얻으려면 다음과 같이 변경하십시오.ROW_NUMBER기능:

ON b.catid = t.catid AND (b.value > t.value OR b.value = t.value AND b.pkid < t.pkid)

DB <>Fiddle

저는 뭐랄까

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;

아니요, 서브쿼리를 임의로 제한할 수 없습니다(새로운 MySQL에서는 제한적으로 실행할 수 있지만 그룹당 5개의 결과를 얻을 수 없습니다).

이는 SQL에서 쉽게 수행할 수 있는 groupwise-maximum 유형의 쿼리입니다.경우에 따라서는 보다 효율적으로 대처하는 다양한 방법이 있지만, 일반적으로 상위 n개에 대해서는 이전 유사한 질문에 대한 빌의 답변을 살펴보는 것이 좋습니다.

이에 대한 행이 개 할 수 .rate그 때문에, 그것을 확인하려면 , 아직 후처리가 필요하게 되는 경우가 있습니다.

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)

여기에는 일련의 하위 쿼리가 필요합니다. 값의 순위를 매기고, 값을 제한한 다음, 그룹화하는 동안 합계를 수행합니다.

@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;

이것을 시험해 보세요.

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;

Row와 같은 가상 컬럼을 만듭니다.Oracle 아이디 »

표:

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구를 삭제하면 다음과 같이 표시됩니다.

N Record" --> "TOP N Record" --> ★rownum <=3where clause(t3의 where-discription)

--> "" 를 합니다. --> "year" 를 추가합니다. --> add year:BETWEEN 2000 AND 2009where clause(t3의 where-discription)

작업에는 시간이 좀 걸렸지만, 제 솔루션은 우아할 뿐만 아니라 꽤 빠른 것처럼 보이기 때문에 공유할 수 있을 것 같습니다.

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

이 예는 질문의 목적으로 지정되며 다른 유사한 목적으로 쉽게 수정할 수 있습니다.

다음 게시물: sql: selcting top N record per group에서는 서브쿼리 없이 이를 실현하는 복잡한 방법에 대해 설명합니다.

여기서 제공하는 다른 솔루션보다 다음과 같은 이점이 있습니다.

단일 쿼리에서 모든 작업 수행
인덱스를 적절하게 활용할 수 있는 능력
MySQL에서 잘못된 실행 계획을 생성하는 것으로 유명한 서브쿼리 회피

하지만 그것은 예쁘지 않다.MySQL에서 Window Functions(일명 Analytic Functions)를 활성화하면 좋은 해결책이 될 수 있지만 그렇지 않습니다.이 게시물에 사용된 트릭은 GROUP_CONCAT를 사용하고 있으며, "MySQL의 창 기능 불량"이라고도 합니다.

나처럼 질문이 타임아웃된 사람들을 위해.특정 그룹별로 한도 등을 사용하기 위해 아래와 같이 작성했습니다.

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개의 제한만 삽입합니다.

이것을 시험해 보세요.

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;

아래의 저장 프로시저를 사용해 보십시오.미미확확 확다다다다다, using using using i i i using using using using using using 。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

언급URL : https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group

'programing' 카테고리의 다른 글

NUXT의 SSR vuex 저장소 (0)	2022.12.07
Node.js를 시작하려면 어떻게 해야 하나요? (0)	2022.11.27
마리아에서 조건부로 말하면.DB (0)	2022.11.27
Java 9 이후로 이행할 때 모듈로 전환할 필요가 있습니까? (0)	2022.11.27
생성된 URL의 "public/index.php"를 삭제하려면 어떻게 해야 합니까? (0)	2022.11.27

현재글그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

각종 프로그래밍 정보를 다루는 블로그입니다.

vuex, vuejs2, Oracle, C, typescript, MariaDB, mysql, wordpress, ajax, java, ReactJS, javascript, C#, spring-boot, json, angularJS, PYTHON, php,

Today :
Yesterday :

bestcode

그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2025/06 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

그룹별로 N개의 결과를 얻기 위해 GROUP BY 내의 LIMIT를 사용합니까?

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바