MySQL에서는 그룹 코드당 상위 N에 대한 쿼리가 작동하지만 Maria에서는 결과가 다릅니다.DB

MySQL에서는 그룹 코드당 상위 N에 대한 쿼리가 작동하지만 Maria에서는 결과가 다릅니다.DB

각 그룹의 최신 3개의 레코드를 추출하는 SQL 쿼리가 있습니다.MySQL에 대한 쿼리 결과가 MariaDB와 다릅니다.이 쿼리는 아래 sqlfiddle에서 구현됩니다.

http://sqlfiddle.com/ #!9/c09fe/2

표 내용

CREATE TABLE tmp
    (`mac_addr` varchar(10), `reader_name` varchar(22), `value` numeric, `time_change` datetime)
;

INSERT INTO tmp
    (`mac_addr`, `reader_name`, `value`, `time_change`)
VALUES
    ('''B99A88''', '''name_8''', 1, '2016-07-07 19:21:48'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-06-21 13:30:00'),
    ('''B99A88''', '''own__temperature_1''', 37.4, '2016-05-04 18:23:03'),
    ('''B99A88''', '''own__temperature_1''', 29.4, '2016-05-04 18:19:33'),
    ('''B99A88''', '''own__temperature_1''', 28.4, '2016-05-04 18:17:32'),
    ('''B99A88''', '''own__temperature_1''', 27.4, '2016-05-04 18:04:08'),
    ('''B99A88''', '''own__temperature_1''', 21.4, '2016-05-04 15:11:42'),
    ('''B99A88''', '''own__detect_1''', 0, '2016-04-20 15:22:23'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-04-15 17:39:52'),
    ('''B99A88''', '''own__detect_1''', 0, '2016-04-15 17:39:46'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-04-11 17:34:00'),
    ('''B99A88''', '''own__detect_1''', 1, '2016-04-11 17:33:00'),
    ('''B99A88''', '''own__detect_1''', 0, '2016-04-11 17:33:00'),
    ('''B99A88''', '''own__temperature_1''', 28.4, '2016-04-10 21:20:20'),
    ('''B99A88''', '''own__temperature_1''', 32.5, '2016-04-10 21:00:00'),
    ('''B99A88''', '''own__temperature_1''', 34.2, '2016-04-10 11:29:00')
;

쿼리: 각 그룹의 최신 3개의 레코드를 추출합니다.

SELECT mac_addr, reader_name, value, time_change
FROM (
    SELECT t1.*,
           IF(@rn = reader_name, @rowno := @rowno + 1, @rowno := 1) AS rowno,
           @rn := reader_name
    FROM (
        SELECT *
          FROM tmp
        ORDER BY reader_name, time_change DESC
    ) t1
    CROSS JOIN (SELECT @rn := null, @rowno := 0) t2
) t
WHERE rowno <= 3

MySQL v5.6을 사용했을 때의 결과는 다음과 같습니다.

mac_addr    reader_name             value   time_change
'B99A88'    'name_8'                 1      July, 07 2016 19:21:48
'B99A88'    'own__detect_1'          1      June, 21 2016 13:30:00
'B99A88'    'own__detect_1'          0      April, 20 2016 15:22:23
'B99A88'    'own__detect_1'          1      April, 15 2016 17:39:52
'B99A88'    'own__temperature_1'    37      May, 04 2016 18:23:03
'B99A88'    'own__temperature_1'    29      May, 04 2016 18:19:33
'B99A88'    'own__temperature_1'    28      May, 04 2016 18:17:32

MySQL 결과는 제가 원하는 것입니다.하지만 MariaDB를 사용하고 있는데 MySQL 결과와 결과가 다릅니다.

MariaDB 결과는 다음과 같습니다.

mac_addr    reader_name             value   time_change
'B99A88'    'name_8'                 1      2016-07-07 19:21:48
'B99A88'    'own__detect_1'          1      2016-06-21 13:30:00
'B99A88'    'own__temperature_1'    37      2016-05-04 18:23:03
'B99A88'    'own__temperature_1'    29      2016-05-04 18:19:33
'B99A88'    'own__temperature_1'    28      2016-05-04 18:17:32
'B99A88'    'own__detect_1'          0      2016-04-20 15:22:23
'B99A88'    'own__detect_1'          1      2016-04-15 17:39:52
'B99A88'    'own__detect_1'          0      2016-04-15 17:39:46
'B99A88'    'own__temperature_1'    28      2016-04-10 21:20:20
'B99A88'    'own__temperature_1'    33      2016-04-10 21:00:00
'B99A88'    'own__temperature_1'    34      2016-04-10 11:29:00

MariaDB의 쿼리 출력이 MySQL과 동일하도록 쿼리 코드를 수정하려면 어떻게 해야 합니까?MariaDB에서 window 기능을 사용하는 것이 좋을까요?

쿼리 실행은 다음 명령을 무시하도록 허용됩니다.ORDER BY FROM ( SELECT ... )이것이 아마 여러분이 보고 있는 차이점의 진짜 이유일 것입니다.(고든의 답변은 관련이 없다고 생각합니다.)

이 문제는 여기서 (4년 전) 설명하겠습니다.https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/ 에서는 설정을 통해1개의 솔루션을 제공하고 있습니다.

그 외의 솔루션은, http://mysql.rjweb.org/doc.php/groupwise_max 를 참조해 주세요.효율적으로 설계되어 있습니다.

또 다른 가능한 해결책은 가짜를 추가하는 것이다.LIMIT서브쿼리에 많은 숫자가 기재되어 있습니다.

ORDER BY사용하고 있는 에는, 다음의 2개의 키가 있습니다.

    ORDER BY reader_name, time_change DESC

그러나 이러한 키는 각 행을 고유하게 식별하지 않습니다.따라서 키가 동일한 행의 순서는 보장되지 않습니다. 같은 데이터베이스에서 두 번의 쿼리 실행 사이에도 마찬가지입니다.인 해결방법은 로 원하는 ID로 입니다.ORDER BY키를 누르면 각 행이 고유하게 식별됩니다.

에서는 "SQL"을 사용합니다.ORDER BY는 안정된 정렬을 사용하지 않습니다.안정적 정렬이란 키가 동일한 경우 키의 원래 순서를 유지하는 정렬입니다.그 이유는 간단하다.SQL 테이블 및 결과 집합은 순서가 매겨지지 않은 집합을 나타냅니다.보관할 초기 주문은 없습니다.

컬럼이 , 「」는 「」입니다.ORDER BY음음음같 뭇매하다

    ORDER BY reader_name, time_change DESC, pk

나머지 코드는 변경할 필요가 없습니다.당신은 단지 그런 부류의 안정만을 원할 뿐입니다.

표준 SQL 언어 구조를 사용하여 그룹별로 상위N을 선택하는 일반적인 방법은 다음과 같습니다.

SELECT
    T.*
FROM
    (
        SELECT *
            ,ROW_NUMBER() OVER (PARTITION BY reader_name ORDER BY time_change DESC) AS rn
        FROM tmp
    ) AS T
WHERE T.rn <= 3
ORDER BY reader_name, time_change DESC;

이 쿼리는 다음을 지원하는 모든 DBMS에서 작동해야 합니다.ROW_NUMBERMySQL은 지원하지 않기 때문에 MySQL 고유의 변수에 취약한 트릭을 사용해야 합니다.

Windows 기능은 MariaDB 10.2.0에서 처음 도입되었습니다.MariaDB는 쿼리를 최적화하는 데 더 많은 자유가 있으며 변수를 사용하는 MySQL 트릭은 더 이상 신뢰할 수 없습니다.

그래서 당신의 질문에 대답하는 것이 좋습니다.MariaDB의 윈도 기능을 사용하는 것이 좋습니다.

---

그룹당 상위 N개를 선택하는 또 다른 일반적인 방법은LATERAL가입하는 것보다 낫다.ROW_NUMBER그룹 수가 적고 표의 행 수가 많고 적절한 색인과 그룹 목록이 있는 두 번째 표가 있는 경우.MariaDB가 다음을 지원하는지 여부를 알 수 없습니다.LATERAL합류하지 않는 것 같아요

서브쿼리 결과 순서를 유지하기 위해 (My)SQL은 필요하지 않습니다.상위 쿼리에서 결과 세트를 정렬해야 하지만, 이 경우 하위 쿼리를 실제로 제거할 수 있습니다.

SELECT mac_addr, reader_name, value, time_change
FROM (
    SELECT t1.*,
           IF(@rn = reader_name, @rowno := @rowno + 1, @rowno := 1) AS rowno,
           @rn := reader_name
    FROM tmp t1, (SELECT @rn := null, @rowno := 0) t2
    ORDER BY reader_name, time_change DESC
) t
WHERE rowno <= 3;

완전성을 위해:이 동작은 변수 사용에 고유하며, 실제로 sql 표준에서 결과가 정의되어 있지 않기 때문에 (현재의 문제를 일으키는 최적화와 같이) 언젠가 변경될 수 있습니다.그러나 윈도 기능이 완전히 지원될 때까지 이 동작은 발생하지 않을 가능성이 높기 때문에 이 자세한 내용은 무시해도 됩니다.주문을 강제하는 다른 방법(예: 추가)도 마찬가지일 것입니다.limit 999999999아직 구현되지 않은 최적화 경로를 몇 가지 생각할 수 있지만, Rick이 제안한 대로 내부 쿼리에 따라서는 다시 특정되지 않은 주문이 발생할 수 있습니다.

언급URL : https://stackoverflow.com/questions/38421218/query-for-top-n-per-group-code-works-for-mysql-but-different-result-for-mariadb