SQLite JSON 태그 빈도 정렬 — json_each 가 배열을 원소당 한 행으로 펼친다. 다시 묶을 때 순서는 집계 함수 안의 ORDER BY 로만 보장된다1

-- 태그별 빈도 계산
SELECT json_each.value AS tag, COUNT(*) AS frequency
FROM example, json_each(example.tags)
GROUP BY json_each.value
ORDER BY frequency DESC, tag ASC;

-- 빈도순 정렬된 태그를 JSON 배열로 (집계 안 ORDER BY — SQLite 3.44+)
SELECT json_group_array(tag ORDER BY frequency DESC, tag ASC) AS tags_by_frequency
FROM (
  SELECT json_each.value AS tag, COUNT(*) AS frequency
  FROM example, json_each(example.tags)
  GROUP BY json_each.value
);

Footnotes

  1. Built-in Aggregate Functions - SQLite: “If no ORDER BY clause is specified, the inputs to the aggregate occur in an arbitrary order that might change from one invocation to the next.” · 3.44.0 릴리스 노트: “Aggregate functions can now include an ORDER BY clause after their last parameter.” ↩

#sqlite#json
`#331`