На самом деле это не кросс-таблица (или "поворот" в других СУБД), а обратная операция, контр-кросс-таблица, если хотите. Одним из элегантных приемов является выражение VALUES в соединении LATERAL.
Базовый запрос может выглядеть следующим образом:
- Затем я должен перенести результат и упорядочить по значению
SELECT c.col, c.ct
FROM (
SELECT count(col1 OR NULL) AS col1
, count(col2 OR NULL) AS col2
-- etc.
FROM tbl
) t
, LATERAL (
VALUES ('col1', col1)
, ('col2', col2)
-- etc.
) c(col, ct)
ORDER BY 2
Это была простая часть. Другой ваш запрос сложнее:
- Мне приходится вручную вводить имена столбцов
Эта функция берет имя вашей таблицы и извлекает метаданные из системного каталога pg_attribute. Это динамическая реализация вышеуказанного запроса, защищенная от SQL-инъекций:
CREATE OR REPLACE FUNCTION f_true_ct(_tbl regclass)
RETURNS TABLE (col text, ct bigint) AS
$func$
BEGIN
RETURN QUERY EXECUTE (
SELECT format('
SELECT c.col, c.ct
FROM (SELECT %s FROM tbl) t
, LATERAL (VALUES %s) c(col, ct)
ORDER BY 2 DESC'
, string_agg (format('count(%1$I OR NULL) AS %1$I', attname), ', ')
, string_agg (format('(%1$L, %1$I)', attname), ', ')
)
FROM pg_attribute
WHERE attrelid = _tbl -- valid, visible, legal table name
AND attnum >= 1 -- exclude tableoid & friends
AND NOT attisdropped -- exclude dropped columns
AND atttypid = 'bool'::regtype -- only character types
);
END
$func$ LANGUAGE plpgsql;
Вызов:
SELECT * FROM f_true_ct('tbl'); -- table name optionally schema-qualified
Результат:
col | ct
------+---
col1 | 3
col3 | 2
col2 | 1
Работает для любой таблицы для ранжирования всех boolean столбцов по их количеству true значений.
Чтобы понять параметр функции, прочтите это:
Связанные ответы с дополнительным объяснением:
13.04.2016
LATERAL and VALUES:) Мне всегда интересно, почему люди хотят имитироватьSELECT *поведение 13.04.2016