交叉表PostgreSQL - Oracle中支点的替代方案
交叉表PostgreSQL - Oracle中支点的替代方案
提问于 2017-12-22 19:48:15
我正在将Oracle的查询迁移到PostgreSQL交叉表。
create table(cntry numeric,week numeric,year numeric,days text,day text);
insert into x_c values(1,15,2015,'DAY1','MON');
...
insert into x_c values(1,15,2015,'DAY7','SUN');
insert into x_c values(2,15,2015,'DAY1','MON');
...
values(4,15,2015,'DAY7','SUN');我有4周的时间，有28排这样的桌子。我的Oracle查询如下所示：
SELECT * FROM(select * from x_c)
PIVOT (MIN(DAY) FOR (DAYS) IN
('DAY1' AS DAY1 ,'DAY2' DAY2,'DAY3' DAY3,'DAY4' DAY4,'DAY5' DAY5,'DAY6' DAY6,'DAY7' DAY7 ));结果：
cntry|week|year|day1|day2|day3|day4|day4|day6|day7|
---------------------------------------------------
1 | 15 |2015| MON| TUE| WED| THU| FRI| SAT| SUN|
...
4 | 18 |2015| MON| ...现在，我编写了一个Postgres交叉表查询，如下所示：
select *
from crosstab('select cntry,week,year,days,min(day) as day
from x_c
group by cntry,week,year,days'
,'select distinct days from x_c order by 1'
) as (cntry numeric,week numeric,year numeric
,day1 text,day2 text,day3 text,day4 text, day5 text,day6 text,day7 text);我只得到一行作为输出：
1|17|2015|MON|TUE| ... -- only this row is coming我哪里做错了？
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-12-26 07:19:58
您的原始查询中缺少ORDER BY。手册：
在实践中，SQL查询应该始终指定
ORDER BY 1,2，以确保输入行得到正确的排序，也就是说，将具有相同row_name的值组合在一起并在行中正确排序。
更重要的(也是更棘手的)，crosstab()需要精确的一个 row_name列。在这个密切相关的答案中，详细解释如下：
solution you found将在数组中嵌套多个列，然后再取消嵌套。这是不必要的昂贵、容易出错和有限的(只适用于具有相同数据类型的列，或者您需要进行强制转换，可能会丢失正确的排序顺序)。
相反，使用rank()或dense_rank() (在我的示例中为rnk)生成代理项dense_rank()列：
SELECT cntry, week, year, day1, day2, day3, day4, day5, day6, day7
FROM crosstab (
'SELECT dense_rank() OVER (ORDER BY cntry, week, year)::int AS rnk
, cntry, week, year, days, day
FROM x_c
ORDER BY rnk, days'
, $$SELECT unnest('{DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,DAY7}'::text[])$$
) AS ct (rnk int, cntry int, week int, year int
, day1 text, day2 text, day3 text, day4 text, day5 text, day6 text, day7 text)
ORDER BY rnk;我将数据类型integer用于out列cntry、week、year，因为这似乎是(更便宜)合适的类型。你也可以使用数字，就像你有它一样。
以下是交叉表查询的基本知识：
Stack Overflow用户
发布于 2017-12-23 03:28:51
我是从http://www.postgresonline.com/journal/categories/24-tablefunc那里搞出来的
select year_wk_cntry.t[1],year_wk_cntry.t[2],year_wk_cntry.t[3],day1,day2,day3,day4,day5,day6,day7
from crosstab('select ARRAY[country :: numeric,week,year] as t,days,min(day) as day
from x_c group by country,week,year,days order by 1,2
','select distinct days from x_c order by 1')
as year_wk_cntry (t numeric[],day1 text,day2 text,day3 text,
day4 text, day5 text,day6 text,day7 text);谢谢！！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47946648
复制相关文章
相似问题
腾讯云开发者
