不支持 pivot 的数据库转置方案
【问题】
I am currently creating a report (jrxml) using Jaspersoft Studio and I am using the amazon redshift as datasource. I have a sql script that will compute base on a series of data and an example is below:
select date_month_yr, sum(total_count), sum(total_nerwork), (sum(total_count)/lag(sum(total_count)) - 1)*100 as per_total_count_change from ( select date_month_yr, total_count, total_count from tb where …. ….. union all select date_month_yr, total_count, total_count from tb where ….)
The result would be:
date\_month\_yr|sum(total\_count)|sum(total\_nerwork)| per\_total\_count_change
2015-05_ _ _ _|1234_ _ _ _ _ _ _ _ |4321_ _ _ _ _ _ _ _ _|
2015-06_ _ _ _|2345_ _ _ _ _ _ _ _ |8642_ _ _ _ _ _ _ _ _|90.03
2015-07_ _ _ _|3456_ _ _ _ _ _ _ _ |9876_ _ _ _ _ _ _ _ _|47.37
......
The above result is not possible to use as data for the crosstab of jasper that should generate similar to:
|2015-05|2015-06|2015-07
sum(total\_count)\_ _ _ |1234_ _ |2345 _ _ |3456
sum(total\_nerwork)\_ _ |4321_ _ |8642_ _ |9876
per\_total\_count\_change|\_ _ _ _ |90.03_ _|47.37
What I initially think was to use the unnest:
select date\_month\_yr, unnest(array\['sum(total\_count)', 'sum(total\_nerwork)', 'per\_total\_count\_change'\]) AS "parameters", unnest(array\[sum(total\_count), sum(total\_nerwork), (sum(total\_count)/lag(sum(total_count)) - 1)*100\]) AS "Values" from (.......)
However the unnest is not supported by Amazon Redshift. I would like to ask does anyone encountered this problem and have a solution? Or any idea?
Any help would be highly appreciated. Thanks!
【回答】
这是简单的转置，如果数据库是 oracle，则可以用 pivot 函数实现，但 amazon redshift 看起来不支持 pivot 函数。这种情况下可用 SPL 进行报表数据准备，下面代码适合任意数据库表的转换：
A |
|
1 |
$select * from tb |
2 |
=create(subtotal,${A1.(date_month_yr).concat@c()}) |
3 |
>A1.fno().to(2,).run(A2.record(A1.fname(~)|A1.field(~))) |
A1：执行 SQL 取数
A2：根据日期创建结果空序表
A3：循环将 A1 中的数据写入序表中
结果如下：
Jasper 可以通过 JDBC 连接集算器，调用脚本方法和调用存储过程一样，详情参考【JasperReport 调用 SPL 脚本】。
更多转置方面的问题，可参考【集算器实现 SQL 转置的通用方法】。
