Sql query - как применять лимит в группе по

Ответ 1

Вот довольно портативный запрос, чтобы делать то, что вы хотите:

SELECT *
FROM table1 a 
WHERE a."ROWID" IN (
    SELECT b."ROWID" 
    FROM table1 b 
    WHERE b."Score" >= 20 
      AND b."ROWID" IS NOT NULL 
      AND a."CID" = b."CID" 
    ORDER BY b."CID", b."SortKey" 
    LIMIT 2
)
ORDER BY a."CID", a."SortKey";

В запросе используется коррелированный подзапрос с сортировкой и лимитом для создания списка ROWID, который должен появиться в конечном результате. Поскольку коррелированный подзапрос выполняется для каждой строки, независимо от того, включен ли он в результат, он может быть не таким эффективным, как приведенная ниже версия оконной функции, но в отличие от этой версии она будет работать на SQLite3, которая не поддерживает окно функции.

Для этого запроса требуется, чтобы ROWID был уникальным (может использоваться как первичный ключ).

Я тестировал выше в PostgreSQL 9.2 и SQLite3 3.7.11; он отлично работает в обоих. Он не будет работать на MySQL 5.5 или в последней версии 5.6, потому что MySQL не поддерживает LIMIT в подзапросе, используемом с IN.

Демонстрации SQLFiddle:

• PostgreSQL (отлично работает): http://sqlfiddle.com/#!12/22829/3

• SQLite3 (отлично работает, тот же текст запроса, но требует однозначных вставок из-за очевидного ограничения драйвера JDBC): http://sqlfiddle.com/#!7/9ecd8/1

• MySQL 5.5 (не работает два пути: MySQL не любит a."ROWID" цитирования даже в режиме ANSI, поэтому мне пришлось не указывать, а затем он терпит неудачу с This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery): http://sqlfiddle.com/#!2/e1f31/2

Демонстрационная версия SQLite, показывающая его, отлично работает в командной строке SQLite3: http://pastebin.com/26n4NiUC

Выход (PostgreSQL):

 ROWID | CID | PID | Score | SortKey 
-------+-----+-----+-------+---------
     2 | C1  | P2  |    20 |       2
     3 | C1  | P3  |    30 |       3
     5 | C2  | P5  |    30 |       2
     4 | C2  | P4  |    20 |       3
     7 | C3  | P7  |    20 |       2
(5 rows)

Если вы хотите отфильтровать конкретный CID, просто добавьте AND "CID" = 'C1' или что-то еще в внешнее предложение WHERE.

Вот тесный ответ с более подробными примерами: fooobar.com/questions/336936/...

Поскольку это изначально было помечено как раз SQL (без SQLite)... только для полноты, в PostgreSQL или других БД со стандартной поддержкой оконных функций SQL, я бы, вероятно, сделал это:

SELECT "ROWID", "CID", "PID", "Score", "SortKey"
FROM (
  SELECT *, row_number() OVER (PARTITION BY "CID" ORDER BY "SortKey") AS n
  FROM table1
  WHERE "Score" >= 20
) x
WHERE n < 3
ORDER BY "CID", "SortKey";

который дает тот же результат. SQLFiddle, включая дополнительную строку C1, чтобы продемонстрировать, что фактически работает лимитирующий фильтр: http://sqlfiddle.com/#!12/22829/1

Если вы хотите отфильтровать конкретный CID, просто добавьте AND "CID" = 'C1' или что-то другое во внутреннее предложение WHERE.

BTW, ваши тестовые данные недостаточны, так как он никогда не может иметь более двух строк для любого CID со счетом > 20 в любом случае.

Ответ 2

Это не проблема GROUP BY (вы не агрегируете значения). Это проблема с наибольшей-n-на-группой (я думаю, что там есть тег greatest-n-per-group здесь, в Stackoverflow).

Точные детали решения будут зависеть от таких проблем, как наличие одного и того же ключа сортировки дважды для каждой группы. Вы можете начать с чего-то вроде этого:

SELECT * FROM table T1 WHERE Score > 20 AND
  (SELECT COUNT(*) FROM table T2 
      WHERE T2.CID = T1.CID AND T2.SortKey <= T1.SortKey AND T2.RowID <> T1.RowID
        AND T1.Score > 20) < 2;
  ORDER BY CID, SortKey;

В этом случае учитываются только те строки с оценками выше 20. Затем для каждой строки-кандидата подсчитывается количество других строк в той же таблице, у которых есть оценки > 20, а sortkeys меньше или равно этой группе сортировки. Если это число равно 0 или 1 строке, то эта строка подходит для включения в результаты.

Наконец, ORDER выполняет ваш вид.

Ответ 3

В MySQL:

SELECT  l.*
FROM    (
        SELECT  cid,
                COALESCE(
                (
                SELECT  id
                FROM    mytable li
                WHERE   li.cid = dlo.cid
                        AND li.score >= 20
                ORDER BY
                        li.cid, li.id
                LIMIT 1, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT cid
                FROM    mytable dl
                ) dlo
        ) lo, mytable l
WHERE   l.cid >= lo.cid
        AND l.cid <= lo.cid
        AND l.id <= lo.mid
        AND l.score >= 20

Ответ 4

Разве это не так просто? **

SELECT CID, PID, Score, SortKey
FROM T1
WHERE score >= 20
ORDER BY CID, Sortkey

**

или я что-то не хватает?

Ответ 5

Мы можем использовать предложение LIMIT в SQLite. Например:

select * from tableName where field1='something' limit 10;

это дает 10 первых строк.