Кто-либо знает, как создать перекрестные запросы в PostgreSQL?
Например, у меня есть следующая таблица:
Section Status Count
A Active 1
A Inactive 2
B Active 4
B Inactive 5
Я хотел бы, чтобы запрос возвратил следующий crosstab:
Section Active Inactive
A 1 2
B 4 5
Действительно ли это возможно?
Вы можете использовать функцию crossstab ()
функции дополнительного модуля tablefunc - которую вы необходимо установить один раз для каждой базы данных. Начиная с PostgreSQL 9.1, вы можете использовать для этого CREATE EXTENSION
:
CREATE EXTENSION tablefunc;
В вашем случае, я думаю, это будет выглядеть примерно так:
CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);
INSERT INTO t VALUES ('A', 'Active', 1);
INSERT INTO t VALUES ('A', 'Inactive', 2);
INSERT INTO t VALUES ('B', 'Active', 4);
INSERT INTO t VALUES ('B', 'Inactive', 5);
SELECT row_name AS Section,
category_1::integer AS Active,
category_2::integer AS Inactive
FROM crosstab('select section::text, status, count::text from t',2)
AS ct (row_name text, category_1 text, category_2 text);
SELECT section,
SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly
SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status value as a separate column explicitly
FROM t
GROUP BY section
Извините, что это не полностью, потому что я не могу проверить это здесь, но это может направить вас в правильном направлении. Я перевожу с того, что я использую, который делает похожий запрос:
select mt.section, mt1.count as Active, mt2.count as Inactive
from mytable mt
left join (select section, count from mytable where status='Active')mt1
on mt.section = mt1.section
left join (select section, count from mytable where status='Inactive')mt2
on mt.section = mt2.section
group by mt.section,
mt1.count,
mt2.count
order by mt.section asc;
Код, с которым я работаю:
select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent
from mktTrades m
left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1
on m.typeID = m1.typeID
left join (select typeID,MIN(price) as lowAsk from mktTrades where bid=0 group by typeID)m2
on m1.typeID = m2.typeID
group by m.typeID,
m1.highBid,
m2.lowAsk
order by diffPercent desc;
который вернет идентификатор типа, самую высокую цену предложения и самую низкую цену запроса, а также разницу между ними (положительная разница означает, что что-то может быть куплено за меньшую цену, чем может быть продано).