Sqlite group_concat заказ

В Sqlite я могу использовать group_concat, чтобы сделать:

1...A
1...B
1...C
2...A
2...B
2...C

1...C,B,A
2...C,B,A

но порядок объединения является случайным - согласно документам.

Мне нужно отсортировать вывод group_concat, чтобы

1...A,B,C
2...A,B,C

Как я могу это сделать?

Источник

4 ответа

Решение

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

Что-то вроде

SELECT ID, GROUP_CONCAT(Val)
FROM (
   SELECT ID, Val
   FROM YourTable
   ORDER BY ID, Val
   )
GROUP BY ID;
Источник

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

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

На самом деле это не означает случайный, это просто означает, что разработчики оставляют за собой право использовать любой порядок, который они пожелают, даже разные для разных запросов или в разных версиях 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;
Источник

АЛЬТЕРНАТИВНОЕ решение: используйте рекурсию вместо GROUP_CONCAT. Для демонстрации вот таблица WORKGROUPS:

      create table Workgroups as 
select 1 as workgroup,'Daniel' as name union all
select 2,'Marc' union all
select 3,'Chris' union all
select 3,'Evelyn' union all
select 2,'Valentine' union all
select 1,'John' union all
select 3,'Luca' union all
select 2,'Thomas' union all
select 3,'Harry' union all
select 4,'Tom' union all
select 4,'Marilyn' union all
select 1,'Ben' union all
select 2,'Ann';

Теперь я готовлю ранжированное представление в предложении With, используя два ранжирования:rk для общего порядка вывода, чтобы обеспечить рекурсивное связывание. rk2, чтобы позже идентифицировать ПОСЛЕДНЕЕ вхождение каждой группы как ранг 1. Хитрость заключается в предложении Iif, которое очищает агрегированную цепочку всякий раз, когда происходит разрыв группы.

      with Ranked as (
select workgroup,
  rank() over (order by workgroup,name) as rk,
  rank() over (partition by workgroup order by name desc) as rk2,
  name 
 from Workgroups
),Recursed as ( --follows the initial part
 select Ranked.workgroup,Ranked.rk,Ranked.rk2, Ranked.name as names from Ranked 
  where Ranked.rk=1 
   union all --follows the recursion part
 select Ranked.workgroup,Ranked.rk,Ranked.rk2, 
   iif(Recursed.workgroup=Ranked.workgroup,names || ', ','') || Ranked.name as names 
 from Recursed 
  join Ranked on Recursed.rk+1=Ranked.rk 
)
select workgroup,names from Recursed where rk2=1;

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

Преимущество по сравнению с GROUP_CONCAT может заключаться в том, что внутри предложения Iif вы можете делать все, что пожелаете: использовать сложные разделители, решать, что делать с пустыми (Null) записями, обрабатывать несколько полей, даже создавать HTML-код и т. д.

Комментарии приветствуются!

Источник

Наткнувшись на основную проблему сортировки, я попробовал это:(... на 10.4.18-MariaDB)

      select GROUP_CONCAT(ex.ID) as ID_list
FROM (
SELECT usr.ID
FROM (
SELECT u1.ID as ID
FROM table_users u1
) usr
GROUP BY ID
) ex

... и обнаружил, что сериализованный ID_list заказан! Но у меня нет объяснения этому теперь "правильному" (?) Результату.

Источник
Другие вопросы по тегам