Отключить и PostgreSQL
Есть ли в PostgreSQL эквивалентная функция, не являющаяся сводной?
10 ответов
Создайте таблицу примеров:
CREATE TEMP TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');
Вы можете "отменить" или "открепить" с помощью UNION ALL:
SELECT id,
'a' AS colname,
a AS thing
FROM foo
UNION ALL
SELECT id,
'b' AS colname,
b AS thing
FROM foo
UNION ALL
SELECT id,
'c' AS colname,
c AS thing
FROM foo
ORDER BY id;
Это запускает 3 разных подзапроса на fooпо одному для каждого столбца, который мы хотим отменить, и возвращает в одной таблице каждую запись из каждого из подзапросов.
Но это отсканирует таблицу N раз, где N - количество столбцов, которые вы хотите отключить. Это неэффективно и является большой проблемой, когда, например, вы работаете с очень большой таблицей, сканирование которой занимает много времени.
Вместо этого используйте:
SELECT id,
unnest(array['a', 'b', 'c']) AS colname,
unnest(array[a, b, c]) AS thing
FROM foo
ORDER BY id;
Это легче написать, и он будет сканировать таблицу только один раз.
array[a, b, c] возвращает объект массива со значениями a, b и c в качестве его элементов.unnest(array[a, b, c]) разбивает результаты в одну строку для каждого из элементов массива.
Надеюсь, это поможет!
Вы могли бы использовать VALUES()а также JOIN LATERAL отключить столбцы.
Пример данных:
CREATE TABLE test(id int, a INT, b INT, c INT);
INSERT INTO test(id,b,c) VALUES (1,11,12,13),(2,21,22,23),(3,31,32,33);
Запрос:
SELECT t.id, s.col_name, s.col_value
FROM test t
JOIN LATERAL(VALUES('a',t.a),('b',t.b),('c',t.c)) s(col_name, col_value) ON TRUE;
Используя этот подход, можно отключить несколько групп столбцов одновременно.
Отличная статья Томаса Келлерера найдена здесь
Отменить пивот с помощью Postgres
Иногда необходимо нормализовать ненормализованные таблицы - в отличие от операции «перекрестная таблица» или «сводная таблица». Postgres не поддерживает оператор UNPIVOT, такой как Oracle или SQL Server, но имитировать его очень просто.
Возьмем следующую таблицу, в которой хранятся агрегированные значения за квартал:
create table customer_turnover
(
customer_id integer,
q1 integer,
q2 integer,
q3 integer,
q4 integer
);
И следующие образцы данных:
customer_id | q1 | q2 | q3 | q4
------------+-----+-----+-----+----
1 | 100 | 210 | 203 | 304
2 | 150 | 118 | 422 | 257
3 | 220 | 311 | 271 | 269
Но мы хотим, чтобы кварталы были строками (как и должно быть в нормализованной модели данных).
В Oracle или SQL Server этого можно добиться с помощью оператора UNPIVOT, но это недоступно в Postgres. Однако способность Postgres использовать предложение VALUES как таблицу делает это на самом деле довольно просто:
select c.customer_id, t.*
from customer_turnover c
cross join lateral (
values
(c.q1, 'Q1'),
(c.q2, 'Q2'),
(c.q3, 'Q3'),
(c.q4, 'Q4')
) as t(turnover, quarter)
order by customer_id, quarter;
вернет следующий результат:
customer_id | turnover | quarter
------------+----------+--------
1 | 100 | Q1
1 | 210 | Q2
1 | 203 | Q3
1 | 304 | Q4
2 | 150 | Q1
2 | 118 | Q2
2 | 422 | Q3
2 | 257 | Q4
3 | 220 | Q1
3 | 311 | Q2
3 | 271 | Q3
3 | 269 | Q4
Эквивалентный запрос со стандартным оператором UNPIVOT будет:
select customer_id, turnover, quarter
from customer_turnover c
UNPIVOT (turnover for quarter in (q1 as 'Q1',
q2 as 'Q2',
q3 as 'Q3',
q4 as 'Q4'))
order by customer_id, quarter;
К вашему сведению для тех из нас, кто ищет, как отключить в RedShift.
Полное решение формы, данное Stew, кажется, единственный способ сделать это.
https://forums.aws.amazon.com/thread.jspa?threadID=126369
Для тех, кто не может видеть это здесь, текст вставлен ниже...
У нас нет встроенных функций, которые будут выполнять поворот или отключение. Однако вы всегда можете написать SQL для этого.
create table sales (regionid integer, q1 integer, q2 integer, q3 integer, q4 integer); insert into sales values (1,10,12,14,16), (2,20,22,24,26); select * from sales order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows)сводный запрос
create table sales_pivoted (regionid, quarter, sales) as select regionid, 'Q1', q1 from sales UNION ALL select regionid, 'Q2', q2 from sales UNION ALL select regionid, 'Q3', q3 from sales UNION ALL select regionid, 'Q4', q4 from sales ; select * from sales_pivoted order by regionid, quarter; regionid | quarter | sales ----------+---------+------- 1 | Q1 | 10 1 | Q2 | 12 1 | Q3 | 14 1 | Q4 | 16 2 | Q1 | 20 2 | Q2 | 22 2 | Q3 | 24 2 | Q4 | 26 (8 rows)отключить запрос
select regionid, sum(Q1) as Q1, sum(Q2) as Q2, sum(Q3) as Q3, sum(Q4) as Q4 from (select regionid, case quarter when 'Q1' then sales else 0 end as Q1, case quarter when 'Q2' then sales else 0 end as Q2, case quarter when 'Q3' then sales else 0 end as Q3, case quarter when 'Q4' then sales else 0 end as Q4 from sales_pivoted) group by regionid order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows)Надеюсь, это поможет, Нил
Просто используйте JSON:
with data (id, name) as (
values (1, 'a'), (2, 'b')
)
select t.*
from data, lateral jsonb_each_text(to_jsonb(data)) with ordinality as t
order by data.id, t.ordinality;
Это дает
|key |value|ordinality|
|----|-----|----------|
|id |1 |1 |
|name|a |2 |
|id |2 |1 |
|name|b |2 |
Потянув слегка измененный контент по ссылке в комментарии из @a_horse_with_no_name в ответ, потому что это работает:
Установка Hstore Если у вас не установлен hstore и вы используете PostgreSQL 9.1+, вы можете использовать удобный
CREATE EXTENSION hstore;
Для более низких версий найдите файл hstore.sql в share/contrib и запустите его в своей базе данных.
Предполагая, что ваша исходная таблица (например, широкие данные) имеет один столбец id, названный id_fieldи любое количество столбцов 'value' одного и того же типа, приведенное ниже, создаст неповернутое представление этой таблицы.
CREATE VIEW vw_unpivot
AS
SELECT id_field, (h).key AS column_name, (h).value AS column_value
FROM (SELECT id_field, each(hstore(foo) - 'id_field'::text) AS h
FROM zcta5 as foo
) AS unpiv ;
Это работает с любым количеством столбцов "значение". Все результирующие значения будут текстовыми, если вы не приведете, например, (h).value::numeric,
Я написал ужасную функцию разворота для PostgreSQL. Это довольно медленно, но, по крайней мере, возвращает результаты, которые вы ожидаете от операции отмены.
https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/
Надеюсь, вы найдете это полезным.
В зависимости от того, что вы хотите сделать... нечто подобное может оказаться полезным.
with wide_table as (
select 1 a, 2 b, 3 c
union all
select 4 a, 5 b, 6 c
) выберите unnest(array[a,b,c]) из wide_table
Вы можете использовать обработку массива FROM UNNEST() для UnPivot набора данных в тандеме с коррелированным подзапросом (работает с PG 9.4).
FROM UNNEST() более мощный и гибкий, чем типичный метод использования FROM (VALUES ....) для отмены вращения наборов данных. Это b / c FROM UNNEST() является вариативным (с n-арностью) . Использование коррелированного подзапроса устраняет необходимость в боковом предложении ORDINAL, и Postgres сохраняет результирующие параллельные наборы столбцов в правильной порядковой последовательности.
Это, кстати, БЫСТРЫЙ - на практике порождение 8 миллионов строк за <15 секунд в 24-ядерной системе.
WITH _students AS ( /** CTE **/
SELECT * FROM
( SELECT 'jane'::TEXT ,'doe'::TEXT , 1::INT
UNION
SELECT 'john'::TEXT ,'doe'::TEXT , 2::INT
UNION
SELECT 'jerry'::TEXT ,'roe'::TEXT , 3::INT
UNION
SELECT 'jodi'::TEXT ,'roe'::TEXT , 4::INT
) s ( fn, ln, id )
) /** end WITH **/
SELECT s.id
, ax.fanm -- field labels, now expanded to two rows
, ax.anm -- field data, now expanded to two rows
, ax.someval -- manually incl. data
, ax.rankednum -- manually assigned ranks
,ax.genser -- auto-generate ranks
FROM _students s
,UNNEST /** MULTI-UNNEST() BLOCK **/
(
( SELECT ARRAY[ fn, ln ]::text[] AS anm -- expanded into two rows by outer UNNEST()
/** CORRELATED SUBQUERY **/
FROM _students s2 WHERE s2.id = s.id -- outer relation
)
,( /** ordinal relationship preserved in variadic UNNEST() **/
SELECT ARRAY[ 'first name', 'last name' ]::text[] -- exp. into 2 rows
AS fanm
)
,( SELECT ARRAY[ 'z','x','y'] -- only 3 rows gen'd, but ordinal rela. kept
AS someval
)
,( SELECT ARRAY[ 1,2,3,4,5 ] -- 5 rows gen'd, ordinal rela. kept.
AS rankednum
)
,( SELECT ARRAY( /** you may go wild ... **/
SELECT generate_series(1, 15, 3 )
AS genser
)
)
) ax ( anm, fanm, someval, rankednum , genser )
;
НАБОР РЕЗУЛЬТАТОВ:
+--------+----------------+-----------+----------+---------+-------
| id | fanm | anm | someval |rankednum| [ etc. ]
+--------+----------------+-----------+----------+---------+-------
| 2 | first name | john | z | 1 | .
| 2 | last name | doe | y | 2 | .
| 2 | [null] | [null] | x | 3 | .
| 2 | [null] | [null] | [null] | 4 | .
| 2 | [null] | [null] | [null] | 5 | .
| 1 | first name | jane | z | 1 | .
| 1 | last name | doe | y | 2 | .
| 1 | | | x | 3 | .
| 1 | | | | 4 | .
| 1 | | | | 5 | .
| 4 | first name | jodi | z | 1 | .
| 4 | last name | roe | y | 2 | .
| 4 | | | x | 3 | .
| 4 | | | | 4 | .
| 4 | | | | 5 | .
| 3 | first name | jerry | z | 1 | .
| 3 | last name | roe | y | 2 | .
| 3 | | | x | 3 | .
| 3 | | | | 4 | .
| 3 | | | | 5 | .
+--------+----------------+-----------+----------+---------+ ----
Вот способ, который сочетает в себе подходы hstore и CROSS JOIN из других ответов.
Это модифицированная версия моего ответа на аналогичный вопрос , который сам основан на методе https://blog.sql-workbench.eu/post/dynamic-unpivot/ и другом ответе на этот вопрос .
-- Example wide data with a column for each year...
WITH example_wide_data("id", "2001", "2002", "2003", "2004") AS (
VALUES
(1, 4, 5, 6, 7),
(2, 8, 9, 10, 11)
)
-- that is tided to have "year" and "value" columns
SELECT
id,
r.key AS year,
r.value AS value
FROM
example_wide_data w
CROSS JOIN
each(hstore(w.*)) AS r(key, value)
WHERE
-- This chooses columns that look like years
-- In other cases you might need a different condition
r.key ~ '^[0-9]{4}$';
У него есть несколько преимуществ перед другими решениями:
- Мы надеемся, что использование hstore, а не jsonb, сводит к минимуму проблемы с преобразованием типов (хотя hstore преобразует все в текст).
- Столбцы не обязательно должны быть жестко запрограммированы или известны заранее. Здесь столбцы выбираются регулярным выражением по имени, но вы можете использовать любую логику SQL на основе имени или даже значения.
- Это не требует PL/pgSQL - это все SQL