SQL count, если столбцы

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

я видел

SELECT sum(CASE WHEN question1 = 0 THEN 1 ELSE 0 END) AS ZERO, sum(CASE WHEN question1 = 1 THEN 1 ELSE 0 END) AS ONE, sum(CASE WHEN question1 = 2 THEN 1 ELSE 0 END) AS TWO, category FROM reviews GROUP BY category 

где question1 может иметь значение либо 0, 1, либо 2.

Я также видел версию этого использования count(CASE WHEN question1 = 0 THEN 1)

Однако это становится более громоздким, поскольку число возможных значений для вопроса 1 возрастает. Есть ли удобный способ написать этот запрос, возможно, оптимизируя производительность?

PS. Моя база данных PostgreSQL

В Postgres 9.4 есть новый, более чистый агрегат FILTER :

 SELECT category , count(*) FILTER (WHERE question1 = 0) AS zero , count(*) FILTER (WHERE question1 = 1) AS one , count(*) FILTER (WHERE question1 = 2) AS two FROM reviews GROUP BY 1; 

Подробности для нового предложения FILTER :

  • Как я могу упростить этот запрос статистики игры?

Если вы хотите, чтобы это было коротко :

 SELECT category , count(question1 = 0 OR NULL) AS zero , count(question1 = 1 OR NULL) AS one , count(question1 = 2 OR NULL) AS two FROM reviews GROUP BY 1; 

Обзор возможных вариантов:

  • Для абсолютной производительности SUM быстрее или COUNT?

Правильный запрос кросс-таблицы

crosstab() дает лучшую производительность и короче для более длинных списков опций:

 SELECT * FROM crosstab( 'SELECT category, question1, count(*)::int AS ct FROM reviews GROUP BY 1, 2 ORDER BY 1, 2' , 'VALUES (0), (1), (2)' ) AS ct (category text, zero int, one int, two int); 

Детальное объяснение:

  • Запрос кросс-таблицы PostgreSQL

«Лучший» способ (для меня) – написать запрос типа:

 SELECT category, question1, count(*) FROM reviews GROUP BY category, question1 - SELECT category, question1, count(*) FROM reviews GROUP BY category, question1 

Затем я использую эти данные для рисования таблицы в логике приложения.

Другой вариант – использовать один столбец JSON для всех результатов группировки. Это приведет к чему-то вроде:

 category1 | {"zero": 1, "one": 3, "two": 5} category2 | {"one": 7, "two": 4} 

и так далее.

Запрос для этой опции вы можете построить из предыдущего с помощью json_build_object и json_agg . Лучшее для этого варианта – вам не нужно заранее знать количество возможных значений вопроса1.