Заказ Sqlite group_concat

Ответ 1

Не можете ли вы использовать подзаголовок с предложением order by, а затем группировать concat значения?

Что-то вроде

SELECT ID, GROUP_CONCAT(Val)
FROM (
   SELECT ID, Val
   FROM YourTable
   ORDER BY ID, Val
   )
GROUP BY ID;

Ответ 2

Чтобы быть более точным, в соответствии с документами:

Порядок составных элементов произвольный.

На самом деле это не означает случайность, это просто означает, что разработчики оставляют за собой право использовать любой порядок, который они пожелают, даже разные для разных запросов или в разных версиях SQLite.

В текущей версии этот порядок может быть тем, что подразумевает ответ Адриана Стандера, так как его код, похоже, работает. Таким образом, вы можете просто охранять себя с помощью некоторых модульных тестов и называть это днем. Но без тщательного изучения исходного кода SQLite вы никогда не сможете быть на 100% уверены, что это всегда будет работать.

Если вы хотите создать SQLite из исходного кода, вы также можете попробовать написать собственную пользовательскую агрегатную функцию, но есть более простой способ.

К счастью, начиная с версии 3.25.0, у вас есть оконные функции, обеспечивающие гарантированную работу, хотя и несколько уродливое решение вашей проблемы.

Как вы можете видеть в документации, функции Windows имеют свои собственные предложения ORDER BY:

В приведенном выше примере оконная рама состоит из всех строк между предыдущей строкой ("1 PRECEDING") и следующей строкой ("1 FOLLOWING") включительно, где строки сортируются в соответствии с предложением ORDER BY в window-defn (в данном случае "ЗАКАЗАТЬ ПО").

Обратите внимание, что это само по себе не означает, что все агрегатные функции соблюдать порядок внутри оконной рамы, но если вы посмотрите на юнит - тестов, вы можете увидеть, что это на самом деле так:

do_execsql_test 4.10.1 {
  SELECT a, 
    count() OVER (ORDER BY a DESC),
    group_concat(a, '.') OVER (ORDER BY a DESC) 
  FROM t2 ORDER BY a DESC
} {
  6 1 6
  5 2 6.5
  4 3 6.5.4
  3 4 6.5.4.3
  2 5 6.5.4.3.2
  1 6 6.5.4.3.2.1
  0 7 6.5.4.3.2.1.0
}

Итак, подведя итог, вы можете написать

SELECT ID, GROUP_CONCAT(Val) OVER (PARTITION BY ID ORDER BY Val) FROM YourTable;

в результате чего:

1|A
1|A,B
1|A,B,C
2|A
2|A,B
2|A,B,C

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

SELECT DISTINCT ID, GROUP_CONCAT(Val)
OVER (PARTITION BY ID ORDER BY Val ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM YourTable;

или вот так:

SELECT * FROM (
    SELECT ID, GROUP_CONCAT(Val)
    OVER (PARTITION BY ID ORDER BY Val ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    FROM YourTable
)
GROUP BY ID;