programing

각 카테고리별 상위 10개 레코드 선택

javamemo 2023. 5. 20. 00:23
반응형

각 카테고리별 상위 10개 레코드 선택

한 번의 쿼리로 각 섹션의 상위 10개 레코드를 반환하고 싶습니다.누가 그것을 어떻게 하는지 도와줄 수 있습니까?섹션은 테이블의 열 중 하나입니다.

데이터베이스는 SQL Server 2005입니다.저는 입력한 날짜별로 상위 10개를 반품하고 싶습니다.섹션은 비즈니스, 로컬 및 기능입니다.특정 날짜에 대해 상위(10개) 비즈니스 행(최신 항목), 상위(10개) 로컬 행 및 상위(10개) 기능만 원합니다.

SQL 2005를 사용하는 경우 다음과 같은 작업을 수행할 수 있습니다.

SELECT rs.Field1,rs.Field2 
    FROM (
        SELECT Field1,Field2, Rank() 
          over (Partition BY Section
                ORDER BY RankCriteria DESC ) AS Rank
        FROM table
        ) rs WHERE Rank <= 10

순위 기준에 동점이 있는 경우 10개 이상의 행을 반환할 수 있으며 Matt의 솔루션이 더 적합할 수 있습니다.

T-SQL에서 저는 다음을 수행합니다.

WITH TOPTEN AS (
    SELECT *, ROW_NUMBER() 
    over (
        PARTITION BY [group_by_field] 
        order by [prioritise_field]
    ) AS RowNo 
    FROM [table_name]
)
SELECT * FROM TOPTEN WHERE RowNo <= 10
SELECT r.*
FROM
(
    SELECT
        r.*,
        ROW_NUMBER() OVER(PARTITION BY r.[SectionID]
                          ORDER BY r.[DateEntered] DESC) rn
    FROM [Records] r
) r
WHERE r.rn <= 10
ORDER BY r.[DateEntered] DESC

이 기능은 SQL Server 2005에서 작동합니다(설명을 반영하도록 편집됨).

select *
from Things t
where t.ThingID in (
    select top 10 ThingID
    from Things tt
    where tt.Section = t.Section and tt.ThingDate = @Date
    order by tt.DateEntered desc
    )
    and t.ThingDate = @Date
order by Section, DateEntered desc

이런 식으로 합니다.

SELECT a.* FROM articles AS a
  LEFT JOIN articles AS a2 
    ON a.section = a2.section AND a.article_date <= a2.article_date
GROUP BY a.article_id
HAVING COUNT(*) <= 10;

update: GROUP BY의 이 예제는 MySQL 및 SQLite에서만 작동합니다. 이러한 데이터베이스는 GROUP BY와 관련된 표준 SQL보다 허용 범위가 넓기 때문입니다.대부분의 SQL 구현에서는 집계 식에 포함되지 않은 선택 목록의 모든 열이 GROUP BY에도 있어야 합니다.

SQL Server > = 2005를 사용하는 경우 다음 중 하나만 선택하여 작업을 해결할 수 있습니다.

declare @t table (
    Id      int ,
    Section int,
    Moment  date
);

insert into @t values
(   1   ,   1   , '2014-01-01'),
(   2   ,   1   , '2014-01-02'),
(   3   ,   1   , '2014-01-03'),
(   4   ,   1   , '2014-01-04'),
(   5   ,   1   , '2014-01-05'),

(   6   ,   2   , '2014-02-06'),
(   7   ,   2   , '2014-02-07'),
(   8   ,   2   , '2014-02-08'),
(   9   ,   2   , '2014-02-09'),
(   10  ,   2   , '2014-02-10'),

(   11  ,   3   , '2014-03-11'),
(   12  ,   3   , '2014-03-12'),
(   13  ,   3   , '2014-03-13'),
(   14  ,   3   , '2014-03-14'),
(   15  ,   3   , '2014-03-15');


-- TWO earliest records in each Section

select top 1 with ties
    Id, Section, Moment 
from
    @t
order by 
    case 
        when row_number() over(partition by Section order by Moment) <= 2 
        then 0 
        else 1 
    end;


-- THREE earliest records in each Section

select top 1 with ties
    Id, Section, Moment 
from
    @t
order by 
    case 
        when row_number() over(partition by Section order by Moment) <= 3 
        then 0 
        else 1 
    end;


-- three LATEST records in each Section

select top 1 with ties
    Id, Section, Moment 
from
    @t
order by 
    case 
        when row_number() over(partition by Section order by Moment desc) <= 3 
        then 0 
        else 1 
    end;

섹션이 무엇인지 알고 있는 경우 다음 작업을 수행할 수 있습니다.

select top 10 * from table where section=1
union
select top 10 * from table where section=2
union
select top 10 * from table where section=3

이 스레드가 약간 오래된 것은 알지만 비슷한 문제에 부딪혔을 뿐입니다(각 범주에서 최신 기사 선택). 이것이 제가 생각해 낸 해결책입니다.

WITH [TopCategoryArticles] AS (
    SELECT 
        [ArticleID],
        ROW_NUMBER() OVER (
            PARTITION BY [ArticleCategoryID]
            ORDER BY [ArticleDate] DESC
        ) AS [Order]
    FROM [dbo].[Articles]
)
SELECT [Articles].* 
FROM 
    [TopCategoryArticles] LEFT JOIN 
    [dbo].[Articles] ON
        [TopCategoryArticles].[ArticleID] = [Articles].[ArticleID]
WHERE [TopCategoryArticles].[Order] = 1

이는 Darrel의 솔루션과 매우 유사하지만 의도한 것보다 더 많은 행을 반환할 수 있는 RANK 문제를 해결합니다.

다음을 시도했고 넥타이도 작동했습니다.

SELECT rs.Field1,rs.Field2 
FROM (
    SELECT Field1,Field2, ROW_NUMBER() 
      OVER (Partition BY Section
            ORDER BY RankCriteria DESC ) AS Rank
    FROM table
    ) rs WHERE Rank <= 10

섹션별로 그룹화된 출력을 생성하고 각 섹션의 상위 N개 레코드만 표시하려면 다음과 같이 하십시오.

SECTION     SUBSECTION

deer        American Elk/Wapiti
deer        Chinese Water Deer
dog         Cocker Spaniel
dog         German Shephard
horse       Appaloosa
horse       Morgan

...그러면 모든 SQL 데이터베이스에서 다음과 같이 일반적으로 작동합니다.상위 10개를 원하는 경우 쿼리가 끝날 때 2를 10으로 변경하면 됩니다.

select
    x1.section
    , x1.subsection
from example x1
where
    (
    select count(*)
    from example x2
    where x2.section = x1.section
    and x2.subsection <= x1.subsection
    ) <= 2
order by section, subsection;

설정 방법:

create table example ( id int, section varchar(25), subsection varchar(25) );

insert into example select 0, 'dog', 'Labrador Retriever';
insert into example select 1, 'deer', 'Whitetail';
insert into example select 2, 'horse', 'Morgan';
insert into example select 3, 'horse', 'Tarpan';
insert into example select 4, 'deer', 'Row';
insert into example select 5, 'horse', 'Appaloosa';
insert into example select 6, 'dog', 'German Shephard';
insert into example select 7, 'horse', 'Thoroughbred';
insert into example select 8, 'dog', 'Mutt';
insert into example select 9, 'horse', 'Welara Pony';
insert into example select 10, 'dog', 'Cocker Spaniel';
insert into example select 11, 'deer', 'American Elk/Wapiti';
insert into example select 12, 'horse', 'Shetland Pony';
insert into example select 13, 'deer', 'Chinese Water Deer';
insert into example select 14, 'deer', 'Fallow';

각 그룹에서 TOP X 레코드 찾기(Oracle)

SQL> select * from emp e 
  2  where e.empno in (select d.empno from emp d 
  3  where d.deptno=e.deptno and rownum<3)
  4  order by deptno
  5  ;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

  7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
  7839 KING       PRESIDENT            17-NOV-81       5000                    10
  7369 SMITH      CLERK           7902 17-DEC-80        800                    20
  7566 JONES      MANAGER         7839 02-APR-81       2975                    20
  7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

6개 행이 선택되었습니다.


유니온 운영자가 당신을 위해 일할 수 있습니까?각 섹션에 대해 하나씩 선택한 다음 함께 연합합니다.하지만 그것은 정해진 수의 섹션에만 효과가 있을 것이라고 추측합니다.

SQL Server 2005에 대한 질문이었지만 대부분의 사용자가 이 질문을 발견하면 이 블로그 게시물에 나와 있는 것처럼 다른 상황에서 선호하는 답변이 될 수 있습니다.

SELECT *
FROM t
CROSS APPLY (
  SELECT TOP 10 u.*
  FROM u
  WHERE u.t_id = t.t_id
  ORDER BY u.something DESC
) u

이 쿼리에는 두 개의 테이블이 포함됩니다.OP의 쿼리는 윈도우 함수 기반 솔루션이 더 효율적일 수 있는 경우에 한 테이블만 포함합니다.

당신은 이 방법을 시도할 수 있습니다.이 쿼리는 각 국가에 대해 가장 인구가 많은 10개 도시를 반환합니다.

   SELECT city, country, population
   FROM
   (SELECT city, country, population, 
   @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
   @current_country := country 
   FROM cities
   ORDER BY country, population DESC
   ) ranked
   WHERE country_rank <= 10;

참고: OP에는 3개의 그룹만 있는 것으로 알고 있지만, 이것은 많은 개발자들에게 알려진 일반적인 문제이며 SQL에는 좋은 솔루션이 없습니다.다른 방법을 보여드리겠습니다.


이론상:

하나의 쿼리로 작성할 수 있습니다., 공식적으로 하나의 쿼리이지만 하위 쿼리 또는 실제로 후드 아래에서 여러 작업을 수행하는 자체 JOIN을 포함합니다.따라서 각 그룹을 개별적으로 선택하는 것이 좋습니다.


실무:

성능이 뛰어난 솔루션을 원한다면 좀 더 노력해야 합니다.직원이 100명이고, A에서 Z까지 26개의 건물이 있고, 사람들이 그 주변을 이동(출입/퇴출)하고, 각 건물에 대해 마지막 5개의 이벤트가 필요하다고 가정해 보겠습니다.

EVENT_ID   EVENT_TIME            EMPOYEE_ID   EVENT_CODE   BUILDING                           
-------------------------------------------------------------------------                                
883691     2023-03-29 11:00:00   92           enter        A                                           
883690     2023-03-29 11:00:21   78           enter        C                                           
883689     2023-03-29 11:00:25   58           enter        A                                           
883688     2023-03-29 11:02:10   22           leave        H                                           
883687     2023-03-29 11:31:42   73           leave        P                                           
...
...

26개의 쿼리를 피하려고 합니다.

다음과 같은 작업을 수행할 수 있습니다.

  1. 모든 건물에 대한 마지막 N개의 이벤트를 가져오려면 간단한 ORDER BY EVENT_ID DESC(또는 EVENT_TIME DESC)로 쿼리를 작성합니다.
  2. N(한계)을 대부분의 건물에 대한 데이터를 포함하는 합리적인 추정치로 설정하면 모든 건물을 가질 필요는 없지만 많을수록 좋습니다.LIMIT 5000이라고 칩시다.
  3. 응용 프로그램 측에서 결과 집합을 처리하여 상위 10개가 없는 건물을 확인합니다.
  4. 이러한 건물의 경우 별도의 쿼리를 실행하여 상위 10개를 얻으십시오.

이론가들에게 이것은 반패턴입니다.그러나 첫 번째 쿼리는 단일 건물 중 하나와 거의 동일한 성능을 제공하며, 필요한 대부분의 것을 제공합니다. 직원이 정상적으로 이동하는 방식에 따라 몇 개의 건물이 누락될 수 있습니다.그런 다음 해당 건물에 대해 5개의 쿼리가 더 필요할 수 있으며 일부는 응용프로그램 수준에서 병합됩니다.

간단히 말해서, 거의 완성된 결과를 얻은 다음, 그것을 완성시킵니다.

따라서 성능이 필요한 경우 이 방법이 하나의 방법입니다.비즈니스 논리의 명확성이 필요한 경우 다른 답변을 선택하십시오.이거 무서워요.하지만 여러분이 속도를 낼 때, 종종 무서운 기술이 필요합니다.

언급URL : https://stackoverflow.com/questions/176964/select-top-10-records-for-each-category

반응형