카테고리별로 최신 4가지 아이템을 선택하는 방법은?

카테고리별로 최신 4가지 아이템을 선택하는 방법은?

저는 아이템 데이터베이스를 가지고 있습니다.각 항목은 카테고리 테이블에서 카테고리 ID로 분류됩니다.저는 모든 카테고리를 나열하는 페이지를 만들려고 하는데, 각 카테고리 아래에 해당 카테고리의 최신 아이템 4개를 보여주고 싶습니다.

예:

반려동물 용품

img1
img2
img3
img4

펫푸드

img1
img2
img3
img4

저는 이렇게 각 카테고리별로 데이터베이스를 조회하면 이 문제를 쉽게 해결할 수 있다는 것을 알고 있습니다.

카테고리에서 ID 선택

해당 데이터를 반복하고 각 범주에 대해 데이터베이스를 쿼리하여 최신 항목을 가져옵니다.

category_id = :category_id인 항목에서 이미지를 선택합니다.ORDER by date_listed Desc LIMIT 4

제가 알아내려는 것은 제가 단지 하나의 쿼리를 사용해서 그 모든 데이터를 얻을 수 있는지 여부입니다.저는 33개의 카테고리를 가지고 있기 때문에 데이터베이스에 대한 호출 횟수를 줄이는 데 도움이 될 것이라고 생각했습니다.

이게 가능한지 아는 사람?아니면 33번의 전화가 그렇게 큰 문제가 아니고 그냥 쉽게 해야 하는 거라면요.

이것은 그룹별로 가장 큰 문제이며 매우 일반적인 SQL 질문입니다.

외부 접합을 사용하여 해결하는 방법은 다음과 같습니다.

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4
ORDER BY category_id, date_listed;

나는 의 .itemㅇitem_id으로 증가하는 합니다 은 하는 롭게 은 합니다 item_id는의새에다합니다의 새로운 합니다.item.

각 항목에 대해 보다 최신인 항목이 몇 가지 있습니다.예를 들어, 네 번째로 새로 나온 제품보다 새로운 제품이 세 가지 있습니다.최신 제품보다 더 새로운 제품은 0개입니다.그래서 우리는 각 품목을 비교하고 싶습니다 (i1( ) ( )i2) 이며 ( )와것입니다.i1 그 의 개수가, . 의 가 이면 가 이면 의 ,i1우리가 포함하고 있는 것들 중 하나입니다.그렇지 않으면 포함하지 마십시오.

이 솔루션의 장점은 카테고리가 아무리 많아도 작동하고, 카테고리를 변경해도 계속 작동한다는 것입니다.일부 범주의 항목 수가 4개 미만인 경우에도 적용됩니다.

---

작동하지만 MySQL 사용자 변수 기능에 의존하는 또 다른 솔루션:

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (@g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 3;

---

MySQL 8.0.3은 SQL 표준 윈도우 기능을 지원합니다.이제 우리는 다른 RDBMS가 하는 방식으로 이와 같은 문제를 해결할 수 있습니다.

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;

이 솔루션은 다른 SO 솔루션을 적용한 것입니다. 이와 관련된/유사한 질문을 찾아주신 RageZ에 감사드립니다.

메모

이 솔루션은 저스틴의 사용 사례에 대해 만족스러운 것 같습니다.사용 사례에 따라 이 게시물에서 빌 카윈 또는 데이비드 안드레스의 솔루션을 확인할 수 있습니다.빌의 해결책은 내 표입니다!두 쿼리를 서로 옆에 놓으니 이유를 확인하십시오 ;-)

제 솔루션의 장점은 category_id 당 하나의 레코드를 반환한다는 것입니다(항목 테이블의 정보는 "roll-up"입니다).제 솔루션의 가장 큰 단점은 가독성이 부족하고 원하는 행의 수가 증가함에 따라 복잡성이 증가한다는 것입니다(범주당 6개가 아닌 6개의 행이 있다고 함).또한 항목 테이블의 행 수가 증가함에 따라 약간 느려질 수도 있습니다. (어쨌든 모든 솔루션은 항목 테이블의 적합한 행 수가 적을수록 성능이 향상되므로 주기적으로 이전 항목을 삭제하거나 이동하거나 SQL이 행을 조기에 필터링하는 데 도움이 되는 플래그를 도입하는 것이 좋습니다.

첫 번째 시도(안됨!!!)...

이 접근 방식의 문제점은 하위 쿼리가 자체 조인에 의해 정의된 데카르트 곱에 기초하여 매우 많은 행을 생성할 것이라는 점이었습니다.

SELECT id, CategoryName(?), tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  here_some_addtional l criteria if needed
ORDER BY id ASC;

두번째 시도. (괜찮습니다!)

하위 쿼리에 WHERE 조항이 추가되어 나열된 날짜를 i1, i2, i3 등에 대해 각각 최신, 두 번째, 세 번째 최신 등으로 강제합니다(또한 주어진 카테고리 ID에 대해 4개 미만의 항목이 있는 경우에는 null인 경우도 허용됩니다).또한 "판매"된 항목 또는 이미지가 없는 항목을 표시하지 않도록 관련 없는 필터 절이 추가되었습니다(추가된 요구 사항).

주어진 category_id에 대해 중복된 날짜 나열 값이 없다고 가정합니다.그렇지 않으면 중복 행이 생성됩니다.실제로 나열된 날짜를 사용하는 것은 Bill의 솔루션에 정의된/필요한 대로 단조롭게 증가된 기본 키를 사용하는 것입니다.

SELECT id, CategoryName, tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4, i4.date_listed
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed AND i2.sold = FALSE AND i2.image IS NOT NULL
          AND i1.sold = FALSE AND i1.image IS NOT NULL
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed AND i3.sold = FALSE AND i3.image IS NOT NULL
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed AND i4.sold = FALSE AND i4.image IS NOT NULL
    WHERE NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i1.date_listed)
      AND (i2.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i2.date_listed AND date_listed <> i1.date_listed)))
      AND (i3.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i3.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed)))
      AND (i4.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i4.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed AND date_listed <> i3.date_listed)))
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  --
ORDER BY id ASC;

이제... 다음을 비교해 보겠습니다. item_id 키를 소개하고 빌의 솔루션을 사용하여 이 목록을 "outside" 쿼리에 제공합니다.빌의 접근 방식이 더 나은 이유를 알 수 있습니다.

SELECT id, CategoryName, image, date_listed, item_id
FROM item I
LEFT OUTER JOIN category C ON C.id = I.category_id
WHERE I.item_id IN 
(
SELECT i1.item_id
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id
      AND i1.sold = 'N' AND i2.sold = 'N'
      AND i1.image <> '' AND i2.image <> ''
      )
GROUP BY i1.item_id
HAVING COUNT(*) < 4
)
ORDER BY category_id, item_id DESC

에서는 에서 을 할 을 사용하여 이 작업을 할 수 .ROW_NUMBER기능.

SELECT
    category_id, image, date_listed
FROM
(
    SELECT
        category_id, image, date_listed,
        ROW_NUMBER() OVER (PARTITION BY category_id
                           ORDER BY date_listed DESC) AS rn
    FROM item
) AS T1
WHERE rn <= 4

은 "MySQL"을 .ROW_NUMBER함수이지만 변수를 사용하여 에뮬레이션할 수 있습니다.

SELECT
    category_id, image, date_listed
FROM
(
    SELECT
        category_id, image, date_listed,
        @rn := IF(@prev = category_id, @rn + 1, 1) AS rn,
        @prev := category_id
    FROM item
    JOIN (SELECT @prev := NULL, @rn = 0) AS vars
    ORDER BY category_id, date_listed DESC
) AS T1
WHERE rn <= 4

온라인으로 작동하는 보기: sqlfiddle

다음과 같이 작동합니다.

• 원래 @prev는 NULL로 설정되고 @rn은 0으로 설정됩니다.
• 각 행에 대해 category_id가 이전 행과 동일한지 확인합니다.
  • 예인 경우 행 번호를 증가시킵니다.
  • 그렇지 않으면 새 범주를 시작하고 행 번호를 다시 1로 재설정합니다.
• 하위 쿼리가 완료되면 마지막 단계는 행 번호가 4 이하인 행만 유지되도록 필터링하는 것입니다.

범주가 얼마나 일정한지에 따라 다음이 가장 간단한 경로입니다.

SELECT C.CategoryName, R.Image, R.date_listed
FROM
(
    SELECT CategoryId, Image, date_listed
    FROM 
    (
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = 'Pet Supplies'
      ORDER BY date_listed DESC LIMIT 4
    ) T

    UNION ALL

    SELECT CategoryId, Image, date_listed
    FROM
    (        
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = 'Pet Food'
      ORDER BY date_listed DESC LIMIT 4
    ) T
) RecentItemImages R
INNER JOIN Categories C ON C.CategoryId = R.CategoryId
ORDER BY C.CategoryName, R.Image, R.date_listed

아래 코드는 루프에서 할 수 있는 방법을 보여줍니다. 많은 편집이 필요하지만 도움이 되었으면 합니다.

        declare @RowId int
 declare @CategoryId int
        declare @CategoryName varchar(MAX)

 create table PART (RowId int, CategoryId int, CategoryName varchar)
 create table  NEWESTFOUR(RowId int, CategoryId int, CategoryName varchar, Image image)
        select RowId = ROW_NUMBER(),CategoryId,CategoryName into PART from [Category Table]


        set @PartId = 0
 set @CategoryId = 0 
 while @Part_Id <= --count
 begin
   set @PartId = @PartId + 1
          SELECT @CategoryId = category_id, @CategoryName = category_name from PART where PartId = @Part_Id
          SELECT RowId = @PartId, image,CategoryId = @category_id, CategoryName = @category_name   FROM item into NEWESTFOUR where category_id = :category_id 
ORDER BY date_listed DESC LIMIT 4

 end
 select * from NEWESTFOUR
 drop table NEWESTFOUR
        drop table PART

최근에 비슷한 상황을 접했는데 데이터베이스에 독립적인 쿼리를 시도했습니다.

SELECT i.* FROM Item AS i JOIN Category c ON i.category_id=c.id WHERE
(SELECT count(*) FROM Item i1 WHERE 
i1.category_id=i.category_id AND 
i1.date_listed>=i.date_listed) <=3 
ORDER BY category_id,date_listed DESC;

루프에 대해 2개를 실행하고 이보다 새로운 항목이 3개 미만인지 확인하는 것과 같습니다.

그다지 예쁘지는 않지만:

SELECT image 
FROM item 
WHERE date_listed IN (SELECT date_listed 
                      FROM item 
                      ORDER BY date_listed DESC LIMIT 4)

알았어 구글 검색 후 빠른 답변은 적어도 mysql에서는 불가능할 것입니다.

참고할 만한 이 실

서버가 다운되는 것을 두려워하고 코드가 더 잘 수행되기를 원한다면 당신은 그 쿼리의 결과를 캐시해야 합니다.

언급URL : https://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category